question Not possible to use IS NULL / IS NOT NULL operator on structs in Archives of Support Questions (Read Only)

Not possible to use IS NULL / IS NOT NULL operator on structs

Pettax — Fri, 16 Sep 2022 10:05:17 GMT

It does not seem like the IS NULL / IS NOT NULL operator is supported for struct data types. We are using Impala 2.3.0/CDH5.5.1. This seem like a basic and vital operator to have. Especially when using wide tables. Anybody out there that has a patch or workaround or that actually succeeded to use this operator on structs?

I have reported IMPALA-3060 on the topic.

Re: Not possible to use IS NULL / IS NOT NULL operator on structs

alex.behm — Tue, 23 Feb 2016 16:28:11 GMT

Thanks for reporting this issue! I agree that it is an unfortunate limitation.

FWIW, this limitation is explained here in the docs here: http://www.cloudera.com/documentation/enterprise/latest/topics/impala_complex_types.html#complex_types_limits_unique_2

"Currently, Impala built-in functions and user-defined functions cannot accept complex types as parameters or produce them as function return values"

Re: Not possible to use IS NULL / IS NOT NULL operator on structs

Pettax — Wed, 24 Feb 2016 20:25:40 GMT

Thank you for your prompt reply! I will hold my attempts using this operation.

Re: Not possible to use IS NULL / IS NOT NULL operator on structs

nav2014 — Thu, 21 Feb 2019 22:38:25 GMT

Is there a solution to this yet ?

I am running into the same issue. I am using a IS NOT NULL clause in the Where condition and it is being ignored and rows with null values in that column are being retured by the query.

Re: Not possible to use IS NULL / IS NOT NULL operator on structs

Pettax — Fri, 22 Feb 2019 08:27:59 GMT

Hi,

I have not followed the development of Impala lateley.If this i still a limitation you might try the following approach.Design the schema with an additional column with information about which rows holds information for a particular struct column and then use this additional column in the WHERE clause. Something like:

name      complex1      complex2   complex3
complex1  content       NULL       NULL
complex3  NULL          NULL       content

and then:

SELECT complex1.*
FROM myTable
WHERE name = 'complex1'

Br,

Petter