pgsql 8.4 "not" + "is contained by" operators return wrong result
*Description:*
By pgsql 8.4 I have the following bug:
If the haystack array is null (for example by empty result of array_agg) by
is contained by, and you use it with negation, the the result is false
instead of true.
*Reproduce:*
The following query returns nothing, but it should return 1.
*SELECT 1 FROM NOT(ARRAY[1] <@ NULL**); *
*Workaround:*
*SELECT 1 FROM (haystack IS NULL OR NOT(ARRAY[1] <@ **haystack**)); *
=?ISO-8859-1?Q?L=E1szl=F3_Lajos_J=E1nszky?= <laszlo.janszky@gmail.com> writes:
The following query returns nothing, but it should return 1.
*SELECT 1 FROM NOT(ARRAY[1] <@ NULL**); *
I assume you meant SELECT 1 WHERE NOT(ARRAY[1] <@ NULL) ?
Because what you wrote doesn't parse.
This is not a bug. "ARRAY[1] <@ NULL" yields NULL. NOT (NULL) is still
NULL. WHERE treats a NULL result as FALSE.
It might help you to consider that NULL means "unknown". It does not
mean "empty array".
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs