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.