000001 # 2010 November 6
000002 #
000003 # The author disclaims copyright to this source code. In place of
000004 # a legal notice, here is a blessing:
000005 #
000006 # May you do good and not evil.
000007 # May you find forgiveness for yourself and forgive others.
000008 # May you share freely, never taking more than you give.
000009 #
000010 #***********************************************************************
000011 #
000012
000013 set testdir [file dirname $argv0]
000014 source $testdir/tester.tcl
000015
000016 ifcapable !compound {
000017 finish_test
000018 return
000019 }
000020
000021 set testprefix eqp
000022
000023 #-------------------------------------------------------------------------
000024 #
000025 # eqp-1.*: Assorted tests.
000026 # eqp-2.*: Tests for single select statements.
000027 # eqp-3.*: Select statements that execute sub-selects.
000028 # eqp-4.*: Compound select statements.
000029 # ...
000030 # eqp-7.*: "SELECT count(*) FROM tbl" statements (VDBE code OP_Count).
000031 #
000032
000033 proc det {args} { uplevel do_eqp_test $args }
000034
000035 do_execsql_test 1.1 {
000036 CREATE TABLE t1(a INT, b INT, ex TEXT);
000037 CREATE INDEX i1 ON t1(a);
000038 CREATE INDEX i2 ON t1(b);
000039 CREATE TABLE t2(a INT, b INT, ex TEXT);
000040 CREATE TABLE t3(a INT, b INT, ex TEXT);
000041 }
000042
000043 do_eqp_test 1.2 {
000044 SELECT * FROM t2, t1 WHERE t1.a=1 OR t1.b=2;
000045 } {
000046 0 0 1 {SEARCH TABLE t1 USING INDEX i1 (a=?)}
000047 0 0 1 {SEARCH TABLE t1 USING INDEX i2 (b=?)}
000048 0 1 0 {SCAN TABLE t2}
000049 }
000050 do_eqp_test 1.3 {
000051 SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=1 OR t1.b=2;
000052 } {
000053 0 0 0 {SCAN TABLE t2}
000054 0 1 1 {SEARCH TABLE t1 USING INDEX i1 (a=?)}
000055 0 1 1 {SEARCH TABLE t1 USING INDEX i2 (b=?)}
000056 }
000057 do_eqp_test 1.3 {
000058 SELECT a FROM t1 ORDER BY a
000059 } {
000060 0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1}
000061 }
000062 do_eqp_test 1.4 {
000063 SELECT a FROM t1 ORDER BY +a
000064 } {
000065 0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1}
000066 0 0 0 {USE TEMP B-TREE FOR ORDER BY}
000067 }
000068 do_eqp_test 1.5 {
000069 SELECT a FROM t1 WHERE a=4
000070 } {
000071 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?)}
000072 }
000073 do_eqp_test 1.6 {
000074 SELECT DISTINCT count(*) FROM t3 GROUP BY a;
000075 } {
000076 0 0 0 {SCAN TABLE t3}
000077 0 0 0 {USE TEMP B-TREE FOR GROUP BY}
000078 0 0 0 {USE TEMP B-TREE FOR DISTINCT}
000079 }
000080
000081 do_eqp_test 1.7 {
000082 SELECT * FROM t3 JOIN (SELECT 1)
000083 } {
000084 0 0 1 {SCAN SUBQUERY 1}
000085 0 1 0 {SCAN TABLE t3}
000086 }
000087 do_eqp_test 1.8 {
000088 SELECT * FROM t3 JOIN (SELECT 1 UNION SELECT 2)
000089 } {
000090 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)}
000091 0 0 1 {SCAN SUBQUERY 1}
000092 0 1 0 {SCAN TABLE t3}
000093 }
000094 do_eqp_test 1.9 {
000095 SELECT * FROM t3 JOIN (SELECT 1 EXCEPT SELECT a FROM t3 LIMIT 17)
000096 } {
000097 3 0 0 {SCAN TABLE t3}
000098 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (EXCEPT)}
000099 0 0 1 {SCAN SUBQUERY 1}
000100 0 1 0 {SCAN TABLE t3}
000101 }
000102 do_eqp_test 1.10 {
000103 SELECT * FROM t3 JOIN (SELECT 1 INTERSECT SELECT a FROM t3 LIMIT 17)
000104 } {
000105 3 0 0 {SCAN TABLE t3}
000106 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (INTERSECT)}
000107 0 0 1 {SCAN SUBQUERY 1}
000108 0 1 0 {SCAN TABLE t3}
000109 }
000110
000111 do_eqp_test 1.11 {
000112 SELECT * FROM t3 JOIN (SELECT 1 UNION ALL SELECT a FROM t3 LIMIT 17)
000113 } {
000114 3 0 0 {SCAN TABLE t3}
000115 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)}
000116 0 0 1 {SCAN SUBQUERY 1}
000117 0 1 0 {SCAN TABLE t3}
000118 }
000119
000120 #-------------------------------------------------------------------------
000121 # Test cases eqp-2.* - tests for single select statements.
000122 #
000123 drop_all_tables
000124 do_execsql_test 2.1 {
000125 CREATE TABLE t1(x INT, y INT, ex TEXT);
000126
000127 CREATE TABLE t2(x INT, y INT, ex TEXT);
000128 CREATE INDEX t2i1 ON t2(x);
000129 }
000130
000131 det 2.2.1 "SELECT DISTINCT min(x), max(x) FROM t1 GROUP BY x ORDER BY 1" {
000132 0 0 0 {SCAN TABLE t1}
000133 0 0 0 {USE TEMP B-TREE FOR GROUP BY}
000134 0 0 0 {USE TEMP B-TREE FOR DISTINCT}
000135 0 0 0 {USE TEMP B-TREE FOR ORDER BY}
000136 }
000137 det 2.2.2 "SELECT DISTINCT min(x), max(x) FROM t2 GROUP BY x ORDER BY 1" {
000138 0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1}
000139 0 0 0 {USE TEMP B-TREE FOR DISTINCT}
000140 0 0 0 {USE TEMP B-TREE FOR ORDER BY}
000141 }
000142 det 2.2.3 "SELECT DISTINCT * FROM t1" {
000143 0 0 0 {SCAN TABLE t1}
000144 0 0 0 {USE TEMP B-TREE FOR DISTINCT}
000145 }
000146 det 2.2.4 "SELECT DISTINCT * FROM t1, t2" {
000147 0 0 0 {SCAN TABLE t1}
000148 0 1 1 {SCAN TABLE t2}
000149 0 0 0 {USE TEMP B-TREE FOR DISTINCT}
000150 }
000151 det 2.2.5 "SELECT DISTINCT * FROM t1, t2 ORDER BY t1.x" {
000152 0 0 0 {SCAN TABLE t1}
000153 0 1 1 {SCAN TABLE t2}
000154 0 0 0 {USE TEMP B-TREE FOR DISTINCT}
000155 0 0 0 {USE TEMP B-TREE FOR ORDER BY}
000156 }
000157 det 2.2.6 "SELECT DISTINCT t2.x FROM t1, t2 ORDER BY t2.x" {
000158 0 0 1 {SCAN TABLE t2 USING COVERING INDEX t2i1}
000159 0 1 0 {SCAN TABLE t1}
000160 }
000161
000162 det 2.3.1 "SELECT max(x) FROM t2" {
000163 0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2i1}
000164 }
000165 det 2.3.2 "SELECT min(x) FROM t2" {
000166 0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2i1}
000167 }
000168 det 2.3.3 "SELECT min(x), max(x) FROM t2" {
000169 0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1}
000170 }
000171
000172 det 2.4.1 "SELECT * FROM t1 WHERE rowid=?" {
000173 0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)}
000174 }
000175
000176
000177
000178 #-------------------------------------------------------------------------
000179 # Test cases eqp-3.* - tests for select statements that use sub-selects.
000180 #
000181 do_eqp_test 3.1.1 {
000182 SELECT (SELECT x FROM t1 AS sub) FROM t1;
000183 } {
000184 0 0 0 {SCAN TABLE t1}
000185 0 0 0 {EXECUTE SCALAR SUBQUERY 1}
000186 1 0 0 {SCAN TABLE t1 AS sub}
000187 }
000188 do_eqp_test 3.1.2 {
000189 SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub);
000190 } {
000191 0 0 0 {SCAN TABLE t1}
000192 0 0 0 {EXECUTE SCALAR SUBQUERY 1}
000193 1 0 0 {SCAN TABLE t1 AS sub}
000194 }
000195 do_eqp_test 3.1.3 {
000196 SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub ORDER BY y);
000197 } {
000198 0 0 0 {SCAN TABLE t1}
000199 0 0 0 {EXECUTE SCALAR SUBQUERY 1}
000200 1 0 0 {SCAN TABLE t1 AS sub}
000201 1 0 0 {USE TEMP B-TREE FOR ORDER BY}
000202 }
000203 do_eqp_test 3.1.4 {
000204 SELECT * FROM t1 WHERE (SELECT x FROM t2 ORDER BY x);
000205 } {
000206 0 0 0 {SCAN TABLE t1}
000207 0 0 0 {EXECUTE SCALAR SUBQUERY 1}
000208 1 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1}
000209 }
000210
000211 det 3.2.1 {
000212 SELECT * FROM (SELECT * FROM t1 ORDER BY x LIMIT 10) ORDER BY y LIMIT 5
000213 } {
000214 1 0 0 {SCAN TABLE t1}
000215 1 0 0 {USE TEMP B-TREE FOR ORDER BY}
000216 0 0 0 {SCAN SUBQUERY 1}
000217 0 0 0 {USE TEMP B-TREE FOR ORDER BY}
000218 }
000219 det 3.2.2 {
000220 SELECT * FROM
000221 (SELECT * FROM t1 ORDER BY x LIMIT 10) AS x1,
000222 (SELECT * FROM t2 ORDER BY x LIMIT 10) AS x2
000223 ORDER BY x2.y LIMIT 5
000224 } {
000225 1 0 0 {SCAN TABLE t1}
000226 1 0 0 {USE TEMP B-TREE FOR ORDER BY}
000227 2 0 0 {SCAN TABLE t2 USING INDEX t2i1}
000228 0 0 0 {SCAN SUBQUERY 1 AS x1}
000229 0 1 1 {SCAN SUBQUERY 2 AS x2}
000230 0 0 0 {USE TEMP B-TREE FOR ORDER BY}
000231 }
000232
000233 det 3.3.1 {
000234 SELECT * FROM t1 WHERE y IN (SELECT y FROM t2)
000235 } {
000236 0 0 0 {SCAN TABLE t1}
000237 0 0 0 {EXECUTE LIST SUBQUERY 1}
000238 1 0 0 {SCAN TABLE t2}
000239 }
000240 det 3.3.2 {
000241 SELECT * FROM t1 WHERE y IN (SELECT y FROM t2 WHERE t1.x!=t2.x)
000242 } {
000243 0 0 0 {SCAN TABLE t1}
000244 0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1}
000245 1 0 0 {SCAN TABLE t2}
000246 }
000247 det 3.3.3 {
000248 SELECT * FROM t1 WHERE EXISTS (SELECT y FROM t2 WHERE t1.x!=t2.x)
000249 } {
000250 0 0 0 {SCAN TABLE t1}
000251 0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 1}
000252 1 0 0 {SCAN TABLE t2}
000253 }
000254
000255 #-------------------------------------------------------------------------
000256 # Test cases eqp-4.* - tests for composite select statements.
000257 #
000258 do_eqp_test 4.1.1 {
000259 SELECT * FROM t1 UNION ALL SELECT * FROM t2
000260 } {
000261 1 0 0 {SCAN TABLE t1}
000262 2 0 0 {SCAN TABLE t2}
000263 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)}
000264 }
000265 do_eqp_test 4.1.2 {
000266 SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 2
000267 } {
000268 1 0 0 {SCAN TABLE t1}
000269 1 0 0 {USE TEMP B-TREE FOR ORDER BY}
000270 2 0 0 {SCAN TABLE t2}
000271 2 0 0 {USE TEMP B-TREE FOR ORDER BY}
000272 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)}
000273 }
000274 do_eqp_test 4.1.3 {
000275 SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 2
000276 } {
000277 1 0 0 {SCAN TABLE t1}
000278 1 0 0 {USE TEMP B-TREE FOR ORDER BY}
000279 2 0 0 {SCAN TABLE t2}
000280 2 0 0 {USE TEMP B-TREE FOR ORDER BY}
000281 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION)}
000282 }
000283 do_eqp_test 4.1.4 {
000284 SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 2
000285 } {
000286 1 0 0 {SCAN TABLE t1}
000287 1 0 0 {USE TEMP B-TREE FOR ORDER BY}
000288 2 0 0 {SCAN TABLE t2}
000289 2 0 0 {USE TEMP B-TREE FOR ORDER BY}
000290 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (INTERSECT)}
000291 }
000292 do_eqp_test 4.1.5 {
000293 SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 2
000294 } {
000295 1 0 0 {SCAN TABLE t1}
000296 1 0 0 {USE TEMP B-TREE FOR ORDER BY}
000297 2 0 0 {SCAN TABLE t2}
000298 2 0 0 {USE TEMP B-TREE FOR ORDER BY}
000299 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)}
000300 }
000301
000302 do_eqp_test 4.2.2 {
000303 SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 1
000304 } {
000305 1 0 0 {SCAN TABLE t1}
000306 1 0 0 {USE TEMP B-TREE FOR ORDER BY}
000307 2 0 0 {SCAN TABLE t2 USING INDEX t2i1}
000308 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)}
000309 }
000310 do_eqp_test 4.2.3 {
000311 SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 1
000312 } {
000313 1 0 0 {SCAN TABLE t1}
000314 1 0 0 {USE TEMP B-TREE FOR ORDER BY}
000315 2 0 0 {SCAN TABLE t2 USING INDEX t2i1}
000316 2 0 0 {USE TEMP B-TREE FOR RIGHT PART OF ORDER BY}
000317 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION)}
000318 }
000319 do_eqp_test 4.2.4 {
000320 SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 1
000321 } {
000322 1 0 0 {SCAN TABLE t1}
000323 1 0 0 {USE TEMP B-TREE FOR ORDER BY}
000324 2 0 0 {SCAN TABLE t2 USING INDEX t2i1}
000325 2 0 0 {USE TEMP B-TREE FOR RIGHT PART OF ORDER BY}
000326 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (INTERSECT)}
000327 }
000328 do_eqp_test 4.2.5 {
000329 SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 1
000330 } {
000331 1 0 0 {SCAN TABLE t1}
000332 1 0 0 {USE TEMP B-TREE FOR ORDER BY}
000333 2 0 0 {SCAN TABLE t2 USING INDEX t2i1}
000334 2 0 0 {USE TEMP B-TREE FOR RIGHT PART OF ORDER BY}
000335 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)}
000336 }
000337
000338 do_eqp_test 4.3.1 {
000339 SELECT x FROM t1 UNION SELECT x FROM t2
000340 } {
000341 1 0 0 {SCAN TABLE t1}
000342 2 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1}
000343 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)}
000344 }
000345
000346 do_eqp_test 4.3.2 {
000347 SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1
000348 } {
000349 2 0 0 {SCAN TABLE t1}
000350 3 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1}
000351 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)}
000352 4 0 0 {SCAN TABLE t1}
000353 0 0 0 {COMPOUND SUBQUERIES 1 AND 4 USING TEMP B-TREE (UNION)}
000354 }
000355 do_eqp_test 4.3.3 {
000356 SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1 ORDER BY 1
000357 } {
000358 2 0 0 {SCAN TABLE t1}
000359 2 0 0 {USE TEMP B-TREE FOR ORDER BY}
000360 3 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1}
000361 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 (UNION)}
000362 4 0 0 {SCAN TABLE t1}
000363 4 0 0 {USE TEMP B-TREE FOR ORDER BY}
000364 0 0 0 {COMPOUND SUBQUERIES 1 AND 4 (UNION)}
000365 }
000366
000367 #-------------------------------------------------------------------------
000368 # This next block of tests verifies that the examples on the
000369 # lang_explain.html page are correct.
000370 #
000371 drop_all_tables
000372
000373 # EVIDENCE-OF: R-47779-47605 sqlite> EXPLAIN QUERY PLAN SELECT a, b
000374 # FROM t1 WHERE a=1;
000375 # 0|0|0|SCAN TABLE t1
000376 #
000377 do_execsql_test 5.1.0 { CREATE TABLE t1(a INT, b INT, ex TEXT) }
000378 det 5.1.1 "SELECT a, b FROM t1 WHERE a=1" {
000379 0 0 0 {SCAN TABLE t1}
000380 }
000381
000382 # EVIDENCE-OF: R-55852-17599 sqlite> CREATE INDEX i1 ON t1(a);
000383 # sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
000384 # 0|0|0|SEARCH TABLE t1 USING INDEX i1
000385 #
000386 do_execsql_test 5.2.0 { CREATE INDEX i1 ON t1(a) }
000387 det 5.2.1 "SELECT a, b FROM t1 WHERE a=1" {
000388 0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}
000389 }
000390
000391 # EVIDENCE-OF: R-21179-11011 sqlite> CREATE INDEX i2 ON t1(a, b);
000392 # sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
000393 # 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)
000394 #
000395 do_execsql_test 5.3.0 { CREATE INDEX i2 ON t1(a, b) }
000396 det 5.3.1 "SELECT a, b FROM t1 WHERE a=1" {
000397 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)}
000398 }
000399
000400 # EVIDENCE-OF: R-09991-48941 sqlite> EXPLAIN QUERY PLAN
000401 # SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2;
000402 # 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)
000403 # 0|1|1|SCAN TABLE t2
000404 #
000405 do_execsql_test 5.4.0 {CREATE TABLE t2(c INT, d INT, ex TEXT)}
000406 det 5.4.1 "SELECT t1.a, t2.c FROM t1, t2 WHERE t1.a=1 AND t1.b>2" {
000407 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)}
000408 0 1 1 {SCAN TABLE t2}
000409 }
000410
000411 # EVIDENCE-OF: R-33626-61085 sqlite> EXPLAIN QUERY PLAN
000412 # SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2;
000413 # 0|0|1|SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)
000414 # 0|1|0|SCAN TABLE t2
000415 #
000416 det 5.5 "SELECT t1.a, t2.c FROM t2, t1 WHERE t1.a=1 AND t1.b>2" {
000417 0 0 1 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)}
000418 0 1 0 {SCAN TABLE t2}
000419 }
000420
000421 # EVIDENCE-OF: R-04002-25654 sqlite> CREATE INDEX i3 ON t1(b);
000422 # sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2;
000423 # 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)
000424 # 0|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?)
000425 #
000426 do_execsql_test 5.5.0 {CREATE INDEX i3 ON t1(b)}
000427 det 5.6.1 "SELECT a, b FROM t1 WHERE a=1 OR b=2" {
000428 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)}
000429 0 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?)}
000430 }
000431
000432 # EVIDENCE-OF: R-24577-38891 sqlite> EXPLAIN QUERY PLAN
000433 # SELECT c, d FROM t2 ORDER BY c;
000434 # 0|0|0|SCAN TABLE t2
000435 # 0|0|0|USE TEMP B-TREE FOR ORDER BY
000436 #
000437 det 5.7 "SELECT c, d FROM t2 ORDER BY c" {
000438 0 0 0 {SCAN TABLE t2}
000439 0 0 0 {USE TEMP B-TREE FOR ORDER BY}
000440 }
000441
000442 # EVIDENCE-OF: R-58157-12355 sqlite> CREATE INDEX i4 ON t2(c);
000443 # sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c;
000444 # 0|0|0|SCAN TABLE t2 USING INDEX i4
000445 #
000446 do_execsql_test 5.8.0 {CREATE INDEX i4 ON t2(c)}
000447 det 5.8.1 "SELECT c, d FROM t2 ORDER BY c" {
000448 0 0 0 {SCAN TABLE t2 USING INDEX i4}
000449 }
000450
000451 # EVIDENCE-OF: R-13931-10421 sqlite> EXPLAIN QUERY PLAN SELECT
000452 # (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2;
000453 # 0|0|0|SCAN TABLE t2
000454 # 0|0|0|EXECUTE SCALAR SUBQUERY 1
000455 # 1|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)
000456 # 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
000457 # 2|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?)
000458 #
000459 det 5.9 {
000460 SELECT (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2
000461 } {
000462 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i4}
000463 0 0 0 {EXECUTE SCALAR SUBQUERY 1}
000464 1 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)}
000465 0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2}
000466 2 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?)}
000467 }
000468
000469 # EVIDENCE-OF: R-50892-45943 sqlite> EXPLAIN QUERY PLAN
000470 # SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x;
000471 # 1|0|0|SCAN TABLE t1 USING COVERING INDEX i2
000472 # 0|0|0|SCAN SUBQUERY 1
000473 # 0|0|0|USE TEMP B-TREE FOR GROUP BY
000474 #
000475 det 5.10 {
000476 SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x
000477 } {
000478 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2}
000479 0 0 0 {SCAN SUBQUERY 1}
000480 0 0 0 {USE TEMP B-TREE FOR GROUP BY}
000481 }
000482
000483 # EVIDENCE-OF: R-46219-33846 sqlite> EXPLAIN QUERY PLAN
000484 # SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1;
000485 # 0|0|0|SEARCH TABLE t2 USING INDEX i4 (c=?)
000486 # 0|1|1|SCAN TABLE t1
000487 #
000488 det 5.11 "SELECT a, b FROM (SELECT * FROM t2 WHERE c=1), t1" {
000489 0 0 0 {SEARCH TABLE t2 USING INDEX i4 (c=?)}
000490 0 1 1 {SCAN TABLE t1 USING COVERING INDEX i2}
000491 }
000492
000493 # EVIDENCE-OF: R-37879-39987 sqlite> EXPLAIN QUERY PLAN
000494 # SELECT a FROM t1 UNION SELECT c FROM t2;
000495 # 1|0|0|SCAN TABLE t1
000496 # 2|0|0|SCAN TABLE t2
000497 # 0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)
000498 #
000499 det 5.12 "SELECT a,b FROM t1 UNION SELECT c, 99 FROM t2" {
000500 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2}
000501 2 0 0 {SCAN TABLE t2 USING COVERING INDEX i4}
000502 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)}
000503 }
000504
000505 # EVIDENCE-OF: R-44864-63011 sqlite> EXPLAIN QUERY PLAN
000506 # SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1;
000507 # 1|0|0|SCAN TABLE t1 USING COVERING INDEX i2
000508 # 2|0|0|SCAN TABLE t2 2|0|0|USE TEMP B-TREE FOR ORDER BY
000509 # 0|0|0|COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)
000510 #
000511 det 5.13 "SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1" {
000512 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i1}
000513 2 0 0 {SCAN TABLE t2}
000514 2 0 0 {USE TEMP B-TREE FOR ORDER BY}
000515 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)}
000516 }
000517
000518
000519 if {![nonzero_reserved_bytes]} {
000520 #-------------------------------------------------------------------------
000521 # The following tests - eqp-6.* - test that the example C code on
000522 # documentation page eqp.html works. The C code is duplicated in test1.c
000523 # and wrapped in Tcl command [print_explain_query_plan]
000524 #
000525 set boilerplate {
000526 proc explain_query_plan {db sql} {
000527 set stmt [sqlite3_prepare_v2 db $sql -1 DUMMY]
000528 print_explain_query_plan $stmt
000529 sqlite3_finalize $stmt
000530 }
000531 sqlite3 db test.db
000532 explain_query_plan db {%SQL%}
000533 db close
000534 exit
000535 }
000536
000537 # Do a "Print Explain Query Plan" test.
000538 proc do_peqp_test {tn sql res} {
000539 set fd [open script.tcl w]
000540 puts $fd [string map [list %SQL% $sql] $::boilerplate]
000541 close $fd
000542
000543 uplevel do_test $tn [list {
000544 set fd [open "|[info nameofexec] script.tcl"]
000545 set data [read $fd]
000546 close $fd
000547 set data
000548 }] [list $res]
000549 }
000550
000551 do_peqp_test 6.1 {
000552 SELECT a, b FROM t1 EXCEPT SELECT d, 99 FROM t2 ORDER BY 1
000553 } [string trimleft {
000554 1 0 0 SCAN TABLE t1 USING COVERING INDEX i2
000555 2 0 0 SCAN TABLE t2
000556 2 0 0 USE TEMP B-TREE FOR ORDER BY
000557 0 0 0 COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)
000558 }]
000559 }
000560
000561 #-------------------------------------------------------------------------
000562 # The following tests - eqp-7.* - test that queries that use the OP_Count
000563 # optimization return something sensible with EQP.
000564 #
000565 drop_all_tables
000566
000567 do_execsql_test 7.0 {
000568 CREATE TABLE t1(a INT, b INT, ex CHAR(100));
000569 CREATE TABLE t2(a INT, b INT, ex CHAR(100));
000570 CREATE INDEX i1 ON t2(a);
000571 }
000572
000573 det 7.1 "SELECT count(*) FROM t1" {
000574 0 0 0 {SCAN TABLE t1}
000575 }
000576
000577 det 7.2 "SELECT count(*) FROM t2" {
000578 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i1}
000579 }
000580
000581 do_execsql_test 7.3 {
000582 INSERT INTO t1(a,b) VALUES(1, 2);
000583 INSERT INTO t1(a,b) VALUES(3, 4);
000584
000585 INSERT INTO t2(a,b) VALUES(1, 2);
000586 INSERT INTO t2(a,b) VALUES(3, 4);
000587 INSERT INTO t2(a,b) VALUES(5, 6);
000588
000589 ANALYZE;
000590 }
000591
000592 db close
000593 sqlite3 db test.db
000594
000595 det 7.4 "SELECT count(*) FROM t1" {
000596 0 0 0 {SCAN TABLE t1}
000597 }
000598
000599 det 7.5 "SELECT count(*) FROM t2" {
000600 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i1}
000601 }
000602
000603 #-------------------------------------------------------------------------
000604 # The following tests - eqp-8.* - test that queries that use the OP_Count
000605 # optimization return something sensible with EQP.
000606 #
000607 drop_all_tables
000608
000609 do_execsql_test 8.0 {
000610 CREATE TABLE t1(a, b, c, PRIMARY KEY(b, c)) WITHOUT ROWID;
000611 CREATE TABLE t2(a, b, c);
000612 }
000613
000614 det 8.1.1 "SELECT * FROM t2" {
000615 0 0 0 {SCAN TABLE t2}
000616 }
000617
000618 det 8.1.2 "SELECT * FROM t2 WHERE rowid=?" {
000619 0 0 0 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)}
000620 }
000621
000622 det 8.1.3 "SELECT count(*) FROM t2" {
000623 0 0 0 {SCAN TABLE t2}
000624 }
000625
000626 det 8.2.1 "SELECT * FROM t1" {
000627 0 0 0 {SCAN TABLE t1}
000628 }
000629
000630 det 8.2.2 "SELECT * FROM t1 WHERE b=?" {
000631 0 0 0 {SEARCH TABLE t1 USING PRIMARY KEY (b=?)}
000632 }
000633
000634 det 8.2.3 "SELECT * FROM t1 WHERE b=? AND c=?" {
000635 0 0 0 {SEARCH TABLE t1 USING PRIMARY KEY (b=? AND c=?)}
000636 }
000637
000638 det 8.2.4 "SELECT count(*) FROM t1" {
000639 0 0 0 {SCAN TABLE t1}
000640 }
000641
000642
000643
000644
000645
000646
000647
000648 finish_test