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 <jinc...@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 >
