SQLite

The final query in the following SQL generates an incorrect result. It returns just one row when it should return two.

REATE TABLE t1(a);
CREATE TABLE t2(b);
INSERT INTO t1 VALUES(1), (1), (2);
INSERT INTO t2 VALUES(1), (2);
SELECT * FROM t2 WHERE b IN (SELECT DISTINCT a FROM t1 LIMIT 2);

This bug can be hit when an IN operator has a subquery on its right-hand side and the subquery contains both a DISTINCT keyword and a LIMIT clause.

The cause of this problem is check-in [f4cb53651b1e352f] which is an optimization introduced in version 3.8.6 that suppresses the DISTINCT on an IN subquery. But that optimization is not always valid, as the example above shows.

This problem was discovered by Igor Stassiy and reported on the sqlite-users mailing list.