gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/aggnested.test (about) 1 # 2012-08-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 set testprefix aggnested 21 22 do_test aggnested-1.1 { 23 db eval { 24 CREATE TABLE t1(a1 INTEGER); 25 INSERT INTO t1 VALUES(1), (2), (3); 26 CREATE TABLE t2(b1 INTEGER); 27 INSERT INTO t2 VALUES(4), (5); 28 SELECT (SELECT group_concat(a1,'x') FROM t2) FROM t1; 29 } 30 } {1x2x3} 31 do_test aggnested-1.2 { 32 db eval { 33 SELECT 34 (SELECT group_concat(a1,'x') || '-' || group_concat(b1,'y') FROM t2) 35 FROM t1; 36 } 37 } {1x2x3-4y5} 38 do_test aggnested-1.3 { 39 db eval { 40 SELECT (SELECT group_concat(b1,a1) FROM t2) FROM t1; 41 } 42 } {415 425 435} 43 do_test aggnested-1.4 { 44 db eval { 45 SELECT (SELECT group_concat(a1,b1) FROM t2) FROM t1; 46 } 47 } {151 252 353} 48 49 50 # This test case is a copy of the one in 51 # http://www.mail-archive.com/sqlite-users@sqlite.org/msg70787.html 52 # 53 do_test aggnested-2.0 { 54 sqlite3 db2 :memory: 55 db2 eval { 56 CREATE TABLE t1 (A1 INTEGER NOT NULL,A2 INTEGER NOT NULL,A3 INTEGER NOT 57 NULL,A4 INTEGER NOT NULL,PRIMARY KEY(A1)); 58 REPLACE INTO t1 VALUES(1,11,111,1111); 59 REPLACE INTO t1 VALUES(2,22,222,2222); 60 REPLACE INTO t1 VALUES(3,33,333,3333); 61 CREATE TABLE t2 (B1 INTEGER NOT NULL,B2 INTEGER NOT NULL,B3 INTEGER NOT 62 NULL,B4 INTEGER NOT NULL,PRIMARY KEY(B1)); 63 REPLACE INTO t2 VALUES(1,88,888,8888); 64 REPLACE INTO t2 VALUES(2,99,999,9999); 65 SELECT (SELECT GROUP_CONCAT(CASE WHEN a1=1 THEN'A' ELSE 'B' END) FROM t2), 66 t1.* 67 FROM t1; 68 } 69 } {A,B,B 1 11 111 1111} 70 db2 close 71 72 ##################### Test cases for ticket [bfbf38e5e9956ac69f] ############ 73 # 74 # This first test case is the original problem report: 75 do_test aggnested-3.0 { 76 db eval { 77 CREATE TABLE AAA ( 78 aaa_id INTEGER PRIMARY KEY AUTOINCREMENT 79 ); 80 CREATE TABLE RRR ( 81 rrr_id INTEGER PRIMARY KEY AUTOINCREMENT, 82 rrr_date INTEGER NOT NULL, 83 rrr_aaa INTEGER 84 ); 85 CREATE TABLE TTT ( 86 ttt_id INTEGER PRIMARY KEY AUTOINCREMENT, 87 target_aaa INTEGER NOT NULL, 88 source_aaa INTEGER NOT NULL 89 ); 90 insert into AAA (aaa_id) values (2); 91 insert into TTT (ttt_id, target_aaa, source_aaa) 92 values (4469, 2, 2); 93 insert into TTT (ttt_id, target_aaa, source_aaa) 94 values (4476, 2, 1); 95 insert into RRR (rrr_id, rrr_date, rrr_aaa) 96 values (0, 0, NULL); 97 insert into RRR (rrr_id, rrr_date, rrr_aaa) 98 values (2, 4312, 2); 99 SELECT i.aaa_id, 100 (SELECT sum(CASE WHEN (t.source_aaa == i.aaa_id) THEN 1 ELSE 0 END) 101 FROM TTT t 102 ) AS segfault 103 FROM 104 (SELECT curr.rrr_aaa as aaa_id 105 FROM RRR curr 106 -- you also can comment out the next line 107 -- it causes segfault to happen after one row is outputted 108 INNER JOIN AAA a ON (curr.rrr_aaa = aaa_id) 109 LEFT JOIN RRR r ON (r.rrr_id <> 0 AND r.rrr_date < curr.rrr_date) 110 GROUP BY curr.rrr_id 111 HAVING r.rrr_date IS NULL 112 ) i; 113 } 114 } {2 1} 115 116 # Further variants of the test case, as found in the ticket 117 # 118 do_test aggnested-3.1 { 119 db eval { 120 DROP TABLE IF EXISTS t1; 121 DROP TABLE IF EXISTS t2; 122 CREATE TABLE t1 ( 123 id1 INTEGER PRIMARY KEY AUTOINCREMENT, 124 value1 INTEGER 125 ); 126 INSERT INTO t1 VALUES(4469,2),(4476,1); 127 CREATE TABLE t2 ( 128 id2 INTEGER PRIMARY KEY AUTOINCREMENT, 129 value2 INTEGER 130 ); 131 INSERT INTO t2 VALUES(0,1),(2,2); 132 SELECT 133 (SELECT sum(value2==xyz) FROM t2) 134 FROM 135 (SELECT curr.value1 as xyz 136 FROM t1 AS curr LEFT JOIN t1 AS other 137 GROUP BY curr.id1); 138 } 139 } {1 1} 140 do_test aggnested-3.1-rj { 141 db eval { 142 SELECT 143 (SELECT sum(value2==xyz) FROM t2) 144 FROM 145 (SELECT curr.value1 as xyz 146 FROM t1 AS other RIGHT JOIN t1 AS curr 147 GROUP BY curr.id1); 148 } 149 } {1 1} 150 151 do_test aggnested-3.2 { 152 db eval { 153 DROP TABLE IF EXISTS t1; 154 DROP TABLE IF EXISTS t2; 155 CREATE TABLE t1 ( 156 id1 INTEGER, 157 value1 INTEGER, 158 x1 INTEGER 159 ); 160 INSERT INTO t1 VALUES(4469,2,98),(4469,1,99),(4469,3,97); 161 CREATE TABLE t2 ( 162 value2 INTEGER 163 ); 164 INSERT INTO t2 VALUES(1); 165 SELECT 166 (SELECT sum(value2==xyz) FROM t2) 167 FROM 168 (SELECT value1 as xyz, max(x1) AS pqr 169 FROM t1 170 GROUP BY id1); 171 SELECT 172 (SELECT sum(value2<>xyz) FROM t2) 173 FROM 174 (SELECT value1 as xyz, max(x1) AS pqr 175 FROM t1 176 GROUP BY id1); 177 } 178 } {1 0} 179 do_test aggnested-3.3 { 180 db eval { 181 DROP TABLE IF EXISTS t1; 182 DROP TABLE IF EXISTS t2; 183 CREATE TABLE t1(id1, value1); 184 INSERT INTO t1 VALUES(4469,2),(4469,1); 185 CREATE TABLE t2 (value2); 186 INSERT INTO t2 VALUES(1); 187 SELECT (SELECT sum(value2=value1) FROM t2), max(value1) 188 FROM t1 189 GROUP BY id1; 190 } 191 } {0 2} 192 193 # A batch of queries all doing approximately the same operation involving 194 # two nested aggregate queries. 195 # 196 do_test aggnested-3.11 { 197 db eval { 198 DROP TABLE IF EXISTS t1; 199 DROP TABLE IF EXISTS t2; 200 CREATE TABLE t1(id1, value1); 201 INSERT INTO t1 VALUES(4469,12),(4469,11),(4470,34); 202 CREATE INDEX t1id1 ON t1(id1); 203 CREATE TABLE t2 (value2); 204 INSERT INTO t2 VALUES(12),(34),(34); 205 INSERT INTO t2 SELECT value2 FROM t2; 206 207 SELECT max(value1), (SELECT count(*) FROM t2 WHERE value2=max(value1)) 208 FROM t1 209 GROUP BY id1; 210 } 211 } {12 2 34 4} 212 do_test aggnested-3.12 { 213 db eval { 214 SELECT max(value1), (SELECT count(*) FROM t2 WHERE value2=value1) 215 FROM t1 216 GROUP BY id1; 217 } 218 } {12 2 34 4} 219 do_test aggnested-3.13 { 220 db eval { 221 SELECT value1, (SELECT sum(value2=value1) FROM t2) 222 FROM t1; 223 } 224 } {12 2 11 0 34 4} 225 do_test aggnested-3.14 { 226 db eval { 227 SELECT value1, (SELECT sum(value2=value1) FROM t2) 228 FROM t1 229 WHERE value1 IN (SELECT max(value1) FROM t1 GROUP BY id1); 230 } 231 } {12 2 34 4} 232 do_test aggnested-3.15 { 233 # FIXME: If case 3.16 works, then this case really ought to work too... 234 catchsql { 235 SELECT max(value1), (SELECT sum(value2=max(value1)) FROM t2) 236 FROM t1 237 GROUP BY id1; 238 } 239 } {1 {misuse of aggregate function max()}} 240 do_test aggnested-3.16 { 241 db eval { 242 SELECT max(value1), (SELECT sum(value2=value1) FROM t2) 243 FROM t1 244 GROUP BY id1; 245 } 246 } {12 2 34 4} 247 248 # 2019-08-31 249 # Problem found by dbsqlfuzz 250 # 251 do_execsql_test aggnested-4.1 { 252 DROP TABLE IF EXISTS aa; 253 DROP TABLE IF EXISTS bb; 254 CREATE TABLE aa(x INT); INSERT INTO aa(x) VALUES(123); 255 CREATE TABLE bb(y INT); INSERT INTO bb(y) VALUES(456); 256 SELECT (SELECT sum(x+(SELECT y)) FROM bb) FROM aa; 257 } {579} 258 do_execsql_test aggnested-4.2 { 259 SELECT (SELECT sum(x+y) FROM bb) FROM aa; 260 } {579} 261 do_execsql_test aggnested-4.3 { 262 DROP TABLE IF EXISTS tx; 263 DROP TABLE IF EXISTS ty; 264 CREATE TABLE tx(x INT); 265 INSERT INTO tx VALUES(1),(2),(3),(4),(5); 266 CREATE TABLE ty(y INT); 267 INSERT INTO ty VALUES(91),(92),(93); 268 SELECT min((SELECT count(y) FROM ty)) FROM tx; 269 } {3} 270 do_execsql_test aggnested-4.4 { 271 SELECT max((SELECT a FROM (SELECT count(*) AS a FROM ty) AS s)) FROM tx; 272 } {3} 273 274 #-------------------------------------------------------------------------- 275 # 276 reset_db 277 do_execsql_test 5.0 { 278 CREATE TABLE x1(a, b); 279 INSERT INTO x1 VALUES(1, 2); 280 CREATE TABLE x2(x); 281 INSERT INTO x2 VALUES(NULL), (NULL), (NULL); 282 } 283 284 # At one point, aggregate "total()" in the query below was being processed 285 # as part of the outer SELECT, not as part of the sub-select with no FROM 286 # clause. 287 do_execsql_test 5.1 { 288 SELECT ( SELECT total( (SELECT b FROM x1) ) ) FROM x2; 289 } {2.0 2.0 2.0} 290 291 do_execsql_test 5.2 { 292 SELECT ( SELECT total( (SELECT 2 FROM x1) ) ) FROM x2; 293 } {2.0 2.0 2.0} 294 295 do_execsql_test 5.3 { 296 CREATE TABLE t1(a); 297 CREATE TABLE t2(b); 298 } 299 300 do_execsql_test 5.4 { 301 SELECT( 302 SELECT max(b) LIMIT ( 303 SELECT total( (SELECT a FROM t1) ) 304 ) 305 ) 306 FROM t2; 307 } {{}} 308 309 do_execsql_test 5.5 { 310 CREATE TABLE a(b); 311 WITH c AS(SELECT a) 312 SELECT(SELECT(SELECT group_concat(b, b) 313 LIMIT(SELECT 0.100000 * 314 AVG(DISTINCT(SELECT 0 FROM a ORDER BY b, b, b)))) 315 FROM a GROUP BY b, 316 b, b) FROM a EXCEPT SELECT b FROM a ORDER BY b, 317 b, b; 318 } 319 320 #------------------------------------------------------------------------- 321 # dbsqlfuzz a779227f721a834df95f4f42d0c31550a1f8b8a2 322 # 323 reset_db 324 do_execsql_test 6.0 { 325 CREATE TABLE t1(a); 326 CREATE TABLE t2(b); 327 328 INSERT INTO t1 VALUES('x'); 329 INSERT INTO t2 VALUES(1); 330 } 331 332 do_execsql_test 6.1.1 { 333 SELECT ( 334 SELECT t2.b FROM (SELECT t2.b AS c FROM t1) GROUP BY 1 HAVING t2.b 335 ) 336 FROM t2 GROUP BY 'constant_string'; 337 } {1} 338 do_execsql_test 6.1.2 { 339 SELECT ( 340 SELECT c FROM (SELECT t2.b AS c FROM t1) GROUP BY c HAVING t2.b 341 ) 342 FROM t2 GROUP BY 'constant_string'; 343 } {1} 344 345 do_execsql_test 6.2.0 { 346 UPDATE t2 SET b=0 347 } 348 do_execsql_test 6.2.1 { 349 SELECT ( 350 SELECT t2.b FROM (SELECT t2.b AS c FROM t1) GROUP BY 1 HAVING t2.b 351 ) 352 FROM t2 GROUP BY 'constant_string'; 353 } {{}} 354 do_execsql_test 6.2.2 { 355 SELECT ( 356 SELECT c FROM (SELECT t2.b AS c FROM t1) GROUP BY c HAVING t2.b 357 ) 358 FROM t2 GROUP BY 'constant_string'; 359 } {{}} 360 361 362 363 364 365 finish_test