(text/x-fossil-wiki)
The final query in the following SQL generates an incorrect result. It
returns just one row when it should return two.
<blockquote><verbatim>
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);
</verbatim></blockquote>
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.
|