PostgreSQL Null breaks lists when looking for NOT IN

Question

I think this might be a PostgreSQL bug but I'm posting it here in case I'm just missing something. When my WHERE clause has a NOT IN () clause, having null in the list makes the clause no longer truthy. Below is a dumbed down example of my issue.

=# select 1 where 1 not in (1);
 ?column? 
----------
(0 rows)

=# select 1 where 1 not in (2);
 ?column? 
----------
        1
(1 row)

=# select 1 where 1 not in (null);
 ?column? 
----------
(0 rows)

=# select 1 where 1 not in (null, 2);
 ?column? 
----------
(0 rows)

=# select 1 where 1 not in (2, null);
 ?column? 
----------
(0 rows)

=# select 1 where 1 not in (2, 3);
 ?column? 
----------
        1
(1 row)

So where 1 not in (1) returns 0 rows as expected since 1 is in the list, where 1 not in (2) returns 1 row as expected since 1 is not in the list, but where 1 not in (null) returns 0 rows even though 1 is not in the list.

Bruno · Accepted Answer · 2019-03-15 18:03:59Z

6

This is not a PostgreSQL bug.

The problem is that NOT IN is just the short version for testing all inequalities one by one.

1 NOT IN (null, 2) is equivalent to:

1 <> null
AND
1 <> 2

However, NULL is a special value, so 1 <> null is itself NULL (not TRUE). See the documentation:

Do not write expression = NULL because NULL is not “equal to” NULL. (The null value represents an unknown value, and it is not known whether two unknown values are equal.)

As far as I know that's the standard SQL behaviour.

PostgreSQL has an additional keyword to check whether a value is different from null:

1 IS DISTINCT FROM NULL would be TRUE.

answered Mar 15, 2019 at 18:03

Bruno

123k33 gold badges278 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

CaldwellYSR Over a year ago

I'm glad I asked here then. I figured it was either a bug or I was missing something crucial about how NOT IN works.

Raymond Nijland Over a year ago

"As far as I know that's the standard SQL behaviour." indeed SQL 2003 defines "4.6.1 Comparison and assignment of booleans All boolean data type values and SQL truth values are mutually comparable and assignable. The value true is greater than the value false , and any comparison involving the null value or an unknown truth value will return an unknown result" That also means the optimizer can directly preprocess1 <> null with null and replace null AND 1 <> 2 also with null.. Not sure if PostgreSQL optimizier does this kind of preprocessing..

user330315 Over a year ago

@RaymondNijland: yes, the optimizer replaces 1 <> null (or column <> null) with false (you will see a line with One-Time Filter: false in the execution plan)

Raymond Nijland Over a year ago

Great i would expected it from the PostgreSQL optimizer @a_horse_with_no_name

Collectives™ on Stack Overflow

PostgreSQL Null breaks lists when looking for NOT IN

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related