View Ticket
Omit the "x IN (y)" to "x==y" optimization of check-in [e68b427afbc82e20] (and ticket [e39d032577df6942]) as it causes difficult affinity problems as demonstrated by ticket [dbaf8a6820be1ece] and the original assertion fault is no longer a factor due to countless other changes of the previous 5 years. (check-in: 7f5168a7 user: drh tags: trunk)
Previous check-in is not quite correct. "x IN (?)" is not exactly the same as "x==?" do to collation and affinity issues. The correct converstion should be to "x==(+? COLLATE binary)". The current check-in fixes this problem and provides test cases. Ticket [e39d032577df69] (check-in: 2ff3b25f user: drh tags: trunk)
13:31 Fixed ticket [e39d0325]: Assertion fault on a query containing "x IN (?)" plus 6 other changes (artifact: 8a7c5a08 user: drh)
Convert expressions of the form "X IN (?)" with exactly one value on the RHS of the IN into equality tests: "X=?". Add test cases to verify that statements work correctly on this corner case. Fix for ticket [e39d032577df6942]. (check-in: e68b427a user: drh tags: trunk)
One possible fix for the [e39d032577d] problem it to replace the failing assert() with a testcase() as shown here. (Closed-Leaf check-in: eae6dfbe user: drh tags: tkt-e39d0325)
11:52 New ticket [e39d0325] Assertion fault on a query containing "x IN (?)". (artifact: c3311ddb user: drh)

Ticket Hash: e39d032577df6942f4a91ad7159076fad7710e2a
Title: Assertion fault on a query containing "x IN (?)"
Status: Fixed Type: Code_Defect
Severity: Important Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2014-03-20 13:31:59
Version Found In:
User Comments:
drh added on 2014-03-20 11:52:02:

The following SQL generates an assertion fault:

CREATE INDEX t1ab ON t1(b,a);
SELECT * FROM t1 WHERE a=? AND b IN (?);

The problem appears to have been introduced by the NGQP cut-over on 2013-06-26 (check-in [0fe31f60ca] for version 3.8.0).

drh added on 2014-03-20 13:31:59:

The failing assert() statement is incorrect. So compiling without -DSQLITE_DEBUG and thus omitting all assert() statements is an easy work-around.

This problem only arises in cases where there is a multi-column index and the earlier column is constrained by "x IN (?1)" and the later column is constrained by "y=?2". The fix on trunk is to change the parser so that when the right-hand side of IN is a single expression, the IN operator is converted into ==. This accomplishes the same thing, and it results in tighter VDBE code in the corner-case of a single value on the RHS of IN.

Since after the change above, all IN operators have two or more elements on their RHS, the assert() statement is now correct and is retained.