Subject: Ambiguous usage of 'any' in explanation

From: PG Doc comments form <[email-masked]>
Date: Sat, 15 Sep 2018 11:53:47 +0000

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/10/static/functions-subquery.html
Description:

Hi.

The [DOC](https://www.postgresql.org/docs/10/static/functions-subquery.html)
says:
 
    The result is NULL if the comparison does not return false for any
subquery row

does *any* mean here: some or all?

For example in
[MySQL](https://dev.mysql.com/doc/refman/8.0/en/any-in-some-subqueries.html)
I have found this explanation:

>Use of the word SOME is rare, but this example shows why it might be
useful. To most people, the English phrase “a is not equal to any b” means
“there is no b which is equal to a,” but that is not what is meant by the
SQL syntax. The syntax means “there is some b to which a is not equal.”
Using <> SOME instead helps ensure that everyone understands the true
meaning of the query.

---

From: Bruce Momjian <[email-masked]>
Date: Thu, 11 Oct 2018 17:09:34 -0400

> Using <> SOME instead helps ensure that everyone understands the true

I am kind of confused by this report.  You mention SOME in the text

>     The result is NULL if the comparison does not return false for any

is from ALL:

	The result of <token>ALL</token> is <quote>true</quote> if
	the comparison returns true for all subquery rows (including
	the case where the subquery returns no rows).  The result is
	<quote>false</quote> if the comparison returns false for any
	subquery row.  The result is NULL if the comparison does not
	return false for any subquery row, and it returns NULL for at
	least one row.

I see the last line is just trying to negate the line above it, but the
mix of "not" and "any" is confusing.  I can reword this last line to be:

	The result is NULL if no comparison with a subquery row returns
	false, and it returns NULL for at least one row.

I can make similar adjustments in other places, and have attached a doc
patch.  Does that help?

-- 
  Bruce Momjian  <[email-masked]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 5193df3..183f081 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -15246,7 +15246,7 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
    The result is <quote>false</quote> if the comparison returns false for every
    subquery row (including the case where the subquery returns no
    rows).
-   The result is NULL if the comparison does not return true for any row,
+   The result is NULL if no comparison with a subquery row returns true,
    and it returns NULL for at least one row.
   </para>
 
@@ -15272,7 +15272,7 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
    The result of <token>ALL</token> is <quote>true</quote> if all rows yield true
    (including the case where the subquery returns no rows).
    The result is <quote>false</quote> if any false result is found.
-   The result is NULL if the comparison does not return false for any row,
+   The result is NULL if no comparison with a subquery row returns false,
    and it returns NULL for at least one row.
   </para>
 
@@ -15302,8 +15302,8 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
    case where the subquery returns no rows).
    The result is <quote>false</quote> if the comparison returns false for any
    subquery row.
-   The result is NULL if the comparison does not return false for any
-   subquery row, and it returns NULL for at least one row.
+   The result is NULL if no comparison with a subquery row returns false,
+   and it returns NULL for at least one row.
   </para>
 
   <para>

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 11 Oct 2018 14:20:07 -0700

> I can make similar adjustments in other places, and have attached a doc

+1

David J.

---

From: KES <[email-masked]>
Date: Fri, 12 Oct 2018 13:25:26 +0300

either it is hard to understand.

I will rephrase one part to show how it is easy to understand from my side:

>ALL

> The result is “false” if any false result is found.

The result of ALL is "false" even if *at least one*  row yield false

(we can use 'some' word here, but it is not such clear as *at least one*)

This sentence is easyly can be compared to:

>The result of ALL is “true” if all rows yield true

We can notice difference with first statement so can understand when result is different:

(I got next picture in my mind in this case:)

all true -> true

at least one false -> false

Thus if other parts of DOC would be constructed in same manner, it would be cool

PS. When DOC not just document behavior but also allow eaisly detect difference when/why behavior of a command is changed.

Thank you.

12.10.2018, 00:20, "David G. Johnston" <[email-masked]>:

patch.  Does that help?

+1

David J.

---

From: KES <[email-masked]>
Date: Fri, 12 Oct 2018 13:42:03 +0300

-The result of ANY is “true” if the comparison returns true for any subquery row. The result is “false” if the comparison returns false for every subquery row (including the case where the subquery returns no rows). The result is NULL if the comparison does not return true for any row, and it returns NULL for at least one row.

>The result of ALL is "false" even if *at least one*  row yield false

+The result of ANY is "true" if at least one row yields true.

The result of ANY is "false" if all rows yield false

The result of ANY is NULL if no one row yelds true and at least one row yields NULL

When subquery return no rows the result of ANY is "false"

sentences are short, easy to spot when and how result is changed

+1

David J.

---

From: Bruce Momjian <[email-masked]>
Date: Fri, 12 Oct 2018 11:04:33 -0400

On Fri, Oct 12, 2018 at 01:42:03PM +0300, KES wrote:
> -The result of ANY is “true” if the comparison returns true for any subquery
> row. The result is “false” if the comparison returns false for every subquery
> row (including the case where the subquery returns no rows). The result is NULL
> if the comparison does not return true for any row, and it returns NULL for at
> least one row.
>  
> Similary to:

Sorry, but I don't like this wording.  The problem is that the
comparison has two row sets --- the left-hand side, and the right-hand
side.  Each row on the left-hand side is compared with the row set on
the right.  I also don't like people thinking about the result of ANY
since it is really <comparison> ANY that is being used.

---

From: "David G. Johnston" <[email-masked]>
Date: Fri, 12 Oct 2018 14:37:33 -0700

On Fri, Oct 12, 2018 at 8:04 AM Bruce Momjian <[email-masked]> wrote:

> side.

Huh...the left hand side must be a non-set scalar or row constructor.

  Each row on the left-hand side is compared with the row set on
> since it is really <comparison> ANY that is being used.

Then there is some more rewording to be done since: "The result of ANY is
“true” if any true result is obtained." (v10; 9.22.4)

Maybe:

The result of ANY is “true” if the comparison returns true for any subquery
row; otherwise the result is “false” (or NULL if any of the comparisons
result in unknown)

David J.

---

From: Bruce Momjian <[email-masked]>
Date: Fri, 12 Oct 2018 23:23:09 -0400

> Huh...the left hand side must be a non-set scalar or row constructor.

I meant that each row feeds a value to the left-hand side to be
evaluated.

> row; otherwise the result is “false” (or NULL if any of the comparisons result
> in unknown)

Sure.

---

From: KES <[email-masked]>
Date: Sat, 13 Oct 2018 12:25:38 +0300

>or NULL if any of the comparisons result in unknown

result in unknown??

13.10.2018, 00:37, "David G. Johnston" <[email-masked]>:

side.

Then there is some more rewording to be done since: "The result of ANY is “true” if any true result is obtained." (v10; 9.22.4)

Maybe:

The result of ANY is “true” if the comparison returns true for any subquery row; otherwise the result is “false” (or NULL if any of the comparisons result in unknown)

David J.

---

From: Bruce Momjian <[email-masked]>
Date: Sat, 13 Oct 2018 13:31:48 -0400

> >or NULL if any of the comparisons result in unknown

Well, SQL has a three-valued logic, and UNKOWN values are treated like
NULL.  For me they have always been the same, and I would like to avoid
"unknown" in this context, if possible.

---------------------------------------------------------------------------

---

From: "David G. Johnston" <[email-masked]>
Date: Sun, 14 Oct 2018 23:00:18 -0700

> "unknown" in this context, if possible.
I was just trying to avoid using the word null twice in the same sentence
but it’s not a strong aversion.

David J.

---

From: Bruce Momjian <[email-masked]>
Date: Mon, 15 Oct 2018 12:32:20 -0400

On Sun, Oct 14, 2018 at 11:00:18PM -0700, David G. Johnston wrote:
> I was just trying to avoid using the word null twice in the same sentence but
> it’s not a strong aversion.

I am thinking of applying my earlier patch and calling it done.

---

From: Bruce Momjian <[email-masked]>
Date: Fri, 2 Nov 2018 08:55:15 -0400

> I am thinking of applying my earlier patch and calling it done.

Applied and backpatched to 9.3.