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
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
|
# 2006 January 31
#
# 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.
#
#***********************************************************************
# This file implements regression tests for SQLite library. The
# focus of this file is testing the join reordering optimization
# in cases that include a LEFT JOIN.
#
# $Id: where3.test,v 1.4 2008/04/17 19:14:02 drh Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl
# The following is from ticket #1652.
#
# A comma join then a left outer join: A,B left join C.
# Arrange indices so that the B table is chosen to go first.
# Also put an index on C, but make sure that A is chosen before C.
#
do_test where3-1.1 {
execsql {
CREATE TABLE t1(a, b);
CREATE TABLE t2(p, q);
CREATE TABLE t3(x, y);
INSERT INTO t1 VALUES(111,'one');
INSERT INTO t1 VALUES(222,'two');
INSERT INTO t1 VALUES(333,'three');
INSERT INTO t2 VALUES(1,111);
INSERT INTO t2 VALUES(2,222);
INSERT INTO t2 VALUES(4,444);
CREATE INDEX t2i1 ON t2(p);
INSERT INTO t3 VALUES(999,'nine');
CREATE INDEX t3i1 ON t3(x);
SELECT * FROM t1, t2 LEFT JOIN t3 ON q=x WHERE p=2 AND a=q;
}
} {222 two 2 222 {} {}}
ifcapable explain {
do_test where3-1.1.1 {
explain_no_trace {SELECT * FROM t1, t2 LEFT JOIN t3 ON q=x
WHERE p=2 AND a=q}
} [explain_no_trace {SELECT * FROM t1, t2 LEFT JOIN t3 ON x=q
WHERE p=2 AND a=q}]
}
# Ticket #1830
#
# This is similar to the above but with the LEFT JOIN on the
# other side.
#
do_test where3-1.2 {
execsql {
CREATE TABLE parent1(parent1key, child1key, Child2key, child3key);
CREATE TABLE child1 ( child1key NVARCHAR, value NVARCHAR );
CREATE UNIQUE INDEX PKIDXChild1 ON child1 ( child1key );
CREATE TABLE child2 ( child2key NVARCHAR, value NVARCHAR );
INSERT INTO parent1(parent1key,child1key,child2key)
VALUES ( 1, 'C1.1', 'C2.1' );
INSERT INTO child1 ( child1key, value ) VALUES ( 'C1.1', 'Value for C1.1' );
INSERT INTO child2 ( child2key, value ) VALUES ( 'C2.1', 'Value for C2.1' );
INSERT INTO parent1 ( parent1key, child1key, child2key )
VALUES ( 2, 'C1.2', 'C2.2' );
INSERT INTO child2 ( child2key, value ) VALUES ( 'C2.2', 'Value for C2.2' );
INSERT INTO parent1 ( parent1key, child1key, child2key )
VALUES ( 3, 'C1.3', 'C2.3' );
INSERT INTO child1 ( child1key, value ) VALUES ( 'C1.3', 'Value for C1.3' );
INSERT INTO child2 ( child2key, value ) VALUES ( 'C2.3', 'Value for C2.3' );
SELECT parent1.parent1key, child1.value, child2.value
FROM parent1
LEFT OUTER JOIN child1 ON child1.child1key = parent1.child1key
INNER JOIN child2 ON child2.child2key = parent1.child2key;
}
} {1 {Value for C1.1} {Value for C2.1} 2 {} {Value for C2.2} 3 {Value for C1.3} {Value for C2.3}}
ifcapable explain {
do_test where3-1.2.1 {
explain_no_trace {
SELECT parent1.parent1key, child1.value, child2.value
FROM parent1
LEFT OUTER JOIN child1 ON child1.child1key = parent1.child1key
INNER JOIN child2 ON child2.child2key = parent1.child2key;
}
} [explain_no_trace {
SELECT parent1.parent1key, child1.value, child2.value
FROM parent1
LEFT OUTER JOIN child1 ON parent1.child1key = child1.child1key
INNER JOIN child2 ON child2.child2key = parent1.child2key;
}]
}
# This procedure executes the SQL. Then it appends
# the ::sqlite_query_plan variable.
#
proc queryplan {sql} {
set ::sqlite_sort_count 0
set data [execsql $sql]
return [concat $data $::sqlite_query_plan]
}
# If you have a from clause of the form: A B C left join D
# then make sure the query optimizer is able to reorder the
# A B C part anyway it wants.
#
# Following the fix to ticket #1652, there was a time when
# the C table would not reorder. So the following reorderings
# were possible:
#
# A B C left join D
# B A C left join D
#
# But these reorders were not allowed
#
# C A B left join D
# A C B left join D
# C B A left join D
# B C A left join D
#
# The following tests are here to verify that the latter four
# reorderings are allowed again.
#
do_test where3-2.1 {
execsql {
CREATE TABLE tA(apk integer primary key, ax);
CREATE TABLE tB(bpk integer primary key, bx);
CREATE TABLE tC(cpk integer primary key, cx);
CREATE TABLE tD(dpk integer primary key, dx);
}
queryplan {
SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
WHERE cpk=bx AND bpk=ax
}
} {tA {} tB * tC * tD *}
do_test where3-2.1.1 {
queryplan {
SELECT * FROM tA, tB, tC LEFT JOIN tD ON cx=dpk
WHERE cpk=bx AND bpk=ax
}
} {tA {} tB * tC * tD *}
do_test where3-2.1.2 {
queryplan {
SELECT * FROM tA, tB, tC LEFT JOIN tD ON cx=dpk
WHERE bx=cpk AND bpk=ax
}
} {tA {} tB * tC * tD *}
do_test where3-2.1.3 {
queryplan {
SELECT * FROM tA, tB, tC LEFT JOIN tD ON cx=dpk
WHERE bx=cpk AND ax=bpk
}
} {tA {} tB * tC * tD *}
do_test where3-2.1.4 {
queryplan {
SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
WHERE bx=cpk AND ax=bpk
}
} {tA {} tB * tC * tD *}
do_test where3-2.1.5 {
queryplan {
SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
WHERE cpk=bx AND ax=bpk
}
} {tA {} tB * tC * tD *}
do_test where3-2.2 {
queryplan {
SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
WHERE cpk=bx AND apk=bx
}
} {tB {} tA * tC * tD *}
do_test where3-2.3 {
queryplan {
SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
WHERE cpk=bx AND apk=bx
}
} {tB {} tA * tC * tD *}
do_test where3-2.4 {
queryplan {
SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
WHERE apk=cx AND bpk=ax
}
} {tC {} tA * tB * tD *}
do_test where3-2.5 {
queryplan {
SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
WHERE cpk=ax AND bpk=cx
}
} {tA {} tC * tB * tD *}
do_test where3-2.5 {
queryplan {
SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
WHERE bpk=cx AND apk=bx
}
} {tC {} tB * tA * tD *}
do_test where3-2.6 {
queryplan {
SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
WHERE cpk=bx AND apk=cx
}
} {tB {} tC * tA * tD *}
finish_test
|
