| OLD | NEW |
|---|
| (Empty) |
| 1 # 2012 August 23 | |
| 2 # | |
| 3 # The author disclaims copyright to this source code. In place of | |
| 4 # a legal notice, here is a blessing: | |
| 5 # | |
| 6 # May you do good and not evil. | |
| 7 # May you find forgiveness for yourself and forgive others. | |
| 8 # May you share freely, never taking more than you give. | |
| 9 # | |
| 10 #*********************************************************************** | |
| 11 # This file implements regression tests for SQLite library. | |
| 12 # | |
| 13 # This file implements tests for processing aggregate queries with | |
| 14 # subqueries in which the subqueries hold the aggregate functions | |
| 15 # or in which the subqueries are themselves aggregate queries | |
| 16 # | |
| 17 | |
| 18 set testdir [file dirname $argv0] | |
| 19 source $testdir/tester.tcl | |
| 20 | |
| 21 do_test aggnested-1.1 { | |
| 22 db eval { | |
| 23 CREATE TABLE t1(a1 INTEGER); | |
| 24 INSERT INTO t1 VALUES(1), (2), (3); | |
| 25 CREATE TABLE t2(b1 INTEGER); | |
| 26 INSERT INTO t2 VALUES(4), (5); | |
| 27 SELECT (SELECT group_concat(a1,'x') FROM t2) FROM t1; | |
| 28 } | |
| 29 } {1x2x3} | |
| 30 do_test aggnested-1.2 { | |
| 31 db eval { | |
| 32 SELECT | |
| 33 (SELECT group_concat(a1,'x') || '-' || group_concat(b1,'y') FROM t2) | |
| 34 FROM t1; | |
| 35 } | |
| 36 } {1x2x3-4y5} | |
| 37 do_test aggnested-1.3 { | |
| 38 db eval { | |
| 39 SELECT (SELECT group_concat(b1,a1) FROM t2) FROM t1; | |
| 40 } | |
| 41 } {415 425 435} | |
| 42 do_test aggnested-1.4 { | |
| 43 db eval { | |
| 44 SELECT (SELECT group_concat(a1,b1) FROM t2) FROM t1; | |
| 45 } | |
| 46 } {151 252 353} | |
| 47 | |
| 48 | |
| 49 # This test case is a copy of the one in | |
| 50 # http://www.mail-archive.com/sqlite-users@sqlite.org/msg70787.html | |
| 51 # | |
| 52 do_test aggnested-2.0 { | |
| 53 sqlite3 db2 :memory: | |
| 54 db2 eval { | |
| 55 CREATE TABLE t1 (A1 INTEGER NOT NULL,A2 INTEGER NOT NULL,A3 INTEGER NOT | |
| 56 NULL,A4 INTEGER NOT NULL,PRIMARY KEY(A1)); | |
| 57 REPLACE INTO t1 VALUES(1,11,111,1111); | |
| 58 REPLACE INTO t1 VALUES(2,22,222,2222); | |
| 59 REPLACE INTO t1 VALUES(3,33,333,3333); | |
| 60 CREATE TABLE t2 (B1 INTEGER NOT NULL,B2 INTEGER NOT NULL,B3 INTEGER NOT | |
| 61 NULL,B4 INTEGER NOT NULL,PRIMARY KEY(B1)); | |
| 62 REPLACE INTO t2 VALUES(1,88,888,8888); | |
| 63 REPLACE INTO t2 VALUES(2,99,999,9999); | |
| 64 SELECT (SELECT GROUP_CONCAT(CASE WHEN a1=1 THEN'A' ELSE 'B' END) FROM t2), | |
| 65 t1.* | |
| 66 FROM t1; | |
| 67 } | |
| 68 } {A,B,B 3 33 333 3333} | |
| 69 db2 close | |
| 70 | |
| 71 ##################### Test cases for ticket [bfbf38e5e9956ac69f] ############ | |
| 72 # | |
| 73 # This first test case is the original problem report: | |
| 74 do_test aggnested-3.0 { | |
| 75 db eval { | |
| 76 CREATE TABLE AAA ( | |
| 77 aaa_id INTEGER PRIMARY KEY AUTOINCREMENT | |
| 78 ); | |
| 79 CREATE TABLE RRR ( | |
| 80 rrr_id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| 81 rrr_date INTEGER NOT NULL, | |
| 82 rrr_aaa INTEGER | |
| 83 ); | |
| 84 CREATE TABLE TTT ( | |
| 85 ttt_id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| 86 target_aaa INTEGER NOT NULL, | |
| 87 source_aaa INTEGER NOT NULL | |
| 88 ); | |
| 89 insert into AAA (aaa_id) values (2); | |
| 90 insert into TTT (ttt_id, target_aaa, source_aaa) | |
| 91 values (4469, 2, 2); | |
| 92 insert into TTT (ttt_id, target_aaa, source_aaa) | |
| 93 values (4476, 2, 1); | |
| 94 insert into RRR (rrr_id, rrr_date, rrr_aaa) | |
| 95 values (0, 0, NULL); | |
| 96 insert into RRR (rrr_id, rrr_date, rrr_aaa) | |
| 97 values (2, 4312, 2); | |
| 98 SELECT i.aaa_id, | |
| 99 (SELECT sum(CASE WHEN (t.source_aaa == i.aaa_id) THEN 1 ELSE 0 END) | |
| 100 FROM TTT t | |
| 101 ) AS segfault | |
| 102 FROM | |
| 103 (SELECT curr.rrr_aaa as aaa_id | |
| 104 FROM RRR curr | |
| 105 -- you also can comment out the next line | |
| 106 -- it causes segfault to happen after one row is outputted | |
| 107 INNER JOIN AAA a ON (curr.rrr_aaa = aaa_id) | |
| 108 LEFT JOIN RRR r ON (r.rrr_id <> 0 AND r.rrr_date < curr.rrr_date) | |
| 109 GROUP BY curr.rrr_id | |
| 110 HAVING r.rrr_date IS NULL | |
| 111 ) i; | |
| 112 } | |
| 113 } {2 1} | |
| 114 | |
| 115 # Further variants of the test case, as found in the ticket | |
| 116 # | |
| 117 do_test aggnested-3.1 { | |
| 118 db eval { | |
| 119 DROP TABLE IF EXISTS t1; | |
| 120 DROP TABLE IF EXISTS t2; | |
| 121 CREATE TABLE t1 ( | |
| 122 id1 INTEGER PRIMARY KEY AUTOINCREMENT, | |
| 123 value1 INTEGER | |
| 124 ); | |
| 125 INSERT INTO t1 VALUES(4469,2),(4476,1); | |
| 126 CREATE TABLE t2 ( | |
| 127 id2 INTEGER PRIMARY KEY AUTOINCREMENT, | |
| 128 value2 INTEGER | |
| 129 ); | |
| 130 INSERT INTO t2 VALUES(0,1),(2,2); | |
| 131 SELECT | |
| 132 (SELECT sum(value2==xyz) FROM t2) | |
| 133 FROM | |
| 134 (SELECT curr.value1 as xyz | |
| 135 FROM t1 AS curr LEFT JOIN t1 AS other | |
| 136 GROUP BY curr.id1); | |
| 137 } | |
| 138 } {1 1} | |
| 139 do_test aggnested-3.2 { | |
| 140 db eval { | |
| 141 DROP TABLE IF EXISTS t1; | |
| 142 DROP TABLE IF EXISTS t2; | |
| 143 CREATE TABLE t1 ( | |
| 144 id1 INTEGER, | |
| 145 value1 INTEGER, | |
| 146 x1 INTEGER | |
| 147 ); | |
| 148 INSERT INTO t1 VALUES(4469,2,98),(4469,1,99),(4469,3,97); | |
| 149 CREATE TABLE t2 ( | |
| 150 value2 INTEGER | |
| 151 ); | |
| 152 INSERT INTO t2 VALUES(1); | |
| 153 SELECT | |
| 154 (SELECT sum(value2==xyz) FROM t2) | |
| 155 FROM | |
| 156 (SELECT value1 as xyz, max(x1) AS pqr | |
| 157 FROM t1 | |
| 158 GROUP BY id1); | |
| 159 SELECT | |
| 160 (SELECT sum(value2<>xyz) FROM t2) | |
| 161 FROM | |
| 162 (SELECT value1 as xyz, max(x1) AS pqr | |
| 163 FROM t1 | |
| 164 GROUP BY id1); | |
| 165 } | |
| 166 } {1 0} | |
| 167 do_test aggnested-3.3 { | |
| 168 db eval { | |
| 169 DROP TABLE IF EXISTS t1; | |
| 170 DROP TABLE IF EXISTS t2; | |
| 171 CREATE TABLE t1(id1, value1); | |
| 172 INSERT INTO t1 VALUES(4469,2),(4469,1); | |
| 173 CREATE TABLE t2 (value2); | |
| 174 INSERT INTO t2 VALUES(1); | |
| 175 SELECT (SELECT sum(value2=value1) FROM t2), max(value1) | |
| 176 FROM t1 | |
| 177 GROUP BY id1; | |
| 178 } | |
| 179 } {0 2} | |
| 180 | |
| 181 # A batch of queries all doing approximately the same operation involving | |
| 182 # two nested aggregate queries. | |
| 183 # | |
| 184 do_test aggnested-3.11 { | |
| 185 db eval { | |
| 186 DROP TABLE IF EXISTS t1; | |
| 187 DROP TABLE IF EXISTS t2; | |
| 188 CREATE TABLE t1(id1, value1); | |
| 189 INSERT INTO t1 VALUES(4469,12),(4469,11),(4470,34); | |
| 190 CREATE INDEX t1id1 ON t1(id1); | |
| 191 CREATE TABLE t2 (value2); | |
| 192 INSERT INTO t2 VALUES(12),(34),(34); | |
| 193 INSERT INTO t2 SELECT value2 FROM t2; | |
| 194 | |
| 195 SELECT max(value1), (SELECT count(*) FROM t2 WHERE value2=max(value1)) | |
| 196 FROM t1 | |
| 197 GROUP BY id1; | |
| 198 } | |
| 199 } {12 2 34 4} | |
| 200 do_test aggnested-3.12 { | |
| 201 db eval { | |
| 202 SELECT max(value1), (SELECT count(*) FROM t2 WHERE value2=value1) | |
| 203 FROM t1 | |
| 204 GROUP BY id1; | |
| 205 } | |
| 206 } {12 2 34 4} | |
| 207 do_test aggnested-3.13 { | |
| 208 db eval { | |
| 209 SELECT value1, (SELECT sum(value2=value1) FROM t2) | |
| 210 FROM t1; | |
| 211 } | |
| 212 } {12 2 11 0 34 4} | |
| 213 do_test aggnested-3.14 { | |
| 214 db eval { | |
| 215 SELECT value1, (SELECT sum(value2=value1) FROM t2) | |
| 216 FROM t1 | |
| 217 WHERE value1 IN (SELECT max(value1) FROM t1 GROUP BY id1); | |
| 218 } | |
| 219 } {12 2 34 4} | |
| 220 do_test aggnested-3.15 { | |
| 221 # FIXME: If case 3.16 works, then this case really ought to work too... | |
| 222 catchsql { | |
| 223 SELECT max(value1), (SELECT sum(value2=max(value1)) FROM t2) | |
| 224 FROM t1 | |
| 225 GROUP BY id1; | |
| 226 } | |
| 227 } {1 {misuse of aggregate function max()}} | |
| 228 do_test aggnested-3.16 { | |
| 229 db eval { | |
| 230 SELECT max(value1), (SELECT sum(value2=value1) FROM t2) | |
| 231 FROM t1 | |
| 232 GROUP BY id1; | |
| 233 } | |
| 234 } {12 2 34 4} | |
| 235 | |
| 236 | |
| 237 finish_test | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2846743003:
[sql] Remove SQLite 3.10.2 reference directory. (Closed)
