AryaWu/sqlite
0
1# 2010 November 62#3# The author disclaims copyright to this source code. In place of4# 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.tcl15 16ifcapable !compound {17 finish_test18 return19}20 21set testprefix eqp22 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 PLAN47 |--MULTI-INDEX OR48 | |--INDEX 149 | | `--SEARCH t1 USING INDEX i1 (a=?)50 | `--INDEX 251 | `--SEARCH t1 USING INDEX i2 (b=?)52 `--SCAN t253}54do_eqp_test 1.3 {55 SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=1 OR t1.b=2;56} {57 QUERY PLAN58 |--SCAN t259 `--MULTI-INDEX OR60 |--INDEX 161 | `--SEARCH t1 USING INDEX i1 (a=?)62 `--INDEX 263 `--SEARCH t1 USING INDEX i2 (b=?)64}65do_eqp_test 1.3 {66 SELECT a FROM t1 ORDER BY a67} {68 QUERY PLAN69 `--SCAN t1 USING COVERING INDEX i170}71do_eqp_test 1.4 {72 SELECT a FROM t1 ORDER BY +a73} {74 QUERY PLAN75 |--SCAN t1 USING COVERING INDEX i176 `--USE TEMP B-TREE FOR ORDER BY77}78do_eqp_test 1.5 {79 SELECT a FROM t1 WHERE a=480} {81 QUERY PLAN82 `--SEARCH t1 USING COVERING INDEX i1 (a=?)83}84do_eqp_test 1.6 {85 SELECT DISTINCT count(*) FROM t3 GROUP BY a;86} {87 QUERY PLAN88 |--SCAN t389 |--USE TEMP B-TREE FOR GROUP BY90 `--USE TEMP B-TREE FOR DISTINCT91}92 93do_eqp_test 1.7.1 {94 SELECT * FROM t3 JOIN (SELECT 1)95} {96 QUERY PLAN97 |--CO-ROUTINE (subquery-xxxxxx)98 | `--SCAN CONSTANT ROW99 |--SCAN (subquery-xxxxxx)100 `--SCAN t3101}102do_eqp_test 1.7.2 {103 SELECT * FROM t3 JOIN (SELECT 1) AS v1104} {105 QUERY PLAN106 |--CO-ROUTINE v1107 | `--SCAN CONSTANT ROW108 |--SCAN v1109 `--SCAN t3110}111do_eqp_test 1.7.3 {112 SELECT * FROM t3 AS xx JOIN (SELECT 1) AS yy113} {114 QUERY PLAN115 |--CO-ROUTINE yy116 | `--SCAN CONSTANT ROW117 |--SCAN yy118 `--SCAN xx119}120 121 122do_eqp_test 1.8 {123 SELECT * FROM t3 JOIN (SELECT 1 UNION SELECT 2)124} {125 QUERY PLAN126 |--CO-ROUTINE (subquery-xxxxxx)127 | `--COMPOUND QUERY128 | |--LEFT-MOST SUBQUERY129 | | `--SCAN CONSTANT ROW130 | `--UNION USING TEMP B-TREE131 | `--SCAN CONSTANT ROW132 |--SCAN (subquery-xxxxxx)133 `--SCAN t3134}135do_eqp_test 1.9 {136 SELECT * FROM t3 JOIN (SELECT 1 EXCEPT SELECT a FROM t3 LIMIT 17) AS abc137} {138 QUERY PLAN139 |--CO-ROUTINE abc140 | `--COMPOUND QUERY141 | |--LEFT-MOST SUBQUERY142 | | `--SCAN CONSTANT ROW143 | `--EXCEPT USING TEMP B-TREE144 | `--SCAN t3145 |--SCAN abc146 `--SCAN t3147}148do_eqp_test 1.10 {149 SELECT * FROM t3 JOIN (SELECT 1 INTERSECT SELECT a FROM t3 LIMIT 17) AS abc150} {151 QUERY PLAN152 |--CO-ROUTINE abc153 | `--COMPOUND QUERY154 | |--LEFT-MOST SUBQUERY155 | | `--SCAN CONSTANT ROW156 | `--INTERSECT USING TEMP B-TREE157 | `--SCAN t3158 |--SCAN abc159 `--SCAN t3160}161 162do_eqp_test 1.11 {163 SELECT * FROM t3 JOIN (SELECT 1 UNION ALL SELECT a FROM t3 LIMIT 17) abc164} {165 QUERY PLAN166 |--CO-ROUTINE abc167 | `--COMPOUND QUERY168 | |--LEFT-MOST SUBQUERY169 | | `--SCAN CONSTANT ROW170 | `--UNION ALL171 | `--SCAN t3172 |--SCAN abc173 `--SCAN t3174}175 176#-------------------------------------------------------------------------177# Test cases eqp-2.* - tests for single select statements.178#179drop_all_tables180do_execsql_test 2.1 {181 CREATE TABLE t1(x INT, y INT, ex TEXT);182 183 CREATE TABLE t2(x INT, y INT, ex TEXT);184 CREATE INDEX t2i1 ON t2(x);185}186 187det 2.2.1 "SELECT DISTINCT min(x), max(x) FROM t1 GROUP BY x ORDER BY 1" {188 QUERY PLAN189 |--SCAN t1190 |--USE TEMP B-TREE FOR GROUP BY191 |--USE TEMP B-TREE FOR DISTINCT192 `--USE TEMP B-TREE FOR ORDER BY193}194det 2.2.2 "SELECT DISTINCT min(x), max(x) FROM t2 GROUP BY x ORDER BY 1" {195 QUERY PLAN196 |--SCAN t2 USING COVERING INDEX t2i1197 |--USE TEMP B-TREE FOR DISTINCT198 `--USE TEMP B-TREE FOR ORDER BY199}200det 2.2.3 "SELECT DISTINCT * FROM t1" {201 QUERY PLAN202 |--SCAN t1203 `--USE TEMP B-TREE FOR DISTINCT204}205det 2.2.4 "SELECT DISTINCT * FROM t1, t2" {206 QUERY PLAN207 |--SCAN t1208 |--SCAN t2209 `--USE TEMP B-TREE FOR DISTINCT210}211det 2.2.5 "SELECT DISTINCT * FROM t1, t2 ORDER BY t1.x" {212 QUERY PLAN213 |--SCAN t1214 |--SCAN t2215 |--USE TEMP B-TREE FOR DISTINCT216 `--USE TEMP B-TREE FOR ORDER BY217}218det 2.2.6 "SELECT DISTINCT t2.x FROM t1, t2 ORDER BY t2.x" {219 QUERY PLAN220 |--SCAN t2 USING COVERING INDEX t2i1221 `--SCAN t1222}223 224det 2.3.1 "SELECT max(x) FROM t2" {225 QUERY PLAN226 `--SEARCH t2 USING COVERING INDEX t2i1227}228det 2.3.2 "SELECT min(x) FROM t2" {229 QUERY PLAN230 `--SEARCH t2 USING COVERING INDEX t2i1231}232det 2.3.3 "SELECT min(x), max(x) FROM t2" {233 QUERY PLAN234 `--SCAN t2 USING COVERING INDEX t2i1235}236 237det 2.4.1 "SELECT * FROM t1 WHERE rowid=?" {238 QUERY PLAN239 `--SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?)240}241 242 243 244#-------------------------------------------------------------------------245# Test cases eqp-3.* - tests for select statements that use sub-selects.246#247do_eqp_test 3.1.1 {248 SELECT (SELECT x FROM t1 AS sub) FROM t1;249} {250 QUERY PLAN251 |--SCAN t1252 `--SCALAR SUBQUERY xxxxxx253 `--SCAN sub254}255do_eqp_test 3.1.2 {256 SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub);257} {258 QUERY PLAN259 |--SCAN t1260 `--SCALAR SUBQUERY xxxxxx261 `--SCAN sub262}263do_eqp_test 3.1.3 {264 SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub ORDER BY y);265} {266 QUERY PLAN267 |--SCAN t1268 `--SCALAR SUBQUERY xxxxxx269 |--SCAN sub270 `--USE TEMP B-TREE FOR ORDER BY271}272do_eqp_test 3.1.4 {273 SELECT * FROM t1 WHERE (SELECT x FROM t2 ORDER BY x);274} {275 QUERY PLAN276 |--SCAN t1277 `--SCALAR SUBQUERY xxxxxx278 `--SCAN t2 USING COVERING INDEX t2i1279}280 281det 3.2.1 {282 SELECT * FROM (SELECT * FROM t1 ORDER BY x LIMIT 10) ORDER BY y LIMIT 5283} {284 QUERY PLAN285 |--CO-ROUTINE (subquery-xxxxxx)286 | |--SCAN t1287 | `--USE TEMP B-TREE FOR ORDER BY288 |--SCAN (subquery-xxxxxx)289 `--USE TEMP B-TREE FOR ORDER BY290}291 292det 3.2.2 {293 SELECT * FROM 294 (SELECT * FROM t1 ORDER BY x LIMIT 10) AS x1,295 (SELECT * FROM t2 ORDER BY x LIMIT 10) AS x2296 ORDER BY x2.y LIMIT 5297} {298 QUERY PLAN299 |--CO-ROUTINE x1300 | |--SCAN t1301 | `--USE TEMP B-TREE FOR ORDER BY302 |--MATERIALIZE x2303 | `--SCAN t2 USING INDEX t2i1304 |--SCAN x1305 |--SCAN x2306 `--USE TEMP B-TREE FOR ORDER BY307}308 309det 3.2.3 {310 SELECT * FROM (SELECT * FROM t1 ORDER BY x LIMIT 10) ORDER BY x LIMIT 5311} {312 QUERY PLAN313 |--CO-ROUTINE (subquery-xxxxxx)314 | |--SCAN t1315 | `--USE TEMP B-TREE FOR ORDER BY316 `--SCAN (subquery-xxxxxx)317}318 319det 3.3.1 {320 SELECT * FROM t1 WHERE y IN (SELECT y FROM t2)321} {322 QUERY PLAN323 |--SCAN t1324 `--LIST SUBQUERY xxxxxx325 |--SCAN t2326 `--CREATE BLOOM FILTER327}328det 3.3.2 {329 SELECT * FROM t1 WHERE y IN (SELECT y FROM t2 WHERE t1.x!=t2.x)330} {331 QUERY PLAN332 |--SCAN t1333 `--CORRELATED LIST SUBQUERY xxxxxx334 `--SCAN t2335}336det 3.3.3 {337 SELECT * FROM t1 WHERE EXISTS (SELECT y FROM t2 WHERE t1.x!=t2.x)338} {339 QUERY PLAN340 |--SCAN t1341 `--SCAN t2 EXISTS342}343 344#-------------------------------------------------------------------------345# Test cases eqp-4.* - tests for composite select statements.346#347do_eqp_test 4.1.1 {348 SELECT * FROM t1 UNION ALL SELECT * FROM t2349} {350 QUERY PLAN351 `--COMPOUND QUERY352 |--LEFT-MOST SUBQUERY353 | `--SCAN t1354 `--UNION ALL355 `--SCAN t2356}357do_eqp_test 4.1.2 {358 SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 2359} {360 QUERY PLAN361 `--MERGE (UNION ALL)362 |--LEFT363 | |--SCAN t1364 | `--USE TEMP B-TREE FOR ORDER BY365 `--RIGHT366 |--SCAN t2367 `--USE TEMP B-TREE FOR ORDER BY368}369do_eqp_test 4.1.3 {370 SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 2371} {372 QUERY PLAN373 `--MERGE (UNION)374 |--LEFT375 | |--SCAN t1376 | `--USE TEMP B-TREE FOR ORDER BY377 `--RIGHT378 |--SCAN t2379 `--USE TEMP B-TREE FOR ORDER BY380}381do_eqp_test 4.1.4 {382 SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 2383} {384 QUERY PLAN385 `--MERGE (INTERSECT)386 |--LEFT387 | |--SCAN t1388 | `--USE TEMP B-TREE FOR ORDER BY389 `--RIGHT390 |--SCAN t2391 `--USE TEMP B-TREE FOR ORDER BY392}393do_eqp_test 4.1.5 {394 SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 2395} {396 QUERY PLAN397 `--MERGE (EXCEPT)398 |--LEFT399 | |--SCAN t1400 | `--USE TEMP B-TREE FOR ORDER BY401 `--RIGHT402 |--SCAN t2403 `--USE TEMP B-TREE FOR ORDER BY404}405 406do_eqp_test 4.2.2 {407 SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 1408} {409 QUERY PLAN410 `--MERGE (UNION ALL)411 |--LEFT412 | |--SCAN t1413 | `--USE TEMP B-TREE FOR ORDER BY414 `--RIGHT415 `--SCAN t2 USING INDEX t2i1416}417do_eqp_test 4.2.3 {418 SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 1419} {420 QUERY PLAN421 `--MERGE (UNION)422 |--LEFT423 | |--SCAN t1424 | `--USE TEMP B-TREE FOR ORDER BY425 `--RIGHT426 |--SCAN t2 USING INDEX t2i1427 `--USE TEMP B-TREE FOR LAST 2 TERMS OF ORDER BY428}429do_eqp_test 4.2.4 {430 SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 1431} {432 QUERY PLAN433 `--MERGE (INTERSECT)434 |--LEFT435 | |--SCAN t1436 | `--USE TEMP B-TREE FOR ORDER BY437 `--RIGHT438 |--SCAN t2 USING INDEX t2i1439 `--USE TEMP B-TREE FOR LAST 2 TERMS OF ORDER BY440}441do_eqp_test 4.2.5 {442 SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 1443} {444 QUERY PLAN445 `--MERGE (EXCEPT)446 |--LEFT447 | |--SCAN t1448 | `--USE TEMP B-TREE FOR ORDER BY449 `--RIGHT450 |--SCAN t2 USING INDEX t2i1451 `--USE TEMP B-TREE FOR LAST 2 TERMS OF ORDER BY452}453 454do_eqp_test 4.3.1 {455 SELECT x FROM t1 UNION SELECT x FROM t2456} {457 QUERY PLAN458 `--COMPOUND QUERY459 |--LEFT-MOST SUBQUERY460 | `--SCAN t1461 `--UNION USING TEMP B-TREE462 `--SCAN t2 USING COVERING INDEX t2i1463}464 465do_eqp_test 4.3.2 {466 SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1467} {468 QUERY PLAN469 `--COMPOUND QUERY470 |--LEFT-MOST SUBQUERY471 | `--SCAN t1472 |--UNION USING TEMP B-TREE473 | `--SCAN t2 USING COVERING INDEX t2i1474 `--UNION USING TEMP B-TREE475 `--SCAN t1476}477do_eqp_test 4.3.3 {478 SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1 ORDER BY 1479} {480 QUERY PLAN481 `--MERGE (UNION)482 |--LEFT483 | `--MERGE (UNION)484 | |--LEFT485 | | |--SCAN t1486 | | `--USE TEMP B-TREE FOR ORDER BY487 | `--RIGHT488 | `--SCAN t2 USING COVERING INDEX t2i1489 `--RIGHT490 |--SCAN t1491 `--USE TEMP B-TREE FOR ORDER BY492}493 494if 0 {495#-------------------------------------------------------------------------496# This next block of tests verifies that the examples on the 497# lang_explain.html page are correct.498#499drop_all_tables500 501# XVIDENCE-OF: R-47779-47605 sqlite> EXPLAIN QUERY PLAN SELECT a, b502# FROM t1 WHERE a=1;503# 0|0|0|SCAN t1504#505do_execsql_test 5.1.0 { CREATE TABLE t1(a INT, b INT, ex TEXT) }506det 5.1.1 "SELECT a, b FROM t1 WHERE a=1" {507 0 0 0 {SCAN t1}508}509 510# XVIDENCE-OF: R-55852-17599 sqlite> CREATE INDEX i1 ON t1(a);511# sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;512# 0|0|0|SEARCH t1 USING INDEX i1513#514do_execsql_test 5.2.0 { CREATE INDEX i1 ON t1(a) }515det 5.2.1 "SELECT a, b FROM t1 WHERE a=1" {516 0 0 0 {SEARCH t1 USING INDEX i1 (a=?)}517}518 519# XVIDENCE-OF: R-21179-11011 sqlite> CREATE INDEX i2 ON t1(a, b);520# sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;521# 0|0|0|SEARCH t1 USING COVERING INDEX i2 (a=?)522#523do_execsql_test 5.3.0 { CREATE INDEX i2 ON t1(a, b) }524det 5.3.1 "SELECT a, b FROM t1 WHERE a=1" {525 0 0 0 {SEARCH t1 USING COVERING INDEX i2 (a=?)}526}527 528# XVIDENCE-OF: R-09991-48941 sqlite> EXPLAIN QUERY PLAN529# SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2;530# 0|0|0|SEARCH t1 USING COVERING INDEX i2 (a=? AND b>?)531# 0|1|1|SCAN t2532#533do_execsql_test 5.4.0 {CREATE TABLE t2(c INT, d INT, ex TEXT)}534det 5.4.1 "SELECT t1.a, t2.c FROM t1, t2 WHERE t1.a=1 AND t1.b>2" {535 0 0 0 {SEARCH t1 USING COVERING INDEX i2 (a=? AND b>?)}536 0 1 1 {SCAN t2}537}538 539# XVIDENCE-OF: R-33626-61085 sqlite> EXPLAIN QUERY PLAN540# SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2;541# 0|0|1|SEARCH t1 USING COVERING INDEX i2 (a=? AND b>?)542# 0|1|0|SCAN t2543#544det 5.5 "SELECT t1.a, t2.c FROM t2, t1 WHERE t1.a=1 AND t1.b>2" {545 0 0 1 {SEARCH t1 USING COVERING INDEX i2 (a=? AND b>?)}546 0 1 0 {SCAN t2}547}548 549# XVIDENCE-OF: R-04002-25654 sqlite> CREATE INDEX i3 ON t1(b);550# sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2;551# 0|0|0|SEARCH t1 USING COVERING INDEX i2 (a=?)552# 0|0|0|SEARCH t1 USING INDEX i3 (b=?)553#554do_execsql_test 5.5.0 {CREATE INDEX i3 ON t1(b)}555det 5.6.1 "SELECT a, b FROM t1 WHERE a=1 OR b=2" {556 0 0 0 {SEARCH t1 USING COVERING INDEX i2 (a=?)}557 0 0 0 {SEARCH t1 USING INDEX i3 (b=?)}558}559 560# XVIDENCE-OF: R-24577-38891 sqlite> EXPLAIN QUERY PLAN561# SELECT c, d FROM t2 ORDER BY c;562# 0|0|0|SCAN t2563# 0|0|0|USE TEMP B-TREE FOR ORDER BY564#565det 5.7 "SELECT c, d FROM t2 ORDER BY c" {566 0 0 0 {SCAN t2}567 0 0 0 {USE TEMP B-TREE FOR ORDER BY}568}569 570# XVIDENCE-OF: R-58157-12355 sqlite> CREATE INDEX i4 ON t2(c);571# sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c;572# 0|0|0|SCAN t2 USING INDEX i4573#574do_execsql_test 5.8.0 {CREATE INDEX i4 ON t2(c)}575det 5.8.1 "SELECT c, d FROM t2 ORDER BY c" {576 0 0 0 {SCAN t2 USING INDEX i4}577}578 579# XVIDENCE-OF: R-13931-10421 sqlite> EXPLAIN QUERY PLAN SELECT580# (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2;581# 0|0|0|SCAN t2582# 0|0|0|EXECUTE SCALAR SUBQUERY 1583# 1|0|0|SEARCH t1 USING COVERING INDEX i2 (a=?)584# 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2585# 2|0|0|SEARCH t1 USING INDEX i3 (b=?)586#587det 5.9 {588 SELECT (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2589} {590 0 0 0 {SCAN t2 USING COVERING INDEX i4}591 0 0 0 {EXECUTE SCALAR SUBQUERY 1}592 1 0 0 {SEARCH t1 USING COVERING INDEX i2 (a=?)}593 0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2}594 2 0 0 {SEARCH t1 USING INDEX i3 (b=?)}595}596 597# XVIDENCE-OF: R-50892-45943 sqlite> EXPLAIN QUERY PLAN598# SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x;599# 1|0|0|SCAN t1 USING COVERING INDEX i2600# 0|0|0|SCAN SUBQUERY 1601# 0|0|0|USE TEMP B-TREE FOR GROUP BY602#603det 5.10 {604 SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x605} {606 1 0 0 {SCAN t1 USING COVERING INDEX i2}607 0 0 0 {SCAN SUBQUERY 1}608 0 0 0 {USE TEMP B-TREE FOR GROUP BY}609}610 611# XVIDENCE-OF: R-46219-33846 sqlite> EXPLAIN QUERY PLAN612# SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1;613# 0|0|0|SEARCH t2 USING INDEX i4 (c=?)614# 0|1|1|SCAN t1615#616det 5.11 "SELECT a, b FROM (SELECT * FROM t2 WHERE c=1), t1" {617 0 0 0 {SEARCH t2 USING INDEX i4 (c=?)}618 0 1 1 {SCAN t1 USING COVERING INDEX i2}619}620 621# XVIDENCE-OF: R-37879-39987 sqlite> EXPLAIN QUERY PLAN622# SELECT a FROM t1 UNION SELECT c FROM t2;623# 1|0|0|SCAN t1624# 2|0|0|SCAN t2625# 0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)626#627det 5.12 "SELECT a,b FROM t1 UNION SELECT c, 99 FROM t2" {628 1 0 0 {SCAN t1 USING COVERING INDEX i2}629 2 0 0 {SCAN t2 USING COVERING INDEX i4}630 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)}631}632 633# XVIDENCE-OF: R-44864-63011 sqlite> EXPLAIN QUERY PLAN634# SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1;635# 1|0|0|SCAN t1 USING COVERING INDEX i2636# 2|0|0|SCAN t2 2|0|0|USE TEMP B-TREE FOR ORDER BY637# 0|0|0|COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)638#639det 5.13 "SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1" {640 1 0 0 {SCAN t1 USING COVERING INDEX i1}641 2 0 0 {SCAN t2}642 2 0 0 {USE TEMP B-TREE FOR ORDER BY}643 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)}644}645 646if {![nonzero_reserved_bytes]} {647 #-------------------------------------------------------------------------648 # The following tests - eqp-6.* - test that the example C code on 649 # documentation page eqp.html works. The C code is duplicated in test1.c650 # and wrapped in Tcl command [print_explain_query_plan] 651 #652 set boilerplate {653 proc explain_query_plan {db sql} {654 set stmt [sqlite3_prepare_v2 db $sql -1 DUMMY]655 print_explain_query_plan $stmt656 sqlite3_finalize $stmt657 }658 sqlite3 db test.db659 explain_query_plan db {%SQL%}660 db close661 exit662 }663 664 # Do a "Print Explain Query Plan" test.665 proc do_peqp_test {tn sql res} {666 set fd [open script.tcl w]667 puts $fd [string map [list %SQL% $sql] $::boilerplate]668 close $fd669 670 uplevel do_test $tn [list {671 set fd [open "|[info nameofexec] script.tcl"]672 set data [read $fd]673 close $fd674 set data675 }] [list $res]676 }677 678 do_peqp_test 6.1 {679 SELECT a, b FROM t1 EXCEPT SELECT d, 99 FROM t2 ORDER BY 1680 } [string trimleft {6811 0 0 SCAN t1 USING COVERING INDEX i26822 0 0 SCAN t26832 0 0 USE TEMP B-TREE FOR ORDER BY6840 0 0 COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)685}]686}687}688 689#-------------------------------------------------------------------------690# The following tests - eqp-7.* - test that queries that use the OP_Count691# optimization return something sensible with EQP.692#693drop_all_tables694 695do_execsql_test 7.0 {696 CREATE TABLE t1(a INT, b INT, ex CHAR(100));697 CREATE TABLE t2(a INT, b INT, ex CHAR(100));698 CREATE INDEX i1 ON t2(a);699}700 701det 7.1 "SELECT count(*) FROM t1" {702 QUERY PLAN703 `--SCAN t1704}705 706det 7.2 "SELECT count(*) FROM t2" {707 QUERY PLAN708 `--SCAN t2 USING COVERING INDEX i1709}710 711do_execsql_test 7.3 {712 INSERT INTO t1(a,b) VALUES(1, 2);713 INSERT INTO t1(a,b) VALUES(3, 4);714 715 INSERT INTO t2(a,b) VALUES(1, 2);716 INSERT INTO t2(a,b) VALUES(3, 4);717 INSERT INTO t2(a,b) VALUES(5, 6);718 719 ANALYZE;720}721 722db close723sqlite3 db test.db724 725det 7.4 "SELECT count(*) FROM t1" {726 QUERY PLAN727 `--SCAN t1728}729 730det 7.5 "SELECT count(*) FROM t2" {731 QUERY PLAN732 `--SCAN t2 USING COVERING INDEX i1733}734 735#-------------------------------------------------------------------------736# The following tests - eqp-8.* - test that queries that use the OP_Count737# optimization return something sensible with EQP.738#739drop_all_tables740 741do_execsql_test 8.0 {742 CREATE TABLE t1(a, b, c, PRIMARY KEY(b, c)) WITHOUT ROWID;743 CREATE TABLE t2(a, b, c);744}745 746det 8.1.1 "SELECT * FROM t2" {747 QUERY PLAN748 `--SCAN t2749}750 751det 8.1.2 "SELECT * FROM t2 WHERE rowid=?" {752 QUERY PLAN753 `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?)754}755 756det 8.1.3 "SELECT count(*) FROM t2" {757 QUERY PLAN758 `--SCAN t2759}760 761det 8.2.1 "SELECT * FROM t1" {762 QUERY PLAN763 `--SCAN t1764}765 766det 8.2.2 "SELECT * FROM t1 WHERE b=?" {767 QUERY PLAN768 `--SEARCH t1 USING PRIMARY KEY (b=?)769}770 771det 8.2.3 "SELECT * FROM t1 WHERE b=? AND c=?" {772 QUERY PLAN773 `--SEARCH t1 USING PRIMARY KEY (b=? AND c=?)774}775 776det 8.2.4 "SELECT count(*) FROM t1" {777 QUERY PLAN778 `--SCAN t1779}780 781# 2018-08-16: While working on Fossil I discovered that EXPLAIN QUERY PLAN782# did not describe IN operators implemented using a ROWID lookup. These783# test cases ensure that problem as been fixed.784#785do_execsql_test 9.0 {786 -- Schema from Fossil 2018-08-16787 CREATE TABLE forumpost(788 fpid INTEGER PRIMARY KEY,789 froot INT,790 fprev INT,791 firt INT,792 fmtime REAL793 );794 CREATE INDEX forumthread ON forumpost(froot,fmtime);795 CREATE TABLE blob(796 rid INTEGER PRIMARY KEY,797 rcvid INTEGER,798 size INTEGER,799 uuid TEXT UNIQUE NOT NULL,800 content BLOB,801 CHECK( length(uuid)>=40 AND rid>0 )802 );803 CREATE TABLE event(804 type TEXT,805 mtime DATETIME,806 objid INTEGER PRIMARY KEY,807 tagid INTEGER,808 uid INTEGER REFERENCES user,809 bgcolor TEXT,810 euser TEXT,811 user TEXT,812 ecomment TEXT,813 comment TEXT,814 brief TEXT,815 omtime DATETIME816 );817 CREATE INDEX event_i1 ON event(mtime);818 CREATE TABLE private(rid INTEGER PRIMARY KEY);819}820optimization_control db order-by-subquery off821do_eqp_test 9.1 {822 WITH thread(age,duration,cnt,root,last) AS (823 SELECT824 julianday('now') - max(fmtime) AS age,825 max(fmtime) - min(fmtime) AS duration,826 sum(fprev IS NULL) AS msg_count,827 froot,828 (SELECT fpid FROM forumpost829 WHERE froot=x.froot830 AND fpid NOT IN private831 ORDER BY fmtime DESC LIMIT 1)832 FROM forumpost AS x833 WHERE fpid NOT IN private --- Ensure this table mentioned in EQP output!834 GROUP BY froot835 ORDER BY 1 LIMIT 26 OFFSET 5836 )837 SELECT838 thread.age,839 thread.duration,840 thread.cnt,841 blob.uuid,842 substr(event.comment,instr(event.comment,':')+1)843 FROM thread, blob, event844 WHERE blob.rid=thread.last845 AND event.objid=thread.last846 ORDER BY 1;847} {848 QUERY PLAN849 |--CO-ROUTINE thread850 | |--SCAN x USING INDEX forumthread851 | |--USING ROWID SEARCH ON TABLE private FOR IN-OPERATOR852 | |--CORRELATED SCALAR SUBQUERY xxxxxx853 | | |--SEARCH forumpost USING COVERING INDEX forumthread (froot=?)854 | | `--USING ROWID SEARCH ON TABLE private FOR IN-OPERATOR855 | `--USE TEMP B-TREE FOR ORDER BY856 |--SCAN thread857 |--SEARCH blob USING INTEGER PRIMARY KEY (rowid=?)858 |--SEARCH event USING INTEGER PRIMARY KEY (rowid=?)859 `--USE TEMP B-TREE FOR ORDER BY860}861optimization_control db all on862db cache flush863do_eqp_test 9.2 {864 WITH thread(age,duration,cnt,root,last) AS (865 SELECT866 julianday('now') - max(fmtime) AS age,867 max(fmtime) - min(fmtime) AS duration,868 sum(fprev IS NULL) AS msg_count,869 froot,870 (SELECT fpid FROM forumpost871 WHERE froot=x.froot872 AND fpid NOT IN private873 ORDER BY fmtime DESC LIMIT 1)874 FROM forumpost AS x875 WHERE fpid NOT IN private --- Ensure this table mentioned in EQP output!876 GROUP BY froot877 ORDER BY 1 LIMIT 26 OFFSET 5878 )879 SELECT880 thread.age,881 thread.duration,882 thread.cnt,883 blob.uuid,884 substr(event.comment,instr(event.comment,':')+1)885 FROM thread, blob, event886 WHERE blob.rid=thread.last887 AND event.objid=thread.last888 ORDER BY 1;889} {890 QUERY PLAN891 |--CO-ROUTINE thread892 | |--SCAN x USING INDEX forumthread893 | |--USING ROWID SEARCH ON TABLE private FOR IN-OPERATOR894 | |--CORRELATED SCALAR SUBQUERY xxxxxx895 | | |--SEARCH forumpost USING COVERING INDEX forumthread (froot=?)896 | | `--USING ROWID SEARCH ON TABLE private FOR IN-OPERATOR897 | `--USE TEMP B-TREE FOR ORDER BY898 |--SCAN thread899 |--SEARCH blob USING INTEGER PRIMARY KEY (rowid=?)900 `--SEARCH event USING INTEGER PRIMARY KEY (rowid=?)901}902 903finish_test904