Reply
Explorer
Posts: 21
Registered: ‎10-07-2015

impala select with between clause

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

Cloudera Employee
Posts: 7
Registered: ‎08-26-2014

Re: impala select with between clause

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

New Contributor
Posts: 2
Registered: ‎08-14-2017

Hello    Can you please help me with this query. i want t...

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