Обсуждение: Test of value equivalency of row type, feature or bug?

=?UTF-8?B?5a2Z5Yaw?= <subi.the.dream.walker@gmail.com> writes:
> select t=t from (select 1, null) t;

> If I manage to catch the documents, the result of the query should be null,
> as an equivalency comparison of null-contained row-typed values is null.
> Surprisingly, the query result turns out to be true.

> Is this behavior a feature or a bug?

It's a feature.  The btree comparison functions (<, =, >, etc) for
composite types have to provide a total order for their datatypes,
and treating an individual null field as a reason to return null
would break that.

The whole question of when a composite value "is null" is messy.
It ought not be --- IMO, either you have a tuple or you don't ---
but the SQL committee did nobody any favors with their creative
specification for what "x IS [NOT] NULL" means for composite x.

Having said that, though, perhaps "t IS NOT NULL" would do what
you want here, since it's defined to be true only when each of
t's fields is not null.

            regards, tom lane

On Thu, Dec 20, 2018 at 7:35 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> =?UTF-8?B?5a2Z5Yaw?= <subi.the.dream.walker@gmail.com> writes:
> > select t=t from (select 1, null) t;
>
> > If I manage to catch the documents, the result of the query should be null,
> > as an equivalency comparison of null-contained row-typed values is null.
> > Surprisingly, the query result turns out to be true.
>
> > Is this behavior a feature or a bug?
>
> It's a feature.  The btree comparison functions (<, =, >, etc) for
> composite types have to provide a total order for their datatypes,
> and treating an individual null field as a reason to return null
> would break that.

That doesn't seem to be what is going on here nor is it immediately
obvious that such is what is documented.

https://www.postgresql.org/docs/11/functions-comparisons.html#ROW-WISE-COMPARISON

"""
The = and <> cases work slightly differently from the others. Two rows
are considered equal if all their corresponding members are non-null
and equal; the rows are unequal if any corresponding members are
non-null and unequal; otherwise the result of the row comparison is
unknown (null).
"""

I'm reading that as the OP did, which seems to contradict what you are saying.

Furthermore:

(9.6)
select (1,null::int)=(1,null::int) -- null
select t=t from (select (1, null::int)) t -- true

The t=t version must be true due to not caring what the contents of
"t" are, otherwise it would report null as the first example does...

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thu, Dec 20, 2018 at 7:35 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> It's a feature.  The btree comparison functions (<, =, >, etc) for
>> composite types have to provide a total order for their datatypes,
>> and treating an individual null field as a reason to return null
>> would break that.

> That doesn't seem to be what is going on here nor is it immediately
> obvious that such is what is documented.
> https://www.postgresql.org/docs/11/functions-comparisons.html#ROW-WISE-COMPARISON

That's talking specifically about the results of a comparison of
two row constructors, ie "ROW(...) = ROW(...)".

The fact that this acts differently from other seemingly-related cases
can be blamed directly on the SQL spec.

            regards, tom lane