TransWikia.com

Testing a Hive array for IS NULL says not null

Database Administrators Asked by PhilHibbs on December 28, 2021

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<string> 
) 
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  |
+-----------+------------+------+-----------+

One Answer

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.

Answered by heapoverflow on December 28, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP