Re: BUG #9007: List comparison

On Tue, Jan 28, 2014 at 10:56 AM, <stefan(dot)kirchev(at)gmail(dot)com> wrote:
> select * from table1 where (c1, c2) not in (select c1, c2 from table2);

Note that if there are any (NULL, NULL) values in table2 then NOT IN
*always* returns zero results. This counter-intuitive behavior is
mandated by the SQL specification and also makes the NOT IN clause
hard to optimize. It's not a bug, however.

It's almost always faster and more foolproof to convert them to NOT
EXISTS clauses like this:
select * from table1 where not exists
(select * from table2 where (table1.c1, table1.c2) = (table2.c1,
table2.c2));

db=# create table table1 (c1, c2) as values (1, 1);
db=# create table table2 (c1, c2) as values (2, 2);
db=# select * from table1 where (c1, c2) not in (select c1, c2 from table2);
c1 | c2
----+----
1 | 1
(1 row)

db=# insert into table2 values(null, null);
db=# select * from table1 where (c1, c2) not in (select c1, c2 from table2);
c1 | c2
----+----
(0 rows)

db=# select * from table1 where not exists (select * from table2 where
(table1.c1, table1.c2) = (table2.c1, table2.c2));
c1 | c2
----+----
1 | 1
(1 row)

> Is that a buffers size issue?

No, tuning parameters should never affect the correctness of returned
results, only time.

Regards,
Marti