1# 2010 November 6 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# 12 13set testdir [file dirname $argv0] 14source $testdir/tester.tcl 15 16ifcapable !compound { 17 finish_test 18 return 19} 20 21set testprefix eqp 22 23#------------------------------------------------------------------------- 24# 25# eqp-1.*: Assorted tests. 26# eqp-2.*: Tests for single select statements. 27# eqp-3.*: Select statements that execute sub-selects. 28# eqp-4.*: Compound select statements. 29# ... 30# eqp-7.*: "SELECT count(*) FROM tbl" statements (VDBE code OP_Count). 31# 32 33proc det {args} { uplevel do_eqp_test $args } 34 35do_execsql_test 1.1 { 36 CREATE TABLE t1(a INT, b INT, ex TEXT); 37 CREATE INDEX i1 ON t1(a); 38 CREATE INDEX i2 ON t1(b); 39 CREATE TABLE t2(a INT, b INT, ex TEXT); 40 CREATE TABLE t3(a INT, b INT, ex TEXT); 41} 42 43do_eqp_test 1.2 { 44 SELECT * FROM t2, t1 WHERE t1.a=1 OR t1.b=2; 45} { 46 QUERY PLAN 47 |--MULTI-INDEX OR 48 | |--INDEX 1 49 | | `--SEARCH TABLE t1 USING INDEX i1 (a=?) 50 | `--INDEX 2 51 | `--SEARCH TABLE t1 USING INDEX i2 (b=?) 52 `--SCAN TABLE t2 53} 54do_eqp_test 1.3 { 55 SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=1 OR t1.b=2; 56} { 57 QUERY PLAN 58 |--SCAN TABLE t2 59 `--MULTI-INDEX OR 60 |--INDEX 1 61 | `--SEARCH TABLE t1 USING INDEX i1 (a=?) 62 `--INDEX 2 63 `--SEARCH TABLE t1 USING INDEX i2 (b=?) 64} 65do_eqp_test 1.3 { 66 SELECT a FROM t1 ORDER BY a 67} { 68 QUERY PLAN 69 `--SCAN TABLE t1 USING COVERING INDEX i1 70} 71do_eqp_test 1.4 { 72 SELECT a FROM t1 ORDER BY +a 73} { 74 QUERY PLAN 75 |--SCAN TABLE t1 USING COVERING INDEX i1 76 `--USE TEMP B-TREE FOR ORDER BY 77} 78do_eqp_test 1.5 { 79 SELECT a FROM t1 WHERE a=4 80} { 81 QUERY PLAN 82 `--SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) 83} 84do_eqp_test 1.6 { 85 SELECT DISTINCT count(*) FROM t3 GROUP BY a; 86} { 87 QUERY PLAN 88 |--SCAN TABLE t3 89 |--USE TEMP B-TREE FOR GROUP BY 90 `--USE TEMP B-TREE FOR DISTINCT 91} 92 93do_eqp_test 1.7 { 94 SELECT * FROM t3 JOIN (SELECT 1) 95} { 96 QUERY PLAN 97 |--MATERIALIZE xxxxxx 98 | `--SCAN CONSTANT ROW 99 |--SCAN SUBQUERY xxxxxx 100 `--SCAN TABLE t3 101} 102do_eqp_test 1.8 { 103 SELECT * FROM t3 JOIN (SELECT 1 UNION SELECT 2) 104} { 105 QUERY PLAN 106 |--MATERIALIZE xxxxxx 107 | `--COMPOUND QUERY 108 | |--LEFT-MOST SUBQUERY 109 | | `--SCAN CONSTANT ROW 110 | `--UNION USING TEMP B-TREE 111 | `--SCAN CONSTANT ROW 112 |--SCAN SUBQUERY xxxxxx 113 `--SCAN TABLE t3 114} 115do_eqp_test 1.9 { 116 SELECT * FROM t3 JOIN (SELECT 1 EXCEPT SELECT a FROM t3 LIMIT 17) 117} { 118 QUERY PLAN 119 |--MATERIALIZE xxxxxx 120 | `--COMPOUND QUERY 121 | |--LEFT-MOST SUBQUERY 122 | | `--SCAN CONSTANT ROW 123 | `--EXCEPT USING TEMP B-TREE 124 | `--SCAN TABLE t3 125 |--SCAN SUBQUERY xxxxxx 126 `--SCAN TABLE t3 127} 128do_eqp_test 1.10 { 129 SELECT * FROM t3 JOIN (SELECT 1 INTERSECT SELECT a FROM t3 LIMIT 17) 130} { 131 QUERY PLAN 132 |--MATERIALIZE xxxxxx 133 | `--COMPOUND QUERY 134 | |--LEFT-MOST SUBQUERY 135 | | `--SCAN CONSTANT ROW 136 | `--INTERSECT USING TEMP B-TREE 137 | `--SCAN TABLE t3 138 |--SCAN SUBQUERY xxxxxx 139 `--SCAN TABLE t3 140} 141 142do_eqp_test 1.11 { 143 SELECT * FROM t3 JOIN (SELECT 1 UNION ALL SELECT a FROM t3 LIMIT 17) 144} { 145 QUERY PLAN 146 |--MATERIALIZE xxxxxx 147 | `--COMPOUND QUERY 148 | |--LEFT-MOST SUBQUERY 149 | | `--SCAN CONSTANT ROW 150 | `--UNION ALL 151 | `--SCAN TABLE t3 152 |--SCAN SUBQUERY xxxxxx 153 `--SCAN TABLE t3 154} 155 156#------------------------------------------------------------------------- 157# Test cases eqp-2.* - tests for single select statements. 158# 159drop_all_tables 160do_execsql_test 2.1 { 161 CREATE TABLE t1(x INT, y INT, ex TEXT); 162 163 CREATE TABLE t2(x INT, y INT, ex TEXT); 164 CREATE INDEX t2i1 ON t2(x); 165} 166 167det 2.2.1 "SELECT DISTINCT min(x), max(x) FROM t1 GROUP BY x ORDER BY 1" { 168 QUERY PLAN 169 |--SCAN TABLE t1 170 |--USE TEMP B-TREE FOR GROUP BY 171 |--USE TEMP B-TREE FOR DISTINCT 172 `--USE TEMP B-TREE FOR ORDER BY 173} 174det 2.2.2 "SELECT DISTINCT min(x), max(x) FROM t2 GROUP BY x ORDER BY 1" { 175 QUERY PLAN 176 |--SCAN TABLE t2 USING COVERING INDEX t2i1 177 |--USE TEMP B-TREE FOR DISTINCT 178 `--USE TEMP B-TREE FOR ORDER BY 179} 180det 2.2.3 "SELECT DISTINCT * FROM t1" { 181 QUERY PLAN 182 |--SCAN TABLE t1 183 `--USE TEMP B-TREE FOR DISTINCT 184} 185det 2.2.4 "SELECT DISTINCT * FROM t1, t2" { 186 QUERY PLAN 187 |--SCAN TABLE t1 188 |--SCAN TABLE t2 189 `--USE TEMP B-TREE FOR DISTINCT 190} 191det 2.2.5 "SELECT DISTINCT * FROM t1, t2 ORDER BY t1.x" { 192 QUERY PLAN 193 |--SCAN TABLE t1 194 |--SCAN TABLE t2 195 |--USE TEMP B-TREE FOR DISTINCT 196 `--USE TEMP B-TREE FOR ORDER BY 197} 198det 2.2.6 "SELECT DISTINCT t2.x FROM t1, t2 ORDER BY t2.x" { 199 QUERY PLAN 200 |--SCAN TABLE t2 USING COVERING INDEX t2i1 201 `--SCAN TABLE t1 202} 203 204det 2.3.1 "SELECT max(x) FROM t2" { 205 QUERY PLAN 206 `--SEARCH TABLE t2 USING COVERING INDEX t2i1 207} 208det 2.3.2 "SELECT min(x) FROM t2" { 209 QUERY PLAN 210 `--SEARCH TABLE t2 USING COVERING INDEX t2i1 211} 212det 2.3.3 "SELECT min(x), max(x) FROM t2" { 213 QUERY PLAN 214 `--SCAN TABLE t2 USING COVERING INDEX t2i1 215} 216 217det 2.4.1 "SELECT * FROM t1 WHERE rowid=?" { 218 QUERY PLAN 219 `--SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) 220} 221 222 223 224#------------------------------------------------------------------------- 225# Test cases eqp-3.* - tests for select statements that use sub-selects. 226# 227do_eqp_test 3.1.1 { 228 SELECT (SELECT x FROM t1 AS sub) FROM t1; 229} { 230 QUERY PLAN 231 |--SCAN TABLE t1 232 `--SCALAR SUBQUERY xxxxxx 233 `--SCAN TABLE t1 AS sub 234} 235do_eqp_test 3.1.2 { 236 SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub); 237} { 238 QUERY PLAN 239 |--SCAN TABLE t1 240 `--SCALAR SUBQUERY xxxxxx 241 `--SCAN TABLE t1 AS sub 242} 243do_eqp_test 3.1.3 { 244 SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub ORDER BY y); 245} { 246 QUERY PLAN 247 |--SCAN TABLE t1 248 `--SCALAR SUBQUERY xxxxxx 249 |--SCAN TABLE t1 AS sub 250 `--USE TEMP B-TREE FOR ORDER BY 251} 252do_eqp_test 3.1.4 { 253 SELECT * FROM t1 WHERE (SELECT x FROM t2 ORDER BY x); 254} { 255 QUERY PLAN 256 |--SCAN TABLE t1 257 `--SCALAR SUBQUERY xxxxxx 258 `--SCAN TABLE t2 USING COVERING INDEX t2i1 259} 260 261det 3.2.1 { 262 SELECT * FROM (SELECT * FROM t1 ORDER BY x LIMIT 10) ORDER BY y LIMIT 5 263} { 264 QUERY PLAN 265 |--CO-ROUTINE xxxxxx 266 | |--SCAN TABLE t1 267 | `--USE TEMP B-TREE FOR ORDER BY 268 |--SCAN SUBQUERY xxxxxx 269 `--USE TEMP B-TREE FOR ORDER BY 270} 271det 3.2.2 { 272 SELECT * FROM 273 (SELECT * FROM t1 ORDER BY x LIMIT 10) AS x1, 274 (SELECT * FROM t2 ORDER BY x LIMIT 10) AS x2 275 ORDER BY x2.y LIMIT 5 276} { 277 QUERY PLAN 278 |--MATERIALIZE xxxxxx 279 | |--SCAN TABLE t1 280 | `--USE TEMP B-TREE FOR ORDER BY 281 |--MATERIALIZE xxxxxx 282 | `--SCAN TABLE t2 USING INDEX t2i1 283 |--SCAN SUBQUERY xxxxxx AS x1 284 |--SCAN SUBQUERY xxxxxx AS x2 285 `--USE TEMP B-TREE FOR ORDER BY 286} 287 288det 3.3.1 { 289 SELECT * FROM t1 WHERE y IN (SELECT y FROM t2) 290} { 291 QUERY PLAN 292 |--SCAN TABLE t1 293 `--LIST SUBQUERY xxxxxx 294 `--SCAN TABLE t2 295} 296det 3.3.2 { 297 SELECT * FROM t1 WHERE y IN (SELECT y FROM t2 WHERE t1.x!=t2.x) 298} { 299 QUERY PLAN 300 |--SCAN TABLE t1 301 `--CORRELATED LIST SUBQUERY xxxxxx 302 `--SCAN TABLE t2 303} 304det 3.3.3 { 305 SELECT * FROM t1 WHERE EXISTS (SELECT y FROM t2 WHERE t1.x!=t2.x) 306} { 307 QUERY PLAN 308 |--SCAN TABLE t1 309 `--CORRELATED SCALAR SUBQUERY xxxxxx 310 `--SCAN TABLE t2 311} 312 313#------------------------------------------------------------------------- 314# Test cases eqp-4.* - tests for composite select statements. 315# 316do_eqp_test 4.1.1 { 317 SELECT * FROM t1 UNION ALL SELECT * FROM t2 318} { 319 QUERY PLAN 320 `--COMPOUND QUERY 321 |--LEFT-MOST SUBQUERY 322 | `--SCAN TABLE t1 323 `--UNION ALL 324 `--SCAN TABLE t2 325} 326do_eqp_test 4.1.2 { 327 SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 2 328} { 329 QUERY PLAN 330 `--MERGE (UNION ALL) 331 |--LEFT 332 | |--SCAN TABLE t1 333 | `--USE TEMP B-TREE FOR ORDER BY 334 `--RIGHT 335 |--SCAN TABLE t2 336 `--USE TEMP B-TREE FOR ORDER BY 337} 338do_eqp_test 4.1.3 { 339 SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 2 340} { 341 QUERY PLAN 342 `--MERGE (UNION) 343 |--LEFT 344 | |--SCAN TABLE t1 345 | `--USE TEMP B-TREE FOR ORDER BY 346 `--RIGHT 347 |--SCAN TABLE t2 348 `--USE TEMP B-TREE FOR ORDER BY 349} 350do_eqp_test 4.1.4 { 351 SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 2 352} { 353 QUERY PLAN 354 `--MERGE (INTERSECT) 355 |--LEFT 356 | |--SCAN TABLE t1 357 | `--USE TEMP B-TREE FOR ORDER BY 358 `--RIGHT 359 |--SCAN TABLE t2 360 `--USE TEMP B-TREE FOR ORDER BY 361} 362do_eqp_test 4.1.5 { 363 SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 2 364} { 365 QUERY PLAN 366 `--MERGE (EXCEPT) 367 |--LEFT 368 | |--SCAN TABLE t1 369 | `--USE TEMP B-TREE FOR ORDER BY 370 `--RIGHT 371 |--SCAN TABLE t2 372 `--USE TEMP B-TREE FOR ORDER BY 373} 374 375do_eqp_test 4.2.2 { 376 SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 1 377} { 378 QUERY PLAN 379 `--MERGE (UNION ALL) 380 |--LEFT 381 | |--SCAN TABLE t1 382 | `--USE TEMP B-TREE FOR ORDER BY 383 `--RIGHT 384 `--SCAN TABLE t2 USING INDEX t2i1 385} 386do_eqp_test 4.2.3 { 387 SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 1 388} { 389 QUERY PLAN 390 `--MERGE (UNION) 391 |--LEFT 392 | |--SCAN TABLE t1 393 | `--USE TEMP B-TREE FOR ORDER BY 394 `--RIGHT 395 |--SCAN TABLE t2 USING INDEX t2i1 396 `--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY 397} 398do_eqp_test 4.2.4 { 399 SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 1 400} { 401 QUERY PLAN 402 `--MERGE (INTERSECT) 403 |--LEFT 404 | |--SCAN TABLE t1 405 | `--USE TEMP B-TREE FOR ORDER BY 406 `--RIGHT 407 |--SCAN TABLE t2 USING INDEX t2i1 408 `--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY 409} 410do_eqp_test 4.2.5 { 411 SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 1 412} { 413 QUERY PLAN 414 `--MERGE (EXCEPT) 415 |--LEFT 416 | |--SCAN TABLE t1 417 | `--USE TEMP B-TREE FOR ORDER BY 418 `--RIGHT 419 |--SCAN TABLE t2 USING INDEX t2i1 420 `--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY 421} 422 423do_eqp_test 4.3.1 { 424 SELECT x FROM t1 UNION SELECT x FROM t2 425} { 426 QUERY PLAN 427 `--COMPOUND QUERY 428 |--LEFT-MOST SUBQUERY 429 | `--SCAN TABLE t1 430 `--UNION USING TEMP B-TREE 431 `--SCAN TABLE t2 USING COVERING INDEX t2i1 432} 433 434do_eqp_test 4.3.2 { 435 SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1 436} { 437 QUERY PLAN 438 `--COMPOUND QUERY 439 |--LEFT-MOST SUBQUERY 440 | `--SCAN TABLE t1 441 |--UNION USING TEMP B-TREE 442 | `--SCAN TABLE t2 USING COVERING INDEX t2i1 443 `--UNION USING TEMP B-TREE 444 `--SCAN TABLE t1 445} 446do_eqp_test 4.3.3 { 447 SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1 ORDER BY 1 448} { 449 QUERY PLAN 450 `--MERGE (UNION) 451 |--LEFT 452 | `--MERGE (UNION) 453 | |--LEFT 454 | | |--SCAN TABLE t1 455 | | `--USE TEMP B-TREE FOR ORDER BY 456 | `--RIGHT 457 | `--SCAN TABLE t2 USING COVERING INDEX t2i1 458 `--RIGHT 459 |--SCAN TABLE t1 460 `--USE TEMP B-TREE FOR ORDER BY 461} 462 463if 0 { 464#------------------------------------------------------------------------- 465# This next block of tests verifies that the examples on the 466# lang_explain.html page are correct. 467# 468drop_all_tables 469 470# XVIDENCE-OF: R-47779-47605 sqlite> EXPLAIN QUERY PLAN SELECT a, b 471# FROM t1 WHERE a=1; 472# 0|0|0|SCAN TABLE t1 473# 474do_execsql_test 5.1.0 { CREATE TABLE t1(a INT, b INT, ex TEXT) } 475det 5.1.1 "SELECT a, b FROM t1 WHERE a=1" { 476 0 0 0 {SCAN TABLE t1} 477} 478 479# XVIDENCE-OF: R-55852-17599 sqlite> CREATE INDEX i1 ON t1(a); 480# sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; 481# 0|0|0|SEARCH TABLE t1 USING INDEX i1 482# 483do_execsql_test 5.2.0 { CREATE INDEX i1 ON t1(a) } 484det 5.2.1 "SELECT a, b FROM t1 WHERE a=1" { 485 0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)} 486} 487 488# XVIDENCE-OF: R-21179-11011 sqlite> CREATE INDEX i2 ON t1(a, b); 489# sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; 490# 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) 491# 492do_execsql_test 5.3.0 { CREATE INDEX i2 ON t1(a, b) } 493det 5.3.1 "SELECT a, b FROM t1 WHERE a=1" { 494 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)} 495} 496 497# XVIDENCE-OF: R-09991-48941 sqlite> EXPLAIN QUERY PLAN 498# SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2; 499# 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) 500# 0|1|1|SCAN TABLE t2 501# 502do_execsql_test 5.4.0 {CREATE TABLE t2(c INT, d INT, ex TEXT)} 503det 5.4.1 "SELECT t1.a, t2.c FROM t1, t2 WHERE t1.a=1 AND t1.b>2" { 504 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)} 505 0 1 1 {SCAN TABLE t2} 506} 507 508# XVIDENCE-OF: R-33626-61085 sqlite> EXPLAIN QUERY PLAN 509# SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2; 510# 0|0|1|SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) 511# 0|1|0|SCAN TABLE t2 512# 513det 5.5 "SELECT t1.a, t2.c FROM t2, t1 WHERE t1.a=1 AND t1.b>2" { 514 0 0 1 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)} 515 0 1 0 {SCAN TABLE t2} 516} 517 518# XVIDENCE-OF: R-04002-25654 sqlite> CREATE INDEX i3 ON t1(b); 519# sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2; 520# 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) 521# 0|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?) 522# 523do_execsql_test 5.5.0 {CREATE INDEX i3 ON t1(b)} 524det 5.6.1 "SELECT a, b FROM t1 WHERE a=1 OR b=2" { 525 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)} 526 0 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?)} 527} 528 529# XVIDENCE-OF: R-24577-38891 sqlite> EXPLAIN QUERY PLAN 530# SELECT c, d FROM t2 ORDER BY c; 531# 0|0|0|SCAN TABLE t2 532# 0|0|0|USE TEMP B-TREE FOR ORDER BY 533# 534det 5.7 "SELECT c, d FROM t2 ORDER BY c" { 535 0 0 0 {SCAN TABLE t2} 536 0 0 0 {USE TEMP B-TREE FOR ORDER BY} 537} 538 539# XVIDENCE-OF: R-58157-12355 sqlite> CREATE INDEX i4 ON t2(c); 540# sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c; 541# 0|0|0|SCAN TABLE t2 USING INDEX i4 542# 543do_execsql_test 5.8.0 {CREATE INDEX i4 ON t2(c)} 544det 5.8.1 "SELECT c, d FROM t2 ORDER BY c" { 545 0 0 0 {SCAN TABLE t2 USING INDEX i4} 546} 547 548# XVIDENCE-OF: R-13931-10421 sqlite> EXPLAIN QUERY PLAN SELECT 549# (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2; 550# 0|0|0|SCAN TABLE t2 551# 0|0|0|EXECUTE SCALAR SUBQUERY 1 552# 1|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) 553# 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2 554# 2|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?) 555# 556det 5.9 { 557 SELECT (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2 558} { 559 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i4} 560 0 0 0 {EXECUTE SCALAR SUBQUERY 1} 561 1 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)} 562 0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2} 563 2 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?)} 564} 565 566# XVIDENCE-OF: R-50892-45943 sqlite> EXPLAIN QUERY PLAN 567# SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x; 568# 1|0|0|SCAN TABLE t1 USING COVERING INDEX i2 569# 0|0|0|SCAN SUBQUERY 1 570# 0|0|0|USE TEMP B-TREE FOR GROUP BY 571# 572det 5.10 { 573 SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x 574} { 575 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2} 576 0 0 0 {SCAN SUBQUERY 1} 577 0 0 0 {USE TEMP B-TREE FOR GROUP BY} 578} 579 580# XVIDENCE-OF: R-46219-33846 sqlite> EXPLAIN QUERY PLAN 581# SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1; 582# 0|0|0|SEARCH TABLE t2 USING INDEX i4 (c=?) 583# 0|1|1|SCAN TABLE t1 584# 585det 5.11 "SELECT a, b FROM (SELECT * FROM t2 WHERE c=1), t1" { 586 0 0 0 {SEARCH TABLE t2 USING INDEX i4 (c=?)} 587 0 1 1 {SCAN TABLE t1 USING COVERING INDEX i2} 588} 589 590# XVIDENCE-OF: R-37879-39987 sqlite> EXPLAIN QUERY PLAN 591# SELECT a FROM t1 UNION SELECT c FROM t2; 592# 1|0|0|SCAN TABLE t1 593# 2|0|0|SCAN TABLE t2 594# 0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION) 595# 596det 5.12 "SELECT a,b FROM t1 UNION SELECT c, 99 FROM t2" { 597 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2} 598 2 0 0 {SCAN TABLE t2 USING COVERING INDEX i4} 599 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)} 600} 601 602# XVIDENCE-OF: R-44864-63011 sqlite> EXPLAIN QUERY PLAN 603# SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1; 604# 1|0|0|SCAN TABLE t1 USING COVERING INDEX i2 605# 2|0|0|SCAN TABLE t2 2|0|0|USE TEMP B-TREE FOR ORDER BY 606# 0|0|0|COMPOUND SUBQUERIES 1 AND 2 (EXCEPT) 607# 608det 5.13 "SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1" { 609 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i1} 610 2 0 0 {SCAN TABLE t2} 611 2 0 0 {USE TEMP B-TREE FOR ORDER BY} 612 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)} 613} 614 615if {![nonzero_reserved_bytes]} { 616 #------------------------------------------------------------------------- 617 # The following tests - eqp-6.* - test that the example C code on 618 # documentation page eqp.html works. The C code is duplicated in test1.c 619 # and wrapped in Tcl command [print_explain_query_plan] 620 # 621 set boilerplate { 622 proc explain_query_plan {db sql} { 623 set stmt [sqlite3_prepare_v2 db $sql -1 DUMMY] 624 print_explain_query_plan $stmt 625 sqlite3_finalize $stmt 626 } 627 sqlite3 db test.db 628 explain_query_plan db {%SQL%} 629 db close 630 exit 631 } 632 633 # Do a "Print Explain Query Plan" test. 634 proc do_peqp_test {tn sql res} { 635 set fd [open script.tcl w] 636 puts $fd [string map [list %SQL% $sql] $::boilerplate] 637 close $fd 638 639 uplevel do_test $tn [list { 640 set fd [open "|[info nameofexec] script.tcl"] 641 set data [read $fd] 642 close $fd 643 set data 644 }] [list $res] 645 } 646 647 do_peqp_test 6.1 { 648 SELECT a, b FROM t1 EXCEPT SELECT d, 99 FROM t2 ORDER BY 1 649 } [string trimleft { 6501 0 0 SCAN TABLE t1 USING COVERING INDEX i2 6512 0 0 SCAN TABLE t2 6522 0 0 USE TEMP B-TREE FOR ORDER BY 6530 0 0 COMPOUND SUBQUERIES 1 AND 2 (EXCEPT) 654}] 655} 656} 657 658#------------------------------------------------------------------------- 659# The following tests - eqp-7.* - test that queries that use the OP_Count 660# optimization return something sensible with EQP. 661# 662drop_all_tables 663 664do_execsql_test 7.0 { 665 CREATE TABLE t1(a INT, b INT, ex CHAR(100)); 666 CREATE TABLE t2(a INT, b INT, ex CHAR(100)); 667 CREATE INDEX i1 ON t2(a); 668} 669 670det 7.1 "SELECT count(*) FROM t1" { 671 QUERY PLAN 672 `--SCAN TABLE t1 673} 674 675det 7.2 "SELECT count(*) FROM t2" { 676 QUERY PLAN 677 `--SCAN TABLE t2 USING COVERING INDEX i1 678} 679 680do_execsql_test 7.3 { 681 INSERT INTO t1(a,b) VALUES(1, 2); 682 INSERT INTO t1(a,b) VALUES(3, 4); 683 684 INSERT INTO t2(a,b) VALUES(1, 2); 685 INSERT INTO t2(a,b) VALUES(3, 4); 686 INSERT INTO t2(a,b) VALUES(5, 6); 687 688 ANALYZE; 689} 690 691db close 692sqlite3 db test.db 693 694det 7.4 "SELECT count(*) FROM t1" { 695 QUERY PLAN 696 `--SCAN TABLE t1 697} 698 699det 7.5 "SELECT count(*) FROM t2" { 700 QUERY PLAN 701 `--SCAN TABLE t2 USING COVERING INDEX i1 702} 703 704#------------------------------------------------------------------------- 705# The following tests - eqp-8.* - test that queries that use the OP_Count 706# optimization return something sensible with EQP. 707# 708drop_all_tables 709 710do_execsql_test 8.0 { 711 CREATE TABLE t1(a, b, c, PRIMARY KEY(b, c)) WITHOUT ROWID; 712 CREATE TABLE t2(a, b, c); 713} 714 715det 8.1.1 "SELECT * FROM t2" { 716 QUERY PLAN 717 `--SCAN TABLE t2 718} 719 720det 8.1.2 "SELECT * FROM t2 WHERE rowid=?" { 721 QUERY PLAN 722 `--SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) 723} 724 725det 8.1.3 "SELECT count(*) FROM t2" { 726 QUERY PLAN 727 `--SCAN TABLE t2 728} 729 730det 8.2.1 "SELECT * FROM t1" { 731 QUERY PLAN 732 `--SCAN TABLE t1 733} 734 735det 8.2.2 "SELECT * FROM t1 WHERE b=?" { 736 QUERY PLAN 737 `--SEARCH TABLE t1 USING PRIMARY KEY (b=?) 738} 739 740det 8.2.3 "SELECT * FROM t1 WHERE b=? AND c=?" { 741 QUERY PLAN 742 `--SEARCH TABLE t1 USING PRIMARY KEY (b=? AND c=?) 743} 744 745det 8.2.4 "SELECT count(*) FROM t1" { 746 QUERY PLAN 747 `--SCAN TABLE t1 748} 749 750# 2018-08-16: While working on Fossil I discovered that EXPLAIN QUERY PLAN 751# did not describe IN operators implemented using a ROWID lookup. These 752# test cases ensure that problem as been fixed. 753# 754do_execsql_test 9.0 { 755 -- Schema from Fossil 2018-08-16 756 CREATE TABLE forumpost( 757 fpid INTEGER PRIMARY KEY, 758 froot INT, 759 fprev INT, 760 firt INT, 761 fmtime REAL 762 ); 763 CREATE INDEX forumthread ON forumpost(froot,fmtime); 764 CREATE TABLE blob( 765 rid INTEGER PRIMARY KEY, 766 rcvid INTEGER, 767 size INTEGER, 768 uuid TEXT UNIQUE NOT NULL, 769 content BLOB, 770 CHECK( length(uuid)>=40 AND rid>0 ) 771 ); 772 CREATE TABLE event( 773 type TEXT, 774 mtime DATETIME, 775 objid INTEGER PRIMARY KEY, 776 tagid INTEGER, 777 uid INTEGER REFERENCES user, 778 bgcolor TEXT, 779 euser TEXT, 780 user TEXT, 781 ecomment TEXT, 782 comment TEXT, 783 brief TEXT, 784 omtime DATETIME 785 ); 786 CREATE INDEX event_i1 ON event(mtime); 787 CREATE TABLE private(rid INTEGER PRIMARY KEY); 788} 789do_eqp_test 9.1 { 790 WITH thread(age,duration,cnt,root,last) AS ( 791 SELECT 792 julianday('now') - max(fmtime) AS age, 793 max(fmtime) - min(fmtime) AS duration, 794 sum(fprev IS NULL) AS msg_count, 795 froot, 796 (SELECT fpid FROM forumpost 797 WHERE froot=x.froot 798 AND fpid NOT IN private 799 ORDER BY fmtime DESC LIMIT 1) 800 FROM forumpost AS x 801 WHERE fpid NOT IN private --- Ensure this table mentioned in EQP output! 802 GROUP BY froot 803 ORDER BY 1 LIMIT 26 OFFSET 5 804 ) 805 SELECT 806 thread.age, 807 thread.duration, 808 thread.cnt, 809 blob.uuid, 810 substr(event.comment,instr(event.comment,':')+1) 811 FROM thread, blob, event 812 WHERE blob.rid=thread.last 813 AND event.objid=thread.last 814 ORDER BY 1; 815} { 816 QUERY PLAN 817 |--MATERIALIZE xxxxxx 818 | |--SCAN TABLE forumpost AS x USING INDEX forumthread 819 | |--USING ROWID SEARCH ON TABLE private FOR IN-OPERATOR 820 | |--CORRELATED SCALAR SUBQUERY xxxxxx 821 | | |--SEARCH TABLE forumpost USING COVERING INDEX forumthread (froot=?) 822 | | `--USING ROWID SEARCH ON TABLE private FOR IN-OPERATOR 823 | `--USE TEMP B-TREE FOR ORDER BY 824 |--SCAN SUBQUERY xxxxxx 825 |--SEARCH TABLE blob USING INTEGER PRIMARY KEY (rowid=?) 826 |--SEARCH TABLE event USING INTEGER PRIMARY KEY (rowid=?) 827 `--USE TEMP B-TREE FOR ORDER BY 828} 829 830finish_test 831