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@technowledgy.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@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.