From: Wolfgang Walther <walther@technowledgy.de>
To: pgsql-bugs@lists.postgresql.org
Subject: Re: Wrong result for comparing ROW(...) with IS NOT NULL
Date: Thu, 5 Nov 2020 16:06:01 +0100
Message-ID: <fdb5eb66-8f31-4e45-7388-f83fdbaef507@technowledgy.de> (raw)
In-Reply-To: <4a853592-a43b-b677-5026-212196b6e9c2@technowledgy.de>
References: <21ff8e9c-627a-f949-fb00-a41b9ddcc9d3@technowledgy.de>
<CAFj8pRAKiVUUFQawx70MXwd72rJ6D4UQFwAcmL5VVByE90HuYg@mail.gmail.com>
<2ba24707-e7ba-2ccb-7269-71202a400a70@technowledgy.de>
<CALT9ZEEN9x-OoFqixs-Gs9XXhJ3uUJzMerEJ-d3Lo4geWFzAMw@mail.gmail.com>
<4a853592-a43b-b677-5026-212196b6e9c2@technowledgy.de>
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
Attachments:download all (2 files, .tar.gz)0001-doc-Add-IS-NOT-NULL-example-and-link-to-section-9.2-.patch (text/plain, 1.2K) download
>From 75704f4196e1dd7d92df58cb98aca803e6913e51 Mon Sep 17 00:00:00 2001
From: Wolfgang Walther <walther@technowledgy.de>
Date: Thu, 5 Nov 2020 15:45:34 +0100
Subject: [PATCH 1/2] doc: Add IS NOT NULL example and link to section 9.2 to
4.2.13 Row Constructors
---
doc/src/sgml/syntax.sgml | 4 +++-
1 file changed, 3 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgmlindex 3fdd87823e..8b5aa30ab4 100644--- a/doc/src/sgml/syntax.sgml+++ b/doc/src/sgml/syntax.sgml@@ -2434,8 +2434,10 @@ SELECT getf1(CAST(ROW(11,'this is a test',2.5) AS myrowtype));
SELECT ROW(1,2.5,'this is a test') = ROW(1, 3, 'not the same');
SELECT ROW(table.*) IS NULL FROM table; -- detect all-null rows
++SELECT ROW(table.*) IS NOT NULL FROM table; -- detect all-non-null rows
</programlisting>
- For more detail see <xref linkend="functions-comparisons"/>.+ For more detail see <xref linkend="functions-comparisons"/> (comparison) and <xref linkend="functions-comparison"/> (<literal>IS [NOT] NULL</literal>).
Row constructors can also be used in connection with subqueries,
as discussed in <xref linkend="functions-subquery"/>.
</para>
--
2.29.2
0002-doc-change-id-of-section-9.24-to-functions-row-array.patch (text/plain, 2.6K) download
>From a7c55ec8c93b1b7bce885f099b7ca7c9aabf2d92 Mon Sep 17 00:00:00 2001
From: Wolfgang Walther <walther@technowledgy.de>
Date: Thu, 5 Nov 2020 15:48:27 +0100
Subject: [PATCH 2/2] doc: change id of section 9.24 to
functions-row-array-comparisons
Section 9.2 and 9.24 have nearly identical ids in functions-comparison
and functions-comparisons.
---
doc/src/sgml/array.sgml | 2 +-
doc/src/sgml/func.sgml | 4 ++--
doc/src/sgml/syntax.sgml | 2 +-
3 files changed, 4 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/array.sgml b/doc/src/sgml/array.sgmlindex 56185b9b03..6d81e58ef4 100644--- a/doc/src/sgml/array.sgml+++ b/doc/src/sgml/array.sgml@@ -616,7 +616,7 @@ SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
However, this quickly becomes tedious for large arrays, and is not
helpful if the size of the array is unknown. An alternative method is
- described in <xref linkend="functions-comparisons"/>. The above+ described in <xref linkend="functions-row-array-comparisons"/>. The above
query could be replaced by:
<programlisting>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgmlindex bf6004f321..39ec76c487 100644--- a/doc/src/sgml/func.sgml+++ b/doc/src/sgml/func.sgml@@ -20099,7 +20099,7 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
</sect1>
- <sect1 id="functions-comparisons">+ <sect1 id="functions-row-array-comparisons">
<title>Row and Array Comparisons</title>
<indexterm>
@@ -20161,7 +20161,7 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
Boolean (true/false) results.
</para>
- <sect2 id="functions-comparisons-in-scalar">+ <sect2 id="functions-row-array-comparisons-in-scalar">
<title><literal>IN</literal></title>
<synopsis>
diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgmlindex 8b5aa30ab4..31b1da8011 100644--- a/doc/src/sgml/syntax.sgml+++ b/doc/src/sgml/syntax.sgml@@ -2437,7 +2437,7 @@ SELECT ROW(table.*) IS NULL FROM table; -- detect all-null rows
SELECT ROW(table.*) IS NOT NULL FROM table; -- detect all-non-null rows
</programlisting>
- For more detail see <xref linkend="functions-comparisons"/> (comparison) and <xref linkend="functions-comparison"/> (<literal>IS [NOT] NULL</literal>).+ For more detail see <xref linkend="functions-row-array-comparisons"/> (comparison) and <xref linkend="functions-comparison"/> (<literal>IS [NOT] NULL</literal>).
Row constructors can also be used in connection with subqueries,
as discussed in <xref linkend="functions-subquery"/>.
</para>
--
2.29.2
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: walther@technowledgy.de, pgsql-bugs@lists.postgresql.org
Subject: Re: Wrong result for comparing ROW(...) with IS NOT NULL
In-Reply-To: <fdb5eb66-8f31-4e45-7388-f83fdbaef507@technowledgy.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox