Why does count( distinct ) with NULL columns return 0 in Hive SQL?

Viewed 5711

I have struggled with an issue in Hive SQL and just found out what the issue was:

select distinct 'A', NULL;        

returns 'A', NULL

select count(distinct 'A', NULL);  

returns 0

select count(distinct 'A', coalesce(NULL,''));  

returns 1.

I was using the select line inside of a larger query and filtered on the result (=1). With the outcome being 0, I lost a lot of rows.

How come that a row with NULL column does not contribute to the result of a count(distinct) query?

1 Answers
Related