pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedBug in code sample in "8.15.5. Searching in Arrays"
2+ messages / 2 participants
[nested] [flat]
* Bug in code sample in "8.15.5. Searching in Arrays"
@ 2022-07-22 22:41 PG Doc comments form <noreply@postgresql.org>
0 siblings, 1 reply; 2+ messages in thread
From: PG Doc comments form @ 2022-07-22 22:41 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: eric.mutta@gmail.com
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Bug in code sample in "8.15.5. Searching in Arrays"
@ 2022-07-24 19:41 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Doc comments form <noreply@postgresql.org>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2022-07-24 19:41 UTC (permalink / raw)
To: eric.mutta@gmail.com; +Cc: pgsql-docs@lists.postgresql.org
PG Doc comments form <noreply@postgresql.org> writes:
> 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);
Yeah ...
> ...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)."
I don't see any conflict there.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2022-07-24 19:41 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-07-22 22:41 Bug in code sample in "8.15.5. Searching in Arrays" PG Doc comments form <noreply@postgresql.org>
2022-07-24 19:41 ` Tom Lane <tgl@sss.pgh.pa.us>
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