Merge pull request #229 from Pinkbyte/libressl-build-fix2
[sqlcipher.git] / test / whereF.test
blobb9580bb196d200054c3e5d0802ec3a000b3e76f3
1 # 2012 November 9
3 # The author disclaims copyright to this source code.  In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
11
12 # Test cases for query planning decisions.
16 # The tests in this file demonstrate the behaviour of the query planner
17 # in determining the order in which joined tables are scanned.
19 # Assume there are two tables being joined - t1 and t2. Each has a cost
20 # if it is the outer loop, and a cost if it is the inner loop. As follows:
22 #   t1(outer) - cost of scanning t1 as the outer loop.
23 #   t1(inner) - cost of scanning t1 as the inner loop.
24 #   t2(outer) - cost of scanning t2 as the outer loop.
25 #   t2(inner) - cost of scanning t2 as the inner loop.
27 # Depending on the order in which the planner nests the scans, the total
28 # cost of the join query is one of:
30 #   t1(outer) * t2(inner)
31 #   t2(outer) * t1(inner)
33 # The tests in this file attempt to verify that the planner nests joins in
34 # the correct order when the following are true:
36 #   + (t1(outer) * t2(inner)) > (t1(inner) * t2(outer)
37 #   +  t1(outer) < t2(outer)
39 # In other words, when the best overall query plan has t2 as the outer loop,
40 # but when the outer loop is considered independent of the inner, t1 is the
41 # most efficient choice.
43 # In order to make them more predictable, automatic indexes are turned off for
44 # the tests in this file.
47 set testdir [file dirname $argv0]
48 source $testdir/tester.tcl
49 set testprefix whereF
51 do_execsql_test 1.0 {
52   PRAGMA automatic_index = 0;
53   CREATE TABLE t1(a, b, c);
54   CREATE TABLE t2(d, e, f);
55   CREATE UNIQUE INDEX i1 ON t1(a);
56   CREATE UNIQUE INDEX i2 ON t2(d);
57 } {}
59 foreach {tn sql} {
60   1 "SELECT * FROM t1,           t2 WHERE t1.a=t2.e AND t2.d<t1.b AND t1.c!=10"
61   2 "SELECT * FROM t2,           t1 WHERE t1.a=t2.e AND t2.d<t1.b AND t1.c!=10"
62   3 "SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=t2.e AND t2.d<t1.b AND t1.c!=10"
63 } {
64   do_test 1.$tn {
65     db eval "EXPLAIN QUERY PLAN $sql"
66    } {/.*SCAN TABLE t2\y.*SEARCH TABLE t1\y.*/}
69 do_execsql_test 2.0 {
70   DROP TABLE t1;
71   DROP TABLE t2;
72   CREATE TABLE t1(a, b, c);
73   CREATE TABLE t2(d, e, f);
75   CREATE UNIQUE INDEX i1 ON t1(a);
76   CREATE UNIQUE INDEX i2 ON t1(b);
77   CREATE UNIQUE INDEX i3 ON t2(d);
78 } {}
80 foreach {tn sql} {
81   1 "SELECT * FROM t1,           t2 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e"
82   2 "SELECT * FROM t2,           t1 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e"
83   3 "SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e"
84 } {
85   do_test 2.$tn {
86     db eval "EXPLAIN QUERY PLAN $sql"
87    } {/.*SCAN TABLE t2\y.*SEARCH TABLE t1\y.*/}
90 do_execsql_test 3.0 {
91   DROP TABLE t1;
92   DROP TABLE t2;
93   CREATE TABLE t1(a, b, c);
94   CREATE TABLE t2(d, e, f);
96   CREATE UNIQUE INDEX i1 ON t1(a, b);
97   CREATE INDEX i2 ON t2(d);
98 } {}
100 foreach {tn sql} {
101   1 {SELECT t1.a, t1.b, t2.d, t2.e FROM t1, t2 
102      WHERE t2.d=t1.b AND t1.a=(t2.d+1) AND t1.b = (t2.e+1)}
104   2 {SELECT t1.a, t1.b, t2.d, t2.e FROM t2, t1 
105      WHERE t2.d=t1.b AND t1.a=(t2.d+1) AND t1.b = (t2.e+1)}
107   3 {SELECT t1.a, t1.b, t2.d, t2.e FROM t2 CROSS JOIN t1 
108      WHERE t2.d=t1.b AND t1.a=(t2.d+1) AND t1.b = (t2.e+1)}
109 } {
110   do_test 3.$tn {
111     db eval "EXPLAIN QUERY PLAN $sql"
112    } {/.*SCAN TABLE t2\y.*SEARCH TABLE t1\y.*/}
115 do_execsql_test 4.0 {
116   CREATE TABLE t4(a,b,c,d,e, PRIMARY KEY(a,b,c));
117   CREATE INDEX t4adc ON t4(a,d,c);
118   CREATE UNIQUE INDEX t4aebc ON t4(a,e,b,c);
119   EXPLAIN QUERY PLAN SELECT rowid FROM t4 WHERE a=? AND b=?;
120 } {/a=. AND b=./}
122 finish_test