SQLite Forum

Simplified SQL:

> ~~~
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES(0),(3);
CREATE TABLE t2(b INT UNIQUE, c INT);
INSERT INTO t2 VALUES(1,4),(0,5);
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE a=c AND a IS b);
~~~

The one-row answer returned is incorrect.  The correct answer is no rows.
This can be seen by disabling the "transitive constraint" query optimization:

> ~~~
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES(0),(3);
CREATE TABLE t2(b INT UNIQUE, c INT);
INSERT INTO t2 VALUES(1,4),(0,5);
.echo on
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE a=c AND a IS b);
.testctrl optimizations 0x80
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE a=c AND a IS b);
~~~

The first of the two identical queries returns one row, but the
second (with the 0x80 optimization disabled) returns zero rows.

The equivalent PostgreSQL query is:

> ~~~
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES(0),(3);
CREATE TABLE t2(b INT UNIQUE, c INT);
INSERT INTO t2 VALUES(1,4),(0,5);
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE a=c AND a IS NOT DISTINCT FROM b);
~~~

PostgreSQL gives a zero-row result, further confirming that something is wrong.