Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

impala select with between clause

avatar
Contributor

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 REPLIES 2

avatar
Cloudera Employee

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

avatar
New Contributor

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