Thread: 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