1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
|
# 2012 November 9
#
# The author disclaims copyright to this source code. In place of
# a legal notice, here is a blessing:
#
# May you do good and not evil.
# May you find forgiveness for yourself and forgive others.
# May you share freely, never taking more than you give.
#
#***********************************************************************
#
# Test cases for query planning decisions.
#
# The tests in this file demonstrate the behaviour of the query planner
# in determining the order in which joined tables are scanned.
#
# Assume there are two tables being joined - t1 and t2. Each has a cost
# if it is the outer loop, and a cost if it is the inner loop. As follows:
#
# t1(outer) - cost of scanning t1 as the outer loop.
# t1(inner) - cost of scanning t1 as the inner loop.
# t2(outer) - cost of scanning t2 as the outer loop.
# t2(inner) - cost of scanning t2 as the inner loop.
#
# Depending on the order in which the planner nests the scans, the total
# cost of the join query is one of:
#
# t1(outer) * t2(inner)
# t2(outer) * t1(inner)
#
# The tests in this file attempt to verify that the planner nests joins in
# the correct order when the following are true:
#
# + (t1(outer) * t2(inner)) > (t1(inner) * t2(outer)
# + t1(outer) < t2(outer)
#
# In other words, when the best overall query plan has t2 as the outer loop,
# but when the outer loop is considered independent of the inner, t1 is the
# most efficient choice.
#
# In order to make them more predictable, automatic indexes are turned off for
# the tests in this file.
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix x
do_execsql_test 1.0 {
PRAGMA automatic_index = 0;
CREATE TABLE t1(a, b, c);
CREATE TABLE t2(d, e, f);
CREATE UNIQUE INDEX i1 ON t1(a);
CREATE UNIQUE INDEX i2 ON t2(d);
} {}
foreach {tn sql} {
1 "SELECT * FROM t1, t2 WHERE t1.a=t2.e AND t2.d<t1.b AND t1.c!=10"
2 "SELECT * FROM t2, t1 WHERE t1.a=t2.e AND t2.d<t1.b AND t1.c!=10"
3 "SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=t2.e AND t2.d<t1.b AND t1.c!=10"
} {
do_test 1.$tn {
db eval "EXPLAIN QUERY PLAN $sql"
} {/.*SCAN TABLE t2 .*SEARCH TABLE t1 .*/}
}
do_execsql_test 2.0 {
DROP TABLE t1;
DROP TABLE t2;
CREATE TABLE t1(a, b, c);
CREATE TABLE t2(d, e, f);
CREATE UNIQUE INDEX i1 ON t1(a);
CREATE UNIQUE INDEX i2 ON t1(b);
CREATE UNIQUE INDEX i3 ON t2(d);
} {}
foreach {tn sql} {
1 "SELECT * FROM t1, t2 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e"
2 "SELECT * FROM t2, t1 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e"
3 "SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a>? AND t2.d>t1.c AND t1.b=t2.e"
} {
do_test 2.$tn {
db eval "EXPLAIN QUERY PLAN $sql"
} {/.*SCAN TABLE t2 .*SEARCH TABLE t1 .*/}
}
do_execsql_test 3.0 {
DROP TABLE t1;
DROP TABLE t2;
CREATE TABLE t1(a, b, c);
CREATE TABLE t2(d, e, f);
CREATE UNIQUE INDEX i1 ON t1(a, b);
CREATE INDEX i2 ON t2(d);
} {}
foreach {tn sql} {
1 {SELECT t1.a, t1.b, t2.d, t2.e FROM t1, t2
WHERE t2.d=t1.b AND t1.a=(t2.d+1) AND t1.b = (t2.e+1)}
2 {SELECT t1.a, t1.b, t2.d, t2.e FROM t2, t1
WHERE t2.d=t1.b AND t1.a=(t2.d+1) AND t1.b = (t2.e+1)}
3 {SELECT t1.a, t1.b, t2.d, t2.e FROM t2 CROSS JOIN t1
WHERE t2.d=t1.b AND t1.a=(t2.d+1) AND t1.b = (t2.e+1)}
} {
do_test 3.$tn {
db eval "EXPLAIN QUERY PLAN $sql"
} {/.*SCAN TABLE t2 .*SEARCH TABLE t1 .*/}
}
finish_test
|
