Why Postgres does not delete rows if one of the columns is NULL when USING/IN/EXISTS is used?

Question

Let's say we have a table:

CREATE TABLE rows(
  a int NOT NULL,
  b int,
  c int
);
INSERT INTO rows(a, b, c) VALUES (1, 1, NULL), (2, NULL, 1), (3, 1, 1);

Why Postgres does not delete the row using the following query?

DELETE FROM rows WHERE (a, b, c) IN ((1, 1, NULL));
-- DELETE 0

It works if there is no NULL value in one of the columns:

DELETE FROM rows WHERE (a, b, c) IN ((3, 1, 1));
-- DELETE 1

The same problem if we'll try to use one of the following queries:

WITH to_delete AS (
    SELECT * FROM UNNEST (
        ARRAY[1, 2],
        ARRAY[1, NULL],
        ARRAY[NULL, 1]
    ) data(a, b, c)
)
DELETE FROM rows 
USING to_delete
WHERE
    rows.a = to_delete.a AND
    rows.b = to_delete.b AND
    rows.c = to_delete.c;
-- DELETE 0

WITH to_delete AS (
    SELECT * FROM UNNEST (
        ARRAY[1, 2],
        ARRAY[1, NULL],
        ARRAY[NULL, 1]
    ) data(a, b, c)
)
DELETE FROM rows 
WHERE EXISTS (
    SELECT 1 FROM to_delete
    WHERE
        to_delete.a = rows.a AND
        to_delete.b = rows.b AND
        to_delete.c = rows.c
)
-- DELETE 0

WITH to_delete AS (
    SELECT * FROM UNNEST (
        ARRAY[1, 2],
        ARRAY[1, NULL],
        ARRAY[NULL, 1]
    ) data(a, b, c)
)
DELETE FROM rows 
WHERE (a, b, c) IN (SELECT * FROM to_delete)
-- DELETE 0

The previous 3 queries delete rows successfully when each column is not NULL.

So there are 2 questions:

1. Why these SQL-queries don't delete rows where one of the columns is NULL?
2. How to solve it? I use CTE where I'm receiving the rows I should delete. One of the columns in these rows is always NULL.

Answer 1

NULL can only be detected if you use IS

like

CREATE TABLE rows(
  a int NOT NULL,
  b int,
  c int
);
INSERT INTO rows(a, b, c) VALUES (1, 1, NULL), (2, NULL, 1), (3, 1, 1);

CREATE TABLE

INSERT 0 3

DELETE FROM rows WHERE a= 1 AND  b =  1 AND  C IS NULL;

DELETE 1

INSERT INTO rows(a, b, c) VALUES (1, 1, NULL)

INSERT 0 1

WITH to_delete AS (
    SELECT * FROM UNNEST (
        ARRAY[1, 2],
        ARRAY[1, NULL],
        ARRAY[NULL, 1]
    ) data(a, b, c)
)
DELETE FROM rows 
USING to_delete
WHERE
    ((rows.a = to_delete.a) OR (rows.a IS NULL AND to_delete.a IS NULL)) AND
    ((rows.b = to_delete.b) OR (rows.b IS NULL AND to_delete.b IS NULL)) AND
   ((rows.c = to_delete.c) OR (rows.c IS NULL AND to_delete.c IS NULL));

DELETE 2

fiddle