Re: "IS [NOT] NULL for a complex type"
Hi Furcy,
Thanks for your reply. I could make a test data by using OUTER JOIN query.
Best regards,
Jinchul
2017-11-29 17:23 GMT+09:00 Furcy Pin :
> 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 :
>
>> 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);
>> 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
>>
>
>
Re: "IS [NOT] NULL for a complex type"
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 :
> 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);
> 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
>
"IS [NOT] NULL for a complex type"
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);
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
