Reported on the mailing list:
<blockquote><pre>
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;
</pre></blockquote>
<hr><i>drh added on 2012-11-02 12:58:45:</i><br>
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.
<hr><i>drh added on 2012-11-02 13:51:11:</i><br>
Simplified test case:
<blockquote><pre>
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);
</pre></blockquote>
<hr><i>drh added on 2012-11-02 14:43:51:</i><br>
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:
<blockquote><pre>
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);
</pre></blockquote>
<hr><i>drh added on 2012-11-02 15:14:42:</i><br>
An even simpler demonstration that omits the subquery in the FROM clause:
<blockquote><pre>
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;
</pre></blockquote>
|