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 #***********************************************************************
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
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);
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"
65 db eval "EXPLAIN QUERY PLAN $sql"
66 } {/.*SCAN TABLE t2\y.*SEARCH TABLE t1\y.*/}
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);
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"
86 db eval "EXPLAIN QUERY PLAN $sql"
87 } {/.*SCAN TABLE t2\y.*SEARCH TABLE t1\y.*/}
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);
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)}
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=?;
122 #-------------------------------------------------------------------------
123 # Test the following case:
125 # ... FROM t1, t2 WHERE (
126 # t2.rowid = +t1.rowid OR (t2.f2 = t1.f1 AND t1.f1!=-1)
129 # where there is an index on t2(f2). The planner should use "t1" as the
130 # outer loop. The inner loop, on "t2", is an OR optimization. One pass
137 # t2.f2=$1 AND $1!=-1
139 # the test is to ensure that on the second pass, the ($1!=-1) condition
140 # is tested before any seek operations are performed - i.e. outside of
141 # the loop through the f2=$1 range of the t2(f2) index.
144 do_execsql_test 5.0 {
147 CREATE INDEX t2f ON t2(f2);
149 INSERT INTO t1 VALUES(-1);
150 INSERT INTO t1 VALUES(-1);
151 INSERT INTO t1 VALUES(-1);
152 INSERT INTO t1 VALUES(-1);
155 SELECT 1 UNION ALL SELECT i+1 FROM w WHERE i<1000
157 INSERT INTO t2 SELECT -1 FROM w;
160 do_execsql_test 5.1 {
161 SELECT count(*) FROM t1, t2 WHERE t2.rowid = +t1.rowid
163 do_test 5.2 { expr [db status vmstep]<200 } 1
165 do_execsql_test 5.3 {
166 SELECT count(*) FROM t1, t2 WHERE (
167 t2.rowid = +t1.rowid OR t2.f2 = t1.f1
170 do_test 5.4 { expr [db status vmstep]>1000 } 1
172 do_execsql_test 5.5 {
173 SELECT count(*) FROM t1, t2 WHERE (
174 t2.rowid = +t1.rowid OR (t2.f2 = t1.f1 AND t1.f1!=-1)
177 do_test 5.6 { expr [db status vmstep]<200 } 1