SQLite
View Ticket
Not logged in
Ticket UUID: db872294979bb80b5b3bfb05bc1964bf988442c1
Title: Incorrect result when RHS of IN operator contains DISTINCT and LIMIT
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2014-11-14 18:02:19
Version Found In: 3.8.7.1
User Comments:
drh added on 2014-11-14 15:25:28: (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.