Re: BUG #6701: IS NOT NULL doesn't work on complex composites

2012/6/21 Rikard Pavelic <rikard(dot)pavelic(at)zg(dot)htnet(dot)hr>:
> On 20.6.2012. 21:10, Tom Lane wrote:
>> rikard(dot)pavelic(at)zg(dot)htnet(dot)hr writes:
>>> create type t AS (i int); create type complex as (t t, i int); create table bad(i int, c complex); --This doesn't work as expected select * from bad where c is not null;
>> What do you consider to be "expected"? Have you read the documentation where it points out that IS NULL and IS NOT NULL are not inverses for composite values?
>> http://www.postgresql.org/docs/9.1/static/functions-comparison.html (I'm not that thrilled with this behavior either, but it is per SQL standard AFAICT.) regards, tom lane
>
> I understand the concept behind if one composite property is null then then IS NULL check returns NULL (instead true or false).
> I can even understand IS NULL check returning false.
>
> I can use ::text to get what I expected,
> but Postgres still seems inconsistent in handling NULL checks.
>
> create type complex as (i int, j int);
> create table t (i int, c complex not null);
>
> --error as expected
> insert into t values(1, null);
>
> --unexpected - passed!?
> insert into t values(1, (null,4));
>
> -- this is false - I think it would be better if it's null, but lets move on
> select (null, 4) is not null
>
> --lets try again with check constraint
> alter table t add check(c is not null);
>
> --error as expected from is not null check above
> insert into t values(1, (null,4));
>
> It seems that check constraint behaves differently.
> Docs say: (http://www.postgresql.org/docs/9.1/static/ddl-constraints.html)
> "A not-null constraint is functionally equivalent to creating a check constraint CHECK (column_name IS NOT NULL)"
> And at least there is more notes required ;(
>
> Let's try some more.
>
> create table x (i int, c complex);
>
> insert into x values(1,null);
> insert into x values(2,(1,null));
> insert into x values(3,(1,2));
>
> --first row - ok
> select * from x where c is null;
>
> --last row - ok
> select * from x where c is not null;
>
> --unexpected result again
> select c is null from x;
>
> I must admit I was expecting
> true
> null
> false

but C is not one value - it is composite - and composite in SQL is not
pointer like C or C++, but it is list of values - and composite is
null (list is null) when all fields are null.

Regards

Pavel

>
> Regards,
> Rikard
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs