Re: BUG #15297: Irregular comparison rules for NULLs in tuples

"Raphael 'kena' Poss" <knz(at)thaumogen(dot)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