NULL value not coming as part of query even after condition satisfying

Question

I am having simple query to get all classes which is not 'RS' in hive. My data looks like as follows

Account                 class
3000337430920208808     NULL
3000337394422514891     NULL
3000337317010916590     NULL
3000337153318453626     NULL
3000337129720896321     NULL

I have written query as

select account_number,service_class from cdx_eligibility where   account_number='3000337430920208808' and service_class not in ('RS');

I am not getting output as this account. Ideally NULL <> RS (so its true and should return record). But i am not getting any records. Do we need to handle NULLS in different way? I also tried several options (!=,<>,not in). Please suggest.

http://stackoverflow.com/questions/129077/not-in-clause-and-null-values — Felix Pamittan, Feb 22 '17 at 21:58

score 0 · Answer 1 · answered Feb 22 '17 at 22:06

looks like hive can't handle properly null values for "in" clauses, this workaround should work

select account_number,service_class from cdx_eligibility where   account_number='3000337430920208808' and (isnull(service_class ) or service_class not in ('RS'));

score 0 · Accepted Answer · answered Feb 22 '17 at 22:42

0

This isn't a Hive issue, this is how SQL works. When you compare 'RS' to null, it doesn't return anything, not true or false. You can't do 'foo' = null either. This is one reason why allowing nulls in character fields is a pain. If you have to have null values, then use

where coalesce(service_class,'') not in ('RS').

That way you're comparing an empty string to 'RS', which works as you'd expect it to.

answered Feb 22 '17 at 22:42

Andrew

8,445
3
28
46

Thanks..the database has nulls and it is unavoidable.so this piece of code works for Number datatype also. Right? – sandeep007 Feb 22 '17 at 23:23

NULL value not coming as part of query even after condition satisfying

2 Answers2