Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

impala select with between clause

impala select with between clause

Explorer

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

Re: impala select with between clause

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

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

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