blob: fa3bcd836cbe852f4849552bbe71302d78bc02af [file] [log] [blame]
drh588a9a12008-09-01 15:52:10 +00001# 2001 September 15
drh6de4f4c2000-06-12 12:20:48 +00002#
drhb19a2bc2001-09-16 00:13:26 +00003# The author disclaims copyright to this source code. In place of
4# a legal notice, here is a blessing:
drh6de4f4c2000-06-12 12:20:48 +00005#
drhb19a2bc2001-09-16 00:13:26 +00006# 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.
drh6de4f4c2000-06-12 12:20:48 +00009#
10#***********************************************************************
11# This file implements regression tests for SQLite library. The
12# focus of this file is testing the use of indices in WHERE clases.
13#
danielk19773072c5e2008-11-03 09:06:05 +000014# $Id: where.test,v 1.50 2008/11/03 09:06:06 danielk1977 Exp $
drh6de4f4c2000-06-12 12:20:48 +000015
16set testdir [file dirname $argv0]
17source $testdir/tester.tcl
18
19# Build some test data
20#
21do_test where-1.0 {
22 execsql {
23 CREATE TABLE t1(w int, x int, y int);
24 CREATE TABLE t2(p int, q int, r int, s int);
25 }
26 for {set i 1} {$i<=100} {incr i} {
27 set w $i
28 set x [expr {int(log($i)/log(2))}]
29 set y [expr {$i*$i + 2*$i + 1}]
30 execsql "INSERT INTO t1 VALUES($w,$x,$y)"
31 }
danielk19773e8c37e2005-01-21 03:12:14 +000032
33 ifcapable subquery {
34 execsql {
35 INSERT INTO t2 SELECT 101-w, x, (SELECT max(y) FROM t1)+1-y, y FROM t1;
36 }
37 } else {
38 set maxy [execsql {select max(y) from t1}]
39 execsql "
40 INSERT INTO t2 SELECT 101-w, x, $maxy+1-y, y FROM t1;
41 "
42 }
43
drh6de4f4c2000-06-12 12:20:48 +000044 execsql {
drhedb04ed2015-09-04 12:54:01 +000045 CREATE INDEX i1w ON t1("w"); -- Verify quoted identifier names
46 CREATE INDEX i1xy ON t1(`x`,'y' ASC); -- Old MySQL compatibility
drh6de4f4c2000-06-12 12:20:48 +000047 CREATE INDEX i2p ON t2(p);
48 CREATE INDEX i2r ON t2(r);
49 CREATE INDEX i2qs ON t2(q, s);
50 }
51} {}
52
drh487ab3c2001-11-08 00:45:21 +000053# Do an SQL statement. Append the search count to the end of the result.
54#
55proc count sql {
56 set ::sqlite_search_count 0
57 return [concat [execsql $sql] $::sqlite_search_count]
58}
59
60# Verify that queries use an index. We are using the special variable
61# "sqlite_search_count" which tallys the number of executions of MoveTo
62# and Next operators in the VDBE. By verifing that the search count is
63# small we can be assured that indices are being used properly.
drh6de4f4c2000-06-12 12:20:48 +000064#
drh4f07e5f2007-05-14 11:34:46 +000065do_test where-1.1.1 {
66 count {SELECT x, y, w FROM t1 WHERE w=10}
67} {3 121 10 3}
drhe0cc3c22015-05-13 17:54:08 +000068do_test where-1.1.1b {
69 count {SELECT x, y, w FROM t1 WHERE w IS 10}
70} {3 121 10 3}
drh6fa978d2013-05-30 19:29:19 +000071do_eqp_test where-1.1.2 {
72 SELECT x, y, w FROM t1 WHERE w=10
drh8a4380d2013-06-11 02:32:50 +000073} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
drhe0cc3c22015-05-13 17:54:08 +000074do_eqp_test where-1.1.2b {
75 SELECT x, y, w FROM t1 WHERE w IS 10
76} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
drh4f07e5f2007-05-14 11:34:46 +000077do_test where-1.1.3 {
drhd1d38482008-10-07 23:46:38 +000078 db status step
79} {0}
80do_test where-1.1.4 {
81 db eval {SELECT x, y, w FROM t1 WHERE +w=10}
82} {3 121 10}
83do_test where-1.1.5 {
84 db status step
85} {99}
drh6fa978d2013-05-30 19:29:19 +000086do_eqp_test where-1.1.6 {
87 SELECT x, y, w FROM t1 WHERE +w=10
drh8a4380d2013-06-11 02:32:50 +000088} {*SCAN TABLE t1*}
drhd1d38482008-10-07 23:46:38 +000089do_test where-1.1.7 {
drh4f07e5f2007-05-14 11:34:46 +000090 count {SELECT x, y, w AS abc FROM t1 WHERE abc=10}
91} {3 121 10 3}
drh6fa978d2013-05-30 19:29:19 +000092do_eqp_test where-1.1.8 {
93 SELECT x, y, w AS abc FROM t1 WHERE abc=10
drh8a4380d2013-06-11 02:32:50 +000094} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
drhd1d38482008-10-07 23:46:38 +000095do_test where-1.1.9 {
96 db status step
97} {0}
drh4f07e5f2007-05-14 11:34:46 +000098do_test where-1.2.1 {
99 count {SELECT x, y, w FROM t1 WHERE w=11}
100} {3 144 11 3}
101do_test where-1.2.2 {
102 count {SELECT x, y, w AS abc FROM t1 WHERE abc=11}
103} {3 144 11 3}
104do_test where-1.3.1 {
105 count {SELECT x, y, w AS abc FROM t1 WHERE 11=w}
106} {3 144 11 3}
107do_test where-1.3.2 {
108 count {SELECT x, y, w AS abc FROM t1 WHERE 11=abc}
109} {3 144 11 3}
drhe0cc3c22015-05-13 17:54:08 +0000110do_test where-1.3.3 {
111 count {SELECT x, y, w AS abc FROM t1 WHERE 11 IS abc}
112} {3 144 11 3}
drh4f07e5f2007-05-14 11:34:46 +0000113do_test where-1.4.1 {
114 count {SELECT w, x, y FROM t1 WHERE 11=w AND x>2}
115} {11 3 144 3}
drhe0cc3c22015-05-13 17:54:08 +0000116do_test where-1.4.1b {
117 count {SELECT w, x, y FROM t1 WHERE 11 IS w AND x>2}
118} {11 3 144 3}
drh6fa978d2013-05-30 19:29:19 +0000119do_eqp_test where-1.4.2 {
120 SELECT w, x, y FROM t1 WHERE 11=w AND x>2
drh8a4380d2013-06-11 02:32:50 +0000121} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
drhe0cc3c22015-05-13 17:54:08 +0000122do_eqp_test where-1.4.2b {
123 SELECT w, x, y FROM t1 WHERE 11 IS w AND x>2
124} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
drh4f07e5f2007-05-14 11:34:46 +0000125do_test where-1.4.3 {
126 count {SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2}
127} {11 3 144 3}
drh6fa978d2013-05-30 19:29:19 +0000128do_eqp_test where-1.4.4 {
129 SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2
drh8a4380d2013-06-11 02:32:50 +0000130} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
drh6de4f4c2000-06-12 12:20:48 +0000131do_test where-1.5 {
drh487ab3c2001-11-08 00:45:21 +0000132 count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2}
133} {3 144 3}
drh6fa978d2013-05-30 19:29:19 +0000134do_eqp_test where-1.5.2 {
135 SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2
drh8a4380d2013-06-11 02:32:50 +0000136} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
drh6de4f4c2000-06-12 12:20:48 +0000137do_test where-1.6 {
drh487ab3c2001-11-08 00:45:21 +0000138 count {SELECT x, y FROM t1 WHERE y<200 AND x>2 AND w=11}
139} {3 144 3}
drh6de4f4c2000-06-12 12:20:48 +0000140do_test where-1.7 {
drh487ab3c2001-11-08 00:45:21 +0000141 count {SELECT x, y FROM t1 WHERE w=11 AND y<200 AND x>2}
142} {3 144 3}
drh6de4f4c2000-06-12 12:20:48 +0000143do_test where-1.8 {
drh487ab3c2001-11-08 00:45:21 +0000144 count {SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3}
145} {3 144 3}
drh6fa978d2013-05-30 19:29:19 +0000146do_eqp_test where-1.8.2 {
147 SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3
drh8a4380d2013-06-11 02:32:50 +0000148} {*SEARCH TABLE t1 USING INDEX i1xy (x=? AND y=?)*}
drh6fa978d2013-05-30 19:29:19 +0000149do_eqp_test where-1.8.3 {
150 SELECT x, y FROM t1 WHERE y=144 AND x=3
drh8a4380d2013-06-11 02:32:50 +0000151} {*SEARCH TABLE t1 USING COVERING INDEX i1xy (x=? AND y=?)*}
drh6de4f4c2000-06-12 12:20:48 +0000152do_test where-1.9 {
drh487ab3c2001-11-08 00:45:21 +0000153 count {SELECT x, y FROM t1 WHERE y=144 AND w>10 AND x=3}
154} {3 144 3}
drh6de4f4c2000-06-12 12:20:48 +0000155do_test where-1.10 {
drh487ab3c2001-11-08 00:45:21 +0000156 count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121}
157} {3 121 3}
drh6de4f4c2000-06-12 12:20:48 +0000158do_test where-1.11 {
drh487ab3c2001-11-08 00:45:21 +0000159 count {SELECT x, y FROM t1 WHERE x=3 AND y=100 AND w<10}
160} {3 100 3}
drhe0cc3c22015-05-13 17:54:08 +0000161do_test where-1.11b {
162 count {SELECT x, y FROM t1 WHERE x IS 3 AND y IS 100 AND w<10}
163} {3 100 3}
drh487ab3c2001-11-08 00:45:21 +0000164
165# New for SQLite version 2.1: Verify that that inequality constraints
166# are used correctly.
167#
168do_test where-1.12 {
169 count {SELECT w FROM t1 WHERE x=3 AND y<100}
170} {8 3}
drhe0cc3c22015-05-13 17:54:08 +0000171do_test where-1.12b {
172 count {SELECT w FROM t1 WHERE x IS 3 AND y<100}
173} {8 3}
drh487ab3c2001-11-08 00:45:21 +0000174do_test where-1.13 {
175 count {SELECT w FROM t1 WHERE x=3 AND 100>y}
176} {8 3}
177do_test where-1.14 {
178 count {SELECT w FROM t1 WHERE 3=x AND y<100}
179} {8 3}
drhe0cc3c22015-05-13 17:54:08 +0000180do_test where-1.14b {
181 count {SELECT w FROM t1 WHERE 3 IS x AND y<100}
182} {8 3}
drh487ab3c2001-11-08 00:45:21 +0000183do_test where-1.15 {
184 count {SELECT w FROM t1 WHERE 3=x AND 100>y}
185} {8 3}
186do_test where-1.16 {
187 count {SELECT w FROM t1 WHERE x=3 AND y<=100}
188} {8 9 5}
189do_test where-1.17 {
190 count {SELECT w FROM t1 WHERE x=3 AND 100>=y}
191} {8 9 5}
192do_test where-1.18 {
193 count {SELECT w FROM t1 WHERE x=3 AND y>225}
194} {15 3}
drhe0cc3c22015-05-13 17:54:08 +0000195do_test where-1.18b {
196 count {SELECT w FROM t1 WHERE x IS 3 AND y>225}
197} {15 3}
drh487ab3c2001-11-08 00:45:21 +0000198do_test where-1.19 {
199 count {SELECT w FROM t1 WHERE x=3 AND 225<y}
200} {15 3}
201do_test where-1.20 {
202 count {SELECT w FROM t1 WHERE x=3 AND y>=225}
203} {14 15 5}
204do_test where-1.21 {
205 count {SELECT w FROM t1 WHERE x=3 AND 225<=y}
206} {14 15 5}
207do_test where-1.22 {
208 count {SELECT w FROM t1 WHERE x=3 AND y>121 AND y<196}
209} {11 12 5}
drhe0cc3c22015-05-13 17:54:08 +0000210do_test where-1.22b {
211 count {SELECT w FROM t1 WHERE x IS 3 AND y>121 AND y<196}
212} {11 12 5}
drh487ab3c2001-11-08 00:45:21 +0000213do_test where-1.23 {
214 count {SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196}
215} {10 11 12 13 9}
216do_test where-1.24 {
217 count {SELECT w FROM t1 WHERE x=3 AND 121<y AND 196>y}
218} {11 12 5}
219do_test where-1.25 {
220 count {SELECT w FROM t1 WHERE x=3 AND 121<=y AND 196>=y}
221} {10 11 12 13 9}
222
223# Need to work on optimizing the BETWEEN operator.
224#
225# do_test where-1.26 {
226# count {SELECT w FROM t1 WHERE x=3 AND y BETWEEN 121 AND 196}
227# } {10 11 12 13 9}
228
229do_test where-1.27 {
230 count {SELECT w FROM t1 WHERE x=3 AND y+1==122}
danielk197721de2e72007-11-29 17:43:27 +0000231} {10 10}
drh7cf6e4d2004-05-19 14:56:55 +0000232
drh487ab3c2001-11-08 00:45:21 +0000233do_test where-1.28 {
234 count {SELECT w FROM t1 WHERE x+1=4 AND y+1==122}
235} {10 99}
236do_test where-1.29 {
237 count {SELECT w FROM t1 WHERE y==121}
238} {10 99}
239
240
241do_test where-1.30 {
242 count {SELECT w FROM t1 WHERE w>97}
drh9012bcb2004-12-19 00:11:35 +0000243} {98 99 100 3}
drh487ab3c2001-11-08 00:45:21 +0000244do_test where-1.31 {
245 count {SELECT w FROM t1 WHERE w>=97}
drh9012bcb2004-12-19 00:11:35 +0000246} {97 98 99 100 4}
drh487ab3c2001-11-08 00:45:21 +0000247do_test where-1.33 {
248 count {SELECT w FROM t1 WHERE w==97}
drh9012bcb2004-12-19 00:11:35 +0000249} {97 2}
drhac931eb2005-01-11 18:13:56 +0000250do_test where-1.33.1 {
251 count {SELECT w FROM t1 WHERE w<=97 AND w==97}
252} {97 2}
253do_test where-1.33.2 {
254 count {SELECT w FROM t1 WHERE w<98 AND w==97}
255} {97 2}
256do_test where-1.33.3 {
257 count {SELECT w FROM t1 WHERE w>=97 AND w==97}
258} {97 2}
259do_test where-1.33.4 {
260 count {SELECT w FROM t1 WHERE w>96 AND w==97}
261} {97 2}
262do_test where-1.33.5 {
263 count {SELECT w FROM t1 WHERE w==97 AND w==97}
264} {97 2}
drh487ab3c2001-11-08 00:45:21 +0000265do_test where-1.34 {
266 count {SELECT w FROM t1 WHERE w+1==98}
267} {97 99}
268do_test where-1.35 {
269 count {SELECT w FROM t1 WHERE w<3}
drhcfc6ca42014-02-14 23:49:13 +0000270} {1 2 3}
drh487ab3c2001-11-08 00:45:21 +0000271do_test where-1.36 {
272 count {SELECT w FROM t1 WHERE w<=3}
drhcfc6ca42014-02-14 23:49:13 +0000273} {1 2 3 4}
drh487ab3c2001-11-08 00:45:21 +0000274do_test where-1.37 {
drh8aff1012001-12-22 14:49:24 +0000275 count {SELECT w FROM t1 WHERE w+1<=4 ORDER BY w}
drh9012bcb2004-12-19 00:11:35 +0000276} {1 2 3 99}
drh487ab3c2001-11-08 00:45:21 +0000277
drh6977fea2002-10-22 23:38:04 +0000278do_test where-1.38 {
279 count {SELECT (w) FROM t1 WHERE (w)>(97)}
drh9012bcb2004-12-19 00:11:35 +0000280} {98 99 100 3}
drh6977fea2002-10-22 23:38:04 +0000281do_test where-1.39 {
282 count {SELECT (w) FROM t1 WHERE (w)>=(97)}
drh9012bcb2004-12-19 00:11:35 +0000283} {97 98 99 100 4}
drh6977fea2002-10-22 23:38:04 +0000284do_test where-1.40 {
285 count {SELECT (w) FROM t1 WHERE (w)==(97)}
drh9012bcb2004-12-19 00:11:35 +0000286} {97 2}
drh6977fea2002-10-22 23:38:04 +0000287do_test where-1.41 {
288 count {SELECT (w) FROM t1 WHERE ((w)+(1))==(98)}
289} {97 99}
290
drh6de4f4c2000-06-12 12:20:48 +0000291
292# Do the same kind of thing except use a join as the data source.
293#
294do_test where-2.1 {
drh487ab3c2001-11-08 00:45:21 +0000295 count {
296 SELECT w, p FROM t2, t1
drh6de4f4c2000-06-12 12:20:48 +0000297 WHERE x=q AND y=s AND r=8977
298 }
drh487ab3c2001-11-08 00:45:21 +0000299} {34 67 6}
drh6de4f4c2000-06-12 12:20:48 +0000300do_test where-2.2 {
drh487ab3c2001-11-08 00:45:21 +0000301 count {
302 SELECT w, p FROM t2, t1
drh6de4f4c2000-06-12 12:20:48 +0000303 WHERE x=q AND s=y AND r=8977
304 }
drh487ab3c2001-11-08 00:45:21 +0000305} {34 67 6}
drh6de4f4c2000-06-12 12:20:48 +0000306do_test where-2.3 {
drh487ab3c2001-11-08 00:45:21 +0000307 count {
308 SELECT w, p FROM t2, t1
drh6de4f4c2000-06-12 12:20:48 +0000309 WHERE x=q AND s=y AND r=8977 AND w>10
310 }
drh487ab3c2001-11-08 00:45:21 +0000311} {34 67 6}
drh6de4f4c2000-06-12 12:20:48 +0000312do_test where-2.4 {
drh487ab3c2001-11-08 00:45:21 +0000313 count {
314 SELECT w, p FROM t2, t1
drh6de4f4c2000-06-12 12:20:48 +0000315 WHERE p<80 AND x=q AND s=y AND r=8977 AND w>10
316 }
drh487ab3c2001-11-08 00:45:21 +0000317} {34 67 6}
drh6de4f4c2000-06-12 12:20:48 +0000318do_test where-2.5 {
drh487ab3c2001-11-08 00:45:21 +0000319 count {
320 SELECT w, p FROM t2, t1
drh6de4f4c2000-06-12 12:20:48 +0000321 WHERE p<80 AND x=q AND 8977=r AND s=y AND w>10
322 }
drh487ab3c2001-11-08 00:45:21 +0000323} {34 67 6}
drh6de4f4c2000-06-12 12:20:48 +0000324do_test where-2.6 {
drh487ab3c2001-11-08 00:45:21 +0000325 count {
326 SELECT w, p FROM t2, t1
drh6de4f4c2000-06-12 12:20:48 +0000327 WHERE x=q AND p=77 AND s=y AND w>5
328 }
drh487ab3c2001-11-08 00:45:21 +0000329} {24 77 6}
drh6de4f4c2000-06-12 12:20:48 +0000330do_test where-2.7 {
drh487ab3c2001-11-08 00:45:21 +0000331 count {
332 SELECT w, p FROM t1, t2
drh6de4f4c2000-06-12 12:20:48 +0000333 WHERE x=q AND p>77 AND s=y AND w=5
334 }
drh487ab3c2001-11-08 00:45:21 +0000335} {5 96 6}
drh6de4f4c2000-06-12 12:20:48 +0000336
337# Lets do a 3-way join.
338#
339do_test where-3.1 {
drh487ab3c2001-11-08 00:45:21 +0000340 count {
341 SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
drh6de4f4c2000-06-12 12:20:48 +0000342 WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=11
343 }
drh9012bcb2004-12-19 00:11:35 +0000344} {11 90 11 8}
drh6de4f4c2000-06-12 12:20:48 +0000345do_test where-3.2 {
drh487ab3c2001-11-08 00:45:21 +0000346 count {
347 SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
drh6de4f4c2000-06-12 12:20:48 +0000348 WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=12
349 }
drh9012bcb2004-12-19 00:11:35 +0000350} {12 89 12 8}
drh6de4f4c2000-06-12 12:20:48 +0000351do_test where-3.3 {
drh487ab3c2001-11-08 00:45:21 +0000352 count {
353 SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
drh6de4f4c2000-06-12 12:20:48 +0000354 WHERE A.w=15 AND B.p=C.w AND B.r=10202-A.y
355 }
drh9012bcb2004-12-19 00:11:35 +0000356} {15 86 86 8}
drh6de4f4c2000-06-12 12:20:48 +0000357
drh08192d52002-04-30 19:20:28 +0000358# Test to see that the special case of a constant WHERE clause is
359# handled.
360#
361do_test where-4.1 {
362 count {
363 SELECT * FROM t1 WHERE 0
364 }
365} {0}
366do_test where-4.2 {
367 count {
368 SELECT * FROM t1 WHERE 1 LIMIT 1
369 }
drhec7429a2005-10-06 16:53:14 +0000370} {1 0 4 0}
drh08192d52002-04-30 19:20:28 +0000371do_test where-4.3 {
372 execsql {
373 SELECT 99 WHERE 0
374 }
375} {}
376do_test where-4.4 {
377 execsql {
378 SELECT 99 WHERE 1
379 }
380} {99}
drhba0232a2005-06-06 17:27:19 +0000381do_test where-4.5 {
382 execsql {
383 SELECT 99 WHERE 0.1
384 }
385} {99}
386do_test where-4.6 {
387 execsql {
388 SELECT 99 WHERE 0.0
389 }
390} {}
drh85e9e222008-07-15 00:27:34 +0000391do_test where-4.7 {
392 execsql {
393 SELECT count(*) FROM t1 WHERE t1.w
394 }
395} {100}
drh08192d52002-04-30 19:20:28 +0000396
drh48185c12002-06-09 01:55:20 +0000397# Verify that IN operators in a WHERE clause are handled correctly.
danielk19773e8c37e2005-01-21 03:12:14 +0000398# Omit these tests if the build is not capable of sub-queries.
drh48185c12002-06-09 01:55:20 +0000399#
danielk19773e8c37e2005-01-21 03:12:14 +0000400ifcapable subquery {
401 do_test where-5.1 {
402 count {
403 SELECT * FROM t1 WHERE rowid IN (1,2,3,1234) order by 1;
404 }
drh4db38a72005-09-01 12:16:28 +0000405 } {1 0 4 2 1 9 3 1 16 4}
danielk19773e8c37e2005-01-21 03:12:14 +0000406 do_test where-5.2 {
407 count {
408 SELECT * FROM t1 WHERE rowid+0 IN (1,2,3,1234) order by 1;
409 }
danielk197721de2e72007-11-29 17:43:27 +0000410 } {1 0 4 2 1 9 3 1 16 102}
drh1b8fc652013-02-07 21:15:14 +0000411 do_test where-5.3a {
danielk19773e8c37e2005-01-21 03:12:14 +0000412 count {
413 SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1;
414 }
drhb1d607d2015-11-05 22:30:54 +0000415 } {1 0 4 2 1 9 3 1 16 12}
drh1b8fc652013-02-07 21:15:14 +0000416 do_test where-5.3b {
417 count {
418 SELECT * FROM t1 WHERE w IN (3,-1,1,2) order by 1;
419 }
drhb1d607d2015-11-05 22:30:54 +0000420 } {1 0 4 2 1 9 3 1 16 12}
drh1b8fc652013-02-07 21:15:14 +0000421 do_test where-5.3c {
422 count {
423 SELECT * FROM t1 WHERE w IN (3,2,-1,1,2) order by 1;
424 }
drhb1d607d2015-11-05 22:30:54 +0000425 } {1 0 4 2 1 9 3 1 16 12}
drh1b8fc652013-02-07 21:15:14 +0000426 do_test where-5.3d {
427 count {
428 SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1 DESC;
429 }
drhb1d607d2015-11-05 22:30:54 +0000430 } {3 1 16 2 1 9 1 0 4 11}
danielk19773e8c37e2005-01-21 03:12:14 +0000431 do_test where-5.4 {
432 count {
433 SELECT * FROM t1 WHERE w+0 IN (-1,1,2,3) order by 1;
434 }
danielk197721de2e72007-11-29 17:43:27 +0000435 } {1 0 4 2 1 9 3 1 16 102}
danielk19773e8c37e2005-01-21 03:12:14 +0000436 do_test where-5.5 {
437 count {
438 SELECT * FROM t1 WHERE rowid IN
439 (select rowid from t1 where rowid IN (-1,2,4))
440 ORDER BY 1;
441 }
442 } {2 1 9 4 2 25 3}
443 do_test where-5.6 {
444 count {
445 SELECT * FROM t1 WHERE rowid+0 IN
446 (select rowid from t1 where rowid IN (-1,2,4))
447 ORDER BY 1;
448 }
danielk197721de2e72007-11-29 17:43:27 +0000449 } {2 1 9 4 2 25 103}
danielk19773e8c37e2005-01-21 03:12:14 +0000450 do_test where-5.7 {
451 count {
452 SELECT * FROM t1 WHERE w IN
453 (select rowid from t1 where rowid IN (-1,2,4))
454 ORDER BY 1;
455 }
456 } {2 1 9 4 2 25 9}
457 do_test where-5.8 {
458 count {
459 SELECT * FROM t1 WHERE w+0 IN
460 (select rowid from t1 where rowid IN (-1,2,4))
461 ORDER BY 1;
462 }
danielk197721de2e72007-11-29 17:43:27 +0000463 } {2 1 9 4 2 25 103}
danielk19773e8c37e2005-01-21 03:12:14 +0000464 do_test where-5.9 {
465 count {
466 SELECT * FROM t1 WHERE x IN (1,7) ORDER BY 1;
467 }
drhb1d607d2015-11-05 22:30:54 +0000468 } {2 1 9 3 1 16 6}
danielk19773e8c37e2005-01-21 03:12:14 +0000469 do_test where-5.10 {
470 count {
471 SELECT * FROM t1 WHERE x+0 IN (1,7) ORDER BY 1;
472 }
473 } {2 1 9 3 1 16 199}
474 do_test where-5.11 {
475 count {
476 SELECT * FROM t1 WHERE y IN (6400,8100) ORDER BY 1;
477 }
478 } {79 6 6400 89 6 8100 199}
479 do_test where-5.12 {
480 count {
481 SELECT * FROM t1 WHERE x=6 AND y IN (6400,8100) ORDER BY 1;
482 }
drh3adc9ce2005-07-28 16:51:51 +0000483 } {79 6 6400 89 6 8100 7}
danielk19773e8c37e2005-01-21 03:12:14 +0000484 do_test where-5.13 {
485 count {
486 SELECT * FROM t1 WHERE x IN (1,7) AND y NOT IN (6400,8100) ORDER BY 1;
487 }
drhb1d607d2015-11-05 22:30:54 +0000488 } {2 1 9 3 1 16 6}
danielk19773e8c37e2005-01-21 03:12:14 +0000489 do_test where-5.14 {
490 count {
491 SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,10) ORDER BY 1;
492 }
drhfa17e132020-09-01 01:52:03 +0000493 } {2 1 9 5}
drh3adc9ce2005-07-28 16:51:51 +0000494 do_test where-5.15 {
495 count {
496 SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,16) ORDER BY 1;
497 }
drhfa17e132020-09-01 01:52:03 +0000498 } {2 1 9 3 1 16 9}
drh2d96b932013-02-08 18:48:23 +0000499 do_test where-5.100 {
500 db eval {
501 SELECT w, x, y FROM t1 WHERE x IN (1,5) AND y IN (9,8,3025,1000,3969)
502 ORDER BY x, y
503 }
504 } {2 1 9 54 5 3025 62 5 3969}
505 do_test where-5.101 {
506 db eval {
507 SELECT w, x, y FROM t1 WHERE x IN (1,5) AND y IN (9,8,3025,1000,3969)
508 ORDER BY x DESC, y DESC
509 }
510 } {62 5 3969 54 5 3025 2 1 9}
511 do_test where-5.102 {
512 db eval {
513 SELECT w, x, y FROM t1 WHERE x IN (1,5) AND y IN (9,8,3025,1000,3969)
514 ORDER BY x DESC, y
515 }
516 } {54 5 3025 62 5 3969 2 1 9}
517 do_test where-5.103 {
518 db eval {
519 SELECT w, x, y FROM t1 WHERE x IN (1,5) AND y IN (9,8,3025,1000,3969)
520 ORDER BY x, y DESC
521 }
522 } {2 1 9 62 5 3969 54 5 3025}
danielk19773e8c37e2005-01-21 03:12:14 +0000523}
drh48185c12002-06-09 01:55:20 +0000524
drh6bf89572004-11-03 16:27:01 +0000525# This procedure executes the SQL. Then it checks to see if the OP_Sort
526# opcode was executed. If an OP_Sort did occur, then "sort" is appended
527# to the result. If no OP_Sort happened, then "nosort" is appended.
528#
529# This procedure is used to check to make sure sorting is or is not
530# occurring as expected.
drhe3184742002-06-19 14:27:05 +0000531#
532proc cksort {sql} {
533 set data [execsql $sql]
drhd1d38482008-10-07 23:46:38 +0000534 if {[db status sort]} {set x sort} {set x nosort}
drhe3184742002-06-19 14:27:05 +0000535 lappend data $x
536 return $data
537}
538# Check out the logic that attempts to implement the ORDER BY clause
539# using an index rather than by sorting.
540#
541do_test where-6.1 {
542 execsql {
543 CREATE TABLE t3(a,b,c);
544 CREATE INDEX t3a ON t3(a);
545 CREATE INDEX t3bc ON t3(b,c);
546 CREATE INDEX t3acb ON t3(a,c,b);
547 INSERT INTO t3 SELECT w, 101-w, y FROM t1;
548 SELECT count(*), sum(a), sum(b), sum(c) FROM t3;
549 }
drh3d1d95e2005-09-08 10:37:01 +0000550} {100 5050 5050 348550}
drhe3184742002-06-19 14:27:05 +0000551do_test where-6.2 {
552 cksort {
553 SELECT * FROM t3 ORDER BY a LIMIT 3
554 }
555} {1 100 4 2 99 9 3 98 16 nosort}
556do_test where-6.3 {
557 cksort {
558 SELECT * FROM t3 ORDER BY a+1 LIMIT 3
559 }
560} {1 100 4 2 99 9 3 98 16 sort}
561do_test where-6.4 {
562 cksort {
563 SELECT * FROM t3 WHERE a<10 ORDER BY a LIMIT 3
564 }
565} {1 100 4 2 99 9 3 98 16 nosort}
566do_test where-6.5 {
567 cksort {
568 SELECT * FROM t3 WHERE a>0 AND a<10 ORDER BY a LIMIT 3
569 }
570} {1 100 4 2 99 9 3 98 16 nosort}
571do_test where-6.6 {
572 cksort {
573 SELECT * FROM t3 WHERE a>0 ORDER BY a LIMIT 3
574 }
575} {1 100 4 2 99 9 3 98 16 nosort}
drh8c098e62016-02-25 23:21:41 +0000576do_test where-6.7.1 {
drhe3184742002-06-19 14:27:05 +0000577 cksort {
drh8c098e62016-02-25 23:21:41 +0000578 SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 10
drhe3184742002-06-19 14:27:05 +0000579 }
drh8c098e62016-02-25 23:21:41 +0000580} {/1 100 4 2 99 9 3 98 16 .* nosort/}
581do_test where-6.7.2 {
582 cksort {
583 SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 1
584 }
drhf559ed32018-07-28 21:01:55 +0000585} {1 100 4 nosort}
danielk19773e8c37e2005-01-21 03:12:14 +0000586ifcapable subquery {
drh1b8fc652013-02-07 21:15:14 +0000587 do_test where-6.8a {
danielk19773e8c37e2005-01-21 03:12:14 +0000588 cksort {
589 SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a LIMIT 3
590 }
drh1b8fc652013-02-07 21:15:14 +0000591 } {1 100 4 2 99 9 3 98 16 nosort}
592 do_test where-6.8b {
593 cksort {
594 SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a DESC LIMIT 3
595 }
drh2d96b932013-02-08 18:48:23 +0000596 } {9 92 100 7 94 64 5 96 36 nosort}
danielk19773e8c37e2005-01-21 03:12:14 +0000597}
drhdd4852c2002-12-04 21:50:16 +0000598do_test where-6.9.1 {
drhe3184742002-06-19 14:27:05 +0000599 cksort {
600 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3
601 }
602} {1 100 4 nosort}
drhac931eb2005-01-11 18:13:56 +0000603do_test where-6.9.1.1 {
604 cksort {
605 SELECT * FROM t3 WHERE a>=1 AND a=1 AND c>0 ORDER BY a LIMIT 3
606 }
607} {1 100 4 nosort}
608do_test where-6.9.1.2 {
609 cksort {
610 SELECT * FROM t3 WHERE a<2 AND a=1 AND c>0 ORDER BY a LIMIT 3
611 }
612} {1 100 4 nosort}
drhdd4852c2002-12-04 21:50:16 +0000613do_test where-6.9.2 {
614 cksort {
615 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c LIMIT 3
616 }
617} {1 100 4 nosort}
618do_test where-6.9.3 {
619 cksort {
620 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c LIMIT 3
621 }
622} {1 100 4 nosort}
623do_test where-6.9.4 {
624 cksort {
625 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC LIMIT 3
626 }
627} {1 100 4 nosort}
628do_test where-6.9.5 {
629 cksort {
630 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC, c DESC LIMIT 3
631 }
632} {1 100 4 nosort}
633do_test where-6.9.6 {
634 cksort {
635 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c DESC LIMIT 3
636 }
637} {1 100 4 nosort}
638do_test where-6.9.7 {
639 cksort {
640 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c,a LIMIT 3
641 }
drh4fe425a2013-06-12 17:08:06 +0000642} {1 100 4 nosort}
drhdd4852c2002-12-04 21:50:16 +0000643do_test where-6.9.8 {
644 cksort {
645 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC, c ASC LIMIT 3
646 }
drh9012bcb2004-12-19 00:11:35 +0000647} {1 100 4 nosort}
drhdd4852c2002-12-04 21:50:16 +0000648do_test where-6.9.9 {
649 cksort {
650 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a ASC, c DESC LIMIT 3
651 }
drh9012bcb2004-12-19 00:11:35 +0000652} {1 100 4 nosort}
drhe3184742002-06-19 14:27:05 +0000653do_test where-6.10 {
654 cksort {
655 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3
656 }
657} {1 100 4 nosort}
658do_test where-6.11 {
659 cksort {
660 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c LIMIT 3
661 }
662} {1 100 4 nosort}
663do_test where-6.12 {
664 cksort {
665 SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c,b LIMIT 3
666 }
667} {1 100 4 nosort}
668do_test where-6.13 {
669 cksort {
670 SELECT * FROM t3 WHERE a>0 ORDER BY a DESC LIMIT 3
671 }
drhdd4852c2002-12-04 21:50:16 +0000672} {100 1 10201 99 2 10000 98 3 9801 nosort}
673do_test where-6.13.1 {
674 cksort {
675 SELECT * FROM t3 WHERE a>0 ORDER BY -a LIMIT 3
676 }
drhe3184742002-06-19 14:27:05 +0000677} {100 1 10201 99 2 10000 98 3 9801 sort}
678do_test where-6.14 {
679 cksort {
680 SELECT * FROM t3 ORDER BY b LIMIT 3
681 }
682} {100 1 10201 99 2 10000 98 3 9801 nosort}
683do_test where-6.15 {
684 cksort {
685 SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t3.a LIMIT 3
686 }
687} {1 0 2 1 3 1 nosort}
688do_test where-6.16 {
689 cksort {
690 SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t1.x, t3.a LIMIT 3
691 }
692} {1 0 2 1 3 1 sort}
drhc330af12002-08-14 03:03:57 +0000693do_test where-6.19 {
694 cksort {
695 SELECT y FROM t1 ORDER BY w LIMIT 3;
696 }
697} {4 9 16 nosort}
drhb6c29892004-11-22 19:12:19 +0000698do_test where-6.20 {
699 cksort {
700 SELECT y FROM t1 ORDER BY rowid LIMIT 3;
701 }
702} {4 9 16 nosort}
703do_test where-6.21 {
704 cksort {
705 SELECT y FROM t1 ORDER BY rowid, y LIMIT 3;
706 }
drhcc192542006-12-20 03:24:19 +0000707} {4 9 16 nosort}
drhb6c29892004-11-22 19:12:19 +0000708do_test where-6.22 {
709 cksort {
710 SELECT y FROM t1 ORDER BY rowid, y DESC LIMIT 3;
711 }
drhcc192542006-12-20 03:24:19 +0000712} {4 9 16 nosort}
drhb6c29892004-11-22 19:12:19 +0000713do_test where-6.23 {
714 cksort {
715 SELECT y FROM t1 WHERE y>4 ORDER BY rowid, w, x LIMIT 3;
716 }
drhcc192542006-12-20 03:24:19 +0000717} {9 16 25 nosort}
drhb6c29892004-11-22 19:12:19 +0000718do_test where-6.24 {
719 cksort {
720 SELECT y FROM t1 WHERE y>=9 ORDER BY rowid, x DESC, w LIMIT 3;
721 }
drhcc192542006-12-20 03:24:19 +0000722} {9 16 25 nosort}
drhb6c29892004-11-22 19:12:19 +0000723do_test where-6.25 {
724 cksort {
725 SELECT y FROM t1 WHERE y>4 AND y<25 ORDER BY rowid;
726 }
727} {9 16 nosort}
728do_test where-6.26 {
729 cksort {
730 SELECT y FROM t1 WHERE y>=4 AND y<=25 ORDER BY oid;
731 }
732} {4 9 16 25 nosort}
733do_test where-6.27 {
734 cksort {
735 SELECT y FROM t1 WHERE y<=25 ORDER BY _rowid_, w+y;
736 }
drhcc192542006-12-20 03:24:19 +0000737} {4 9 16 25 nosort}
drhb6c29892004-11-22 19:12:19 +0000738
drhc330af12002-08-14 03:03:57 +0000739
drhdd4852c2002-12-04 21:50:16 +0000740# Tests for reverse-order sorting.
741#
742do_test where-7.1 {
743 cksort {
744 SELECT w FROM t1 WHERE x=3 ORDER BY y;
745 }
746} {8 9 10 11 12 13 14 15 nosort}
747do_test where-7.2 {
748 cksort {
749 SELECT w FROM t1 WHERE x=3 ORDER BY y DESC;
750 }
751} {15 14 13 12 11 10 9 8 nosort}
752do_test where-7.3 {
753 cksort {
754 SELECT w FROM t1 WHERE x=3 AND y>100 ORDER BY y LIMIT 3;
755 }
756} {10 11 12 nosort}
757do_test where-7.4 {
758 cksort {
759 SELECT w FROM t1 WHERE x=3 AND y>100 ORDER BY y DESC LIMIT 3;
760 }
761} {15 14 13 nosort}
762do_test where-7.5 {
763 cksort {
764 SELECT w FROM t1 WHERE x=3 AND y>121 ORDER BY y DESC;
765 }
766} {15 14 13 12 11 nosort}
767do_test where-7.6 {
768 cksort {
769 SELECT w FROM t1 WHERE x=3 AND y>=121 ORDER BY y DESC;
770 }
771} {15 14 13 12 11 10 nosort}
772do_test where-7.7 {
773 cksort {
774 SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<196 ORDER BY y DESC;
775 }
776} {12 11 10 nosort}
777do_test where-7.8 {
778 cksort {
779 SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196 ORDER BY y DESC;
780 }
781} {13 12 11 10 nosort}
782do_test where-7.9 {
783 cksort {
784 SELECT w FROM t1 WHERE x=3 AND y>121 AND y<=196 ORDER BY y DESC;
785 }
786} {13 12 11 nosort}
787do_test where-7.10 {
788 cksort {
789 SELECT w FROM t1 WHERE x=3 AND y>100 AND y<196 ORDER BY y DESC;
790 }
791} {12 11 10 nosort}
792do_test where-7.11 {
793 cksort {
794 SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<196 ORDER BY y;
795 }
796} {10 11 12 nosort}
797do_test where-7.12 {
798 cksort {
799 SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196 ORDER BY y;
800 }
801} {10 11 12 13 nosort}
802do_test where-7.13 {
803 cksort {
804 SELECT w FROM t1 WHERE x=3 AND y>121 AND y<=196 ORDER BY y;
805 }
806} {11 12 13 nosort}
807do_test where-7.14 {
808 cksort {
809 SELECT w FROM t1 WHERE x=3 AND y>100 AND y<196 ORDER BY y;
810 }
811} {10 11 12 nosort}
drh1a844c32002-12-04 22:29:28 +0000812do_test where-7.15 {
813 cksort {
814 SELECT w FROM t1 WHERE x=3 AND y<81 ORDER BY y;
815 }
816} {nosort}
817do_test where-7.16 {
818 cksort {
819 SELECT w FROM t1 WHERE x=3 AND y<=81 ORDER BY y;
820 }
821} {8 nosort}
822do_test where-7.17 {
823 cksort {
824 SELECT w FROM t1 WHERE x=3 AND y>256 ORDER BY y;
825 }
826} {nosort}
827do_test where-7.18 {
828 cksort {
829 SELECT w FROM t1 WHERE x=3 AND y>=256 ORDER BY y;
830 }
831} {15 nosort}
832do_test where-7.19 {
833 cksort {
834 SELECT w FROM t1 WHERE x=3 AND y<81 ORDER BY y DESC;
835 }
836} {nosort}
837do_test where-7.20 {
838 cksort {
839 SELECT w FROM t1 WHERE x=3 AND y<=81 ORDER BY y DESC;
840 }
841} {8 nosort}
842do_test where-7.21 {
843 cksort {
844 SELECT w FROM t1 WHERE x=3 AND y>256 ORDER BY y DESC;
845 }
846} {nosort}
847do_test where-7.22 {
848 cksort {
849 SELECT w FROM t1 WHERE x=3 AND y>=256 ORDER BY y DESC;
850 }
851} {15 nosort}
852do_test where-7.23 {
853 cksort {
854 SELECT w FROM t1 WHERE x=0 AND y<4 ORDER BY y;
855 }
856} {nosort}
857do_test where-7.24 {
858 cksort {
859 SELECT w FROM t1 WHERE x=0 AND y<=4 ORDER BY y;
860 }
861} {1 nosort}
862do_test where-7.25 {
863 cksort {
864 SELECT w FROM t1 WHERE x=6 AND y>10201 ORDER BY y;
865 }
866} {nosort}
867do_test where-7.26 {
868 cksort {
869 SELECT w FROM t1 WHERE x=6 AND y>=10201 ORDER BY y;
870 }
871} {100 nosort}
872do_test where-7.27 {
873 cksort {
874 SELECT w FROM t1 WHERE x=0 AND y<4 ORDER BY y DESC;
875 }
876} {nosort}
877do_test where-7.28 {
878 cksort {
879 SELECT w FROM t1 WHERE x=0 AND y<=4 ORDER BY y DESC;
880 }
881} {1 nosort}
882do_test where-7.29 {
883 cksort {
884 SELECT w FROM t1 WHERE x=6 AND y>10201 ORDER BY y DESC;
885 }
886} {nosort}
887do_test where-7.30 {
888 cksort {
889 SELECT w FROM t1 WHERE x=6 AND y>=10201 ORDER BY y DESC;
890 }
891} {100 nosort}
drhb6c29892004-11-22 19:12:19 +0000892do_test where-7.31 {
893 cksort {
894 SELECT y FROM t1 ORDER BY rowid DESC LIMIT 3
895 }
896} {10201 10000 9801 nosort}
897do_test where-7.32 {
898 cksort {
drha21c8492005-09-01 17:47:51 +0000899 SELECT y FROM t1 WHERE y<25 ORDER BY rowid DESC
drhb6c29892004-11-22 19:12:19 +0000900 }
901} {16 9 4 nosort}
902do_test where-7.33 {
903 cksort {
drha21c8492005-09-01 17:47:51 +0000904 SELECT y FROM t1 WHERE y<=25 ORDER BY rowid DESC
drhb6c29892004-11-22 19:12:19 +0000905 }
906} {25 16 9 4 nosort}
907do_test where-7.34 {
908 cksort {
909 SELECT y FROM t1 WHERE y<25 AND y>4 ORDER BY rowid DESC, y DESC
910 }
drhcc192542006-12-20 03:24:19 +0000911} {16 9 nosort}
drhb6c29892004-11-22 19:12:19 +0000912do_test where-7.35 {
913 cksort {
914 SELECT y FROM t1 WHERE y<25 AND y>=4 ORDER BY rowid DESC
915 }
916} {16 9 4 nosort}
drh1a844c32002-12-04 22:29:28 +0000917
918do_test where-8.1 {
919 execsql {
920 CREATE TABLE t4 AS SELECT * FROM t1;
921 CREATE INDEX i4xy ON t4(x,y);
922 }
923 cksort {
924 SELECT w FROM t4 WHERE x=4 and y<1000 ORDER BY y DESC limit 3;
925 }
926} {30 29 28 nosort}
927do_test where-8.2 {
928 execsql {
929 DELETE FROM t4;
930 }
931 cksort {
932 SELECT w FROM t4 WHERE x=4 and y<1000 ORDER BY y DESC limit 3;
933 }
934} {nosort}
935
drhd4d595f2003-04-17 12:44:23 +0000936# Make sure searches with an index work with an empty table.
937#
938do_test where-9.1 {
939 execsql {
940 CREATE TABLE t5(x PRIMARY KEY);
941 SELECT * FROM t5 WHERE x<10;
942 }
943} {}
944do_test where-9.2 {
945 execsql {
946 SELECT * FROM t5 WHERE x<10 ORDER BY x DESC;
947 }
948} {}
949do_test where-9.3 {
950 execsql {
951 SELECT * FROM t5 WHERE x=10;
952 }
953} {}
954
drh37ea94b2003-04-19 16:34:04 +0000955do_test where-10.1 {
956 execsql {
957 SELECT 1 WHERE abs(random())<0
958 }
959} {}
drh7bdc0c12003-04-19 17:27:24 +0000960do_test where-10.2 {
961 proc tclvar_func {vname} {return [set ::$vname]}
962 db function tclvar tclvar_func
963 set ::v1 0
964 execsql {
965 SELECT count(*) FROM t1 WHERE tclvar('v1');
966 }
967} {0}
968do_test where-10.3 {
969 set ::v1 1
970 execsql {
971 SELECT count(*) FROM t1 WHERE tclvar('v1');
972 }
973} {100}
974do_test where-10.4 {
975 set ::v1 1
976 proc tclvar_func {vname} {
977 upvar #0 $vname v
978 set v [expr {!$v}]
979 return $v
980 }
981 execsql {
982 SELECT count(*) FROM t1 WHERE tclvar('v1');
983 }
984} {50}
985
drh9eb20282005-08-24 03:52:18 +0000986# Ticket #1376. The query below was causing a segfault.
987# The problem was the age-old error of calling realloc() on an
988# array while there are still pointers to individual elements of
989# that array.
990#
991do_test where-11.1 {
drh9eb20282005-08-24 03:52:18 +0000992 execsql {
993 CREATE TABLE t99(Dte INT, X INT);
994 DELETE FROM t99 WHERE (Dte = 2451337) OR (Dte = 2451339) OR
995 (Dte BETWEEN 2451345 AND 2451347) OR (Dte = 2451351) OR
996 (Dte BETWEEN 2451355 AND 2451356) OR (Dte = 2451358) OR
997 (Dte = 2451362) OR (Dte = 2451365) OR (Dte = 2451367) OR
998 (Dte BETWEEN 2451372 AND 2451376) OR (Dte BETWEEN 2451382 AND 2451384) OR
999 (Dte = 2451387) OR (Dte BETWEEN 2451389 AND 2451391) OR
1000 (Dte BETWEEN 2451393 AND 2451395) OR (Dte = 2451400) OR
1001 (Dte = 2451402) OR (Dte = 2451404) OR (Dte BETWEEN 2451416 AND 2451418) OR
1002 (Dte = 2451422) OR (Dte = 2451426) OR (Dte BETWEEN 2451445 AND 2451446) OR
1003 (Dte = 2451456) OR (Dte = 2451458) OR (Dte BETWEEN 2451465 AND 2451467) OR
1004 (Dte BETWEEN 2451469 AND 2451471) OR (Dte = 2451474) OR
1005 (Dte BETWEEN 2451477 AND 2451501) OR (Dte BETWEEN 2451503 AND 2451509) OR
1006 (Dte BETWEEN 2451511 AND 2451514) OR (Dte BETWEEN 2451518 AND 2451521) OR
1007 (Dte BETWEEN 2451523 AND 2451531) OR (Dte BETWEEN 2451533 AND 2451537) OR
1008 (Dte BETWEEN 2451539 AND 2451544) OR (Dte BETWEEN 2451546 AND 2451551) OR
1009 (Dte BETWEEN 2451553 AND 2451555) OR (Dte = 2451557) OR
1010 (Dte BETWEEN 2451559 AND 2451561) OR (Dte = 2451563) OR
1011 (Dte BETWEEN 2451565 AND 2451566) OR (Dte BETWEEN 2451569 AND 2451571) OR
1012 (Dte = 2451573) OR (Dte = 2451575) OR (Dte = 2451577) OR (Dte = 2451581) OR
1013 (Dte BETWEEN 2451583 AND 2451586) OR (Dte BETWEEN 2451588 AND 2451592) OR
1014 (Dte BETWEEN 2451596 AND 2451598) OR (Dte = 2451600) OR
1015 (Dte BETWEEN 2451602 AND 2451603) OR (Dte = 2451606) OR (Dte = 2451611);
1016 }
1017} {}
1018
drhcc192542006-12-20 03:24:19 +00001019# Ticket #2116: Make sure sorting by index works well with nn INTEGER PRIMARY
1020# KEY.
1021#
1022do_test where-12.1 {
1023 execsql {
1024 CREATE TABLE t6(a INTEGER PRIMARY KEY, b TEXT);
1025 INSERT INTO t6 VALUES(1,'one');
1026 INSERT INTO t6 VALUES(4,'four');
1027 CREATE INDEX t6i1 ON t6(b);
1028 }
1029 cksort {
1030 SELECT * FROM t6 ORDER BY b;
1031 }
1032} {4 four 1 one nosort}
1033do_test where-12.2 {
1034 cksort {
1035 SELECT * FROM t6 ORDER BY b, a;
1036 }
1037} {4 four 1 one nosort}
1038do_test where-12.3 {
1039 cksort {
1040 SELECT * FROM t6 ORDER BY a;
1041 }
1042} {1 one 4 four nosort}
1043do_test where-12.4 {
1044 cksort {
1045 SELECT * FROM t6 ORDER BY a, b;
1046 }
1047} {1 one 4 four nosort}
1048do_test where-12.5 {
1049 cksort {
1050 SELECT * FROM t6 ORDER BY b DESC;
1051 }
1052} {1 one 4 four nosort}
1053do_test where-12.6 {
1054 cksort {
1055 SELECT * FROM t6 ORDER BY b DESC, a DESC;
1056 }
1057} {1 one 4 four nosort}
1058do_test where-12.7 {
1059 cksort {
1060 SELECT * FROM t6 ORDER BY b DESC, a ASC;
1061 }
1062} {1 one 4 four sort}
1063do_test where-12.8 {
1064 cksort {
1065 SELECT * FROM t6 ORDER BY b ASC, a DESC;
1066 }
1067} {4 four 1 one sort}
1068do_test where-12.9 {
1069 cksort {
1070 SELECT * FROM t6 ORDER BY a DESC;
1071 }
1072} {4 four 1 one nosort}
1073do_test where-12.10 {
1074 cksort {
1075 SELECT * FROM t6 ORDER BY a DESC, b DESC;
1076 }
1077} {4 four 1 one nosort}
1078do_test where-12.11 {
1079 cksort {
1080 SELECT * FROM t6 ORDER BY a DESC, b ASC;
1081 }
1082} {4 four 1 one nosort}
1083do_test where-12.12 {
1084 cksort {
1085 SELECT * FROM t6 ORDER BY a ASC, b DESC;
1086 }
1087} {1 one 4 four nosort}
1088do_test where-13.1 {
1089 execsql {
1090 CREATE TABLE t7(a INTEGER PRIMARY KEY, b TEXT);
1091 INSERT INTO t7 VALUES(1,'one');
1092 INSERT INTO t7 VALUES(4,'four');
1093 CREATE INDEX t7i1 ON t7(b);
1094 }
1095 cksort {
1096 SELECT * FROM t7 ORDER BY b;
1097 }
1098} {4 four 1 one nosort}
1099do_test where-13.2 {
1100 cksort {
1101 SELECT * FROM t7 ORDER BY b, a;
1102 }
1103} {4 four 1 one nosort}
1104do_test where-13.3 {
1105 cksort {
1106 SELECT * FROM t7 ORDER BY a;
1107 }
1108} {1 one 4 four nosort}
1109do_test where-13.4 {
1110 cksort {
1111 SELECT * FROM t7 ORDER BY a, b;
1112 }
1113} {1 one 4 four nosort}
1114do_test where-13.5 {
1115 cksort {
1116 SELECT * FROM t7 ORDER BY b DESC;
1117 }
1118} {1 one 4 four nosort}
1119do_test where-13.6 {
1120 cksort {
1121 SELECT * FROM t7 ORDER BY b DESC, a DESC;
1122 }
1123} {1 one 4 four nosort}
1124do_test where-13.7 {
1125 cksort {
1126 SELECT * FROM t7 ORDER BY b DESC, a ASC;
1127 }
1128} {1 one 4 four sort}
1129do_test where-13.8 {
1130 cksort {
1131 SELECT * FROM t7 ORDER BY b ASC, a DESC;
1132 }
1133} {4 four 1 one sort}
1134do_test where-13.9 {
1135 cksort {
1136 SELECT * FROM t7 ORDER BY a DESC;
1137 }
1138} {4 four 1 one nosort}
1139do_test where-13.10 {
1140 cksort {
1141 SELECT * FROM t7 ORDER BY a DESC, b DESC;
1142 }
1143} {4 four 1 one nosort}
1144do_test where-13.11 {
1145 cksort {
1146 SELECT * FROM t7 ORDER BY a DESC, b ASC;
1147 }
1148} {4 four 1 one nosort}
1149do_test where-13.12 {
1150 cksort {
1151 SELECT * FROM t7 ORDER BY a ASC, b DESC;
1152 }
1153} {1 one 4 four nosort}
1154
drh7b4fc6a2007-02-06 13:26:32 +00001155# Ticket #2211.
1156#
1157# When optimizing out ORDER BY clauses, make sure that trailing terms
1158# of the ORDER BY clause do not reference other tables in a join.
1159#
dane8258312012-12-05 19:04:32 +00001160if {[permutation] != "no_optimization"} {
drh7b4fc6a2007-02-06 13:26:32 +00001161do_test where-14.1 {
1162 execsql {
drh165674d2013-10-04 15:58:59 +00001163 CREATE TABLE t8(a INTEGER PRIMARY KEY, b TEXT UNIQUE, c CHAR(100));
1164 INSERT INTO t8(a,b) VALUES(1,'one');
1165 INSERT INTO t8(a,b) VALUES(4,'four');
drh7b4fc6a2007-02-06 13:26:32 +00001166 }
1167 cksort {
1168 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b
1169 }
drh6f0e4002012-10-03 12:38:19 +00001170} {1/4 1/1 4/4 4/1 nosort}
drh7b4fc6a2007-02-06 13:26:32 +00001171do_test where-14.2 {
1172 cksort {
1173 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b DESC
1174 }
drh6f0e4002012-10-03 12:38:19 +00001175} {1/1 1/4 4/1 4/4 nosort}
drh7b4fc6a2007-02-06 13:26:32 +00001176do_test where-14.3 {
1177 cksort {
1178 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b
1179 }
drh3f4d1d12012-09-15 18:45:54 +00001180} {1/4 1/1 4/4 4/1 nosort}
drh7b4fc6a2007-02-06 13:26:32 +00001181do_test where-14.4 {
1182 cksort {
1183 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b DESC
1184 }
drh3f4d1d12012-09-15 18:45:54 +00001185} {1/4 1/1 4/4 4/1 nosort}
drh7b4fc6a2007-02-06 13:26:32 +00001186do_test where-14.5 {
dan99f8fb62012-04-20 15:24:53 +00001187 # This test case changed from "nosort" to "sort". See ticket 2a5629202f.
drh7b4fc6a2007-02-06 13:26:32 +00001188 cksort {
1189 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b
1190 }
drh5343b2d2012-09-27 19:53:38 +00001191} {/4/[14] 4/[14] 1/[14] 1/[14] sort/}
drh7b4fc6a2007-02-06 13:26:32 +00001192do_test where-14.6 {
dan99f8fb62012-04-20 15:24:53 +00001193 # This test case changed from "nosort" to "sort". See ticket 2a5629202f.
drh7b4fc6a2007-02-06 13:26:32 +00001194 cksort {
1195 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b DESC
1196 }
drh5343b2d2012-09-27 19:53:38 +00001197} {/4/[14] 4/[14] 1/[14] 1/[14] sort/}
drh7b4fc6a2007-02-06 13:26:32 +00001198do_test where-14.7 {
1199 cksort {
1200 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b
1201 }
1202} {4/1 4/4 1/1 1/4 sort}
drh32ffdb72007-02-06 23:41:34 +00001203do_test where-14.7.1 {
1204 cksort {
1205 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a, y.a||y.b
1206 }
1207} {4/1 4/4 1/1 1/4 sort}
1208do_test where-14.7.2 {
1209 cksort {
1210 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a, x.a||x.b
1211 }
drh3f4d1d12012-09-15 18:45:54 +00001212} {4/4 4/1 1/4 1/1 nosort}
drh7b4fc6a2007-02-06 13:26:32 +00001213do_test where-14.8 {
1214 cksort {
1215 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b DESC
1216 }
1217} {4/4 4/1 1/4 1/1 sort}
1218do_test where-14.9 {
1219 cksort {
1220 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||y.b
1221 }
1222} {4/4 4/1 1/4 1/1 sort}
1223do_test where-14.10 {
1224 cksort {
1225 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||y.b DESC
1226 }
1227} {4/1 4/4 1/1 1/4 sort}
1228do_test where-14.11 {
1229 cksort {
1230 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||x.b
1231 }
1232} {4/1 4/4 1/1 1/4 sort}
1233do_test where-14.12 {
1234 cksort {
1235 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||x.b DESC
1236 }
1237} {4/4 4/1 1/4 1/1 sort}
dane8258312012-12-05 19:04:32 +00001238} ;# {permutation != "no_optimization"}
drhcc192542006-12-20 03:24:19 +00001239
danielk1977c9cf6e32007-06-25 16:29:33 +00001240# Ticket #2445.
1241#
1242# There was a crash that could occur when a where clause contains an
1243# alias for an expression in the result set, and that expression retrieves
1244# a column of the second or subsequent table in a join.
1245#
1246do_test where-15.1 {
1247 execsql {
1248 CREATE TEMP TABLE t1 (a, b, c, d, e);
1249 CREATE TEMP TABLE t2 (f);
1250 SELECT t1.e AS alias FROM t2, t1 WHERE alias = 1 ;
1251 }
1252} {}
drh9eb20282005-08-24 03:52:18 +00001253
danielk19772d605492008-10-01 08:43:03 +00001254# Ticket #3408.
1255#
1256# The branch of code in where.c that generated rowid lookups was
1257# incorrectly deallocating a constant register, meaning that if the
1258# vdbe code ran more than once, the second time around the constant
1259# value may have been clobbered by some other value.
1260#
1261do_test where-16.1 {
1262 execsql {
1263 CREATE TABLE a1(id INTEGER PRIMARY KEY, v);
1264 CREATE TABLE a2(id INTEGER PRIMARY KEY, v);
1265 INSERT INTO a1 VALUES(1, 'one');
1266 INSERT INTO a1 VALUES(2, 'two');
1267 INSERT INTO a2 VALUES(1, 'one');
1268 INSERT INTO a2 VALUES(2, 'two');
1269 }
1270} {}
1271do_test where-16.2 {
1272 execsql {
1273 SELECT * FROM a2 CROSS JOIN a1 WHERE a1.id=1 AND a1.v='one';
1274 }
1275} {1 one 1 one 2 two 1 one}
1276
1277# The actual problem reported in #3408.
1278do_test where-16.3 {
1279 execsql {
1280 CREATE TEMP TABLE foo(idx INTEGER);
1281 INSERT INTO foo VALUES(1);
1282 INSERT INTO foo VALUES(1);
1283 INSERT INTO foo VALUES(1);
1284 INSERT INTO foo VALUES(2);
1285 INSERT INTO foo VALUES(2);
1286 CREATE TEMP TABLE bar(stuff INTEGER);
1287 INSERT INTO bar VALUES(100);
1288 INSERT INTO bar VALUES(200);
1289 INSERT INTO bar VALUES(300);
1290 }
1291} {}
1292do_test where-16.4 {
1293 execsql {
1294 SELECT bar.RowID id FROM foo, bar WHERE foo.idx = bar.RowID AND id = 2;
1295 }
1296} {2 2}
1297
drhed717fe2003-06-15 23:42:24 +00001298integrity_check {where-99.0}
drh08192d52002-04-30 19:20:28 +00001299
danielk19773072c5e2008-11-03 09:06:05 +00001300#---------------------------------------------------------------------
1301# These tests test that a bug surrounding the use of ForceInt has been
1302# fixed in where.c.
1303#
1304do_test where-17.1 {
1305 execsql {
1306 CREATE TABLE tbooking (
1307 id INTEGER PRIMARY KEY,
1308 eventtype INTEGER NOT NULL
1309 );
1310 INSERT INTO tbooking VALUES(42, 3);
1311 INSERT INTO tbooking VALUES(43, 4);
1312 }
1313} {}
1314do_test where-17.2 {
1315 execsql {
1316 SELECT a.id
1317 FROM tbooking AS a
1318 WHERE a.eventtype=3;
1319 }
1320} {42}
1321do_test where-17.3 {
1322 execsql {
1323 SELECT a.id, (SELECT b.id FROM tbooking AS b WHERE b.id>a.id)
1324 FROM tbooking AS a
1325 WHERE a.eventtype=3;
1326 }
1327} {42 43}
1328do_test where-17.4 {
1329 execsql {
1330 SELECT a.id, (SELECT b.id FROM tbooking AS b WHERE b.id>a.id)
1331 FROM (SELECT 1.5 AS id) AS a
1332 }
1333} {1.5 42}
1334do_test where-17.5 {
1335 execsql {
1336 CREATE TABLE tother(a, b);
1337 INSERT INTO tother VALUES(1, 3.7);
1338 SELECT id, a FROM tbooking, tother WHERE id>a;
1339 }
1340} {42 1 43 1}
1341
drh67a5ec72013-09-03 14:03:47 +00001342# Ticket [be84e357c035d068135f20bcfe82761bbf95006b] 2013-09-03
1343# Segfault during query involving LEFT JOIN column in the ORDER BY clause.
1344#
1345do_execsql_test where-18.1 {
1346 CREATE TABLE t181(a);
1347 CREATE TABLE t182(b,c);
1348 INSERT INTO t181 VALUES(1);
1349 SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c IS NULL;
1350} {1}
1351do_execsql_test where-18.2 {
1352 SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +c;
1353} {1}
1354do_execsql_test where-18.3 {
1355 SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c;
1356} {1}
1357do_execsql_test where-18.4 {
1358 INSERT INTO t181 VALUES(1),(1),(1),(1);
1359 SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +c;
1360} {1}
1361do_execsql_test where-18.5 {
1362 INSERT INTO t181 VALUES(2);
1363 SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c IS NULL, +a;
1364} {1 2}
1365do_execsql_test where-18.6 {
1366 INSERT INTO t181 VALUES(2);
1367 SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +a, +c IS NULL;
1368} {1 2}
1369
drhcb23e5d2018-04-24 16:41:37 +00001370# Make sure the OR optimization works on a JOIN
1371#
1372do_execsql_test where-19.0 {
1373 CREATE TABLE t191(a INT UNIQUE NOT NULL, b INT UNIQUE NOT NULL,c,d);
1374 CREATE INDEX t191a ON t1(a);
1375 CREATE INDEX t191b ON t1(b);
1376 CREATE TABLE t192(x INTEGER PRIMARY KEY,y INT, z INT);
1377
1378 EXPLAIN QUERY PLAN
1379 SELECT t191.rowid FROM t192, t191 WHERE (a=y OR b=y) AND x=?1;
1380} {/.* sqlite_autoindex_t191_1 .* sqlite_autoindex_t191_2 .*/}
1381
drhc4475952018-04-24 17:34:03 +00001382# 2018-04-24 ticket [https://www.sqlite.org/src/info/4ba5abf65c5b0f9a]
1383# Index on expressions leads to an incorrect answer for a LEFT JOIN
1384#
1385do_execsql_test where-20.0 {
1386 CREATE TABLE t201(x);
1387 CREATE TABLE t202(y, z);
1388 INSERT INTO t201 VALUES('key');
1389 INSERT INTO t202 VALUES('key', -1);
1390 CREATE INDEX t202i ON t202(y, ifnull(z, 0));
1391 SELECT count(*) FROM t201 LEFT JOIN t202 ON (x=y) WHERE ifnull(z, 0) >=0;
1392} {0}
1393
danccb9eb72018-04-24 18:59:18 +00001394do_execsql_test where-21.0 {
1395 CREATE TABLE t12(a, b, c);
1396 CREATE TABLE t13(x);
1397 CREATE INDEX t12ab ON t12(b, a);
1398 CREATE INDEX t12ac ON t12(c, a);
1399
1400 INSERT INTO t12 VALUES(4, 0, 1);
1401 INSERT INTO t12 VALUES(4, 1, 0);
1402 INSERT INTO t12 VALUES(5, 0, 1);
1403 INSERT INTO t12 VALUES(5, 1, 0);
1404
1405 INSERT INTO t13 VALUES(1), (2), (3), (4);
1406}
1407do_execsql_test where-21.1 {
1408 SELECT * FROM t12 WHERE
1409 a = (SELECT * FROM (SELECT count(*) FROM t13 LIMIT 5) ORDER BY 1 LIMIT 10)
1410 AND (b=1 OR c=1);
1411} {
1412 4 1 0
1413 4 0 1
1414}
drh67a5ec72013-09-03 14:03:47 +00001415
drh383bb4f2018-11-05 07:53:17 +00001416# 2018-11-05: ticket [https://www.sqlite.org/src/tktview/65eb38f6e46de8c75e188a]
1417# Incorrect result in LEFT JOIN when STAT4 is enabled.
1418#
1419sqlite3 db :memory:
1420do_execsql_test where-22.1 {
1421 CREATE TABLE t1(a INT);
1422 CREATE INDEX t1a ON t1(a);
1423 INSERT INTO t1(a) VALUES(NULL),(NULL),(42),(NULL),(NULL);
1424 CREATE TABLE t2(dummy INT);
1425 SELECT count(*) FROM t1 LEFT JOIN t2 ON a IS NOT NULL;
1426} {5}
1427
drhbc0a55c2019-02-22 21:33:56 +00001428# 20190-02-22: A bug introduced by checkin
1429# https://www.sqlite.org/src/info/fa792714ae62fa98.
1430#
1431do_execsql_test where-23.0 {
1432 DROP TABLE IF EXISTS t1;
1433 DROP TABLE IF EXISTS t2;
1434 CREATE TABLE t1(a INTEGER PRIMARY KEY);
1435 INSERT INTO t1(a) VALUES(1),(2),(3);
1436 CREATE TABLE t2(x INTEGER PRIMARY KEY, y INT);
1437 INSERT INTO t2(y) VALUES(2),(3);
1438 SELECT * FROM t1, t2 WHERE a=y AND y=3;
1439} {3 2 3}
drh383bb4f2018-11-05 07:53:17 +00001440
dan6cf30092019-04-05 20:47:15 +00001441#-------------------------------------------------------------------------
1442#
1443reset_db
1444do_execsql_test where-24.0 {
1445 CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
1446 INSERT INTO t1 VALUES(1, 'one');
1447 INSERT INTO t1 VALUES(2, 'two');
1448 INSERT INTO t1 VALUES(3, 'three');
1449 INSERT INTO t1 VALUES(4, 'four');
1450}
1451
1452foreach {tn sql res} {
1453 1 "SELECT b FROM t1" {one two three four}
1454 2 "SELECT b FROM t1 WHERE a<4" {one two three}
1455 3 "SELECT b FROM t1 WHERE a>1" {two three four}
1456 4 "SELECT b FROM t1 WHERE a>1 AND a<4" {two three}
1457
1458 5 "SELECT b FROM t1 WHERE a>? AND a<4" {}
1459 6 "SELECT b FROM t1 WHERE a>1 AND a<?" {}
1460 7 "SELECT b FROM t1 WHERE a>? AND a<?" {}
1461
1462 7 "SELECT b FROM t1 WHERE a>=? AND a<=4" {}
1463 8 "SELECT b FROM t1 WHERE a>=1 AND a<=?" {}
1464 9 "SELECT b FROM t1 WHERE a>=? AND a<=?" {}
1465} {
1466 set rev [list]
1467 foreach r $res { set rev [concat $r $rev] }
1468
1469 do_execsql_test where-24.$tn.1 "$sql" $res
1470 do_execsql_test where-24.$tn.2 "$sql ORDER BY rowid" $res
1471 do_execsql_test where-24.$tn.3 "$sql ORDER BY rowid DESC" $rev
1472
1473 do_execsql_test where-24-$tn.4 "
1474 BEGIN;
1475 DELETE FROM t1;
1476 $sql;
1477 $sql ORDER BY rowid;
1478 $sql ORDER BY rowid DESC;
1479 ROLLBACK;
1480 "
1481}
1482
1483#-------------------------------------------------------------------------
1484#
1485reset_db
1486do_execsql_test where-25.0 {
1487 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
1488 CREATE UNIQUE INDEX i1 ON t1(c);
1489 INSERT INTO t1 VALUES(1, 'one', 'i');
1490 INSERT INTO t1 VALUES(2, 'two', 'ii');
1491
1492 CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c);
1493 CREATE UNIQUE INDEX i2 ON t2(c);
1494 INSERT INTO t2 VALUES(1, 'one', 'i');
1495 INSERT INTO t2 VALUES(2, 'two', 'ii');
1496 INSERT INTO t2 VALUES(3, 'three', 'iii');
1497
1498 PRAGMA writable_schema = 1;
drh346a70c2020-06-15 20:27:35 +00001499 UPDATE sqlite_schema SET rootpage = (
1500 SELECT rootpage FROM sqlite_schema WHERE name = 'i2'
dan6cf30092019-04-05 20:47:15 +00001501 ) WHERE name = 'i1';
1502}
1503db close
1504sqlite3 db test.db
1505do_catchsql_test where-25.1 {
1506 DELETE FROM t1 WHERE c='iii'
1507} {1 {database disk image is malformed}}
1508do_catchsql_test where-25.2 {
1509 INSERT INTO t1 VALUES(4, 'four', 'iii')
1510 ON CONFLICT(c) DO UPDATE SET b=NULL
1511} {1 {database disk image is malformed}}
1512
1513reset_db
1514do_execsql_test where-25.3 {
1515 CREATE TABLE t1(a PRIMARY KEY, b, c) WITHOUT ROWID;
1516 CREATE UNIQUE INDEX i1 ON t1(c);
1517 INSERT INTO t1 VALUES(1, 'one', 'i');
1518 INSERT INTO t1 VALUES(2, 'two', 'ii');
1519
1520 CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c);
1521 CREATE UNIQUE INDEX i2 ON t2(c);
1522 INSERT INTO t2 VALUES(1, 'one', 'i');
1523 INSERT INTO t2 VALUES(2, 'two', 'ii');
1524 INSERT INTO t2 VALUES(3, 'three', 'iii');
1525
1526 PRAGMA writable_schema = 1;
drh346a70c2020-06-15 20:27:35 +00001527 UPDATE sqlite_schema SET rootpage = (
1528 SELECT rootpage FROM sqlite_schema WHERE name = 'i2'
dan6cf30092019-04-05 20:47:15 +00001529 ) WHERE name = 'i1';
1530}
1531db close
1532sqlite3 db test.db
1533do_catchsql_test where-25.4 {
1534 SELECT * FROM t1 WHERE c='iii'
1535} {0 {}}
1536do_catchsql_test where-25.5 {
1537 INSERT INTO t1 VALUES(4, 'four', 'iii')
1538 ON CONFLICT(c) DO UPDATE SET b=NULL
1539} {1 {corrupt database}}
1540
drh3e364802019-08-22 00:53:16 +00001541# 2019-08-21 Ticket https://www.sqlite.org/src/info/d9f584e936c7a8d0
1542#
1543db close
1544sqlite3 db :memory:
1545do_execsql_test where-26.1 {
1546 CREATE TABLE t0(c0 INTEGER PRIMARY KEY, c1 TEXT);
1547 INSERT INTO t0(c0, c1) VALUES (1, 'a');
1548 CREATE TABLE t1(c0 INT PRIMARY KEY, c1 TEXT);
1549 INSERT INTO t1(c0, c1) VALUES (1, 'a');
1550 SELECT * FROM t0 WHERE '-1' BETWEEN 0 AND t0.c0;
1551} {1 a}
1552do_execsql_test where-26.2 {
1553 SELECT * FROM t1 WHERE '-1' BETWEEN 0 AND t1.c0;
1554} {1 a}
1555do_execsql_test where-26.3 {
1556 SELECT * FROM t0 WHERE '-1'>=0 AND '-1'<=t0.c0;
1557} {1 a}
1558do_execsql_test where-26.4 {
1559 SELECT * FROM t1 WHERE '-1'>=0 AND '-1'<=t1.c0;
1560} {1 a}
1561do_execsql_test where-26.5 {
1562 SELECT '-1' BETWEEN 0 AND t0.c0 FROM t0;
1563} {1}
1564do_execsql_test where-26.6 {
1565 SELECT '-1' BETWEEN 0 AND t1.c0 FROM t1;
1566} {1}
1567do_execsql_test where-26.7 {
1568 SELECT '-1'>=0 AND '-1'<=t0.c0 FROM t0;
1569} {1}
1570do_execsql_test where-26.8 {
1571 SELECT '-1'>=0 AND '-1'<=t1.c0 FROM t1;
1572} {1}
1573
drh6de4f4c2000-06-12 12:20:48 +00001574finish_test