Support Questions

Find answers, ask questions, and share your expertise

Hive Null comparison not works properly

avatar
Contributor

Hi,

I have below Hive query and its not giving me "equals" as output. Why?

hive> select case when (NULL=NULL) then "equals" else "not equals" end as value;
OK
not equals
1 ACCEPTED SOLUTION

avatar
Rising Star

@Gnanasekaran G Use the following ( <=>) operator instead of (=) operator

SELECT CASE WHEN (NULL<=>NULL) THEN "equals" ELSE "not equals" end AS value;

Reference:

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-RelationalOper...

View solution in original post

5 REPLIES 5

avatar
Super Collaborator
@Gnanasekaran G

check NULL is NULL instead of NULL = NULL. This worked for me.

avatar
Rising Star

@Gnanasekaran G Use the following ( <=>) operator instead of (=) operator

SELECT CASE WHEN (NULL<=>NULL) THEN "equals" ELSE "not equals" end AS value;

Reference:

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-RelationalOper...

avatar
Rising Star

@Gnanasekaran G if this helped, please vote/accept best answer. we can close the thread

avatar
Contributor

Thank you @Murali Ramasami @Sharmadha Sainath . It works for me now.

avatar
Rising Star

Thanks @Gnanasekaran G please accept best answer. we can close the thread