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.

where clause not working with rand()

Highlighted

where clause not working with rand()

New Contributor

I have a query as simple as this in Impala:

with query1 as (
select user_id, cast (rand() as float) as random1
 from master_table where dt='2015-12-17' 
)
select * from query1 

Result:
0	A60028682230369691	  0.00047147460281848907
1	A60028682230362251	  0.58958953619003296
2	A60028682230777191	  0.44319009780883789

But when I add a where clause to compare value with the random1 column it doesn't work. For example:

select * from query1 where random1 < 0.2
Result: returns all rows
select * from query1 where random1 < 0
Result: returns 0 row (which is correct)

Tried all kinds of variations, including

- cast as float everywhere

- multiplying the rand() by 1000 and cast as int

- do the comparison and store true/false in the column in query1, and then select with where clause random1=true

 

none of the above can give me a portion of the data set. Am I missing something for this simple query? It's not about the randomness of the rand() but the use of the rand() result in a where clause.

 

Thanks.

Don't have an account?
Coming from Hortonworks? Activate your account here