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

Hi

čt 5. 11. 2020 v 13:32 odesílatel Wolfgang Walther <walther@technowledgy.de>
napsal:

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?

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.

Regards

Pavel

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

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?

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."

--
Best regards,
Pavel Borisov

Postgres Professional: http://postgrespro.com <http://www.postgrespro.com&gt;

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 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.

David J.

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.