SQLite

View Ticket
Login
2016-03-09
15:14
Add another test case for bug [5e3c8867]. (check-in: d91e57e4 user: dan tags: trunk)
15:09 Fixed ticket [5e3c8867]: Correlated subquery on the RHS of an IN operator plus 5 other changes (artifact: f54cb735 user: drh)
15:09
When optimizing expressions of the form "x IN (SELECT ...)" make sure that the subquery is not correlated. Fix for ticket [5e3c886796e5512]. (check-in: 1ed6b06e user: drh tags: trunk)
14:48 New ticket [5e3c8867] Correlated subquery on the RHS of an IN operator. (artifact: ada44cb0 user: drh)

Ticket Hash: 5e3c886796e5512e9d884c269945e308100cbc6a
Title: Correlated subquery on the RHS of an IN operator
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2016-03-09 15:09:32
Version Found In: 3.11.1
User Comments:
drh added on 2016-03-09 14:48:30:

The following SQL generates a single row of output where it should generate an empty set:

CREATE TABLE t1(a INTEGER PRIMARY KEY);
INSERT INTO t1(a) VALUES(1),(2);
CREATE TABLE t2(b INTEGER PRIMARY KEY);
INSERT INTO t2(b) VALUES(1);
SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2);

Apparently, the subquery "(SELECT a FROM t2)" is being compiled as if it where "(SELECT b FROM t2)". This problem appears to go back to before version 3.6.1, circa 2008.

Problem reported at stackoverflow and relayed to the sqlite-users mailing list by Clemens Ladisch.