SQLite
View Ticket
Not logged in
Ticket UUID: 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:
Problem history:

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:
Simplified test case:

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:
Here is another simplification of the script that avoids the use of LEFT JOIN and silly constructs like a GROUP BY on a PRIMARY KEY:

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:
An even simpler demonstration that omits the subquery in the FROM clause:

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;