On 21.6.2012. 6:03, Pavel Stehule wrote:
> 2012/6/21 Rikard Pavelic <rikard.pavelic@zg.htnet.hr>:
>> On 20.6.2012. 21:10, Tom Lane wrote:
>>> rikard.pavelic@zg.htnet.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
workas 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
NULLare 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
orfalse).
>> 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
>
Yeah, I said I'm fine with this behavior.
The only inconsistent thing is check constraint, which behaves as
NOT column IS NULL instead of column IS NOT NULL as docs says.
I even prefer that behavior.
Thanks,
Rikard