third_party/sqlite/sqlite-src-3080704/test/eqp.test

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