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

Hive Null Timestamp comparison not works properly

Highlighted

Hive Null Timestamp comparison not works properly

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
3 REPLIES 3
Highlighted

Re: Hive Null Timestamp comparison not works properly

Contributor

@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...

Highlighted

Re: Hive Null Timestamp comparison not works properly

Contributor

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

Re: Hive Null Timestamp comparison not works properly

Cloudera Employee

(NULL=NULL) is neither true nor false it will evaluate to NULL/UNKNOWN. (reference https://www.simple-talk.com/sql/learn-sql-server/sql-and-the-snare-of-three-valued-logic/)

As pointed by @Murali Ramasami above you should use <=> operator if you would like null=null evaluate to true.