Ticket Hash: | bfbf38e5e9956ac69f9491e5112636ff2ecef923 | |||
Title: | Segfault on a nested join | |||
Status: | Fixed | Type: | Code_Defect | |
Severity: | Critical | Priority: | Immediate | |
Subsystem: | Unknown | Resolution: | Fixed | |
Last Modified: | 2012-11-02 23:55:29 | |||
Version Found In: | trunk:2012-11-02 | |||
Description: | ||||
Reported on the mailing list:
CREATE TABLE AAA ( aaa_id INTEGER PRIMARY KEY AUTOINCREMENT ); CREATE TABLE RRR ( rrr_id INTEGER PRIMARY KEY AUTOINCREMENT, rrr_date INTEGER NOT NULL, rrr_aaa INTEGER ); CREATE TABLE TTT ( ttt_id INTEGER PRIMARY KEY AUTOINCREMENT, target_aaa INTEGER NOT NULL, source_aaa INTEGER NOT NULL ); insert into AAA (aaa_id) values (2); insert into TTT (ttt_id, target_aaa, source_aaa) values (4469, 2, 2); insert into TTT (ttt_id, target_aaa, source_aaa) values (4476, 2, 1); insert into RRR (rrr_id, rrr_date, rrr_aaa) values (0, 0, NULL); insert into RRR (rrr_id, rrr_date, rrr_aaa) values (2, 4312, 2); SELECT i.aaa_id, (SELECT sum(CASE WHEN (t.source_aaa == i.aaa_id) THEN 1 ELSE 0 END) FROM TTT t ) AS segfault FROM (SELECT curr.rrr_aaa as aaa_id FROM RRR curr -- you also can comment out the next line -- it causes segfault to happen after one row is outputted INNER JOIN AAA a ON (curr.rrr_aaa = aaa_id) LEFT JOIN RRR r ON (r.rrr_id <> 0 AND r.rrr_date < curr.rrr_date) GROUP BY curr.rrr_id HAVING r.rrr_date IS NULL ) i; drh added on 2012-11-02 12:58:45: The query works prior to [430bb59d798286a] on 2012-04-17 in between versions 3.7.11 and 3.7.12. In release version 3.7.12, 2012-05-14, the script causes a segfault in the query planner. The query planner segfault is recorded by ticket [c2ad16f997ee9c8] and is fixed by check-in [e459c3360ba2543]. After this fix, the query reported by this ticket returns "misuse of aggregate: sum()". That was the result as of version 3.7.13. The current problem (a segfault in the VM while running the query) originates with check-in [d4cd6017c987594] on 2012-08-24, in between version 3.7.13 and 3.7.14. drh added on 2012-11-02 13:51:11: CREATE TABLE t1 ( id1 INTEGER PRIMARY KEY AUTOINCREMENT, value1 INTEGER ); INSERT INTO t1 VALUES(4469,2),(4476,1); CREATE TABLE t2 ( id2 INTEGER PRIMARY KEY AUTOINCREMENT, value2 INTEGER ); INSERT INTO t2 VALUES(0,1),(2,2); SELECT (SELECT sum(value2==xyz) FROM t2) FROM (SELECT curr.value1 as xyz FROM t1 AS curr LEFT JOIN t1 AS other GROUP BY curr.id1); drh added on 2012-11-02 14:43:51: CREATE TABLE t1 ( id1 INTEGER, value1 INTEGER, x1 INTEGER ); INSERT INTO t1 VALUES(4469,2,98),(4469,1,99),(4469,3,97); CREATE TABLE t2 ( value2 INTEGER ); INSERT INTO t2 VALUES(1); pragma vdbe_listing=on; pragma vdbe_trace=on; SELECT (SELECT sum(value2==xyz) FROM t2) FROM (SELECT value1 as xyz, max(x1) AS pqr FROM t1 GROUP BY id1); drh added on 2012-11-02 15:14:42: CREATE TABLE t1(id1, value1); INSERT INTO t1 VALUES(4469,2),(4469,1); CREATE TABLE t2 (value2); INSERT INTO t2 VALUES(1); SELECT (SELECT sum(value2=value1) FROM t2), max(value1) FROM t1 GROUP BY id1; |