Support Questions
Find answers, ask questions, and share your expertise

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
Highlighted

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

Highlighted

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