Testing a Hive array for IS NULL says not null I have a table containing an array, and I want to check if it is empty or NULL. It appears that I cannot check the NULL directly! Can anyone shed light on why the NULL check isn't working?
create table `test_array_split`
( `campaign` string
, `questions` array&lt;string&gt; 
) 
stored as orc ;

insert into `test_array_split` (`campaign`) values ('1');

select
  `campaign`
, `questions`
, size(`questions`)
, case when `questions` is null then 'null' else 'not null' end `isnull`
from `test_array_split`;
+-----------+------------+------+-----------+
| campaign  | questions  | _c2  |  isnull   |
+-----------+------------+------+-----------+
| 1         | NULL       | -1   | not null  |
+-----------+------------+------+-----------+
I just investigated the same issue: In my case it was due to Hive CBO (Cost based optimizer) erroneously replacing the IS NULL evaluator by a constant operator during query planning.
You can try set hive.cbo.enable=false; to see if it solves the issue.
The bug about structs in https://issues.apache.org/jira/browse/HIVE-21778 looks very similar. Haven't tested it yet, however the patch (preventing Calcite type nullability optimization to apply) look like it should solve this bug too.