Postgres ignoring null values when using filter as not in array-CodePudding

SELECT * FROM Entity e WHERE e.Status <> ANY(ARRAY[1,2,3]);

Here Status is a nullable integer column. Using the above query i am unable to fetch the records whose status value is NULL.

SELECT * FROM Entity e WHERE (e.Status is NULL OR e.Status = 4);

This query does the trick. Could someone explain me why the first query was not working as expected.

CodePudding user response：

It is spelled out in the docs Array ANY:

If the array expression yields a null array, the result of ANY will be null. If the left-hand expression yields null, the result of ANY is ordinarily null (though a non-strict comparison operator could possibly yield a different result). Also, if the right-hand array contains any null elements and no true comparison result is obtained, the result of ANY will be null, not false (again, assuming a strict comparison operator). This is in accordance with SQL's normal rules for Boolean combinations of null values.

FYI:

e.Status is NULL OR e.Status = 4

can be shortened to:

e_status IS NOT DISTINCT FROM 4

per Comparison operators.

CodePudding user response：

NULL kinda means "unknown", so the expressions

NULL = NULL

and

NULL != NULL

are neither true nor false, they're NULL. Because it is not known whether an "unknown" value is equal or unequal to another "unknown" value.

Since <> ANY uses an equality test, if the value searched in the array is NULL, then the result will be NULL.

So your second query is correct.