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