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

On Thu, Nov 5, 2020 at 10:43:45AM -0700, David G. Johnston wrote:
> 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.

In reviewing this three-year-old email, I developed the attached patch
which I think captures what David suggested above.

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.