hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Furcy Pin <pin.fu...@gmail.com>
Subject Re: "IS [NOT] NULL for a complex type"
Date Wed, 29 Nov 2017 08:23:25 GMT
Hello Jinchul,

in SQL, any type (even complex types such as structs) can be nulls.
And this happen as often as the use of (LEFT | RIGHT | FULL) JOINS:
when a record is not matched by the joined table, it will be NULL.

As far as I remember, directly creating NULL with complex types (for
testing purpose) is complicated in Hive
because of type checking: you can cast a NULL into a primary type but it is
not that easy to obtain a complex type.

Finally, about the meaning of NULL: it helped me a lot when I realized that
in SQL, a NULL should be interpreted as a "We don't know"
or "This value is missing and could be anything": this is why TRUE, FALSE,
and NULL implement the three-valued logic, as explained here:

https://en.wikipedia.org/wiki/Null_(SQL)#Comparisons_with_NULL_and_the_three-valued_logic_.283VL.29


Regards,

Furcy




2017-11-29 1:18 GMT+01:00 Jin Chul Kim <jinchul@gmail.com>:

> Hi,
>
> May I know the meaning of IS [NOT] NULL for a complex type such as STRUCT?
> As far as I know, we cannot assign NULL to struct directly.
> So, I expected them:
> 1) NULL returns if any of the elements in struct has NULL
> 2) NULL returns if all of the elements in struct have NULL
>
> By the way, my assumption was wrong in my example below. Could you let me
> know when struct is null?
>
> For example,
> create table t1(a struct<c1:int, c2:string, c3:double>);
> insert into t1 select named_struct('c1', 100, 'c2', 'test', 'c3', 1.234);
> insert into t1 select named_struct('c1', cast(null as int), 'c2', 'test',
> 'c3', 1.234);
> insert into t1 select named_struct('c1', 100, 'c2', cast(null as string),
> 'c3', 1.234);
> insert into t1 select named_struct('c1', 100, 'c2', 'test', 'c3',
> cast(null as double));
> insert into t1 select named_struct('c1', cast(null as int), 'c2',
> cast(null as string), 'c3', cast(null as double));
> select a is null, * from t1;
> false   {"c1":100,"c2":"test","c3":1.234}
> false   {"c1":null,"c2":"test","c3":1.234}
> false   {"c1":100,"c2":null,"c3":1.234}
> false   {"c1":100,"c2":"test","c3":null}
> false   {"c1":null,"c2":null,"c3":null}
>
> Best regards,
> Jinchul
>

Mime
View raw message