From: | Rikard Pavelic <rikard(dot)pavelic(at)zg(dot)htnet(dot)hr> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #6701: IS NOT NULL doesn't work on complex composites |
Date: | 2012-06-21 00:16:23 |
Message-ID: | 4FE267D7.9080202@zg.htnet.hr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
Regards,
Rikard
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2012-06-21 02:16:27 | Re: BUG #5823: launchd execution |
Previous Message | Pavel Stehule | 2012-06-20 19:52:04 | Re: BUG #6701: IS NOT NULL doesn't work on complex composites |