Re: Wrong result for comparing ROW(...) with IS NOT NULL

On Thu, Nov 5, 2020 at 8:06 AM Wolfgang Walther <walther(at)technowledgy(dot)de>
wrote:

> Wolfgang Walther:
>
> > Thank you, that explains it very well.
> >
> > When I realized there was something unexpected going on, I was looking
> > at all the ROW() syntax in the docs and I found this (as mentioned
> > upthread):
> >
> >
> https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS
> >
> >
> > It might be worth it to either add another example for IS NOT NULL, like
> >
> > SELECT ROW(table.*) IS NOT NULL FROM table; -- detect all-non-null rows
> >
> > or add a link to section 9.2, that you mentioned. Or do both.
>
> Attached are some patches.
>

I would move examples related to IS NULL to 9.2

I would also include a third example in 9.2: SELECT NOT(ROW(table.*) IS NOT
NULL); -- detect at least one null column in row

Thus:

"Row constructors can be used to build composite values to be stored in a
composite-type table column, or to be passed to a function that accepts a
composite parameter. Also, it is possible to test a row using the standard
comparison operators described in chapter 9.2, compare a row against
subquery results as described in chapter 9.23, or compare one row against
another as described in chapter 9.24."

And drop the examples and the following paragraph.

Also, nothing in 9.2 precludes composite and row constructor
comparisons from being included there, and the intro material suggests that
they probably should be. That we cover the details of (composite IS
DISTINCT FROM composite) in 9.24 instead of 9.2 should be noted in 9.2
somewhere and a link to 9.24 provided.

I do agree with changing the identifier to be more unique but I don't know
if it is this simple.

David J.