|
sl@0
|
1 |
# 2001 September 15
|
|
sl@0
|
2 |
#
|
|
sl@0
|
3 |
# The author disclaims copyright to this source code. In place of
|
|
sl@0
|
4 |
# a legal notice, here is a blessing:
|
|
sl@0
|
5 |
#
|
|
sl@0
|
6 |
# May you do good and not evil.
|
|
sl@0
|
7 |
# May you find forgiveness for yourself and forgive others.
|
|
sl@0
|
8 |
# May you share freely, never taking more than you give.
|
|
sl@0
|
9 |
#
|
|
sl@0
|
10 |
#***********************************************************************
|
|
sl@0
|
11 |
# This file implements regression tests for SQLite library. The
|
|
sl@0
|
12 |
# focus of this file is testing the use of indices in WHERE clases.
|
|
sl@0
|
13 |
#
|
|
sl@0
|
14 |
# $Id: where.test,v 1.47 2008/09/01 15:52:11 drh Exp $
|
|
sl@0
|
15 |
|
|
sl@0
|
16 |
set testdir [file dirname $argv0]
|
|
sl@0
|
17 |
source $testdir/tester.tcl
|
|
sl@0
|
18 |
|
|
sl@0
|
19 |
# Build some test data
|
|
sl@0
|
20 |
#
|
|
sl@0
|
21 |
do_test where-1.0 {
|
|
sl@0
|
22 |
execsql {
|
|
sl@0
|
23 |
CREATE TABLE t1(w int, x int, y int);
|
|
sl@0
|
24 |
CREATE TABLE t2(p int, q int, r int, s int);
|
|
sl@0
|
25 |
}
|
|
sl@0
|
26 |
for {set i 1} {$i<=100} {incr i} {
|
|
sl@0
|
27 |
set w $i
|
|
sl@0
|
28 |
set x [expr {int(log($i)/log(2))}]
|
|
sl@0
|
29 |
set y [expr {$i*$i + 2*$i + 1}]
|
|
sl@0
|
30 |
execsql "INSERT INTO t1 VALUES($w,$x,$y)"
|
|
sl@0
|
31 |
}
|
|
sl@0
|
32 |
|
|
sl@0
|
33 |
ifcapable subquery {
|
|
sl@0
|
34 |
execsql {
|
|
sl@0
|
35 |
INSERT INTO t2 SELECT 101-w, x, (SELECT max(y) FROM t1)+1-y, y FROM t1;
|
|
sl@0
|
36 |
}
|
|
sl@0
|
37 |
} else {
|
|
sl@0
|
38 |
set maxy [execsql {select max(y) from t1}]
|
|
sl@0
|
39 |
execsql "
|
|
sl@0
|
40 |
INSERT INTO t2 SELECT 101-w, x, $maxy+1-y, y FROM t1;
|
|
sl@0
|
41 |
"
|
|
sl@0
|
42 |
}
|
|
sl@0
|
43 |
|
|
sl@0
|
44 |
execsql {
|
|
sl@0
|
45 |
CREATE INDEX i1w ON t1(w);
|
|
sl@0
|
46 |
CREATE INDEX i1xy ON t1(x,y);
|
|
sl@0
|
47 |
CREATE INDEX i2p ON t2(p);
|
|
sl@0
|
48 |
CREATE INDEX i2r ON t2(r);
|
|
sl@0
|
49 |
CREATE INDEX i2qs ON t2(q, s);
|
|
sl@0
|
50 |
}
|
|
sl@0
|
51 |
} {}
|
|
sl@0
|
52 |
|
|
sl@0
|
53 |
# Do an SQL statement. Append the search count to the end of the result.
|
|
sl@0
|
54 |
#
|
|
sl@0
|
55 |
proc count sql {
|
|
sl@0
|
56 |
set ::sqlite_search_count 0
|
|
sl@0
|
57 |
return [concat [execsql $sql] $::sqlite_search_count]
|
|
sl@0
|
58 |
}
|
|
sl@0
|
59 |
|
|
sl@0
|
60 |
# Verify that queries use an index. We are using the special variable
|
|
sl@0
|
61 |
# "sqlite_search_count" which tallys the number of executions of MoveTo
|
|
sl@0
|
62 |
# and Next operators in the VDBE. By verifing that the search count is
|
|
sl@0
|
63 |
# small we can be assured that indices are being used properly.
|
|
sl@0
|
64 |
#
|
|
sl@0
|
65 |
do_test where-1.1.1 {
|
|
sl@0
|
66 |
count {SELECT x, y, w FROM t1 WHERE w=10}
|
|
sl@0
|
67 |
} {3 121 10 3}
|
|
sl@0
|
68 |
do_test where-1.1.2 {
|
|
sl@0
|
69 |
set sqlite_query_plan
|
|
sl@0
|
70 |
} {t1 i1w}
|
|
sl@0
|
71 |
do_test where-1.1.3 {
|
|
sl@0
|
72 |
count {SELECT x, y, w AS abc FROM t1 WHERE abc=10}
|
|
sl@0
|
73 |
} {3 121 10 3}
|
|
sl@0
|
74 |
do_test where-1.1.4 {
|
|
sl@0
|
75 |
set sqlite_query_plan
|
|
sl@0
|
76 |
} {t1 i1w}
|
|
sl@0
|
77 |
do_test where-1.2.1 {
|
|
sl@0
|
78 |
count {SELECT x, y, w FROM t1 WHERE w=11}
|
|
sl@0
|
79 |
} {3 144 11 3}
|
|
sl@0
|
80 |
do_test where-1.2.2 {
|
|
sl@0
|
81 |
count {SELECT x, y, w AS abc FROM t1 WHERE abc=11}
|
|
sl@0
|
82 |
} {3 144 11 3}
|
|
sl@0
|
83 |
do_test where-1.3.1 {
|
|
sl@0
|
84 |
count {SELECT x, y, w AS abc FROM t1 WHERE 11=w}
|
|
sl@0
|
85 |
} {3 144 11 3}
|
|
sl@0
|
86 |
do_test where-1.3.2 {
|
|
sl@0
|
87 |
count {SELECT x, y, w AS abc FROM t1 WHERE 11=abc}
|
|
sl@0
|
88 |
} {3 144 11 3}
|
|
sl@0
|
89 |
do_test where-1.4.1 {
|
|
sl@0
|
90 |
count {SELECT w, x, y FROM t1 WHERE 11=w AND x>2}
|
|
sl@0
|
91 |
} {11 3 144 3}
|
|
sl@0
|
92 |
do_test where-1.4.2 {
|
|
sl@0
|
93 |
set sqlite_query_plan
|
|
sl@0
|
94 |
} {t1 i1w}
|
|
sl@0
|
95 |
do_test where-1.4.3 {
|
|
sl@0
|
96 |
count {SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2}
|
|
sl@0
|
97 |
} {11 3 144 3}
|
|
sl@0
|
98 |
do_test where-1.4.4 {
|
|
sl@0
|
99 |
set sqlite_query_plan
|
|
sl@0
|
100 |
} {t1 i1w}
|
|
sl@0
|
101 |
do_test where-1.5 {
|
|
sl@0
|
102 |
count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2}
|
|
sl@0
|
103 |
} {3 144 3}
|
|
sl@0
|
104 |
do_test where-1.5.2 {
|
|
sl@0
|
105 |
set sqlite_query_plan
|
|
sl@0
|
106 |
} {t1 i1w}
|
|
sl@0
|
107 |
do_test where-1.6 {
|
|
sl@0
|
108 |
count {SELECT x, y FROM t1 WHERE y<200 AND x>2 AND w=11}
|
|
sl@0
|
109 |
} {3 144 3}
|
|
sl@0
|
110 |
do_test where-1.7 {
|
|
sl@0
|
111 |
count {SELECT x, y FROM t1 WHERE w=11 AND y<200 AND x>2}
|
|
sl@0
|
112 |
} {3 144 3}
|
|
sl@0
|
113 |
do_test where-1.8 {
|
|
sl@0
|
114 |
count {SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3}
|
|
sl@0
|
115 |
} {3 144 3}
|
|
sl@0
|
116 |
do_test where-1.8.2 {
|
|
sl@0
|
117 |
set sqlite_query_plan
|
|
sl@0
|
118 |
} {t1 i1xy}
|
|
sl@0
|
119 |
do_test where-1.8.3 {
|
|
sl@0
|
120 |
count {SELECT x, y FROM t1 WHERE y=144 AND x=3}
|
|
sl@0
|
121 |
set sqlite_query_plan
|
|
sl@0
|
122 |
} {{} i1xy}
|
|
sl@0
|
123 |
do_test where-1.9 {
|
|
sl@0
|
124 |
count {SELECT x, y FROM t1 WHERE y=144 AND w>10 AND x=3}
|
|
sl@0
|
125 |
} {3 144 3}
|
|
sl@0
|
126 |
do_test where-1.10 {
|
|
sl@0
|
127 |
count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121}
|
|
sl@0
|
128 |
} {3 121 3}
|
|
sl@0
|
129 |
do_test where-1.11 {
|
|
sl@0
|
130 |
count {SELECT x, y FROM t1 WHERE x=3 AND y=100 AND w<10}
|
|
sl@0
|
131 |
} {3 100 3}
|
|
sl@0
|
132 |
|
|
sl@0
|
133 |
# New for SQLite version 2.1: Verify that that inequality constraints
|
|
sl@0
|
134 |
# are used correctly.
|
|
sl@0
|
135 |
#
|
|
sl@0
|
136 |
do_test where-1.12 {
|
|
sl@0
|
137 |
count {SELECT w FROM t1 WHERE x=3 AND y<100}
|
|
sl@0
|
138 |
} {8 3}
|
|
sl@0
|
139 |
do_test where-1.13 {
|
|
sl@0
|
140 |
count {SELECT w FROM t1 WHERE x=3 AND 100>y}
|
|
sl@0
|
141 |
} {8 3}
|
|
sl@0
|
142 |
do_test where-1.14 {
|
|
sl@0
|
143 |
count {SELECT w FROM t1 WHERE 3=x AND y<100}
|
|
sl@0
|
144 |
} {8 3}
|
|
sl@0
|
145 |
do_test where-1.15 {
|
|
sl@0
|
146 |
count {SELECT w FROM t1 WHERE 3=x AND 100>y}
|
|
sl@0
|
147 |
} {8 3}
|
|
sl@0
|
148 |
do_test where-1.16 {
|
|
sl@0
|
149 |
count {SELECT w FROM t1 WHERE x=3 AND y<=100}
|
|
sl@0
|
150 |
} {8 9 5}
|
|
sl@0
|
151 |
do_test where-1.17 {
|
|
sl@0
|
152 |
count {SELECT w FROM t1 WHERE x=3 AND 100>=y}
|
|
sl@0
|
153 |
} {8 9 5}
|
|
sl@0
|
154 |
do_test where-1.18 {
|
|
sl@0
|
155 |
count {SELECT w FROM t1 WHERE x=3 AND y>225}
|
|
sl@0
|
156 |
} {15 3}
|
|
sl@0
|
157 |
do_test where-1.19 {
|
|
sl@0
|
158 |
count {SELECT w FROM t1 WHERE x=3 AND 225<y}
|
|
sl@0
|
159 |
} {15 3}
|
|
sl@0
|
160 |
do_test where-1.20 {
|
|
sl@0
|
161 |
count {SELECT w FROM t1 WHERE x=3 AND y>=225}
|
|
sl@0
|
162 |
} {14 15 5}
|
|
sl@0
|
163 |
do_test where-1.21 {
|
|
sl@0
|
164 |
count {SELECT w FROM t1 WHERE x=3 AND 225<=y}
|
|
sl@0
|
165 |
} {14 15 5}
|
|
sl@0
|
166 |
do_test where-1.22 {
|
|
sl@0
|
167 |
count {SELECT w FROM t1 WHERE x=3 AND y>121 AND y<196}
|
|
sl@0
|
168 |
} {11 12 5}
|
|
sl@0
|
169 |
do_test where-1.23 {
|
|
sl@0
|
170 |
count {SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196}
|
|
sl@0
|
171 |
} {10 11 12 13 9}
|
|
sl@0
|
172 |
do_test where-1.24 {
|
|
sl@0
|
173 |
count {SELECT w FROM t1 WHERE x=3 AND 121<y AND 196>y}
|
|
sl@0
|
174 |
} {11 12 5}
|
|
sl@0
|
175 |
do_test where-1.25 {
|
|
sl@0
|
176 |
count {SELECT w FROM t1 WHERE x=3 AND 121<=y AND 196>=y}
|
|
sl@0
|
177 |
} {10 11 12 13 9}
|
|
sl@0
|
178 |
|
|
sl@0
|
179 |
# Need to work on optimizing the BETWEEN operator.
|
|
sl@0
|
180 |
#
|
|
sl@0
|
181 |
# do_test where-1.26 {
|
|
sl@0
|
182 |
# count {SELECT w FROM t1 WHERE x=3 AND y BETWEEN 121 AND 196}
|
|
sl@0
|
183 |
# } {10 11 12 13 9}
|
|
sl@0
|
184 |
|
|
sl@0
|
185 |
do_test where-1.27 {
|
|
sl@0
|
186 |
count {SELECT w FROM t1 WHERE x=3 AND y+1==122}
|
|
sl@0
|
187 |
} {10 10}
|
|
sl@0
|
188 |
|
|
sl@0
|
189 |
do_test where-1.28 {
|
|
sl@0
|
190 |
count {SELECT w FROM t1 WHERE x+1=4 AND y+1==122}
|
|
sl@0
|
191 |
} {10 99}
|
|
sl@0
|
192 |
do_test where-1.29 {
|
|
sl@0
|
193 |
count {SELECT w FROM t1 WHERE y==121}
|
|
sl@0
|
194 |
} {10 99}
|
|
sl@0
|
195 |
|
|
sl@0
|
196 |
|
|
sl@0
|
197 |
do_test where-1.30 {
|
|
sl@0
|
198 |
count {SELECT w FROM t1 WHERE w>97}
|
|
sl@0
|
199 |
} {98 99 100 3}
|
|
sl@0
|
200 |
do_test where-1.31 {
|
|
sl@0
|
201 |
count {SELECT w FROM t1 WHERE w>=97}
|
|
sl@0
|
202 |
} {97 98 99 100 4}
|
|
sl@0
|
203 |
do_test where-1.33 {
|
|
sl@0
|
204 |
count {SELECT w FROM t1 WHERE w==97}
|
|
sl@0
|
205 |
} {97 2}
|
|
sl@0
|
206 |
do_test where-1.33.1 {
|
|
sl@0
|
207 |
count {SELECT w FROM t1 WHERE w<=97 AND w==97}
|
|
sl@0
|
208 |
} {97 2}
|
|
sl@0
|
209 |
do_test where-1.33.2 {
|
|
sl@0
|
210 |
count {SELECT w FROM t1 WHERE w<98 AND w==97}
|
|
sl@0
|
211 |
} {97 2}
|
|
sl@0
|
212 |
do_test where-1.33.3 {
|
|
sl@0
|
213 |
count {SELECT w FROM t1 WHERE w>=97 AND w==97}
|
|
sl@0
|
214 |
} {97 2}
|
|
sl@0
|
215 |
do_test where-1.33.4 {
|
|
sl@0
|
216 |
count {SELECT w FROM t1 WHERE w>96 AND w==97}
|
|
sl@0
|
217 |
} {97 2}
|
|
sl@0
|
218 |
do_test where-1.33.5 {
|
|
sl@0
|
219 |
count {SELECT w FROM t1 WHERE w==97 AND w==97}
|
|
sl@0
|
220 |
} {97 2}
|
|
sl@0
|
221 |
do_test where-1.34 {
|
|
sl@0
|
222 |
count {SELECT w FROM t1 WHERE w+1==98}
|
|
sl@0
|
223 |
} {97 99}
|
|
sl@0
|
224 |
do_test where-1.35 {
|
|
sl@0
|
225 |
count {SELECT w FROM t1 WHERE w<3}
|
|
sl@0
|
226 |
} {1 2 2}
|
|
sl@0
|
227 |
do_test where-1.36 {
|
|
sl@0
|
228 |
count {SELECT w FROM t1 WHERE w<=3}
|
|
sl@0
|
229 |
} {1 2 3 3}
|
|
sl@0
|
230 |
do_test where-1.37 {
|
|
sl@0
|
231 |
count {SELECT w FROM t1 WHERE w+1<=4 ORDER BY w}
|
|
sl@0
|
232 |
} {1 2 3 99}
|
|
sl@0
|
233 |
|
|
sl@0
|
234 |
do_test where-1.38 {
|
|
sl@0
|
235 |
count {SELECT (w) FROM t1 WHERE (w)>(97)}
|
|
sl@0
|
236 |
} {98 99 100 3}
|
|
sl@0
|
237 |
do_test where-1.39 {
|
|
sl@0
|
238 |
count {SELECT (w) FROM t1 WHERE (w)>=(97)}
|
|
sl@0
|
239 |
} {97 98 99 100 4}
|
|
sl@0
|
240 |
do_test where-1.40 {
|
|
sl@0
|
241 |
count {SELECT (w) FROM t1 WHERE (w)==(97)}
|
|
sl@0
|
242 |
} {97 2}
|
|
sl@0
|
243 |
do_test where-1.41 {
|
|
sl@0
|
244 |
count {SELECT (w) FROM t1 WHERE ((w)+(1))==(98)}
|
|
sl@0
|
245 |
} {97 99}
|
|
sl@0
|
246 |
|
|
sl@0
|
247 |
|
|
sl@0
|
248 |
# Do the same kind of thing except use a join as the data source.
|
|
sl@0
|
249 |
#
|
|
sl@0
|
250 |
do_test where-2.1 {
|
|
sl@0
|
251 |
count {
|
|
sl@0
|
252 |
SELECT w, p FROM t2, t1
|
|
sl@0
|
253 |
WHERE x=q AND y=s AND r=8977
|
|
sl@0
|
254 |
}
|
|
sl@0
|
255 |
} {34 67 6}
|
|
sl@0
|
256 |
do_test where-2.2 {
|
|
sl@0
|
257 |
count {
|
|
sl@0
|
258 |
SELECT w, p FROM t2, t1
|
|
sl@0
|
259 |
WHERE x=q AND s=y AND r=8977
|
|
sl@0
|
260 |
}
|
|
sl@0
|
261 |
} {34 67 6}
|
|
sl@0
|
262 |
do_test where-2.3 {
|
|
sl@0
|
263 |
count {
|
|
sl@0
|
264 |
SELECT w, p FROM t2, t1
|
|
sl@0
|
265 |
WHERE x=q AND s=y AND r=8977 AND w>10
|
|
sl@0
|
266 |
}
|
|
sl@0
|
267 |
} {34 67 6}
|
|
sl@0
|
268 |
do_test where-2.4 {
|
|
sl@0
|
269 |
count {
|
|
sl@0
|
270 |
SELECT w, p FROM t2, t1
|
|
sl@0
|
271 |
WHERE p<80 AND x=q AND s=y AND r=8977 AND w>10
|
|
sl@0
|
272 |
}
|
|
sl@0
|
273 |
} {34 67 6}
|
|
sl@0
|
274 |
do_test where-2.5 {
|
|
sl@0
|
275 |
count {
|
|
sl@0
|
276 |
SELECT w, p FROM t2, t1
|
|
sl@0
|
277 |
WHERE p<80 AND x=q AND 8977=r AND s=y AND w>10
|
|
sl@0
|
278 |
}
|
|
sl@0
|
279 |
} {34 67 6}
|
|
sl@0
|
280 |
do_test where-2.6 {
|
|
sl@0
|
281 |
count {
|
|
sl@0
|
282 |
SELECT w, p FROM t2, t1
|
|
sl@0
|
283 |
WHERE x=q AND p=77 AND s=y AND w>5
|
|
sl@0
|
284 |
}
|
|
sl@0
|
285 |
} {24 77 6}
|
|
sl@0
|
286 |
do_test where-2.7 {
|
|
sl@0
|
287 |
count {
|
|
sl@0
|
288 |
SELECT w, p FROM t1, t2
|
|
sl@0
|
289 |
WHERE x=q AND p>77 AND s=y AND w=5
|
|
sl@0
|
290 |
}
|
|
sl@0
|
291 |
} {5 96 6}
|
|
sl@0
|
292 |
|
|
sl@0
|
293 |
# Lets do a 3-way join.
|
|
sl@0
|
294 |
#
|
|
sl@0
|
295 |
do_test where-3.1 {
|
|
sl@0
|
296 |
count {
|
|
sl@0
|
297 |
SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
|
|
sl@0
|
298 |
WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=11
|
|
sl@0
|
299 |
}
|
|
sl@0
|
300 |
} {11 90 11 8}
|
|
sl@0
|
301 |
do_test where-3.2 {
|
|
sl@0
|
302 |
count {
|
|
sl@0
|
303 |
SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
|
|
sl@0
|
304 |
WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=12
|
|
sl@0
|
305 |
}
|
|
sl@0
|
306 |
} {12 89 12 8}
|
|
sl@0
|
307 |
do_test where-3.3 {
|
|
sl@0
|
308 |
count {
|
|
sl@0
|
309 |
SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
|
|
sl@0
|
310 |
WHERE A.w=15 AND B.p=C.w AND B.r=10202-A.y
|
|
sl@0
|
311 |
}
|
|
sl@0
|
312 |
} {15 86 86 8}
|
|
sl@0
|
313 |
|
|
sl@0
|
314 |
# Test to see that the special case of a constant WHERE clause is
|
|
sl@0
|
315 |
# handled.
|
|
sl@0
|
316 |
#
|
|
sl@0
|
317 |
do_test where-4.1 {
|
|
sl@0
|
318 |
count {
|
|
sl@0
|
319 |
SELECT * FROM t1 WHERE 0
|
|
sl@0
|
320 |
}
|
|
sl@0
|
321 |
} {0}
|
|
sl@0
|
322 |
do_test where-4.2 {
|
|
sl@0
|
323 |
count {
|
|
sl@0
|
324 |
SELECT * FROM t1 WHERE 1 LIMIT 1
|
|
sl@0
|
325 |
}
|
|
sl@0
|
326 |
} {1 0 4 0}
|
|
sl@0
|
327 |
do_test where-4.3 {
|
|
sl@0
|
328 |
execsql {
|
|
sl@0
|
329 |
SELECT 99 WHERE 0
|
|
sl@0
|
330 |
}
|
|
sl@0
|
331 |
} {}
|
|
sl@0
|
332 |
do_test where-4.4 {
|
|
sl@0
|
333 |
execsql {
|
|
sl@0
|
334 |
SELECT 99 WHERE 1
|
|
sl@0
|
335 |
}
|
|
sl@0
|
336 |
} {99}
|
|
sl@0
|
337 |
do_test where-4.5 {
|
|
sl@0
|
338 |
execsql {
|
|
sl@0
|
339 |
SELECT 99 WHERE 0.1
|
|
sl@0
|
340 |
}
|
|
sl@0
|
341 |
} {99}
|
|
sl@0
|
342 |
do_test where-4.6 {
|
|
sl@0
|
343 |
execsql {
|
|
sl@0
|
344 |
SELECT 99 WHERE 0.0
|
|
sl@0
|
345 |
}
|
|
sl@0
|
346 |
} {}
|
|
sl@0
|
347 |
do_test where-4.7 {
|
|
sl@0
|
348 |
execsql {
|
|
sl@0
|
349 |
SELECT count(*) FROM t1 WHERE t1.w
|
|
sl@0
|
350 |
}
|
|
sl@0
|
351 |
} {100}
|
|
sl@0
|
352 |
|
|
sl@0
|
353 |
# Verify that IN operators in a WHERE clause are handled correctly.
|
|
sl@0
|
354 |
# Omit these tests if the build is not capable of sub-queries.
|
|
sl@0
|
355 |
#
|
|
sl@0
|
356 |
ifcapable subquery {
|
|
sl@0
|
357 |
do_test where-5.1 {
|
|
sl@0
|
358 |
count {
|
|
sl@0
|
359 |
SELECT * FROM t1 WHERE rowid IN (1,2,3,1234) order by 1;
|
|
sl@0
|
360 |
}
|
|
sl@0
|
361 |
} {1 0 4 2 1 9 3 1 16 4}
|
|
sl@0
|
362 |
do_test where-5.2 {
|
|
sl@0
|
363 |
count {
|
|
sl@0
|
364 |
SELECT * FROM t1 WHERE rowid+0 IN (1,2,3,1234) order by 1;
|
|
sl@0
|
365 |
}
|
|
sl@0
|
366 |
} {1 0 4 2 1 9 3 1 16 102}
|
|
sl@0
|
367 |
do_test where-5.3 {
|
|
sl@0
|
368 |
count {
|
|
sl@0
|
369 |
SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1;
|
|
sl@0
|
370 |
}
|
|
sl@0
|
371 |
} {1 0 4 2 1 9 3 1 16 14}
|
|
sl@0
|
372 |
do_test where-5.4 {
|
|
sl@0
|
373 |
count {
|
|
sl@0
|
374 |
SELECT * FROM t1 WHERE w+0 IN (-1,1,2,3) order by 1;
|
|
sl@0
|
375 |
}
|
|
sl@0
|
376 |
} {1 0 4 2 1 9 3 1 16 102}
|
|
sl@0
|
377 |
do_test where-5.5 {
|
|
sl@0
|
378 |
count {
|
|
sl@0
|
379 |
SELECT * FROM t1 WHERE rowid IN
|
|
sl@0
|
380 |
(select rowid from t1 where rowid IN (-1,2,4))
|
|
sl@0
|
381 |
ORDER BY 1;
|
|
sl@0
|
382 |
}
|
|
sl@0
|
383 |
} {2 1 9 4 2 25 3}
|
|
sl@0
|
384 |
do_test where-5.6 {
|
|
sl@0
|
385 |
count {
|
|
sl@0
|
386 |
SELECT * FROM t1 WHERE rowid+0 IN
|
|
sl@0
|
387 |
(select rowid from t1 where rowid IN (-1,2,4))
|
|
sl@0
|
388 |
ORDER BY 1;
|
|
sl@0
|
389 |
}
|
|
sl@0
|
390 |
} {2 1 9 4 2 25 103}
|
|
sl@0
|
391 |
do_test where-5.7 {
|
|
sl@0
|
392 |
count {
|
|
sl@0
|
393 |
SELECT * FROM t1 WHERE w IN
|
|
sl@0
|
394 |
(select rowid from t1 where rowid IN (-1,2,4))
|
|
sl@0
|
395 |
ORDER BY 1;
|
|
sl@0
|
396 |
}
|
|
sl@0
|
397 |
} {2 1 9 4 2 25 9}
|
|
sl@0
|
398 |
do_test where-5.8 {
|
|
sl@0
|
399 |
count {
|
|
sl@0
|
400 |
SELECT * FROM t1 WHERE w+0 IN
|
|
sl@0
|
401 |
(select rowid from t1 where rowid IN (-1,2,4))
|
|
sl@0
|
402 |
ORDER BY 1;
|
|
sl@0
|
403 |
}
|
|
sl@0
|
404 |
} {2 1 9 4 2 25 103}
|
|
sl@0
|
405 |
do_test where-5.9 {
|
|
sl@0
|
406 |
count {
|
|
sl@0
|
407 |
SELECT * FROM t1 WHERE x IN (1,7) ORDER BY 1;
|
|
sl@0
|
408 |
}
|
|
sl@0
|
409 |
} {2 1 9 3 1 16 7}
|
|
sl@0
|
410 |
do_test where-5.10 {
|
|
sl@0
|
411 |
count {
|
|
sl@0
|
412 |
SELECT * FROM t1 WHERE x+0 IN (1,7) ORDER BY 1;
|
|
sl@0
|
413 |
}
|
|
sl@0
|
414 |
} {2 1 9 3 1 16 199}
|
|
sl@0
|
415 |
do_test where-5.11 {
|
|
sl@0
|
416 |
count {
|
|
sl@0
|
417 |
SELECT * FROM t1 WHERE y IN (6400,8100) ORDER BY 1;
|
|
sl@0
|
418 |
}
|
|
sl@0
|
419 |
} {79 6 6400 89 6 8100 199}
|
|
sl@0
|
420 |
do_test where-5.12 {
|
|
sl@0
|
421 |
count {
|
|
sl@0
|
422 |
SELECT * FROM t1 WHERE x=6 AND y IN (6400,8100) ORDER BY 1;
|
|
sl@0
|
423 |
}
|
|
sl@0
|
424 |
} {79 6 6400 89 6 8100 7}
|
|
sl@0
|
425 |
do_test where-5.13 {
|
|
sl@0
|
426 |
count {
|
|
sl@0
|
427 |
SELECT * FROM t1 WHERE x IN (1,7) AND y NOT IN (6400,8100) ORDER BY 1;
|
|
sl@0
|
428 |
}
|
|
sl@0
|
429 |
} {2 1 9 3 1 16 7}
|
|
sl@0
|
430 |
do_test where-5.14 {
|
|
sl@0
|
431 |
count {
|
|
sl@0
|
432 |
SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,10) ORDER BY 1;
|
|
sl@0
|
433 |
}
|
|
sl@0
|
434 |
} {2 1 9 8}
|
|
sl@0
|
435 |
do_test where-5.15 {
|
|
sl@0
|
436 |
count {
|
|
sl@0
|
437 |
SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,16) ORDER BY 1;
|
|
sl@0
|
438 |
}
|
|
sl@0
|
439 |
} {2 1 9 3 1 16 11}
|
|
sl@0
|
440 |
}
|
|
sl@0
|
441 |
|
|
sl@0
|
442 |
# This procedure executes the SQL. Then it checks to see if the OP_Sort
|
|
sl@0
|
443 |
# opcode was executed. If an OP_Sort did occur, then "sort" is appended
|
|
sl@0
|
444 |
# to the result. If no OP_Sort happened, then "nosort" is appended.
|
|
sl@0
|
445 |
#
|
|
sl@0
|
446 |
# This procedure is used to check to make sure sorting is or is not
|
|
sl@0
|
447 |
# occurring as expected.
|
|
sl@0
|
448 |
#
|
|
sl@0
|
449 |
proc cksort {sql} {
|
|
sl@0
|
450 |
set ::sqlite_sort_count 0
|
|
sl@0
|
451 |
set data [execsql $sql]
|
|
sl@0
|
452 |
if {$::sqlite_sort_count} {set x sort} {set x nosort}
|
|
sl@0
|
453 |
lappend data $x
|
|
sl@0
|
454 |
return $data
|
|
sl@0
|
455 |
}
|
|
sl@0
|
456 |
# Check out the logic that attempts to implement the ORDER BY clause
|
|
sl@0
|
457 |
# using an index rather than by sorting.
|
|
sl@0
|
458 |
#
|
|
sl@0
|
459 |
do_test where-6.1 {
|
|
sl@0
|
460 |
execsql {
|
|
sl@0
|
461 |
CREATE TABLE t3(a,b,c);
|
|
sl@0
|
462 |
CREATE INDEX t3a ON t3(a);
|
|
sl@0
|
463 |
CREATE INDEX t3bc ON t3(b,c);
|
|
sl@0
|
464 |
CREATE INDEX t3acb ON t3(a,c,b);
|
|
sl@0
|
465 |
INSERT INTO t3 SELECT w, 101-w, y FROM t1;
|
|
sl@0
|
466 |
SELECT count(*), sum(a), sum(b), sum(c) FROM t3;
|
|
sl@0
|
467 |
}
|
|
sl@0
|
468 |
} {100 5050 5050 348550}
|
|
sl@0
|
469 |
do_test where-6.2 {
|
|
sl@0
|
470 |
cksort {
|
|
sl@0
|
471 |
SELECT * FROM t3 ORDER BY a LIMIT 3
|
|
sl@0
|
472 |
}
|
|
sl@0
|
473 |
} {1 100 4 2 99 9 3 98 16 nosort}
|
|
sl@0
|
474 |
do_test where-6.3 {
|
|
sl@0
|
475 |
cksort {
|
|
sl@0
|
476 |
SELECT * FROM t3 ORDER BY a+1 LIMIT 3
|
|
sl@0
|
477 |
}
|
|
sl@0
|
478 |
} {1 100 4 2 99 9 3 98 16 sort}
|
|
sl@0
|
479 |
do_test where-6.4 {
|
|
sl@0
|
480 |
cksort {
|
|
sl@0
|
481 |
SELECT * FROM t3 WHERE a<10 ORDER BY a LIMIT 3
|
|
sl@0
|
482 |
}
|
|
sl@0
|
483 |
} {1 100 4 2 99 9 3 98 16 nosort}
|
|
sl@0
|
484 |
do_test where-6.5 {
|
|
sl@0
|
485 |
cksort {
|
|
sl@0
|
486 |
SELECT * FROM t3 WHERE a>0 AND a<10 ORDER BY a LIMIT 3
|
|
sl@0
|
487 |
}
|
|
sl@0
|
488 |
} {1 100 4 2 99 9 3 98 16 nosort}
|
|
sl@0
|
489 |
do_test where-6.6 {
|
|
sl@0
|
490 |
cksort {
|
|
sl@0
|
491 |
SELECT * FROM t3 WHERE a>0 ORDER BY a LIMIT 3
|
|
sl@0
|
492 |
}
|
|
sl@0
|
493 |
} {1 100 4 2 99 9 3 98 16 nosort}
|
|
sl@0
|
494 |
do_test where-6.7 {
|
|
sl@0
|
495 |
cksort {
|
|
sl@0
|
496 |
SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 3
|
|
sl@0
|
497 |
}
|
|
sl@0
|
498 |
} {1 100 4 2 99 9 3 98 16 nosort}
|
|
sl@0
|
499 |
ifcapable subquery {
|
|
sl@0
|
500 |
do_test where-6.8 {
|
|
sl@0
|
501 |
cksort {
|
|
sl@0
|
502 |
SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a LIMIT 3
|
|
sl@0
|
503 |
}
|
|
sl@0
|
504 |
} {1 100 4 2 99 9 3 98 16 sort}
|
|
sl@0
|
505 |
}
|
|
sl@0
|
506 |
do_test where-6.9.1 {
|
|
sl@0
|
507 |
cksort {
|
|
sl@0
|
508 |
SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3
|
|
sl@0
|
509 |
}
|
|
sl@0
|
510 |
} {1 100 4 nosort}
|
|
sl@0
|
511 |
do_test where-6.9.1.1 {
|
|
sl@0
|
512 |
cksort {
|
|
sl@0
|
513 |
SELECT * FROM t3 WHERE a>=1 AND a=1 AND c>0 ORDER BY a LIMIT 3
|
|
sl@0
|
514 |
}
|
|
sl@0
|
515 |
} {1 100 4 nosort}
|
|
sl@0
|
516 |
do_test where-6.9.1.2 {
|
|
sl@0
|
517 |
cksort {
|
|
sl@0
|
518 |
SELECT * FROM t3 WHERE a<2 AND a=1 AND c>0 ORDER BY a LIMIT 3
|
|
sl@0
|
519 |
}
|
|
sl@0
|
520 |
} {1 100 4 nosort}
|
|
sl@0
|
521 |
do_test where-6.9.2 {
|
|
sl@0
|
522 |
cksort {
|
|
sl@0
|
523 |
SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c LIMIT 3
|
|
sl@0
|
524 |
}
|
|
sl@0
|
525 |
} {1 100 4 nosort}
|
|
sl@0
|
526 |
do_test where-6.9.3 {
|
|
sl@0
|
527 |
cksort {
|
|
sl@0
|
528 |
SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c LIMIT 3
|
|
sl@0
|
529 |
}
|
|
sl@0
|
530 |
} {1 100 4 nosort}
|
|
sl@0
|
531 |
do_test where-6.9.4 {
|
|
sl@0
|
532 |
cksort {
|
|
sl@0
|
533 |
SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC LIMIT 3
|
|
sl@0
|
534 |
}
|
|
sl@0
|
535 |
} {1 100 4 nosort}
|
|
sl@0
|
536 |
do_test where-6.9.5 {
|
|
sl@0
|
537 |
cksort {
|
|
sl@0
|
538 |
SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC, c DESC LIMIT 3
|
|
sl@0
|
539 |
}
|
|
sl@0
|
540 |
} {1 100 4 nosort}
|
|
sl@0
|
541 |
do_test where-6.9.6 {
|
|
sl@0
|
542 |
cksort {
|
|
sl@0
|
543 |
SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c DESC LIMIT 3
|
|
sl@0
|
544 |
}
|
|
sl@0
|
545 |
} {1 100 4 nosort}
|
|
sl@0
|
546 |
do_test where-6.9.7 {
|
|
sl@0
|
547 |
cksort {
|
|
sl@0
|
548 |
SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c,a LIMIT 3
|
|
sl@0
|
549 |
}
|
|
sl@0
|
550 |
} {1 100 4 sort}
|
|
sl@0
|
551 |
do_test where-6.9.8 {
|
|
sl@0
|
552 |
cksort {
|
|
sl@0
|
553 |
SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC, c ASC LIMIT 3
|
|
sl@0
|
554 |
}
|
|
sl@0
|
555 |
} {1 100 4 nosort}
|
|
sl@0
|
556 |
do_test where-6.9.9 {
|
|
sl@0
|
557 |
cksort {
|
|
sl@0
|
558 |
SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a ASC, c DESC LIMIT 3
|
|
sl@0
|
559 |
}
|
|
sl@0
|
560 |
} {1 100 4 nosort}
|
|
sl@0
|
561 |
do_test where-6.10 {
|
|
sl@0
|
562 |
cksort {
|
|
sl@0
|
563 |
SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3
|
|
sl@0
|
564 |
}
|
|
sl@0
|
565 |
} {1 100 4 nosort}
|
|
sl@0
|
566 |
do_test where-6.11 {
|
|
sl@0
|
567 |
cksort {
|
|
sl@0
|
568 |
SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c LIMIT 3
|
|
sl@0
|
569 |
}
|
|
sl@0
|
570 |
} {1 100 4 nosort}
|
|
sl@0
|
571 |
do_test where-6.12 {
|
|
sl@0
|
572 |
cksort {
|
|
sl@0
|
573 |
SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c,b LIMIT 3
|
|
sl@0
|
574 |
}
|
|
sl@0
|
575 |
} {1 100 4 nosort}
|
|
sl@0
|
576 |
do_test where-6.13 {
|
|
sl@0
|
577 |
cksort {
|
|
sl@0
|
578 |
SELECT * FROM t3 WHERE a>0 ORDER BY a DESC LIMIT 3
|
|
sl@0
|
579 |
}
|
|
sl@0
|
580 |
} {100 1 10201 99 2 10000 98 3 9801 nosort}
|
|
sl@0
|
581 |
do_test where-6.13.1 {
|
|
sl@0
|
582 |
cksort {
|
|
sl@0
|
583 |
SELECT * FROM t3 WHERE a>0 ORDER BY -a LIMIT 3
|
|
sl@0
|
584 |
}
|
|
sl@0
|
585 |
} {100 1 10201 99 2 10000 98 3 9801 sort}
|
|
sl@0
|
586 |
do_test where-6.14 {
|
|
sl@0
|
587 |
cksort {
|
|
sl@0
|
588 |
SELECT * FROM t3 ORDER BY b LIMIT 3
|
|
sl@0
|
589 |
}
|
|
sl@0
|
590 |
} {100 1 10201 99 2 10000 98 3 9801 nosort}
|
|
sl@0
|
591 |
do_test where-6.15 {
|
|
sl@0
|
592 |
cksort {
|
|
sl@0
|
593 |
SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t3.a LIMIT 3
|
|
sl@0
|
594 |
}
|
|
sl@0
|
595 |
} {1 0 2 1 3 1 nosort}
|
|
sl@0
|
596 |
do_test where-6.16 {
|
|
sl@0
|
597 |
cksort {
|
|
sl@0
|
598 |
SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t1.x, t3.a LIMIT 3
|
|
sl@0
|
599 |
}
|
|
sl@0
|
600 |
} {1 0 2 1 3 1 sort}
|
|
sl@0
|
601 |
do_test where-6.19 {
|
|
sl@0
|
602 |
cksort {
|
|
sl@0
|
603 |
SELECT y FROM t1 ORDER BY w LIMIT 3;
|
|
sl@0
|
604 |
}
|
|
sl@0
|
605 |
} {4 9 16 nosort}
|
|
sl@0
|
606 |
do_test where-6.20 {
|
|
sl@0
|
607 |
cksort {
|
|
sl@0
|
608 |
SELECT y FROM t1 ORDER BY rowid LIMIT 3;
|
|
sl@0
|
609 |
}
|
|
sl@0
|
610 |
} {4 9 16 nosort}
|
|
sl@0
|
611 |
do_test where-6.21 {
|
|
sl@0
|
612 |
cksort {
|
|
sl@0
|
613 |
SELECT y FROM t1 ORDER BY rowid, y LIMIT 3;
|
|
sl@0
|
614 |
}
|
|
sl@0
|
615 |
} {4 9 16 nosort}
|
|
sl@0
|
616 |
do_test where-6.22 {
|
|
sl@0
|
617 |
cksort {
|
|
sl@0
|
618 |
SELECT y FROM t1 ORDER BY rowid, y DESC LIMIT 3;
|
|
sl@0
|
619 |
}
|
|
sl@0
|
620 |
} {4 9 16 nosort}
|
|
sl@0
|
621 |
do_test where-6.23 {
|
|
sl@0
|
622 |
cksort {
|
|
sl@0
|
623 |
SELECT y FROM t1 WHERE y>4 ORDER BY rowid, w, x LIMIT 3;
|
|
sl@0
|
624 |
}
|
|
sl@0
|
625 |
} {9 16 25 nosort}
|
|
sl@0
|
626 |
do_test where-6.24 {
|
|
sl@0
|
627 |
cksort {
|
|
sl@0
|
628 |
SELECT y FROM t1 WHERE y>=9 ORDER BY rowid, x DESC, w LIMIT 3;
|
|
sl@0
|
629 |
}
|
|
sl@0
|
630 |
} {9 16 25 nosort}
|
|
sl@0
|
631 |
do_test where-6.25 {
|
|
sl@0
|
632 |
cksort {
|
|
sl@0
|
633 |
SELECT y FROM t1 WHERE y>4 AND y<25 ORDER BY rowid;
|
|
sl@0
|
634 |
}
|
|
sl@0
|
635 |
} {9 16 nosort}
|
|
sl@0
|
636 |
do_test where-6.26 {
|
|
sl@0
|
637 |
cksort {
|
|
sl@0
|
638 |
SELECT y FROM t1 WHERE y>=4 AND y<=25 ORDER BY oid;
|
|
sl@0
|
639 |
}
|
|
sl@0
|
640 |
} {4 9 16 25 nosort}
|
|
sl@0
|
641 |
do_test where-6.27 {
|
|
sl@0
|
642 |
cksort {
|
|
sl@0
|
643 |
SELECT y FROM t1 WHERE y<=25 ORDER BY _rowid_, w+y;
|
|
sl@0
|
644 |
}
|
|
sl@0
|
645 |
} {4 9 16 25 nosort}
|
|
sl@0
|
646 |
|
|
sl@0
|
647 |
|
|
sl@0
|
648 |
# Tests for reverse-order sorting.
|
|
sl@0
|
649 |
#
|
|
sl@0
|
650 |
do_test where-7.1 {
|
|
sl@0
|
651 |
cksort {
|
|
sl@0
|
652 |
SELECT w FROM t1 WHERE x=3 ORDER BY y;
|
|
sl@0
|
653 |
}
|
|
sl@0
|
654 |
} {8 9 10 11 12 13 14 15 nosort}
|
|
sl@0
|
655 |
do_test where-7.2 {
|
|
sl@0
|
656 |
cksort {
|
|
sl@0
|
657 |
SELECT w FROM t1 WHERE x=3 ORDER BY y DESC;
|
|
sl@0
|
658 |
}
|
|
sl@0
|
659 |
} {15 14 13 12 11 10 9 8 nosort}
|
|
sl@0
|
660 |
do_test where-7.3 {
|
|
sl@0
|
661 |
cksort {
|
|
sl@0
|
662 |
SELECT w FROM t1 WHERE x=3 AND y>100 ORDER BY y LIMIT 3;
|
|
sl@0
|
663 |
}
|
|
sl@0
|
664 |
} {10 11 12 nosort}
|
|
sl@0
|
665 |
do_test where-7.4 {
|
|
sl@0
|
666 |
cksort {
|
|
sl@0
|
667 |
SELECT w FROM t1 WHERE x=3 AND y>100 ORDER BY y DESC LIMIT 3;
|
|
sl@0
|
668 |
}
|
|
sl@0
|
669 |
} {15 14 13 nosort}
|
|
sl@0
|
670 |
do_test where-7.5 {
|
|
sl@0
|
671 |
cksort {
|
|
sl@0
|
672 |
SELECT w FROM t1 WHERE x=3 AND y>121 ORDER BY y DESC;
|
|
sl@0
|
673 |
}
|
|
sl@0
|
674 |
} {15 14 13 12 11 nosort}
|
|
sl@0
|
675 |
do_test where-7.6 {
|
|
sl@0
|
676 |
cksort {
|
|
sl@0
|
677 |
SELECT w FROM t1 WHERE x=3 AND y>=121 ORDER BY y DESC;
|
|
sl@0
|
678 |
}
|
|
sl@0
|
679 |
} {15 14 13 12 11 10 nosort}
|
|
sl@0
|
680 |
do_test where-7.7 {
|
|
sl@0
|
681 |
cksort {
|
|
sl@0
|
682 |
SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<196 ORDER BY y DESC;
|
|
sl@0
|
683 |
}
|
|
sl@0
|
684 |
} {12 11 10 nosort}
|
|
sl@0
|
685 |
do_test where-7.8 {
|
|
sl@0
|
686 |
cksort {
|
|
sl@0
|
687 |
SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196 ORDER BY y DESC;
|
|
sl@0
|
688 |
}
|
|
sl@0
|
689 |
} {13 12 11 10 nosort}
|
|
sl@0
|
690 |
do_test where-7.9 {
|
|
sl@0
|
691 |
cksort {
|
|
sl@0
|
692 |
SELECT w FROM t1 WHERE x=3 AND y>121 AND y<=196 ORDER BY y DESC;
|
|
sl@0
|
693 |
}
|
|
sl@0
|
694 |
} {13 12 11 nosort}
|
|
sl@0
|
695 |
do_test where-7.10 {
|
|
sl@0
|
696 |
cksort {
|
|
sl@0
|
697 |
SELECT w FROM t1 WHERE x=3 AND y>100 AND y<196 ORDER BY y DESC;
|
|
sl@0
|
698 |
}
|
|
sl@0
|
699 |
} {12 11 10 nosort}
|
|
sl@0
|
700 |
do_test where-7.11 {
|
|
sl@0
|
701 |
cksort {
|
|
sl@0
|
702 |
SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<196 ORDER BY y;
|
|
sl@0
|
703 |
}
|
|
sl@0
|
704 |
} {10 11 12 nosort}
|
|
sl@0
|
705 |
do_test where-7.12 {
|
|
sl@0
|
706 |
cksort {
|
|
sl@0
|
707 |
SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196 ORDER BY y;
|
|
sl@0
|
708 |
}
|
|
sl@0
|
709 |
} {10 11 12 13 nosort}
|
|
sl@0
|
710 |
do_test where-7.13 {
|
|
sl@0
|
711 |
cksort {
|
|
sl@0
|
712 |
SELECT w FROM t1 WHERE x=3 AND y>121 AND y<=196 ORDER BY y;
|
|
sl@0
|
713 |
}
|
|
sl@0
|
714 |
} {11 12 13 nosort}
|
|
sl@0
|
715 |
do_test where-7.14 {
|
|
sl@0
|
716 |
cksort {
|
|
sl@0
|
717 |
SELECT w FROM t1 WHERE x=3 AND y>100 AND y<196 ORDER BY y;
|
|
sl@0
|
718 |
}
|
|
sl@0
|
719 |
} {10 11 12 nosort}
|
|
sl@0
|
720 |
do_test where-7.15 {
|
|
sl@0
|
721 |
cksort {
|
|
sl@0
|
722 |
SELECT w FROM t1 WHERE x=3 AND y<81 ORDER BY y;
|
|
sl@0
|
723 |
}
|
|
sl@0
|
724 |
} {nosort}
|
|
sl@0
|
725 |
do_test where-7.16 {
|
|
sl@0
|
726 |
cksort {
|
|
sl@0
|
727 |
SELECT w FROM t1 WHERE x=3 AND y<=81 ORDER BY y;
|
|
sl@0
|
728 |
}
|
|
sl@0
|
729 |
} {8 nosort}
|
|
sl@0
|
730 |
do_test where-7.17 {
|
|
sl@0
|
731 |
cksort {
|
|
sl@0
|
732 |
SELECT w FROM t1 WHERE x=3 AND y>256 ORDER BY y;
|
|
sl@0
|
733 |
}
|
|
sl@0
|
734 |
} {nosort}
|
|
sl@0
|
735 |
do_test where-7.18 {
|
|
sl@0
|
736 |
cksort {
|
|
sl@0
|
737 |
SELECT w FROM t1 WHERE x=3 AND y>=256 ORDER BY y;
|
|
sl@0
|
738 |
}
|
|
sl@0
|
739 |
} {15 nosort}
|
|
sl@0
|
740 |
do_test where-7.19 {
|
|
sl@0
|
741 |
cksort {
|
|
sl@0
|
742 |
SELECT w FROM t1 WHERE x=3 AND y<81 ORDER BY y DESC;
|
|
sl@0
|
743 |
}
|
|
sl@0
|
744 |
} {nosort}
|
|
sl@0
|
745 |
do_test where-7.20 {
|
|
sl@0
|
746 |
cksort {
|
|
sl@0
|
747 |
SELECT w FROM t1 WHERE x=3 AND y<=81 ORDER BY y DESC;
|
|
sl@0
|
748 |
}
|
|
sl@0
|
749 |
} {8 nosort}
|
|
sl@0
|
750 |
do_test where-7.21 {
|
|
sl@0
|
751 |
cksort {
|
|
sl@0
|
752 |
SELECT w FROM t1 WHERE x=3 AND y>256 ORDER BY y DESC;
|
|
sl@0
|
753 |
}
|
|
sl@0
|
754 |
} {nosort}
|
|
sl@0
|
755 |
do_test where-7.22 {
|
|
sl@0
|
756 |
cksort {
|
|
sl@0
|
757 |
SELECT w FROM t1 WHERE x=3 AND y>=256 ORDER BY y DESC;
|
|
sl@0
|
758 |
}
|
|
sl@0
|
759 |
} {15 nosort}
|
|
sl@0
|
760 |
do_test where-7.23 {
|
|
sl@0
|
761 |
cksort {
|
|
sl@0
|
762 |
SELECT w FROM t1 WHERE x=0 AND y<4 ORDER BY y;
|
|
sl@0
|
763 |
}
|
|
sl@0
|
764 |
} {nosort}
|
|
sl@0
|
765 |
do_test where-7.24 {
|
|
sl@0
|
766 |
cksort {
|
|
sl@0
|
767 |
SELECT w FROM t1 WHERE x=0 AND y<=4 ORDER BY y;
|
|
sl@0
|
768 |
}
|
|
sl@0
|
769 |
} {1 nosort}
|
|
sl@0
|
770 |
do_test where-7.25 {
|
|
sl@0
|
771 |
cksort {
|
|
sl@0
|
772 |
SELECT w FROM t1 WHERE x=6 AND y>10201 ORDER BY y;
|
|
sl@0
|
773 |
}
|
|
sl@0
|
774 |
} {nosort}
|
|
sl@0
|
775 |
do_test where-7.26 {
|
|
sl@0
|
776 |
cksort {
|
|
sl@0
|
777 |
SELECT w FROM t1 WHERE x=6 AND y>=10201 ORDER BY y;
|
|
sl@0
|
778 |
}
|
|
sl@0
|
779 |
} {100 nosort}
|
|
sl@0
|
780 |
do_test where-7.27 {
|
|
sl@0
|
781 |
cksort {
|
|
sl@0
|
782 |
SELECT w FROM t1 WHERE x=0 AND y<4 ORDER BY y DESC;
|
|
sl@0
|
783 |
}
|
|
sl@0
|
784 |
} {nosort}
|
|
sl@0
|
785 |
do_test where-7.28 {
|
|
sl@0
|
786 |
cksort {
|
|
sl@0
|
787 |
SELECT w FROM t1 WHERE x=0 AND y<=4 ORDER BY y DESC;
|
|
sl@0
|
788 |
}
|
|
sl@0
|
789 |
} {1 nosort}
|
|
sl@0
|
790 |
do_test where-7.29 {
|
|
sl@0
|
791 |
cksort {
|
|
sl@0
|
792 |
SELECT w FROM t1 WHERE x=6 AND y>10201 ORDER BY y DESC;
|
|
sl@0
|
793 |
}
|
|
sl@0
|
794 |
} {nosort}
|
|
sl@0
|
795 |
do_test where-7.30 {
|
|
sl@0
|
796 |
cksort {
|
|
sl@0
|
797 |
SELECT w FROM t1 WHERE x=6 AND y>=10201 ORDER BY y DESC;
|
|
sl@0
|
798 |
}
|
|
sl@0
|
799 |
} {100 nosort}
|
|
sl@0
|
800 |
do_test where-7.31 {
|
|
sl@0
|
801 |
cksort {
|
|
sl@0
|
802 |
SELECT y FROM t1 ORDER BY rowid DESC LIMIT 3
|
|
sl@0
|
803 |
}
|
|
sl@0
|
804 |
} {10201 10000 9801 nosort}
|
|
sl@0
|
805 |
do_test where-7.32 {
|
|
sl@0
|
806 |
cksort {
|
|
sl@0
|
807 |
SELECT y FROM t1 WHERE y<25 ORDER BY rowid DESC
|
|
sl@0
|
808 |
}
|
|
sl@0
|
809 |
} {16 9 4 nosort}
|
|
sl@0
|
810 |
do_test where-7.33 {
|
|
sl@0
|
811 |
cksort {
|
|
sl@0
|
812 |
SELECT y FROM t1 WHERE y<=25 ORDER BY rowid DESC
|
|
sl@0
|
813 |
}
|
|
sl@0
|
814 |
} {25 16 9 4 nosort}
|
|
sl@0
|
815 |
do_test where-7.34 {
|
|
sl@0
|
816 |
cksort {
|
|
sl@0
|
817 |
SELECT y FROM t1 WHERE y<25 AND y>4 ORDER BY rowid DESC, y DESC
|
|
sl@0
|
818 |
}
|
|
sl@0
|
819 |
} {16 9 nosort}
|
|
sl@0
|
820 |
do_test where-7.35 {
|
|
sl@0
|
821 |
cksort {
|
|
sl@0
|
822 |
SELECT y FROM t1 WHERE y<25 AND y>=4 ORDER BY rowid DESC
|
|
sl@0
|
823 |
}
|
|
sl@0
|
824 |
} {16 9 4 nosort}
|
|
sl@0
|
825 |
|
|
sl@0
|
826 |
do_test where-8.1 {
|
|
sl@0
|
827 |
execsql {
|
|
sl@0
|
828 |
CREATE TABLE t4 AS SELECT * FROM t1;
|
|
sl@0
|
829 |
CREATE INDEX i4xy ON t4(x,y);
|
|
sl@0
|
830 |
}
|
|
sl@0
|
831 |
cksort {
|
|
sl@0
|
832 |
SELECT w FROM t4 WHERE x=4 and y<1000 ORDER BY y DESC limit 3;
|
|
sl@0
|
833 |
}
|
|
sl@0
|
834 |
} {30 29 28 nosort}
|
|
sl@0
|
835 |
do_test where-8.2 {
|
|
sl@0
|
836 |
execsql {
|
|
sl@0
|
837 |
DELETE FROM t4;
|
|
sl@0
|
838 |
}
|
|
sl@0
|
839 |
cksort {
|
|
sl@0
|
840 |
SELECT w FROM t4 WHERE x=4 and y<1000 ORDER BY y DESC limit 3;
|
|
sl@0
|
841 |
}
|
|
sl@0
|
842 |
} {nosort}
|
|
sl@0
|
843 |
|
|
sl@0
|
844 |
# Make sure searches with an index work with an empty table.
|
|
sl@0
|
845 |
#
|
|
sl@0
|
846 |
do_test where-9.1 {
|
|
sl@0
|
847 |
execsql {
|
|
sl@0
|
848 |
CREATE TABLE t5(x PRIMARY KEY);
|
|
sl@0
|
849 |
SELECT * FROM t5 WHERE x<10;
|
|
sl@0
|
850 |
}
|
|
sl@0
|
851 |
} {}
|
|
sl@0
|
852 |
do_test where-9.2 {
|
|
sl@0
|
853 |
execsql {
|
|
sl@0
|
854 |
SELECT * FROM t5 WHERE x<10 ORDER BY x DESC;
|
|
sl@0
|
855 |
}
|
|
sl@0
|
856 |
} {}
|
|
sl@0
|
857 |
do_test where-9.3 {
|
|
sl@0
|
858 |
execsql {
|
|
sl@0
|
859 |
SELECT * FROM t5 WHERE x=10;
|
|
sl@0
|
860 |
}
|
|
sl@0
|
861 |
} {}
|
|
sl@0
|
862 |
|
|
sl@0
|
863 |
do_test where-10.1 {
|
|
sl@0
|
864 |
execsql {
|
|
sl@0
|
865 |
SELECT 1 WHERE abs(random())<0
|
|
sl@0
|
866 |
}
|
|
sl@0
|
867 |
} {}
|
|
sl@0
|
868 |
do_test where-10.2 {
|
|
sl@0
|
869 |
proc tclvar_func {vname} {return [set ::$vname]}
|
|
sl@0
|
870 |
db function tclvar tclvar_func
|
|
sl@0
|
871 |
set ::v1 0
|
|
sl@0
|
872 |
execsql {
|
|
sl@0
|
873 |
SELECT count(*) FROM t1 WHERE tclvar('v1');
|
|
sl@0
|
874 |
}
|
|
sl@0
|
875 |
} {0}
|
|
sl@0
|
876 |
do_test where-10.3 {
|
|
sl@0
|
877 |
set ::v1 1
|
|
sl@0
|
878 |
execsql {
|
|
sl@0
|
879 |
SELECT count(*) FROM t1 WHERE tclvar('v1');
|
|
sl@0
|
880 |
}
|
|
sl@0
|
881 |
} {100}
|
|
sl@0
|
882 |
do_test where-10.4 {
|
|
sl@0
|
883 |
set ::v1 1
|
|
sl@0
|
884 |
proc tclvar_func {vname} {
|
|
sl@0
|
885 |
upvar #0 $vname v
|
|
sl@0
|
886 |
set v [expr {!$v}]
|
|
sl@0
|
887 |
return $v
|
|
sl@0
|
888 |
}
|
|
sl@0
|
889 |
execsql {
|
|
sl@0
|
890 |
SELECT count(*) FROM t1 WHERE tclvar('v1');
|
|
sl@0
|
891 |
}
|
|
sl@0
|
892 |
} {50}
|
|
sl@0
|
893 |
|
|
sl@0
|
894 |
# Ticket #1376. The query below was causing a segfault.
|
|
sl@0
|
895 |
# The problem was the age-old error of calling realloc() on an
|
|
sl@0
|
896 |
# array while there are still pointers to individual elements of
|
|
sl@0
|
897 |
# that array.
|
|
sl@0
|
898 |
#
|
|
sl@0
|
899 |
do_test where-11.1 {
|
|
sl@0
|
900 |
execsql {
|
|
sl@0
|
901 |
CREATE TABLE t99(Dte INT, X INT);
|
|
sl@0
|
902 |
DELETE FROM t99 WHERE (Dte = 2451337) OR (Dte = 2451339) OR
|
|
sl@0
|
903 |
(Dte BETWEEN 2451345 AND 2451347) OR (Dte = 2451351) OR
|
|
sl@0
|
904 |
(Dte BETWEEN 2451355 AND 2451356) OR (Dte = 2451358) OR
|
|
sl@0
|
905 |
(Dte = 2451362) OR (Dte = 2451365) OR (Dte = 2451367) OR
|
|
sl@0
|
906 |
(Dte BETWEEN 2451372 AND 2451376) OR (Dte BETWEEN 2451382 AND 2451384) OR
|
|
sl@0
|
907 |
(Dte = 2451387) OR (Dte BETWEEN 2451389 AND 2451391) OR
|
|
sl@0
|
908 |
(Dte BETWEEN 2451393 AND 2451395) OR (Dte = 2451400) OR
|
|
sl@0
|
909 |
(Dte = 2451402) OR (Dte = 2451404) OR (Dte BETWEEN 2451416 AND 2451418) OR
|
|
sl@0
|
910 |
(Dte = 2451422) OR (Dte = 2451426) OR (Dte BETWEEN 2451445 AND 2451446) OR
|
|
sl@0
|
911 |
(Dte = 2451456) OR (Dte = 2451458) OR (Dte BETWEEN 2451465 AND 2451467) OR
|
|
sl@0
|
912 |
(Dte BETWEEN 2451469 AND 2451471) OR (Dte = 2451474) OR
|
|
sl@0
|
913 |
(Dte BETWEEN 2451477 AND 2451501) OR (Dte BETWEEN 2451503 AND 2451509) OR
|
|
sl@0
|
914 |
(Dte BETWEEN 2451511 AND 2451514) OR (Dte BETWEEN 2451518 AND 2451521) OR
|
|
sl@0
|
915 |
(Dte BETWEEN 2451523 AND 2451531) OR (Dte BETWEEN 2451533 AND 2451537) OR
|
|
sl@0
|
916 |
(Dte BETWEEN 2451539 AND 2451544) OR (Dte BETWEEN 2451546 AND 2451551) OR
|
|
sl@0
|
917 |
(Dte BETWEEN 2451553 AND 2451555) OR (Dte = 2451557) OR
|
|
sl@0
|
918 |
(Dte BETWEEN 2451559 AND 2451561) OR (Dte = 2451563) OR
|
|
sl@0
|
919 |
(Dte BETWEEN 2451565 AND 2451566) OR (Dte BETWEEN 2451569 AND 2451571) OR
|
|
sl@0
|
920 |
(Dte = 2451573) OR (Dte = 2451575) OR (Dte = 2451577) OR (Dte = 2451581) OR
|
|
sl@0
|
921 |
(Dte BETWEEN 2451583 AND 2451586) OR (Dte BETWEEN 2451588 AND 2451592) OR
|
|
sl@0
|
922 |
(Dte BETWEEN 2451596 AND 2451598) OR (Dte = 2451600) OR
|
|
sl@0
|
923 |
(Dte BETWEEN 2451602 AND 2451603) OR (Dte = 2451606) OR (Dte = 2451611);
|
|
sl@0
|
924 |
}
|
|
sl@0
|
925 |
} {}
|
|
sl@0
|
926 |
|
|
sl@0
|
927 |
# Ticket #2116: Make sure sorting by index works well with nn INTEGER PRIMARY
|
|
sl@0
|
928 |
# KEY.
|
|
sl@0
|
929 |
#
|
|
sl@0
|
930 |
do_test where-12.1 {
|
|
sl@0
|
931 |
execsql {
|
|
sl@0
|
932 |
CREATE TABLE t6(a INTEGER PRIMARY KEY, b TEXT);
|
|
sl@0
|
933 |
INSERT INTO t6 VALUES(1,'one');
|
|
sl@0
|
934 |
INSERT INTO t6 VALUES(4,'four');
|
|
sl@0
|
935 |
CREATE INDEX t6i1 ON t6(b);
|
|
sl@0
|
936 |
}
|
|
sl@0
|
937 |
cksort {
|
|
sl@0
|
938 |
SELECT * FROM t6 ORDER BY b;
|
|
sl@0
|
939 |
}
|
|
sl@0
|
940 |
} {4 four 1 one nosort}
|
|
sl@0
|
941 |
do_test where-12.2 {
|
|
sl@0
|
942 |
cksort {
|
|
sl@0
|
943 |
SELECT * FROM t6 ORDER BY b, a;
|
|
sl@0
|
944 |
}
|
|
sl@0
|
945 |
} {4 four 1 one nosort}
|
|
sl@0
|
946 |
do_test where-12.3 {
|
|
sl@0
|
947 |
cksort {
|
|
sl@0
|
948 |
SELECT * FROM t6 ORDER BY a;
|
|
sl@0
|
949 |
}
|
|
sl@0
|
950 |
} {1 one 4 four nosort}
|
|
sl@0
|
951 |
do_test where-12.4 {
|
|
sl@0
|
952 |
cksort {
|
|
sl@0
|
953 |
SELECT * FROM t6 ORDER BY a, b;
|
|
sl@0
|
954 |
}
|
|
sl@0
|
955 |
} {1 one 4 four nosort}
|
|
sl@0
|
956 |
do_test where-12.5 {
|
|
sl@0
|
957 |
cksort {
|
|
sl@0
|
958 |
SELECT * FROM t6 ORDER BY b DESC;
|
|
sl@0
|
959 |
}
|
|
sl@0
|
960 |
} {1 one 4 four nosort}
|
|
sl@0
|
961 |
do_test where-12.6 {
|
|
sl@0
|
962 |
cksort {
|
|
sl@0
|
963 |
SELECT * FROM t6 ORDER BY b DESC, a DESC;
|
|
sl@0
|
964 |
}
|
|
sl@0
|
965 |
} {1 one 4 four nosort}
|
|
sl@0
|
966 |
do_test where-12.7 {
|
|
sl@0
|
967 |
cksort {
|
|
sl@0
|
968 |
SELECT * FROM t6 ORDER BY b DESC, a ASC;
|
|
sl@0
|
969 |
}
|
|
sl@0
|
970 |
} {1 one 4 four sort}
|
|
sl@0
|
971 |
do_test where-12.8 {
|
|
sl@0
|
972 |
cksort {
|
|
sl@0
|
973 |
SELECT * FROM t6 ORDER BY b ASC, a DESC;
|
|
sl@0
|
974 |
}
|
|
sl@0
|
975 |
} {4 four 1 one sort}
|
|
sl@0
|
976 |
do_test where-12.9 {
|
|
sl@0
|
977 |
cksort {
|
|
sl@0
|
978 |
SELECT * FROM t6 ORDER BY a DESC;
|
|
sl@0
|
979 |
}
|
|
sl@0
|
980 |
} {4 four 1 one nosort}
|
|
sl@0
|
981 |
do_test where-12.10 {
|
|
sl@0
|
982 |
cksort {
|
|
sl@0
|
983 |
SELECT * FROM t6 ORDER BY a DESC, b DESC;
|
|
sl@0
|
984 |
}
|
|
sl@0
|
985 |
} {4 four 1 one nosort}
|
|
sl@0
|
986 |
do_test where-12.11 {
|
|
sl@0
|
987 |
cksort {
|
|
sl@0
|
988 |
SELECT * FROM t6 ORDER BY a DESC, b ASC;
|
|
sl@0
|
989 |
}
|
|
sl@0
|
990 |
} {4 four 1 one nosort}
|
|
sl@0
|
991 |
do_test where-12.12 {
|
|
sl@0
|
992 |
cksort {
|
|
sl@0
|
993 |
SELECT * FROM t6 ORDER BY a ASC, b DESC;
|
|
sl@0
|
994 |
}
|
|
sl@0
|
995 |
} {1 one 4 four nosort}
|
|
sl@0
|
996 |
do_test where-13.1 {
|
|
sl@0
|
997 |
execsql {
|
|
sl@0
|
998 |
CREATE TABLE t7(a INTEGER PRIMARY KEY, b TEXT);
|
|
sl@0
|
999 |
INSERT INTO t7 VALUES(1,'one');
|
|
sl@0
|
1000 |
INSERT INTO t7 VALUES(4,'four');
|
|
sl@0
|
1001 |
CREATE INDEX t7i1 ON t7(b);
|
|
sl@0
|
1002 |
}
|
|
sl@0
|
1003 |
cksort {
|
|
sl@0
|
1004 |
SELECT * FROM t7 ORDER BY b;
|
|
sl@0
|
1005 |
}
|
|
sl@0
|
1006 |
} {4 four 1 one nosort}
|
|
sl@0
|
1007 |
do_test where-13.2 {
|
|
sl@0
|
1008 |
cksort {
|
|
sl@0
|
1009 |
SELECT * FROM t7 ORDER BY b, a;
|
|
sl@0
|
1010 |
}
|
|
sl@0
|
1011 |
} {4 four 1 one nosort}
|
|
sl@0
|
1012 |
do_test where-13.3 {
|
|
sl@0
|
1013 |
cksort {
|
|
sl@0
|
1014 |
SELECT * FROM t7 ORDER BY a;
|
|
sl@0
|
1015 |
}
|
|
sl@0
|
1016 |
} {1 one 4 four nosort}
|
|
sl@0
|
1017 |
do_test where-13.4 {
|
|
sl@0
|
1018 |
cksort {
|
|
sl@0
|
1019 |
SELECT * FROM t7 ORDER BY a, b;
|
|
sl@0
|
1020 |
}
|
|
sl@0
|
1021 |
} {1 one 4 four nosort}
|
|
sl@0
|
1022 |
do_test where-13.5 {
|
|
sl@0
|
1023 |
cksort {
|
|
sl@0
|
1024 |
SELECT * FROM t7 ORDER BY b DESC;
|
|
sl@0
|
1025 |
}
|
|
sl@0
|
1026 |
} {1 one 4 four nosort}
|
|
sl@0
|
1027 |
do_test where-13.6 {
|
|
sl@0
|
1028 |
cksort {
|
|
sl@0
|
1029 |
SELECT * FROM t7 ORDER BY b DESC, a DESC;
|
|
sl@0
|
1030 |
}
|
|
sl@0
|
1031 |
} {1 one 4 four nosort}
|
|
sl@0
|
1032 |
do_test where-13.7 {
|
|
sl@0
|
1033 |
cksort {
|
|
sl@0
|
1034 |
SELECT * FROM t7 ORDER BY b DESC, a ASC;
|
|
sl@0
|
1035 |
}
|
|
sl@0
|
1036 |
} {1 one 4 four sort}
|
|
sl@0
|
1037 |
do_test where-13.8 {
|
|
sl@0
|
1038 |
cksort {
|
|
sl@0
|
1039 |
SELECT * FROM t7 ORDER BY b ASC, a DESC;
|
|
sl@0
|
1040 |
}
|
|
sl@0
|
1041 |
} {4 four 1 one sort}
|
|
sl@0
|
1042 |
do_test where-13.9 {
|
|
sl@0
|
1043 |
cksort {
|
|
sl@0
|
1044 |
SELECT * FROM t7 ORDER BY a DESC;
|
|
sl@0
|
1045 |
}
|
|
sl@0
|
1046 |
} {4 four 1 one nosort}
|
|
sl@0
|
1047 |
do_test where-13.10 {
|
|
sl@0
|
1048 |
cksort {
|
|
sl@0
|
1049 |
SELECT * FROM t7 ORDER BY a DESC, b DESC;
|
|
sl@0
|
1050 |
}
|
|
sl@0
|
1051 |
} {4 four 1 one nosort}
|
|
sl@0
|
1052 |
do_test where-13.11 {
|
|
sl@0
|
1053 |
cksort {
|
|
sl@0
|
1054 |
SELECT * FROM t7 ORDER BY a DESC, b ASC;
|
|
sl@0
|
1055 |
}
|
|
sl@0
|
1056 |
} {4 four 1 one nosort}
|
|
sl@0
|
1057 |
do_test where-13.12 {
|
|
sl@0
|
1058 |
cksort {
|
|
sl@0
|
1059 |
SELECT * FROM t7 ORDER BY a ASC, b DESC;
|
|
sl@0
|
1060 |
}
|
|
sl@0
|
1061 |
} {1 one 4 four nosort}
|
|
sl@0
|
1062 |
|
|
sl@0
|
1063 |
# Ticket #2211.
|
|
sl@0
|
1064 |
#
|
|
sl@0
|
1065 |
# When optimizing out ORDER BY clauses, make sure that trailing terms
|
|
sl@0
|
1066 |
# of the ORDER BY clause do not reference other tables in a join.
|
|
sl@0
|
1067 |
#
|
|
sl@0
|
1068 |
do_test where-14.1 {
|
|
sl@0
|
1069 |
execsql {
|
|
sl@0
|
1070 |
CREATE TABLE t8(a INTEGER PRIMARY KEY, b TEXT UNIQUE);
|
|
sl@0
|
1071 |
INSERT INTO t8 VALUES(1,'one');
|
|
sl@0
|
1072 |
INSERT INTO t8 VALUES(4,'four');
|
|
sl@0
|
1073 |
}
|
|
sl@0
|
1074 |
cksort {
|
|
sl@0
|
1075 |
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b
|
|
sl@0
|
1076 |
}
|
|
sl@0
|
1077 |
} {1/4 1/1 4/4 4/1 sort}
|
|
sl@0
|
1078 |
do_test where-14.2 {
|
|
sl@0
|
1079 |
cksort {
|
|
sl@0
|
1080 |
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b DESC
|
|
sl@0
|
1081 |
}
|
|
sl@0
|
1082 |
} {1/1 1/4 4/1 4/4 sort}
|
|
sl@0
|
1083 |
do_test where-14.3 {
|
|
sl@0
|
1084 |
cksort {
|
|
sl@0
|
1085 |
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b
|
|
sl@0
|
1086 |
}
|
|
sl@0
|
1087 |
} {1/1 1/4 4/1 4/4 nosort}
|
|
sl@0
|
1088 |
do_test where-14.4 {
|
|
sl@0
|
1089 |
cksort {
|
|
sl@0
|
1090 |
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b DESC
|
|
sl@0
|
1091 |
}
|
|
sl@0
|
1092 |
} {1/1 1/4 4/1 4/4 nosort}
|
|
sl@0
|
1093 |
do_test where-14.5 {
|
|
sl@0
|
1094 |
cksort {
|
|
sl@0
|
1095 |
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b
|
|
sl@0
|
1096 |
}
|
|
sl@0
|
1097 |
} {4/1 4/4 1/1 1/4 nosort}
|
|
sl@0
|
1098 |
do_test where-14.6 {
|
|
sl@0
|
1099 |
cksort {
|
|
sl@0
|
1100 |
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b DESC
|
|
sl@0
|
1101 |
}
|
|
sl@0
|
1102 |
} {4/1 4/4 1/1 1/4 nosort}
|
|
sl@0
|
1103 |
do_test where-14.7 {
|
|
sl@0
|
1104 |
cksort {
|
|
sl@0
|
1105 |
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b
|
|
sl@0
|
1106 |
}
|
|
sl@0
|
1107 |
} {4/1 4/4 1/1 1/4 sort}
|
|
sl@0
|
1108 |
do_test where-14.7.1 {
|
|
sl@0
|
1109 |
cksort {
|
|
sl@0
|
1110 |
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a, y.a||y.b
|
|
sl@0
|
1111 |
}
|
|
sl@0
|
1112 |
} {4/1 4/4 1/1 1/4 sort}
|
|
sl@0
|
1113 |
do_test where-14.7.2 {
|
|
sl@0
|
1114 |
cksort {
|
|
sl@0
|
1115 |
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a, x.a||x.b
|
|
sl@0
|
1116 |
}
|
|
sl@0
|
1117 |
} {4/1 4/4 1/1 1/4 nosort}
|
|
sl@0
|
1118 |
do_test where-14.8 {
|
|
sl@0
|
1119 |
cksort {
|
|
sl@0
|
1120 |
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b DESC
|
|
sl@0
|
1121 |
}
|
|
sl@0
|
1122 |
} {4/4 4/1 1/4 1/1 sort}
|
|
sl@0
|
1123 |
do_test where-14.9 {
|
|
sl@0
|
1124 |
cksort {
|
|
sl@0
|
1125 |
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||y.b
|
|
sl@0
|
1126 |
}
|
|
sl@0
|
1127 |
} {4/4 4/1 1/4 1/1 sort}
|
|
sl@0
|
1128 |
do_test where-14.10 {
|
|
sl@0
|
1129 |
cksort {
|
|
sl@0
|
1130 |
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||y.b DESC
|
|
sl@0
|
1131 |
}
|
|
sl@0
|
1132 |
} {4/1 4/4 1/1 1/4 sort}
|
|
sl@0
|
1133 |
do_test where-14.11 {
|
|
sl@0
|
1134 |
cksort {
|
|
sl@0
|
1135 |
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||x.b
|
|
sl@0
|
1136 |
}
|
|
sl@0
|
1137 |
} {4/1 4/4 1/1 1/4 sort}
|
|
sl@0
|
1138 |
do_test where-14.12 {
|
|
sl@0
|
1139 |
cksort {
|
|
sl@0
|
1140 |
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||x.b DESC
|
|
sl@0
|
1141 |
}
|
|
sl@0
|
1142 |
} {4/4 4/1 1/4 1/1 sort}
|
|
sl@0
|
1143 |
|
|
sl@0
|
1144 |
# Ticket #2445.
|
|
sl@0
|
1145 |
#
|
|
sl@0
|
1146 |
# There was a crash that could occur when a where clause contains an
|
|
sl@0
|
1147 |
# alias for an expression in the result set, and that expression retrieves
|
|
sl@0
|
1148 |
# a column of the second or subsequent table in a join.
|
|
sl@0
|
1149 |
#
|
|
sl@0
|
1150 |
do_test where-15.1 {
|
|
sl@0
|
1151 |
execsql {
|
|
sl@0
|
1152 |
CREATE TEMP TABLE t1 (a, b, c, d, e);
|
|
sl@0
|
1153 |
CREATE TEMP TABLE t2 (f);
|
|
sl@0
|
1154 |
SELECT t1.e AS alias FROM t2, t1 WHERE alias = 1 ;
|
|
sl@0
|
1155 |
}
|
|
sl@0
|
1156 |
} {}
|
|
sl@0
|
1157 |
|
|
sl@0
|
1158 |
integrity_check {where-99.0}
|
|
sl@0
|
1159 |
|
|
sl@0
|
1160 |
finish_test
|