Thread: BUG #6701: IS NOT NULL doesn't work on complex composites
The following bug has been logged on the website: Bug reference: 6701 Logged by: Rikard Pavelic Email address: rikard.pavelic@zg.htnet.hr PostgreSQL version: 9.1.3 Operating system: Windows 7 Description:=20=20=20=20=20=20=20=20 create type t AS (i int); create type complex as (t t, i int); create table bad(i int, c complex); insert into bad values(1, null); insert into bad values(1, ROW(null, 2)); insert into bad values(1, ROW(ROW(1), 2)); select * from bad; select * from bad where c is null; --This doesn't work as expected select * from bad where c is not null; --Fortunately a workaround select * from bad where NOT c is null;
Hello it is not a bug - see http://archives.postgresql.org/pgsql-hackers/2009-07/msg01525.php Regards Pavel Stehule 2012/6/20 <rikard.pavelic@zg.htnet.hr>: > The following bug has been logged on the website: > > Bug reference: =C2=A0 =C2=A0 =C2=A06701 > Logged by: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Rikard Pavelic > Email address: =C2=A0 =C2=A0 =C2=A0rikard.pavelic@zg.htnet.hr > PostgreSQL version: 9.1.3 > Operating system: =C2=A0 Windows 7 > Description: > > create type t AS (i int); > create type complex as (t t, i int); > create table bad(i int, c complex); > > insert into bad values(1, null); > insert into bad values(1, ROW(null, 2)); > insert into bad values(1, ROW(ROW(1), 2)); > > select * from bad; > select * from bad where c is null; > > --This doesn't work as expected > select * from bad where c is not null; > > --Fortunately a workaround > select * from bad where NOT c is null; > > > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs
<rikard.pavelic@zg.htnet.hr> wrote: > --This doesn't work as expected > select * from bad where c is not null; Are you seeing any behavior which does not match the documentation and the standard? http://www.postgresql.org/docs/current/interactive/functions-comparison.html says: | Note: If the expression is row-valued, then IS NULL is true when | the row expression itself is null or when all the row's fields are | null, while IS NOT NULL is true when the row expression itself is | non-null and all the row's fields are non-null. Because of this | behavior, IS NULL and IS NOT NULL do not always return inverse | results for row-valued expressions, i.e., a row-valued expression | that contains both NULL and non-null values will return false for | both tests. This definition conforms to the SQL standard, and is a | change from the inconsistent behavior exhibited by PostgreSQL | versions prior to 8.2. When using a NULL test with a row-value, it can help to imagine the word "ENTIRELY" right after the word IS. The above query will only return a row from "bad" if the row value "c" IS [ENTIRELY] NOT NULL -- in other words, any NULL in the row causes it to be excluded. Moving the NOT in front of the IS results in a test for rows from "bad" where it is NOT true that row value "c" IS [ENTIRELY] NULL. That works for me, anyway. Some find the rules around NULL illogical and argue for just memorizing them as a set of facts rather than trying to make sense of them. -Kevin
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); > insert into bad values(1, null); > insert into bad values(1, ROW(null, 2)); > insert into bad values(1, ROW(ROW(1), 2)); > select * from bad; > select * from bad where c is null; > --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
On 20.6.2012. 20:55, Pavel Stehule wrote: > Hello > > it is not a bug - see > http://archives.postgresql.org/pgsql-hackers/2009-07/msg01525.php > > Regards > > Pavel Stehule > I found that in documentation after reporting bug. I'm sorry for not searching some more, but didn't know where to look exactly. Well, at least is counter intuitive if not a bug ;( Thanks.
2012/6/20 Rikard Pavelic <rikard.pavelic@zg.htnet.hr>: > On 20.6.2012. 20:55, Pavel Stehule wrote: >> Hello >> >> it is not a bug - see >> http://archives.postgresql.org/pgsql-hackers/2009-07/msg01525.php >> >> Regards >> >> Pavel Stehule >> > > I found that in documentation after reporting bug. > I'm sorry for not searching some more, but didn't know where to look exactly. > > Well, at least is counter intuitive if not a bug ;( I had a problem with this issue too, but it is well designed - I believe now. Regards Pavel > > Thanks.
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, butit 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
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 > > Regards, > Rikard > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs
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
Rikard Pavelic <rikard.pavelic@zg.htnet.hr> wrote:
> The only inconsistent thing is check constraint, which behaves as
> NOT column IS NULL instead of column IS NOT NULL as docs says.
So currently a NOT NULL constraint on a column with a composite type
is equivalent to:
CHECK (NOT c IS NULL)
and the question is whether that is correct, or whether it should be
equivalent to:
CHECK (c IS NOT NULL)
> I even prefer that behavior.
I think I prefer current behavior, too; but I'm inclined to be
guided by the SQL spec if it is unambiguous about which is correct.
(I haven't checked yet -- does anyone already know without having to
dig through the spec?) Either way, it probably deserves some brief
mention in the docs. FWIW, a strict reading of the current
PostgreSQL docs ("The column is not allowed to contain null
values.") matches the current behavior, since the other way would
need to be stated as something like "The column can only contain
non-null values."
-Kevin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Rikard Pavelic <rikard.pavelic@zg.htnet.hr> wrote:
>> The only inconsistent thing is check constraint, which behaves as
>> NOT column IS NULL instead of column IS NOT NULL as docs says.
> So currently a NOT NULL constraint on a column with a composite type
> is equivalent to:
> CHECK (NOT c IS NULL)
I don't believe this statement is accurate. What's really happening
is that a column-not-null constraint is a datatype-independent check
for whether the datum per se is null or not. In the case of a composite
column, it's possible that the datum is a heaptuple all of whose fields
are null. IS NULL will say "true" for such a value, per SQL spec, but
the attnotnull code will not reject it. So actually the attnotnull
check doesn't exactly correspond to either IS NOT NULL or NOT IS NULL,
when you're talking about composite types.
There are two ways we could make that more consistent:
1. Force all-null heaptuple datums to become real nulls. This is not
terribly attractive IMV; for one thing it loses any opportunity to carry
the rowtype's OID, which is something I think we need at least in some
contexts. We could narrow the scope for such problems by delaying the
application of the conversion until storage time, but then it would have
to be checked in places that now are datatype-independent, which is
unpleasant from both modularity and performance standpoints. Another
issue is that, while the spec seems not to distinguish between NULL and
ROW(NULL,NULL,...), it is far from clear that we should seek to suppress
the difference. They are different in I/O representation for instance.
2. Change the attnotnull checking code to be datatype-dependent
so that it could peer into a composite value to check the field values.
This is unpleasant for the same modularity and performance reasons
mentioned above.
We've been over this ground before, and not come to any consensus about
changing the behavior. Somebody who really cares about having the
SQL-spec definition can write a CHECK constraint as suggested above,
and then he'll get the composite-type-aware behavior, so it's not like
there's no way to get that.
BTW, the same inconsistency exists for function-argument strictness
checks: those will consider a heaptuple-of-all-nulls to be something
you can call a strict function with. I think changing this would be
a pretty bad idea, not only on modularity and performance grounds but
because it'd likely break existing applications that expect the current
behavior.
Here's another interesting example, using int8_tbl which is just
a two-column composite type:
regression=# select null::int8_tbl;
int8_tbl
----------
(1 row)
regression=# select row(null,null)::int8_tbl;
row
-----
(,)
(1 row)
regression=# select null::int8_tbl is distinct from row(null,null)::int8_tbl;
?column?
----------
t
(1 row)
It's not clear to me whether the SQL standard rules on what should
happen in this case, or whether we should listen to it if it does say
that these values are not distinct. They certainly *look* distinct.
(Oh, and dare I mention arrays of nulls?)
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: >> So currently a NOT NULL constraint on a column with a composite >> type is equivalent to: >> CHECK (NOT c IS NULL) > > I don't believe this statement is accurate. What's really > happening is that a column-not-null constraint is a > datatype-independent check for whether the datum per se is null or > not. I stand corrected. > Somebody who really cares about having the SQL-spec definition can > write a CHECK constraint as suggested above, and then he'll get > the composite-type-aware behavior, so it's not like there's no way > to get that. > > BTW, the same inconsistency exists for function-argument > strictness checks: those will consider a heaptuple-of-all-nulls to > be something you can call a strict function with. I think > changing this would be a pretty bad idea, not only on modularity > and performance grounds but because it'd likely break existing > applications that expect the current behavior. Maybe a comment or two in the docs covers it? > regression=# select null::int8_tbl is distinct from > row(null,null)::int8_tbl; > ?column? > ---------- > t > (1 row) > > It's not clear to me whether the SQL standard rules on what should > happen in this case, or whether we should listen to it if it does > say that these values are not distinct. They certainly *look* > distinct. I do sympathize with the point of view that a row value about which absolutely no applicable facts are known is a lot like not knowing what row you have, but they do seem distinct when you look at the output. > (Oh, and dare I mention arrays of nulls?) Hey, look! An elephant! -Kevin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> It's not clear to me whether the SQL standard rules on what should
>> happen in this case, or whether we should listen to it if it does
>> say that these values are not distinct. They certainly *look*
>> distinct.
> I do sympathize with the point of view that a row value about which
> absolutely no applicable facts are known is a lot like not knowing
> what row you have, but they do seem distinct when you look at the
> output.
>> (Oh, and dare I mention arrays of nulls?)
> Hey, look! An elephant!
The reason I mentioned arrays is that it seems clear to me that nobody
sane would consider ARRAY[NULL,NULL]::int[] to be equivalent to
NULL::int[]. The former has got well-defined array dimensions, for one
thing, while the latter does not. So I think the standard is not being
very bright by conflating a null container with a container full of
nulls in the ROW case. I'm willing to hold my nose and do what they say
for the specific case of "foo IS NULL" and "foo IS NOT NULL"
expressions, but I'm not eager to let that confusion propagate anyplace
else.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> wrote: > The reason I mentioned arrays is that it seems clear to me that > nobody sane would consider ARRAY[NULL,NULL]::int[] to be > equivalent to NULL::int[]. I will defer on that to anyone who has been in a position where the former has any meaningful semantics in a SQL environment; that is a set of people which does not include me. > So I think the standard is not being very bright by conflating a > null container with a container full of nulls in the ROW case. I'm pretty sure that Codd argued that any attempt to create such a row within a relation should be treated as an error; I tend to agree. I'm far less clear on the semantics of a row value which is not a top-level tuple of a relation. That seems like it's in a gray area between set theory and practical details of procedural programming. There are bound to be a few things in that realm which don't make sense when viewed from one perspective or the other. While I might not have chosen to draw the lines where the standard does, they did make choices that allow reasonable things to be done in a wide variety of cases, which is, IMO, the most important thing. > I'm willing to hold my nose and do what they say for the specific > case of "foo IS NULL" and "foo IS NOT NULL" expressions, but I'm > not eager to let that confusion propagate anyplace else. Not having used arrays in SQL, I don't feel able to offer much opinion on implementation details; just the general opinion that we should try to allow reasonable things to be done in a wide variety of cases. Where it's possible to do that and have behavior which is easy to explain, all the better. The vast majority of problematic SQL code that I've seen is a direct consequence of programmers using procedural code where they could have used declarative code, so I would tend to stress clean and complete set operations over concessions to procedural coding. I'll stop waving my hands around now. -Kevin