Hi,
I run the following SQL in Postgres (14_STABLE), and got the results:
zlyu=# create table t1(a int, b int); CREATE TABLE
zlyu=# create table t2(a int, b int);
CREATE TABLE
zlyu=# insert into t1 values (null, 1);
INSERT 0 1
zlyu=# insert into t2 values (1, 1);
INSERT 0 1
zlyu=# select * from t1 where (a, b) not in (select * from t2);
a | b
---+---
(0 rows)
zlyu=# select * from t1 where (a, b) in (select * from t2);
a | b
---+---
(0 rows)
zlyu=# select * from t1 where array[a, b] in (select array[a,b] from t2);
a | b
---+---
(0 rows)
zlyu=# select * from t1 where array[a, b] not in (select array[a,b] from t2);
a | b
---+---
| 1
(1 row)
I run the SQL without array expr in other DBs(orcale, sqlite, ...), they all behave
the same as Postgres.
It seems a bit confusing for me that 'not in' and 'in' the same subquery both return 0
rows, but the table contains data.
Also, manually using array expression behaves differently from the first SQL. For not in case,
I step in the code, and find array_eq will consider null = null as true, however ExecSubPlan will
consider null as unprovable and exclude that row.
How to understand the result? It seems SQL standard does not mention array operation for null
value.
Thanks!