gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/subquery.test (about) 1 # 2005 January 19 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. The 12 # focus of this script is testing correlated subqueries 13 # 14 # $Id: subquery.test,v 1.17 2009/01/09 01:12:28 drh Exp $ 15 # 16 17 set testdir [file dirname $argv0] 18 source $testdir/tester.tcl 19 20 ifcapable !subquery { 21 finish_test 22 return 23 } 24 25 do_test subquery-1.1 { 26 execsql { 27 BEGIN; 28 CREATE TABLE t1(a,b); 29 INSERT INTO t1 VALUES(1,2); 30 INSERT INTO t1 VALUES(3,4); 31 INSERT INTO t1 VALUES(5,6); 32 INSERT INTO t1 VALUES(7,8); 33 CREATE TABLE t2(x,y); 34 INSERT INTO t2 VALUES(1,1); 35 INSERT INTO t2 VALUES(3,9); 36 INSERT INTO t2 VALUES(5,25); 37 INSERT INTO t2 VALUES(7,49); 38 COMMIT; 39 } 40 execsql { 41 SELECT a, (SELECT y FROM t2 WHERE x=a) FROM t1 WHERE b<8 42 } 43 } {1 1 3 9 5 25} 44 do_test subquery-1.2 { 45 execsql { 46 UPDATE t1 SET b=b+(SELECT y FROM t2 WHERE x=a); 47 SELECT * FROM t1; 48 } 49 } {1 3 3 13 5 31 7 57} 50 51 do_test subquery-1.3 { 52 execsql { 53 SELECT b FROM t1 WHERE EXISTS(SELECT * FROM t2 WHERE y=a) 54 } 55 } {3} 56 do_test subquery-1.4 { 57 execsql { 58 SELECT b FROM t1 WHERE NOT EXISTS(SELECT * FROM t2 WHERE y=a) 59 } 60 } {13 31 57} 61 62 # Simple tests to make sure correlated subqueries in WHERE clauses 63 # are used by the query optimizer correctly. 64 do_test subquery-1.5 { 65 execsql { 66 SELECT a, x FROM t1, t2 WHERE t1.a = (SELECT x); 67 } 68 } {1 1 3 3 5 5 7 7} 69 do_test subquery-1.6 { 70 execsql { 71 CREATE INDEX i1 ON t1(a); 72 SELECT a, x FROM t1, t2 WHERE t1.a = (SELECT x); 73 } 74 } {1 1 3 3 5 5 7 7} 75 do_test subquery-1.7 { 76 execsql { 77 SELECT a, x FROM t2, t1 WHERE t1.a = (SELECT x); 78 } 79 } {1 1 3 3 5 5 7 7} 80 81 # Try an aggregate in both the subquery and the parent query. 82 do_test subquery-1.8 { 83 execsql { 84 SELECT count(*) FROM t1 WHERE a > (SELECT count(*) FROM t2); 85 } 86 } {2} 87 88 # Test a correlated subquery disables the "only open the index" optimization. 89 do_test subquery-1.9.1 { 90 execsql { 91 SELECT (y*2)>b FROM t1, t2 WHERE a=x; 92 } 93 } {0 1 1 1} 94 do_test subquery-1.9.2 { 95 execsql { 96 SELECT a FROM t1 WHERE (SELECT (y*2)>b FROM t2 WHERE a=x); 97 } 98 } {3 5 7} 99 100 # Test that the flattening optimization works with subquery expressions. 101 do_test subquery-1.10.1 { 102 execsql { 103 SELECT (SELECT a), b FROM t1; 104 } 105 } {1 3 3 13 5 31 7 57} 106 do_test subquery-1.10.2 { 107 execsql { 108 SELECT * FROM (SELECT (SELECT a), b FROM t1); 109 } 110 } {1 3 3 13 5 31 7 57} 111 do_test subquery-1.10.3 { 112 execsql { 113 SELECT * FROM (SELECT (SELECT sum(a) FROM t1)); 114 } 115 } {16} 116 do_test subquery-1.10.4 { 117 execsql { 118 CREATE TABLE t5 (val int, period text PRIMARY KEY); 119 INSERT INTO t5 VALUES(5, '2001-3'); 120 INSERT INTO t5 VALUES(10, '2001-4'); 121 INSERT INTO t5 VALUES(15, '2002-1'); 122 INSERT INTO t5 VALUES(5, '2002-2'); 123 INSERT INTO t5 VALUES(10, '2002-3'); 124 INSERT INTO t5 VALUES(15, '2002-4'); 125 INSERT INTO t5 VALUES(10, '2003-1'); 126 INSERT INTO t5 VALUES(5, '2003-2'); 127 INSERT INTO t5 VALUES(25, '2003-3'); 128 INSERT INTO t5 VALUES(5, '2003-4'); 129 130 SELECT period, vsum 131 FROM (SELECT 132 a.period, 133 (select sum(val) from t5 where period between a.period and '2002-4') vsum 134 FROM t5 a where a.period between '2002-1' and '2002-4') 135 WHERE vsum < 45 ; 136 } 137 } {2002-2 30 2002-3 25 2002-4 15} 138 do_test subquery-1.10.5 { 139 execsql { 140 SELECT period, vsum from 141 (select a.period, 142 (select sum(val) from t5 where period between a.period and '2002-4') vsum 143 FROM t5 a where a.period between '2002-1' and '2002-4') 144 WHERE vsum < 45 ; 145 } 146 } {2002-2 30 2002-3 25 2002-4 15} 147 do_test subquery-1.10.6 { 148 execsql { 149 DROP TABLE t5; 150 } 151 } {} 152 153 154 155 #------------------------------------------------------------------ 156 # The following test cases - subquery-2.* - are not logically 157 # organized. They're here largely because they were failing during 158 # one stage of development of sub-queries. 159 # 160 do_test subquery-2.1 { 161 execsql { 162 SELECT (SELECT 10); 163 } 164 } {10} 165 do_test subquery-2.2.1 { 166 execsql { 167 CREATE TABLE t3(a PRIMARY KEY, b); 168 INSERT INTO t3 VALUES(1, 2); 169 INSERT INTO t3 VALUES(3, 1); 170 } 171 } {} 172 do_test subquery-2.2.2 { 173 execsql { 174 SELECT * FROM t3 WHERE a IN (SELECT b FROM t3); 175 } 176 } {1 2} 177 do_test subquery-2.2.3 { 178 execsql { 179 DROP TABLE t3; 180 } 181 } {} 182 do_test subquery-2.3.1 { 183 execsql { 184 CREATE TABLE t3(a TEXT); 185 INSERT INTO t3 VALUES('10'); 186 } 187 } {} 188 do_test subquery-2.3.2 { 189 execsql { 190 SELECT a IN (10.0, 20) FROM t3; 191 } 192 } {0} 193 do_test subquery-2.3.3 { 194 execsql { 195 DROP TABLE t3; 196 } 197 } {} 198 do_test subquery-2.4.1 { 199 execsql { 200 CREATE TABLE t3(a TEXT); 201 INSERT INTO t3 VALUES('XX'); 202 } 203 } {} 204 do_test subquery-2.4.2 { 205 execsql { 206 SELECT count(*) FROM t3 WHERE a IN (SELECT 'XX') 207 } 208 } {1} 209 do_test subquery-2.4.3 { 210 execsql { 211 DROP TABLE t3; 212 } 213 } {} 214 do_test subquery-2.5.1 { 215 execsql { 216 CREATE TABLE t3(a INTEGER); 217 INSERT INTO t3 VALUES(10); 218 219 CREATE TABLE t4(x TEXT); 220 INSERT INTO t4 VALUES('10.0'); 221 } 222 } {} 223 do_test subquery-2.5.2 { 224 # In the expr "x IN (SELECT a FROM t3)" the RHS of the IN operator 225 # has text affinity and the LHS has integer affinity. The rule is 226 # that we try to convert both sides to an integer before doing the 227 # comparision. Hence, the integer value 10 in t3 will compare equal 228 # to the string value '10.0' in t4 because the t4 value will be 229 # converted into an integer. 230 execsql { 231 SELECT * FROM t4 WHERE x IN (SELECT a FROM t3); 232 } 233 } {10.0} 234 do_test subquery-2.5.3.1 { 235 # The t4i index cannot be used to resolve the "x IN (...)" constraint 236 # because the constraint has integer affinity but t4i has text affinity. 237 execsql { 238 CREATE INDEX t4i ON t4(x); 239 SELECT * FROM t4 WHERE x IN (SELECT a FROM t3); 240 } 241 } {10.0} 242 do_test subquery-2.5.3.2 { 243 # Verify that the t4i index was not used in the previous query 244 execsql { 245 EXPLAIN QUERY PLAN 246 SELECT * FROM t4 WHERE x IN (SELECT a FROM t3); 247 } 248 } {~/t4i/} 249 do_test subquery-2.5.4 { 250 execsql { 251 DROP TABLE t3; 252 DROP TABLE t4; 253 } 254 } {} 255 256 #------------------------------------------------------------------ 257 # The following test cases - subquery-3.* - test tickets that 258 # were raised during development of correlated subqueries. 259 # 260 261 # Ticket 1083 262 ifcapable view { 263 do_test subquery-3.1 { 264 catchsql { DROP TABLE t1; } 265 catchsql { DROP TABLE t2; } 266 execsql { 267 CREATE TABLE t1(a,b); 268 INSERT INTO t1 VALUES(1,2); 269 CREATE VIEW v1 AS SELECT b FROM t1 WHERE a>0; 270 CREATE TABLE t2(p,q); 271 INSERT INTO t2 VALUES(2,9); 272 SELECT * FROM v1 WHERE EXISTS(SELECT * FROM t2 WHERE p=v1.b); 273 } 274 } {2} 275 do_test subquery-3.1.1 { 276 execsql { 277 SELECT * FROM v1 WHERE EXISTS(SELECT 1); 278 } 279 } {2} 280 } else { 281 catchsql { DROP TABLE t1; } 282 catchsql { DROP TABLE t2; } 283 execsql { 284 CREATE TABLE t1(a,b); 285 INSERT INTO t1 VALUES(1,2); 286 CREATE TABLE t2(p,q); 287 INSERT INTO t2 VALUES(2,9); 288 } 289 } 290 291 # Ticket 1084 292 do_test subquery-3.2 { 293 catchsql { 294 CREATE TABLE t1(a,b); 295 INSERT INTO t1 VALUES(1,2); 296 } 297 execsql { 298 SELECT (SELECT t1.a) FROM t1; 299 } 300 } {1} 301 302 # Test Cases subquery-3.3.* test correlated subqueries where the 303 # parent query is an aggregate query. Ticket #1105 is an example 304 # of such a query. 305 # 306 do_test subquery-3.3.1 { 307 execsql { 308 SELECT a, (SELECT b) FROM t1 GROUP BY a; 309 } 310 } {1 2} 311 do_test subquery-3.3.2 { 312 catchsql {DROP TABLE t2} 313 execsql { 314 CREATE TABLE t2(c, d); 315 INSERT INTO t2 VALUES(1, 'one'); 316 INSERT INTO t2 VALUES(2, 'two'); 317 SELECT a, (SELECT d FROM t2 WHERE a=c) FROM t1 GROUP BY a; 318 } 319 } {1 one} 320 do_test subquery-3.3.3 { 321 execsql { 322 INSERT INTO t1 VALUES(2, 4); 323 SELECT max(a), (SELECT d FROM t2 WHERE a=c) FROM t1; 324 } 325 } {2 two} 326 do_test subquery-3.3.4 { 327 execsql { 328 SELECT a, (SELECT (SELECT d FROM t2 WHERE a=c)) FROM t1 GROUP BY a; 329 } 330 } {1 one 2 two} 331 do_test subquery-3.3.5 { 332 execsql { 333 SELECT a, (SELECT count(*) FROM t2 WHERE a=c) FROM t1; 334 } 335 } {1 1 2 1} 336 337 # The following tests check for aggregate subqueries in an aggregate 338 # query. 339 # 340 do_test subquery-3.4.1 { 341 execsql { 342 CREATE TABLE t34(x,y); 343 INSERT INTO t34 VALUES(106,4), (107,3), (106,5), (107,5); 344 SELECT a.x, avg(a.y) 345 FROM t34 AS a 346 GROUP BY a.x 347 HAVING NOT EXISTS( SELECT b.x, avg(b.y) 348 FROM t34 AS b 349 GROUP BY b.x 350 HAVING avg(a.y) > avg(b.y)); 351 } 352 } {107 4.0} 353 do_test subquery-3.4.2 { 354 execsql { 355 SELECT a.x, avg(a.y) AS avg1 356 FROM t34 AS a 357 GROUP BY a.x 358 HAVING NOT EXISTS( SELECT b.x, avg(b.y) AS avg2 359 FROM t34 AS b 360 GROUP BY b.x 361 HAVING avg1 > avg2); 362 } 363 } {107 4.0} 364 do_test subquery-3.4.3 { 365 execsql { 366 SELECT 367 a.x, 368 avg(a.y), 369 NOT EXISTS ( SELECT b.x, avg(b.y) 370 FROM t34 AS b 371 GROUP BY b.x 372 HAVING avg(a.y) > avg(b.y)), 373 EXISTS ( SELECT c.x, avg(c.y) 374 FROM t34 AS c 375 GROUP BY c.x 376 HAVING avg(a.y) > avg(c.y)) 377 FROM t34 AS a 378 GROUP BY a.x 379 ORDER BY a.x; 380 } 381 } {106 4.5 0 1 107 4.0 1 0} 382 383 do_test subquery-3.5.1 { 384 execsql { 385 CREATE TABLE t35a(x); INSERT INTO t35a VALUES(1),(2),(3); 386 CREATE TABLE t35b(y); INSERT INTO t35b VALUES(98), (99); 387 SELECT max((SELECT avg(y) FROM t35b)) FROM t35a; 388 } 389 } {98.5} 390 do_test subquery-3.5.2 { 391 execsql { 392 SELECT max((SELECT count(y) FROM t35b)) FROM t35a; 393 } 394 } {2} 395 do_test subquery-3.5.3 { 396 execsql { 397 SELECT max((SELECT count() FROM t35b)) FROM t35a; 398 } 399 } {2} 400 do_test subquery-3.5.4 { 401 catchsql { 402 SELECT max((SELECT count(x) FROM t35b)) FROM t35a; 403 } 404 } {1 {misuse of aggregate: count()}} 405 do_test subquery-3.5.5 { 406 catchsql { 407 SELECT max((SELECT count(x) FROM t35b)) FROM t35a; 408 } 409 } {1 {misuse of aggregate: count()}} 410 do_test subquery-3.5.6 { 411 catchsql { 412 SELECT max((SELECT a FROM (SELECT count(x) AS a FROM t35b))) FROM t35a; 413 } 414 } {1 {misuse of aggregate: count()}} 415 do_test subquery-3.5.7 { 416 execsql { 417 SELECT max((SELECT a FROM (SELECT count(y) AS a FROM t35b))) FROM t35a; 418 } 419 } {2} 420 421 422 #------------------------------------------------------------------ 423 # These tests - subquery-4.* - use the TCL statement cache to try 424 # and expose bugs to do with re-using statements that have been 425 # passed to sqlite3_reset(). 426 # 427 # One problem was that VDBE memory cells were not being initialized 428 # to NULL on the second and subsequent executions. 429 # 430 do_test subquery-4.1.1 { 431 execsql { 432 SELECT (SELECT a FROM t1); 433 } 434 } {1} 435 do_test subquery-4.2 { 436 execsql { 437 DELETE FROM t1; 438 SELECT (SELECT a FROM t1); 439 } 440 } {{}} 441 do_test subquery-4.2.1 { 442 execsql { 443 CREATE TABLE t3(a PRIMARY KEY); 444 INSERT INTO t3 VALUES(10); 445 } 446 execsql {INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1)} 447 } {} 448 do_test subquery-4.2.2 { 449 execsql {INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1)} 450 } {} 451 452 #------------------------------------------------------------------ 453 # The subquery-5.* tests make sure string literals in double-quotes 454 # are handled efficiently. Double-quote literals are first checked 455 # to see if they match any column names. If there is not column name 456 # match then those literals are used a string constants. When a 457 # double-quoted string appears, we want to make sure that the search 458 # for a matching column name did not cause an otherwise static subquery 459 # to become a dynamic (correlated) subquery. 460 # 461 do_test subquery-5.1 { 462 proc callcntproc {n} { 463 incr ::callcnt 464 return $n 465 } 466 set callcnt 0 467 db function callcnt callcntproc 468 execsql { 469 CREATE TABLE t4(x,y); 470 INSERT INTO t4 VALUES('one',1); 471 INSERT INTO t4 VALUES('two',2); 472 INSERT INTO t4 VALUES('three',3); 473 INSERT INTO t4 VALUES('four',4); 474 CREATE TABLE t5(a,b); 475 INSERT INTO t5 VALUES(1,11); 476 INSERT INTO t5 VALUES(2,22); 477 INSERT INTO t5 VALUES(3,33); 478 INSERT INTO t5 VALUES(4,44); 479 SELECT b FROM t5 WHERE a IN 480 (SELECT callcnt(y)+0 FROM t4 WHERE x='two') 481 } 482 } {22} 483 do_test subquery-5.2 { 484 # This is the key test. The subquery should have only run once. If 485 # The double-quoted identifier "two" were causing the subquery to be 486 # processed as a correlated subquery, then it would have run 4 times. 487 set callcnt 488 } {1} 489 490 491 # Ticket #1380. Make sure correlated subqueries on an IN clause work 492 # correctly when the left-hand side of the IN operator is constant. 493 # 494 do_test subquery-6.1 { 495 set callcnt 0 496 execsql { 497 SELECT x FROM t4 WHERE 1 IN (SELECT callcnt(count(*)) FROM t5 WHERE a=y) 498 } 499 } {one two three four} 500 do_test subquery-6.2 { 501 set callcnt 502 } {4} 503 do_test subquery-6.3 { 504 set callcnt 0 505 execsql { 506 SELECT x FROM t4 WHERE 1 IN (SELECT callcnt(count(*)) FROM t5 WHERE a=1) 507 } 508 } {one two three four} 509 do_test subquery-6.4 { 510 set callcnt 511 } {1} 512 513 if 0 { ############# disable until we get #2652 fixed 514 # Ticket #2652. Allow aggregate functions of outer queries inside 515 # a non-aggregate subquery. 516 # 517 do_test subquery-7.1 { 518 execsql { 519 CREATE TABLE t7(c7); 520 INSERT INTO t7 VALUES(1); 521 INSERT INTO t7 VALUES(2); 522 INSERT INTO t7 VALUES(3); 523 CREATE TABLE t8(c8); 524 INSERT INTO t8 VALUES(100); 525 INSERT INTO t8 VALUES(200); 526 INSERT INTO t8 VALUES(300); 527 CREATE TABLE t9(c9); 528 INSERT INTO t9 VALUES(10000); 529 INSERT INTO t9 VALUES(20000); 530 INSERT INTO t9 VALUES(30000); 531 532 SELECT (SELECT c7+c8 FROM t7) FROM t8; 533 } 534 } {101 201 301} 535 do_test subquery-7.2 { 536 execsql { 537 SELECT (SELECT max(c7)+c8 FROM t7) FROM t8; 538 } 539 } {103 203 303} 540 do_test subquery-7.3 { 541 execsql { 542 SELECT (SELECT c7+max(c8) FROM t8) FROM t7 543 } 544 } {301} 545 do_test subquery-7.4 { 546 execsql { 547 SELECT (SELECT max(c7)+max(c8) FROM t8) FROM t7 548 } 549 } {303} 550 do_test subquery-7.5 { 551 execsql { 552 SELECT (SELECT c8 FROM t8 WHERE rowid=max(c7)) FROM t7 553 } 554 } {300} 555 do_test subquery-7.6 { 556 execsql { 557 SELECT (SELECT (SELECT max(c7+c8+c9) FROM t9) FROM t8) FROM t7 558 } 559 } {30101 30102 30103} 560 do_test subquery-7.7 { 561 execsql { 562 SELECT (SELECT (SELECT c7+max(c8+c9) FROM t9) FROM t8) FROM t7 563 } 564 } {30101 30102 30103} 565 do_test subquery-7.8 { 566 execsql { 567 SELECT (SELECT (SELECT max(c7)+c8+c9 FROM t9) FROM t8) FROM t7 568 } 569 } {10103} 570 do_test subquery-7.9 { 571 execsql { 572 SELECT (SELECT (SELECT c7+max(c8)+c9 FROM t9) FROM t8) FROM t7 573 } 574 } {10301 10302 10303} 575 do_test subquery-7.10 { 576 execsql { 577 SELECT (SELECT (SELECT c7+c8+max(c9) FROM t9) FROM t8) FROM t7 578 } 579 } {30101 30102 30103} 580 do_test subquery-7.11 { 581 execsql { 582 SELECT (SELECT (SELECT max(c7)+max(c8)+max(c9) FROM t9) FROM t8) FROM t7 583 } 584 } {30303} 585 } ;############# Disabled 586 587 # 2015-04-21. 588 # Verify that a memory leak in the table column type and collation analysis 589 # is plugged. 590 # 591 do_execsql_test subquery-8.1 { 592 CREATE TABLE t8(a TEXT, b INT); 593 SELECT (SELECT 0 FROM (SELECT * FROM t1)) AS x WHERE x; 594 SELECT (SELECT 0 FROM (SELECT * FROM (SELECT 0))) AS x WHERE x; 595 } {} 596 597 # 2022-01-12 https://sqlite.org/forum/forumpost/0ec80f12d02acb3f 598 # 599 reset_db 600 do_execsql_test subquery-9.1 { 601 CREATE TABLE t1(x); 602 INSERT INTO t1 VALUES(1),(1),(1); 603 SELECT (SELECT DISTINCT x FROM t1 ORDER BY +x LIMIT 1 OFFSET 100) FROM t1; 604 } {{} {} {}} 605 do_execsql_test subquery-9.2 { 606 SELECT (SELECT DISTINCT x FROM t1 ORDER BY +x LIMIT 1 OFFSET 0) FROM t1; 607 } {1 1 1} 608 do_execsql_test subquery-9.3 { 609 INSERT INTO t1 VALUES(2); 610 SELECT (SELECT DISTINCT x FROM t1 ORDER BY +x LIMIT 1 OFFSET 1) FROM t1; 611 } {2 2 2 2} 612 do_execsql_test subquery-9.4 { 613 SELECT (SELECT DISTINCT x FROM t1 ORDER BY +x LIMIT 1 OFFSET 2) FROM t1; 614 } {{} {} {} {}} 615 616 finish_test