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

Hi,

when I do the following on PG 12.4, I get some unexpected results:

SELECT
   ROW() IS NULL,     -- true
   ROW() IS NOT NULL; -- true

Both return true here. In any case IS NULL should return the opposite 
from IS NOT NULL, right?

The same happens here:

SELECT
   ROW(NULL, NULL) IS NULL,     -- returns: true  (expected)
   ROW(NULL, NULL) IS NOT NULL, -- returns: false (expected)
   ROW(1, NULL) IS NULL,        -- returns: false (expected)
   ROW(1, NULL) IS NOT NULL,    -- returns: false !!
   ROW(1, 1) IS NULL,           -- returns: false (expected)
   ROW(1, 1) IS NOT NULL;       -- returns: true  (expected)

The docs[1] say:

 > Also, it is possible to [...] test a row with IS NULL or IS NOT NULL, 
for example:
 > [...]
 > SELECT ROW(table.*) IS NULL FROM table;  -- detect all-null rows

So I would expect the ROW(1, NULL) IS NOT NULL to be true, because it's 
not "all-null". I'm not sure what I would expect ROW() to be, but surely 
not the same for IS NULL and IS NOT NULL.

Best

Wolfgang

[1]: 
https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS

Pavel Stehule:
> for composite types this sentence is not valid
> 
> https://til.cybertec-postgresql.com/post/2019-09-29-Composite-types-and-NULL-in-PostgreSQL/
> 
> is null - is true, when all fields are null, and is not null is true, 
> when all fields is not null.

Ok, I can see how this explanation is somehow consistent. The link you 
gave is just another observation of that, though. Can I infer from 
anywhere in the official docs, that this is correct and expected behaviour?

Best

Wolfgang

Pavel Borisov:
> Sure, it is described here: 
> https://www.postgresql.org/docs/13/functions-comparison.html
> 
> "If the /|expression|/ is row-valued, then |IS NULL| is true when the 
> row expression itself is null or when all the row's fields are null, 
> while |IS NOT NULL| is true when the row expression itself is non-null 
> and all the row's fields are non-null. Because of this behavior, |IS 
> NULL| and |IS NOT NULL| do not always return inverse results for 
> row-valued expressions; in particular, a row-valued expression that 
> contains both null and non-null fields will return false for both tests. 
> In some cases, it may be preferable to write /|row|/|IS DISTINCT FROM 
> NULL| or /|row|/|IS NOT DISTINCT FROM NULL|, which will simply check 
> whether the overall row value is null without any additional tests on 
> the row fields."


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.



Another thing that could be improved:

Below that example there is a link to section 9.24. The link is:

https://www.postgresql.org/docs/current/functions-comparisons.html

The link you gave me to section 9.2 is (replaced 13 with current):

https://www.postgresql.org/docs/current/functions-comparison.html

Like really? The only difference is the "s" in comparison(s). That 
confused me at first for a bit, because I thought I had read your link 
already :)

I think that link for 9.24 could be much better chosen. 
row-array-comparisons.html would match the content.


Best

Wolfgang

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.
> 
> 
> 
> Another thing that could be improved:
> 
> Below that example there is a link to section 9.24. The link is:
> 
> https://www.postgresql.org/docs/current/functions-comparisons.html
> 
> The link you gave me to section 9.2 is (replaced 13 with current):
> 
> https://www.postgresql.org/docs/current/functions-comparison.html
> 
> Like really? The only difference is the "s" in comparison(s). That 
> confused me at first for a bit, because I thought I had read your link 
> already :)
> 
> I think that link for 9.24 could be much better chosen. 
> row-array-comparisons.html would match the content.

Attached are some patches.

Best

Wolfgang

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.

On Wed, Nov  1, 2023 at 07:38:47PM -0400, Bruce Momjian wrote:
> > 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.

Patch applied to master.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.