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