SQLite

View Ticket
Login
2011-09-19
17:37 Closed ticket [002caede]: LEFT JOIN with OR terms in WHERE clause causes assertion fault (artifact: 3576c0c2 user: drh)
2011-09-16
18:17 Fixed ticket [002caede]. (artifact: a25beefa user: drh)
16:20 New ticket [1a1308d2] Nested correlated subquery fails when indices used. (artifact: f68bc318 user: drh)
16:00
Further streamlining of the subquery materializer. New test cases for ticket [002caede898a] (check-in: ff8b76b2 user: drh tags: trunk)
01:38
Merge the [002caede898] fix into trunk. (check-in: 95708ae2 user: drh tags: trunk)
01:34
Add the new OP_Once opcode. Use it to clean up and simplify various one-time initialization sections in the code, including the fix for ticket [002caede898ae]. (Closed-Leaf check-in: 7f00552b user: drh tags: tkt-002caede898)
2011-09-15
23:58
Materialize subqueries using a subroutine and invoke that subroutine prior to each use of the materialization. Fix for ticket [002caede898aee4] (check-in: 4b8357ee user: drh tags: tkt-002caede898)
19:39
Adding test case for ticket [002caede898] (check-in: 62dfc51a user: drh tags: tkt-002caede898)
18:54 New ticket [002caede] LEFT JOIN with OR terms in WHERE clause causes assertion fault. (artifact: 80aac112 user: drh)

Ticket Hash: 002caede898aee4dc531ae9995281c2b8b03a1c6
Title: LEFT JOIN with OR terms in WHERE clause causes assertion fault
Status: Closed Type: Code_Defect
Severity: Critical Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2011-09-19 17:37:54
Version Found In: 3.7.7.1
Description:
The following SQL code causes an assertion fault in the VDBE:
CREATE TABLE t1(a,b,c,d);
CREATE TABLE t2(e,f);
INSERT INTO t1 VALUES(1,2,3,4);
INSERT INTO t2 VALUES(10,-8);
CREATE INDEX t1a ON t1(a);
CREATE UNIQUE INDEX t1b ON t1(b);
CREATE TABLE t3(g);
INSERT INTO t3 VALUES(4);
CREATE TABLE t4(h);
INSERT INTO t4 VALUES(5);

SELECT * FROM t3 LEFT JOIN t1 ON d=g LEFT JOIN t4 ON c=h WHERE (a=1 AND h=3) OR b IN ( SELECT x+1 FROM (SELECT e+f AS x, e FROM t2 ORDER BY 1 LIMIT 2) GROUP BY e );

It appears that both LEFT JOINs, the OR operator using indices in the WHERE clause, and the IN operator with unflattenable nested subqueries and located on the right-hand side of the OR are all necessary conditions for this problem to occur. In other words, the problem is very obscure.

The problem appears to have been introduced by check-in [2c2de25266] which was a bug-fix for a similar problem described by ticket [31338dca7e3d]. This problem might be considered a particular case of the prior ticket [31338dca7e3d] that was never fixed.

The problem first appeared in release 3.6.22.