From: PG Doc comments form <noreply@postgresql.org>
To: pgsql-docs@lists.postgresql.org
Cc: eric.mutta@gmail.com
Subject: Bug in code sample in "8.15.5. Searching in Arrays"
Date: Fri, 22 Jul 2022 22:41:17 +0000
Message-ID: <165852967722.271454.6988634580136979082@wrigleys.postgresql.org> (raw)
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/14/arrays.html
Description:
The section on array searching here:
https://www.postgresql.org/docs/current/arrays.html#ARRAYS-SEARCHING
...says "In addition, you can find rows where the array has all values equal
to 10000 with:" and gives the following code example:
SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);
...however the docs for "ALL" over here:
https://www.postgresql.org/docs/current/functions-comparisons.html#id-1.5.8.30.17
...explicitly say that "The result of ALL is “true” if all comparisons yield
true (including the case where the array has zero elements)."
This means that the expression "10000 = ALL (pay_by_quarter)" will be TRUE
when indeed all array elements have that 10,000 value...but it will ALSO be
true when the array is empty! This can produce weird bugs (e.g. in this
case, employees who were never paid and thus have an empty pay_by_quarter
array, will be lumped together with employees who WERE paid the 10,000
amount in all quarters).
This potentially surprising (but documented) behaviour of ALL can be seen in
the query "SELECT 3 = ALL (array[]::int[])" which returns TRUE and in
English could read as "3 is equal to all of nothing"...that sounds like
something a drunk Master Yoda would say :-)
I suggest the code example be changed to the following (perhaps with a NOTE
to call out the behaviour of "ALL" when the array is empty):
SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter) AND
cardinality(pay_by_quarter) > 0;
The above query should now return was most likely intended (logically,
employees who were paid 10,000 in ALL quarters must also be among employees
who were paid anything in ANY quarter).
Many thanks,
Eric Mutta.
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-docs@postgresql.org
Cc: noreply@postgresql.org, pgsql-docs@lists.postgresql.org, eric.mutta@gmail.com
Subject: Re: Bug in code sample in "8.15.5. Searching in Arrays"
In-Reply-To: <165852967722.271454.6988634580136979082@wrigleys.postgresql.org>
* 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