diff options
Diffstat (limited to 'test/aggnested.test')
| -rw-r--r-- | test/aggnested.test | 160 |
1 files changed, 160 insertions, 0 deletions
diff --git a/test/aggnested.test b/test/aggnested.test index 22f0fb6..6e2fd65 100644 --- a/test/aggnested.test +++ b/test/aggnested.test @@ -68,4 +68,164 @@ do_test aggnested-2.0 { } {A,B,B 3 33 333 3333} db2 close +##################### Test cases for ticket [bfbf38e5e9956ac69f] ############ +# +# This first test case is the original problem report: +do_test aggnested-3.0 { + db eval { + 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; + } +} {2 1} + +# Further variants of the test case, as found in the ticket +# +do_test aggnested-3.1 { + db eval { + DROP TABLE IF EXISTS t1; + DROP TABLE IF EXISTS t2; + 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); + } +} {1 1} +do_test aggnested-3.2 { + db eval { + DROP TABLE IF EXISTS t1; + DROP TABLE IF EXISTS t2; + 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); + SELECT + (SELECT sum(value2==xyz) FROM t2) + FROM + (SELECT value1 as xyz, max(x1) AS pqr + FROM t1 + GROUP BY id1); + } +} {0} +do_test aggnested-3.3 { + db eval { + DROP TABLE IF EXISTS t1; + DROP TABLE IF EXISTS t2; + 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; + } +} {0 2} + +# A batch of queries all doing approximately the same operation involving +# two nested aggregate queries. +# +do_test aggnested-3.11 { + db eval { + DROP TABLE IF EXISTS t1; + DROP TABLE IF EXISTS t2; + CREATE TABLE t1(id1, value1); + INSERT INTO t1 VALUES(4469,12),(4469,11),(4470,34); + CREATE INDEX t1id1 ON t1(id1); + CREATE TABLE t2 (value2); + INSERT INTO t2 VALUES(12),(34),(34); + INSERT INTO t2 SELECT value2 FROM t2; + + SELECT max(value1), (SELECT count(*) FROM t2 WHERE value2=max(value1)) + FROM t1 + GROUP BY id1; + } +} {12 2 34 4} +do_test aggnested-3.12 { + db eval { + SELECT max(value1), (SELECT count(*) FROM t2 WHERE value2=value1) + FROM t1 + GROUP BY id1; + } +} {12 2 34 4} +do_test aggnested-3.13 { + db eval { + SELECT value1, (SELECT sum(value2=value1) FROM t2) + FROM t1; + } +} {12 2 11 0 34 4} +do_test aggnested-3.14 { + db eval { + SELECT value1, (SELECT sum(value2=value1) FROM t2) + FROM t1 + WHERE value1 IN (SELECT max(value1) FROM t1 GROUP BY id1); + } +} {12 2 34 4} +do_test aggnested-3.15 { + # FIXME: If case 3.16 works, then this case really ought to work too... + catchsql { + SELECT max(value1), (SELECT sum(value2=max(value1)) FROM t2) + FROM t1 + GROUP BY id1; + } +} {1 {misuse of aggregate function max()}} +do_test aggnested-3.16 { + db eval { + SELECT max(value1), (SELECT sum(value2=value1) FROM t2) + FROM t1 + GROUP BY id1; + } +} {12 2 34 4} + + finish_test |