BUG #15297: Irregular comparison rules for NULLs in tuples
The following bug has been logged on the website:
Bug reference: 15297
Logged by: kena
Email address: knz@thaumogen.net
PostgreSQL version: 10.4
Operating system: FreeBSD + Linux
Description:
The SQL standard mandates ternary logic for values that involve NULL, where
NULL means "unknown".
This works in many cases correctly in pg:
"select NULL in (1,2)" -> returns NULL, correct
"select (1, NULL::int) in ((1, 1), (1, 2))" -> returns NULL, correct
"select 1 < NULL" -> returns NULL, correct
"select (1, NULL::int) > (1, 2)" -> returns NULL, correct
However as soon as a tuple/composite value contains itself composites, the
rule is not obeyed any more recursively:
"select (1, (1, NULL::int)) in ((1, (1, 0)), (1, (1, 2)))" -> returns false
!?
"select (1, (1, NULL::int)) > (1, (1, 2))" -> returns true !?
Is this intended behavior? If so, where is it documented?
If not documented, any suggestion as to how to work around it?
Thanks in advance,
--
Raphael 'kena' Poss
=?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes:
The SQL standard mandates ternary logic for values that involve NULL, where
NULL means "unknown".
Right.
"select (1, (1, NULL::int)) > (1, (1, 2))" -> returns true !?
We insist on non-null record values being totally ordered, because without
that you can't build a working btree opclass for them. So the general
principle for comparing corresponding fields in two records is that nulls
sort after non-nulls and two nulls are treated as equal. The same goes
for elements in other container types such as arrays.
I don't know whether this behavior can be justified by chapter and verse
in the SQL standard, but it doesn't really matter; we will not change it.
regards, tom lane
Op 26-07-18 om 10:03 schreef Tom Lane:
"select (1, (1, NULL::int)) > (1, (1, 2))" -> returns true !?
We insist on non-null record values being totally ordered, because without
that you can't build a working btree opclass for them. So the general
principle for comparing corresponding fields in two records is that nulls
sort after non-nulls and two nulls are treated as equal.
I'd really like this to be true (I like it, it's simple) but then how do
you explain that row(1, null) > row(1, 2) is NULL, and not true? both
sides are record values and they are not null, after all.
--
Raphael 'kena' Poss
"Raphael 'kena' Poss" <knz@thaumogen.net> writes:
Op 26-07-18 om 10:03 schreef Tom Lane:
We insist on non-null record values being totally ordered, because without
that you can't build a working btree opclass for them. So the general
principle for comparing corresponding fields in two records is that nulls
sort after non-nulls and two nulls are treated as equal.
I'd really like this to be true (I like it, it's simple) but then how do
you explain that row(1, null) > row(1, 2) is NULL, and not true? both
sides are record values and they are not null, after all.
Yeah, well, if we have a comparison operator comparing two syntactic row
constructors, it works differently: that case breaks down the two field
lists and applies the named operator to each pair of values. The behavior
for nulls is just one of the discrepancies; that case is also more
forgiving about field type differences. For instance this is allowed:
regression=# select (1, 1, NULL::int) > (1, 1, 2.0);
?column?
----------
(1 row)
but this not so much:
regression=# create type int3 as (f1 int,f2 int,f3 int);
CREATE TYPE
regression=# create table i3 (c1 int3);
CREATE TABLE
regression=# insert into i3 values ((1,1,1));
INSERT 0 1
regression=# select c1 > (1, 1, 2.0) from i3;
ERROR: cannot compare dissimilar column types integer and numeric at record column 3
Personally I'd be happy to lose all that special-case behavior for row
constructors, but we'd get push-back on backwards compatibility.
regards, tom lane