From e0b2d5dabbc73a5fc25f3f314853321c5ea3d978 Mon Sep 17 00:00:00 2001 From: drh Date: Fri, 2 Nov 2012 19:08:31 +0000 Subject: [PATCH] Adding test cases for ticket [bfbf38e5e9956a] FossilOrigin-Name: 62ebfa747635a5593759ea2a28166d054c8eee8a --- manifest | 15 ++--- manifest.uuid | 2 +- test/aggnested.test | 160 ++++++++++++++++++++++++++++++++++++++++++++ 3 files changed, 167 insertions(+), 10 deletions(-) diff --git a/manifest b/manifest index 7cf290f29e..dd1f5d296c 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Proposed\sfix\sfor\sticket\s[bfbf38e5e9]. -D 2012-11-02T18:48:49.928 +C Adding\stest\scases\sfor\sticket\s[bfbf38e5e9956a] +D 2012-11-02T19:08:31.592 F Makefile.arm-wince-mingw32ce-gcc d6df77f1f48d690bd73162294bbba7f59507c72f F Makefile.in 82c41c0ed4cc94dd3cc7d498575b84c57c2c2384 F Makefile.linux-gcc 91d710bdc4998cb015f39edf3cb314ec4f4d7e23 @@ -252,7 +252,7 @@ F src/walker.c 3d75ba73de15e0f8cd0737643badbeb0e002f07b F src/where.c 6a753aa008de6494e64dd265a27afbb0ad80ccf5 F test/8_3_names.test 631ea964a3edb091cf73c3b540f6bcfdb36ce823 F test/aggerror.test a867e273ef9e3d7919f03ef4f0e8c0d2767944f2 -F test/aggnested.test 0be144b453e0622a085fae8665c32f5676708e00 +F test/aggnested.test 45c0201e28045ad38a530b5a144b73cd4aa2cfd6 F test/alias.test 4529fbc152f190268a15f9384a5651bbbabc9d87 F test/all.test 52fc8dee494092031a556911d404ca30a749a30b F test/alter.test 57d96ec9b320bd07af77567034488dcb6642c748 @@ -1021,10 +1021,7 @@ F tool/vdbe-compress.tcl f12c884766bd14277f4fcedcae07078011717381 F tool/warnings-clang.sh f6aa929dc20ef1f856af04a730772f59283631d4 F tool/warnings.sh fbc018d67fd7395f440c28f33ef0f94420226381 F tool/win/sqlite.vsix 97894c2790eda7b5bce3cc79cb2a8ec2fde9b3ac -P 10cc12b16b2f1ae4e6b3a84cef5a44564d282e9c -R 8a1bfe0b9ce6f93a26fa652a9a697188 -T *branch * ticket-bfbf38e5e9 -T *sym-ticket-bfbf38e5e9 * -T -sym-trunk * +P 18ae030dad30303129186738c27cdc4ba06362b9 +R 0bddd84c58415ef799271c8a76e36e44 U drh -Z bedb5a372edd83ae79e50e9ef5149dad +Z d5af1bf3eac39af6d9724e55a6d42186 diff --git a/manifest.uuid b/manifest.uuid index 446670037e..38c8e220ab 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -18ae030dad30303129186738c27cdc4ba06362b9 \ No newline at end of file +62ebfa747635a5593759ea2a28166d054c8eee8a \ No newline at end of file diff --git a/test/aggnested.test b/test/aggnested.test index 22f0fb6b9a..6e2fd6554b 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 -- 2.47.2