Re: pgsql 8.4 "not" + "is contained by" operators return wrong result

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

25 июля 2013 г. в 17:15:33

László Lajos Jánszky  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 "iso-8859-1".  It does not
mean "empty array".
		regards, tom lane

pgsql 8.4 "not" + "is contained by" operators return wrong result

От:

László Lajos Jánszky <laszlo.janszky@gmail.com>

Дата:

25 июля 2013 г. в 17:00:25

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));