impala select with between clause

[#1]

I ran a select query with predicate on a non primary key column. If I specify the higher value first, and then the lower value, select returns zero rows. Not sure if this is a bug or it is working as expected.

Query: select count(ss_item_sk) from store_sales_1p where ss_sold_date_sk between 2450817 and 2450816
+-------------------+
| count(ss_item_sk) |
+-------------------+
| 0                 |
+-------------------+
Fetched 1 row(s) in 1.58s

Query: select count(ss_item_sk) from store_sales_1p where ss_sold_date_sk between 2450816 and 2450817
+-------------------+
| count(ss_item_sk) |
+-------------------+
| 17212             |
+-------------------+
Fetched 1 row(s) in 1.52s

This happens because of the way predicates are pushdown. between x and y, is convereted to >x and <y irrespective of x & y.

Regards,

Bhaskar

[#2]

Hi,

It is working as expected. Please see the BETWEEN section on: http://www.cloudera.com/content/www/en-us/documentation/enterprise/latest/topics/impala_operators.ht...

Dan

[#3]

Hello 

Can you please help me with this query. i want to get one month data of june but i am getting errors.

where `dm_capacity`.`measured_at` >= 6/31/2017 - interval 1 month