1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/where.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,1160 @@
1.4 +# 2001 September 15
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library. The
1.15 +# focus of this file is testing the use of indices in WHERE clases.
1.16 +#
1.17 +# $Id: where.test,v 1.47 2008/09/01 15:52:11 drh Exp $
1.18 +
1.19 +set testdir [file dirname $argv0]
1.20 +source $testdir/tester.tcl
1.21 +
1.22 +# Build some test data
1.23 +#
1.24 +do_test where-1.0 {
1.25 + execsql {
1.26 + CREATE TABLE t1(w int, x int, y int);
1.27 + CREATE TABLE t2(p int, q int, r int, s int);
1.28 + }
1.29 + for {set i 1} {$i<=100} {incr i} {
1.30 + set w $i
1.31 + set x [expr {int(log($i)/log(2))}]
1.32 + set y [expr {$i*$i + 2*$i + 1}]
1.33 + execsql "INSERT INTO t1 VALUES($w,$x,$y)"
1.34 + }
1.35 +
1.36 + ifcapable subquery {
1.37 + execsql {
1.38 + INSERT INTO t2 SELECT 101-w, x, (SELECT max(y) FROM t1)+1-y, y FROM t1;
1.39 + }
1.40 + } else {
1.41 + set maxy [execsql {select max(y) from t1}]
1.42 + execsql "
1.43 + INSERT INTO t2 SELECT 101-w, x, $maxy+1-y, y FROM t1;
1.44 + "
1.45 + }
1.46 +
1.47 + execsql {
1.48 + CREATE INDEX i1w ON t1(w);
1.49 + CREATE INDEX i1xy ON t1(x,y);
1.50 + CREATE INDEX i2p ON t2(p);
1.51 + CREATE INDEX i2r ON t2(r);
1.52 + CREATE INDEX i2qs ON t2(q, s);
1.53 + }
1.54 +} {}
1.55 +
1.56 +# Do an SQL statement. Append the search count to the end of the result.
1.57 +#
1.58 +proc count sql {
1.59 + set ::sqlite_search_count 0
1.60 + return [concat [execsql $sql] $::sqlite_search_count]
1.61 +}
1.62 +
1.63 +# Verify that queries use an index. We are using the special variable
1.64 +# "sqlite_search_count" which tallys the number of executions of MoveTo
1.65 +# and Next operators in the VDBE. By verifing that the search count is
1.66 +# small we can be assured that indices are being used properly.
1.67 +#
1.68 +do_test where-1.1.1 {
1.69 + count {SELECT x, y, w FROM t1 WHERE w=10}
1.70 +} {3 121 10 3}
1.71 +do_test where-1.1.2 {
1.72 + set sqlite_query_plan
1.73 +} {t1 i1w}
1.74 +do_test where-1.1.3 {
1.75 + count {SELECT x, y, w AS abc FROM t1 WHERE abc=10}
1.76 +} {3 121 10 3}
1.77 +do_test where-1.1.4 {
1.78 + set sqlite_query_plan
1.79 +} {t1 i1w}
1.80 +do_test where-1.2.1 {
1.81 + count {SELECT x, y, w FROM t1 WHERE w=11}
1.82 +} {3 144 11 3}
1.83 +do_test where-1.2.2 {
1.84 + count {SELECT x, y, w AS abc FROM t1 WHERE abc=11}
1.85 +} {3 144 11 3}
1.86 +do_test where-1.3.1 {
1.87 + count {SELECT x, y, w AS abc FROM t1 WHERE 11=w}
1.88 +} {3 144 11 3}
1.89 +do_test where-1.3.2 {
1.90 + count {SELECT x, y, w AS abc FROM t1 WHERE 11=abc}
1.91 +} {3 144 11 3}
1.92 +do_test where-1.4.1 {
1.93 + count {SELECT w, x, y FROM t1 WHERE 11=w AND x>2}
1.94 +} {11 3 144 3}
1.95 +do_test where-1.4.2 {
1.96 + set sqlite_query_plan
1.97 +} {t1 i1w}
1.98 +do_test where-1.4.3 {
1.99 + count {SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2}
1.100 +} {11 3 144 3}
1.101 +do_test where-1.4.4 {
1.102 + set sqlite_query_plan
1.103 +} {t1 i1w}
1.104 +do_test where-1.5 {
1.105 + count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2}
1.106 +} {3 144 3}
1.107 +do_test where-1.5.2 {
1.108 + set sqlite_query_plan
1.109 +} {t1 i1w}
1.110 +do_test where-1.6 {
1.111 + count {SELECT x, y FROM t1 WHERE y<200 AND x>2 AND w=11}
1.112 +} {3 144 3}
1.113 +do_test where-1.7 {
1.114 + count {SELECT x, y FROM t1 WHERE w=11 AND y<200 AND x>2}
1.115 +} {3 144 3}
1.116 +do_test where-1.8 {
1.117 + count {SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3}
1.118 +} {3 144 3}
1.119 +do_test where-1.8.2 {
1.120 + set sqlite_query_plan
1.121 +} {t1 i1xy}
1.122 +do_test where-1.8.3 {
1.123 + count {SELECT x, y FROM t1 WHERE y=144 AND x=3}
1.124 + set sqlite_query_plan
1.125 +} {{} i1xy}
1.126 +do_test where-1.9 {
1.127 + count {SELECT x, y FROM t1 WHERE y=144 AND w>10 AND x=3}
1.128 +} {3 144 3}
1.129 +do_test where-1.10 {
1.130 + count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121}
1.131 +} {3 121 3}
1.132 +do_test where-1.11 {
1.133 + count {SELECT x, y FROM t1 WHERE x=3 AND y=100 AND w<10}
1.134 +} {3 100 3}
1.135 +
1.136 +# New for SQLite version 2.1: Verify that that inequality constraints
1.137 +# are used correctly.
1.138 +#
1.139 +do_test where-1.12 {
1.140 + count {SELECT w FROM t1 WHERE x=3 AND y<100}
1.141 +} {8 3}
1.142 +do_test where-1.13 {
1.143 + count {SELECT w FROM t1 WHERE x=3 AND 100>y}
1.144 +} {8 3}
1.145 +do_test where-1.14 {
1.146 + count {SELECT w FROM t1 WHERE 3=x AND y<100}
1.147 +} {8 3}
1.148 +do_test where-1.15 {
1.149 + count {SELECT w FROM t1 WHERE 3=x AND 100>y}
1.150 +} {8 3}
1.151 +do_test where-1.16 {
1.152 + count {SELECT w FROM t1 WHERE x=3 AND y<=100}
1.153 +} {8 9 5}
1.154 +do_test where-1.17 {
1.155 + count {SELECT w FROM t1 WHERE x=3 AND 100>=y}
1.156 +} {8 9 5}
1.157 +do_test where-1.18 {
1.158 + count {SELECT w FROM t1 WHERE x=3 AND y>225}
1.159 +} {15 3}
1.160 +do_test where-1.19 {
1.161 + count {SELECT w FROM t1 WHERE x=3 AND 225<y}
1.162 +} {15 3}
1.163 +do_test where-1.20 {
1.164 + count {SELECT w FROM t1 WHERE x=3 AND y>=225}
1.165 +} {14 15 5}
1.166 +do_test where-1.21 {
1.167 + count {SELECT w FROM t1 WHERE x=3 AND 225<=y}
1.168 +} {14 15 5}
1.169 +do_test where-1.22 {
1.170 + count {SELECT w FROM t1 WHERE x=3 AND y>121 AND y<196}
1.171 +} {11 12 5}
1.172 +do_test where-1.23 {
1.173 + count {SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196}
1.174 +} {10 11 12 13 9}
1.175 +do_test where-1.24 {
1.176 + count {SELECT w FROM t1 WHERE x=3 AND 121<y AND 196>y}
1.177 +} {11 12 5}
1.178 +do_test where-1.25 {
1.179 + count {SELECT w FROM t1 WHERE x=3 AND 121<=y AND 196>=y}
1.180 +} {10 11 12 13 9}
1.181 +
1.182 +# Need to work on optimizing the BETWEEN operator.
1.183 +#
1.184 +# do_test where-1.26 {
1.185 +# count {SELECT w FROM t1 WHERE x=3 AND y BETWEEN 121 AND 196}
1.186 +# } {10 11 12 13 9}
1.187 +
1.188 +do_test where-1.27 {
1.189 + count {SELECT w FROM t1 WHERE x=3 AND y+1==122}
1.190 +} {10 10}
1.191 +
1.192 +do_test where-1.28 {
1.193 + count {SELECT w FROM t1 WHERE x+1=4 AND y+1==122}
1.194 +} {10 99}
1.195 +do_test where-1.29 {
1.196 + count {SELECT w FROM t1 WHERE y==121}
1.197 +} {10 99}
1.198 +
1.199 +
1.200 +do_test where-1.30 {
1.201 + count {SELECT w FROM t1 WHERE w>97}
1.202 +} {98 99 100 3}
1.203 +do_test where-1.31 {
1.204 + count {SELECT w FROM t1 WHERE w>=97}
1.205 +} {97 98 99 100 4}
1.206 +do_test where-1.33 {
1.207 + count {SELECT w FROM t1 WHERE w==97}
1.208 +} {97 2}
1.209 +do_test where-1.33.1 {
1.210 + count {SELECT w FROM t1 WHERE w<=97 AND w==97}
1.211 +} {97 2}
1.212 +do_test where-1.33.2 {
1.213 + count {SELECT w FROM t1 WHERE w<98 AND w==97}
1.214 +} {97 2}
1.215 +do_test where-1.33.3 {
1.216 + count {SELECT w FROM t1 WHERE w>=97 AND w==97}
1.217 +} {97 2}
1.218 +do_test where-1.33.4 {
1.219 + count {SELECT w FROM t1 WHERE w>96 AND w==97}
1.220 +} {97 2}
1.221 +do_test where-1.33.5 {
1.222 + count {SELECT w FROM t1 WHERE w==97 AND w==97}
1.223 +} {97 2}
1.224 +do_test where-1.34 {
1.225 + count {SELECT w FROM t1 WHERE w+1==98}
1.226 +} {97 99}
1.227 +do_test where-1.35 {
1.228 + count {SELECT w FROM t1 WHERE w<3}
1.229 +} {1 2 2}
1.230 +do_test where-1.36 {
1.231 + count {SELECT w FROM t1 WHERE w<=3}
1.232 +} {1 2 3 3}
1.233 +do_test where-1.37 {
1.234 + count {SELECT w FROM t1 WHERE w+1<=4 ORDER BY w}
1.235 +} {1 2 3 99}
1.236 +
1.237 +do_test where-1.38 {
1.238 + count {SELECT (w) FROM t1 WHERE (w)>(97)}
1.239 +} {98 99 100 3}
1.240 +do_test where-1.39 {
1.241 + count {SELECT (w) FROM t1 WHERE (w)>=(97)}
1.242 +} {97 98 99 100 4}
1.243 +do_test where-1.40 {
1.244 + count {SELECT (w) FROM t1 WHERE (w)==(97)}
1.245 +} {97 2}
1.246 +do_test where-1.41 {
1.247 + count {SELECT (w) FROM t1 WHERE ((w)+(1))==(98)}
1.248 +} {97 99}
1.249 +
1.250 +
1.251 +# Do the same kind of thing except use a join as the data source.
1.252 +#
1.253 +do_test where-2.1 {
1.254 + count {
1.255 + SELECT w, p FROM t2, t1
1.256 + WHERE x=q AND y=s AND r=8977
1.257 + }
1.258 +} {34 67 6}
1.259 +do_test where-2.2 {
1.260 + count {
1.261 + SELECT w, p FROM t2, t1
1.262 + WHERE x=q AND s=y AND r=8977
1.263 + }
1.264 +} {34 67 6}
1.265 +do_test where-2.3 {
1.266 + count {
1.267 + SELECT w, p FROM t2, t1
1.268 + WHERE x=q AND s=y AND r=8977 AND w>10
1.269 + }
1.270 +} {34 67 6}
1.271 +do_test where-2.4 {
1.272 + count {
1.273 + SELECT w, p FROM t2, t1
1.274 + WHERE p<80 AND x=q AND s=y AND r=8977 AND w>10
1.275 + }
1.276 +} {34 67 6}
1.277 +do_test where-2.5 {
1.278 + count {
1.279 + SELECT w, p FROM t2, t1
1.280 + WHERE p<80 AND x=q AND 8977=r AND s=y AND w>10
1.281 + }
1.282 +} {34 67 6}
1.283 +do_test where-2.6 {
1.284 + count {
1.285 + SELECT w, p FROM t2, t1
1.286 + WHERE x=q AND p=77 AND s=y AND w>5
1.287 + }
1.288 +} {24 77 6}
1.289 +do_test where-2.7 {
1.290 + count {
1.291 + SELECT w, p FROM t1, t2
1.292 + WHERE x=q AND p>77 AND s=y AND w=5
1.293 + }
1.294 +} {5 96 6}
1.295 +
1.296 +# Lets do a 3-way join.
1.297 +#
1.298 +do_test where-3.1 {
1.299 + count {
1.300 + SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
1.301 + WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=11
1.302 + }
1.303 +} {11 90 11 8}
1.304 +do_test where-3.2 {
1.305 + count {
1.306 + SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
1.307 + WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=12
1.308 + }
1.309 +} {12 89 12 8}
1.310 +do_test where-3.3 {
1.311 + count {
1.312 + SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
1.313 + WHERE A.w=15 AND B.p=C.w AND B.r=10202-A.y
1.314 + }
1.315 +} {15 86 86 8}
1.316 +
1.317 +# Test to see that the special case of a constant WHERE clause is
1.318 +# handled.
1.319 +#
1.320 +do_test where-4.1 {
1.321 + count {
1.322 + SELECT * FROM t1 WHERE 0
1.323 + }
1.324 +} {0}
1.325 +do_test where-4.2 {
1.326 + count {
1.327 + SELECT * FROM t1 WHERE 1 LIMIT 1
1.328 + }
1.329 +} {1 0 4 0}
1.330 +do_test where-4.3 {
1.331 + execsql {
1.332 + SELECT 99 WHERE 0
1.333 + }
1.334 +} {}
1.335 +do_test where-4.4 {
1.336 + execsql {
1.337 + SELECT 99 WHERE 1
1.338 + }
1.339 +} {99}
1.340 +do_test where-4.5 {
1.341 + execsql {
1.342 + SELECT 99 WHERE 0.1
1.343 + }
1.344 +} {99}
1.345 +do_test where-4.6 {
1.346 + execsql {
1.347 + SELECT 99 WHERE 0.0
1.348 + }
1.349 +} {}
1.350 +do_test where-4.7 {
1.351 + execsql {
1.352 + SELECT count(*) FROM t1 WHERE t1.w
1.353 + }
1.354 +} {100}
1.355 +
1.356 +# Verify that IN operators in a WHERE clause are handled correctly.
1.357 +# Omit these tests if the build is not capable of sub-queries.
1.358 +#
1.359 +ifcapable subquery {
1.360 + do_test where-5.1 {
1.361 + count {
1.362 + SELECT * FROM t1 WHERE rowid IN (1,2,3,1234) order by 1;
1.363 + }
1.364 + } {1 0 4 2 1 9 3 1 16 4}
1.365 + do_test where-5.2 {
1.366 + count {
1.367 + SELECT * FROM t1 WHERE rowid+0 IN (1,2,3,1234) order by 1;
1.368 + }
1.369 + } {1 0 4 2 1 9 3 1 16 102}
1.370 + do_test where-5.3 {
1.371 + count {
1.372 + SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1;
1.373 + }
1.374 + } {1 0 4 2 1 9 3 1 16 14}
1.375 + do_test where-5.4 {
1.376 + count {
1.377 + SELECT * FROM t1 WHERE w+0 IN (-1,1,2,3) order by 1;
1.378 + }
1.379 + } {1 0 4 2 1 9 3 1 16 102}
1.380 + do_test where-5.5 {
1.381 + count {
1.382 + SELECT * FROM t1 WHERE rowid IN
1.383 + (select rowid from t1 where rowid IN (-1,2,4))
1.384 + ORDER BY 1;
1.385 + }
1.386 + } {2 1 9 4 2 25 3}
1.387 + do_test where-5.6 {
1.388 + count {
1.389 + SELECT * FROM t1 WHERE rowid+0 IN
1.390 + (select rowid from t1 where rowid IN (-1,2,4))
1.391 + ORDER BY 1;
1.392 + }
1.393 + } {2 1 9 4 2 25 103}
1.394 + do_test where-5.7 {
1.395 + count {
1.396 + SELECT * FROM t1 WHERE w IN
1.397 + (select rowid from t1 where rowid IN (-1,2,4))
1.398 + ORDER BY 1;
1.399 + }
1.400 + } {2 1 9 4 2 25 9}
1.401 + do_test where-5.8 {
1.402 + count {
1.403 + SELECT * FROM t1 WHERE w+0 IN
1.404 + (select rowid from t1 where rowid IN (-1,2,4))
1.405 + ORDER BY 1;
1.406 + }
1.407 + } {2 1 9 4 2 25 103}
1.408 + do_test where-5.9 {
1.409 + count {
1.410 + SELECT * FROM t1 WHERE x IN (1,7) ORDER BY 1;
1.411 + }
1.412 + } {2 1 9 3 1 16 7}
1.413 + do_test where-5.10 {
1.414 + count {
1.415 + SELECT * FROM t1 WHERE x+0 IN (1,7) ORDER BY 1;
1.416 + }
1.417 + } {2 1 9 3 1 16 199}
1.418 + do_test where-5.11 {
1.419 + count {
1.420 + SELECT * FROM t1 WHERE y IN (6400,8100) ORDER BY 1;
1.421 + }
1.422 + } {79 6 6400 89 6 8100 199}
1.423 + do_test where-5.12 {
1.424 + count {
1.425 + SELECT * FROM t1 WHERE x=6 AND y IN (6400,8100) ORDER BY 1;
1.426 + }
1.427 + } {79 6 6400 89 6 8100 7}
1.428 + do_test where-5.13 {
1.429 + count {
1.430 + SELECT * FROM t1 WHERE x IN (1,7) AND y NOT IN (6400,8100) ORDER BY 1;
1.431 + }
1.432 + } {2 1 9 3 1 16 7}
1.433 + do_test where-5.14 {
1.434 + count {
1.435 + SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,10) ORDER BY 1;
1.436 + }
1.437 + } {2 1 9 8}
1.438 + do_test where-5.15 {
1.439 + count {
1.440 + SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,16) ORDER BY 1;
1.441 + }
1.442 + } {2 1 9 3 1 16 11}
1.443 +}
1.444 +
1.445 +# This procedure executes the SQL. Then it checks to see if the OP_Sort
1.446 +# opcode was executed. If an OP_Sort did occur, then "sort" is appended
1.447 +# to the result. If no OP_Sort happened, then "nosort" is appended.
1.448 +#
1.449 +# This procedure is used to check to make sure sorting is or is not
1.450 +# occurring as expected.
1.451 +#
1.452 +proc cksort {sql} {
1.453 + set ::sqlite_sort_count 0
1.454 + set data [execsql $sql]
1.455 + if {$::sqlite_sort_count} {set x sort} {set x nosort}
1.456 + lappend data $x
1.457 + return $data
1.458 +}
1.459 +# Check out the logic that attempts to implement the ORDER BY clause
1.460 +# using an index rather than by sorting.
1.461 +#
1.462 +do_test where-6.1 {
1.463 + execsql {
1.464 + CREATE TABLE t3(a,b,c);
1.465 + CREATE INDEX t3a ON t3(a);
1.466 + CREATE INDEX t3bc ON t3(b,c);
1.467 + CREATE INDEX t3acb ON t3(a,c,b);
1.468 + INSERT INTO t3 SELECT w, 101-w, y FROM t1;
1.469 + SELECT count(*), sum(a), sum(b), sum(c) FROM t3;
1.470 + }
1.471 +} {100 5050 5050 348550}
1.472 +do_test where-6.2 {
1.473 + cksort {
1.474 + SELECT * FROM t3 ORDER BY a LIMIT 3
1.475 + }
1.476 +} {1 100 4 2 99 9 3 98 16 nosort}
1.477 +do_test where-6.3 {
1.478 + cksort {
1.479 + SELECT * FROM t3 ORDER BY a+1 LIMIT 3
1.480 + }
1.481 +} {1 100 4 2 99 9 3 98 16 sort}
1.482 +do_test where-6.4 {
1.483 + cksort {
1.484 + SELECT * FROM t3 WHERE a<10 ORDER BY a LIMIT 3
1.485 + }
1.486 +} {1 100 4 2 99 9 3 98 16 nosort}
1.487 +do_test where-6.5 {
1.488 + cksort {
1.489 + SELECT * FROM t3 WHERE a>0 AND a<10 ORDER BY a LIMIT 3
1.490 + }
1.491 +} {1 100 4 2 99 9 3 98 16 nosort}
1.492 +do_test where-6.6 {
1.493 + cksort {
1.494 + SELECT * FROM t3 WHERE a>0 ORDER BY a LIMIT 3
1.495 + }
1.496 +} {1 100 4 2 99 9 3 98 16 nosort}
1.497 +do_test where-6.7 {
1.498 + cksort {
1.499 + SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 3
1.500 + }
1.501 +} {1 100 4 2 99 9 3 98 16 nosort}
1.502 +ifcapable subquery {
1.503 + do_test where-6.8 {
1.504 + cksort {
1.505 + SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a LIMIT 3
1.506 + }
1.507 + } {1 100 4 2 99 9 3 98 16 sort}
1.508 +}
1.509 +do_test where-6.9.1 {
1.510 + cksort {
1.511 + SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3
1.512 + }
1.513 +} {1 100 4 nosort}
1.514 +do_test where-6.9.1.1 {
1.515 + cksort {
1.516 + SELECT * FROM t3 WHERE a>=1 AND a=1 AND c>0 ORDER BY a LIMIT 3
1.517 + }
1.518 +} {1 100 4 nosort}
1.519 +do_test where-6.9.1.2 {
1.520 + cksort {
1.521 + SELECT * FROM t3 WHERE a<2 AND a=1 AND c>0 ORDER BY a LIMIT 3
1.522 + }
1.523 +} {1 100 4 nosort}
1.524 +do_test where-6.9.2 {
1.525 + cksort {
1.526 + SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c LIMIT 3
1.527 + }
1.528 +} {1 100 4 nosort}
1.529 +do_test where-6.9.3 {
1.530 + cksort {
1.531 + SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c LIMIT 3
1.532 + }
1.533 +} {1 100 4 nosort}
1.534 +do_test where-6.9.4 {
1.535 + cksort {
1.536 + SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC LIMIT 3
1.537 + }
1.538 +} {1 100 4 nosort}
1.539 +do_test where-6.9.5 {
1.540 + cksort {
1.541 + SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC, c DESC LIMIT 3
1.542 + }
1.543 +} {1 100 4 nosort}
1.544 +do_test where-6.9.6 {
1.545 + cksort {
1.546 + SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c DESC LIMIT 3
1.547 + }
1.548 +} {1 100 4 nosort}
1.549 +do_test where-6.9.7 {
1.550 + cksort {
1.551 + SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c,a LIMIT 3
1.552 + }
1.553 +} {1 100 4 sort}
1.554 +do_test where-6.9.8 {
1.555 + cksort {
1.556 + SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC, c ASC LIMIT 3
1.557 + }
1.558 +} {1 100 4 nosort}
1.559 +do_test where-6.9.9 {
1.560 + cksort {
1.561 + SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a ASC, c DESC LIMIT 3
1.562 + }
1.563 +} {1 100 4 nosort}
1.564 +do_test where-6.10 {
1.565 + cksort {
1.566 + SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3
1.567 + }
1.568 +} {1 100 4 nosort}
1.569 +do_test where-6.11 {
1.570 + cksort {
1.571 + SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c LIMIT 3
1.572 + }
1.573 +} {1 100 4 nosort}
1.574 +do_test where-6.12 {
1.575 + cksort {
1.576 + SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c,b LIMIT 3
1.577 + }
1.578 +} {1 100 4 nosort}
1.579 +do_test where-6.13 {
1.580 + cksort {
1.581 + SELECT * FROM t3 WHERE a>0 ORDER BY a DESC LIMIT 3
1.582 + }
1.583 +} {100 1 10201 99 2 10000 98 3 9801 nosort}
1.584 +do_test where-6.13.1 {
1.585 + cksort {
1.586 + SELECT * FROM t3 WHERE a>0 ORDER BY -a LIMIT 3
1.587 + }
1.588 +} {100 1 10201 99 2 10000 98 3 9801 sort}
1.589 +do_test where-6.14 {
1.590 + cksort {
1.591 + SELECT * FROM t3 ORDER BY b LIMIT 3
1.592 + }
1.593 +} {100 1 10201 99 2 10000 98 3 9801 nosort}
1.594 +do_test where-6.15 {
1.595 + cksort {
1.596 + SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t3.a LIMIT 3
1.597 + }
1.598 +} {1 0 2 1 3 1 nosort}
1.599 +do_test where-6.16 {
1.600 + cksort {
1.601 + SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t1.x, t3.a LIMIT 3
1.602 + }
1.603 +} {1 0 2 1 3 1 sort}
1.604 +do_test where-6.19 {
1.605 + cksort {
1.606 + SELECT y FROM t1 ORDER BY w LIMIT 3;
1.607 + }
1.608 +} {4 9 16 nosort}
1.609 +do_test where-6.20 {
1.610 + cksort {
1.611 + SELECT y FROM t1 ORDER BY rowid LIMIT 3;
1.612 + }
1.613 +} {4 9 16 nosort}
1.614 +do_test where-6.21 {
1.615 + cksort {
1.616 + SELECT y FROM t1 ORDER BY rowid, y LIMIT 3;
1.617 + }
1.618 +} {4 9 16 nosort}
1.619 +do_test where-6.22 {
1.620 + cksort {
1.621 + SELECT y FROM t1 ORDER BY rowid, y DESC LIMIT 3;
1.622 + }
1.623 +} {4 9 16 nosort}
1.624 +do_test where-6.23 {
1.625 + cksort {
1.626 + SELECT y FROM t1 WHERE y>4 ORDER BY rowid, w, x LIMIT 3;
1.627 + }
1.628 +} {9 16 25 nosort}
1.629 +do_test where-6.24 {
1.630 + cksort {
1.631 + SELECT y FROM t1 WHERE y>=9 ORDER BY rowid, x DESC, w LIMIT 3;
1.632 + }
1.633 +} {9 16 25 nosort}
1.634 +do_test where-6.25 {
1.635 + cksort {
1.636 + SELECT y FROM t1 WHERE y>4 AND y<25 ORDER BY rowid;
1.637 + }
1.638 +} {9 16 nosort}
1.639 +do_test where-6.26 {
1.640 + cksort {
1.641 + SELECT y FROM t1 WHERE y>=4 AND y<=25 ORDER BY oid;
1.642 + }
1.643 +} {4 9 16 25 nosort}
1.644 +do_test where-6.27 {
1.645 + cksort {
1.646 + SELECT y FROM t1 WHERE y<=25 ORDER BY _rowid_, w+y;
1.647 + }
1.648 +} {4 9 16 25 nosort}
1.649 +
1.650 +
1.651 +# Tests for reverse-order sorting.
1.652 +#
1.653 +do_test where-7.1 {
1.654 + cksort {
1.655 + SELECT w FROM t1 WHERE x=3 ORDER BY y;
1.656 + }
1.657 +} {8 9 10 11 12 13 14 15 nosort}
1.658 +do_test where-7.2 {
1.659 + cksort {
1.660 + SELECT w FROM t1 WHERE x=3 ORDER BY y DESC;
1.661 + }
1.662 +} {15 14 13 12 11 10 9 8 nosort}
1.663 +do_test where-7.3 {
1.664 + cksort {
1.665 + SELECT w FROM t1 WHERE x=3 AND y>100 ORDER BY y LIMIT 3;
1.666 + }
1.667 +} {10 11 12 nosort}
1.668 +do_test where-7.4 {
1.669 + cksort {
1.670 + SELECT w FROM t1 WHERE x=3 AND y>100 ORDER BY y DESC LIMIT 3;
1.671 + }
1.672 +} {15 14 13 nosort}
1.673 +do_test where-7.5 {
1.674 + cksort {
1.675 + SELECT w FROM t1 WHERE x=3 AND y>121 ORDER BY y DESC;
1.676 + }
1.677 +} {15 14 13 12 11 nosort}
1.678 +do_test where-7.6 {
1.679 + cksort {
1.680 + SELECT w FROM t1 WHERE x=3 AND y>=121 ORDER BY y DESC;
1.681 + }
1.682 +} {15 14 13 12 11 10 nosort}
1.683 +do_test where-7.7 {
1.684 + cksort {
1.685 + SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<196 ORDER BY y DESC;
1.686 + }
1.687 +} {12 11 10 nosort}
1.688 +do_test where-7.8 {
1.689 + cksort {
1.690 + SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196 ORDER BY y DESC;
1.691 + }
1.692 +} {13 12 11 10 nosort}
1.693 +do_test where-7.9 {
1.694 + cksort {
1.695 + SELECT w FROM t1 WHERE x=3 AND y>121 AND y<=196 ORDER BY y DESC;
1.696 + }
1.697 +} {13 12 11 nosort}
1.698 +do_test where-7.10 {
1.699 + cksort {
1.700 + SELECT w FROM t1 WHERE x=3 AND y>100 AND y<196 ORDER BY y DESC;
1.701 + }
1.702 +} {12 11 10 nosort}
1.703 +do_test where-7.11 {
1.704 + cksort {
1.705 + SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<196 ORDER BY y;
1.706 + }
1.707 +} {10 11 12 nosort}
1.708 +do_test where-7.12 {
1.709 + cksort {
1.710 + SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196 ORDER BY y;
1.711 + }
1.712 +} {10 11 12 13 nosort}
1.713 +do_test where-7.13 {
1.714 + cksort {
1.715 + SELECT w FROM t1 WHERE x=3 AND y>121 AND y<=196 ORDER BY y;
1.716 + }
1.717 +} {11 12 13 nosort}
1.718 +do_test where-7.14 {
1.719 + cksort {
1.720 + SELECT w FROM t1 WHERE x=3 AND y>100 AND y<196 ORDER BY y;
1.721 + }
1.722 +} {10 11 12 nosort}
1.723 +do_test where-7.15 {
1.724 + cksort {
1.725 + SELECT w FROM t1 WHERE x=3 AND y<81 ORDER BY y;
1.726 + }
1.727 +} {nosort}
1.728 +do_test where-7.16 {
1.729 + cksort {
1.730 + SELECT w FROM t1 WHERE x=3 AND y<=81 ORDER BY y;
1.731 + }
1.732 +} {8 nosort}
1.733 +do_test where-7.17 {
1.734 + cksort {
1.735 + SELECT w FROM t1 WHERE x=3 AND y>256 ORDER BY y;
1.736 + }
1.737 +} {nosort}
1.738 +do_test where-7.18 {
1.739 + cksort {
1.740 + SELECT w FROM t1 WHERE x=3 AND y>=256 ORDER BY y;
1.741 + }
1.742 +} {15 nosort}
1.743 +do_test where-7.19 {
1.744 + cksort {
1.745 + SELECT w FROM t1 WHERE x=3 AND y<81 ORDER BY y DESC;
1.746 + }
1.747 +} {nosort}
1.748 +do_test where-7.20 {
1.749 + cksort {
1.750 + SELECT w FROM t1 WHERE x=3 AND y<=81 ORDER BY y DESC;
1.751 + }
1.752 +} {8 nosort}
1.753 +do_test where-7.21 {
1.754 + cksort {
1.755 + SELECT w FROM t1 WHERE x=3 AND y>256 ORDER BY y DESC;
1.756 + }
1.757 +} {nosort}
1.758 +do_test where-7.22 {
1.759 + cksort {
1.760 + SELECT w FROM t1 WHERE x=3 AND y>=256 ORDER BY y DESC;
1.761 + }
1.762 +} {15 nosort}
1.763 +do_test where-7.23 {
1.764 + cksort {
1.765 + SELECT w FROM t1 WHERE x=0 AND y<4 ORDER BY y;
1.766 + }
1.767 +} {nosort}
1.768 +do_test where-7.24 {
1.769 + cksort {
1.770 + SELECT w FROM t1 WHERE x=0 AND y<=4 ORDER BY y;
1.771 + }
1.772 +} {1 nosort}
1.773 +do_test where-7.25 {
1.774 + cksort {
1.775 + SELECT w FROM t1 WHERE x=6 AND y>10201 ORDER BY y;
1.776 + }
1.777 +} {nosort}
1.778 +do_test where-7.26 {
1.779 + cksort {
1.780 + SELECT w FROM t1 WHERE x=6 AND y>=10201 ORDER BY y;
1.781 + }
1.782 +} {100 nosort}
1.783 +do_test where-7.27 {
1.784 + cksort {
1.785 + SELECT w FROM t1 WHERE x=0 AND y<4 ORDER BY y DESC;
1.786 + }
1.787 +} {nosort}
1.788 +do_test where-7.28 {
1.789 + cksort {
1.790 + SELECT w FROM t1 WHERE x=0 AND y<=4 ORDER BY y DESC;
1.791 + }
1.792 +} {1 nosort}
1.793 +do_test where-7.29 {
1.794 + cksort {
1.795 + SELECT w FROM t1 WHERE x=6 AND y>10201 ORDER BY y DESC;
1.796 + }
1.797 +} {nosort}
1.798 +do_test where-7.30 {
1.799 + cksort {
1.800 + SELECT w FROM t1 WHERE x=6 AND y>=10201 ORDER BY y DESC;
1.801 + }
1.802 +} {100 nosort}
1.803 +do_test where-7.31 {
1.804 + cksort {
1.805 + SELECT y FROM t1 ORDER BY rowid DESC LIMIT 3
1.806 + }
1.807 +} {10201 10000 9801 nosort}
1.808 +do_test where-7.32 {
1.809 + cksort {
1.810 + SELECT y FROM t1 WHERE y<25 ORDER BY rowid DESC
1.811 + }
1.812 +} {16 9 4 nosort}
1.813 +do_test where-7.33 {
1.814 + cksort {
1.815 + SELECT y FROM t1 WHERE y<=25 ORDER BY rowid DESC
1.816 + }
1.817 +} {25 16 9 4 nosort}
1.818 +do_test where-7.34 {
1.819 + cksort {
1.820 + SELECT y FROM t1 WHERE y<25 AND y>4 ORDER BY rowid DESC, y DESC
1.821 + }
1.822 +} {16 9 nosort}
1.823 +do_test where-7.35 {
1.824 + cksort {
1.825 + SELECT y FROM t1 WHERE y<25 AND y>=4 ORDER BY rowid DESC
1.826 + }
1.827 +} {16 9 4 nosort}
1.828 +
1.829 +do_test where-8.1 {
1.830 + execsql {
1.831 + CREATE TABLE t4 AS SELECT * FROM t1;
1.832 + CREATE INDEX i4xy ON t4(x,y);
1.833 + }
1.834 + cksort {
1.835 + SELECT w FROM t4 WHERE x=4 and y<1000 ORDER BY y DESC limit 3;
1.836 + }
1.837 +} {30 29 28 nosort}
1.838 +do_test where-8.2 {
1.839 + execsql {
1.840 + DELETE FROM t4;
1.841 + }
1.842 + cksort {
1.843 + SELECT w FROM t4 WHERE x=4 and y<1000 ORDER BY y DESC limit 3;
1.844 + }
1.845 +} {nosort}
1.846 +
1.847 +# Make sure searches with an index work with an empty table.
1.848 +#
1.849 +do_test where-9.1 {
1.850 + execsql {
1.851 + CREATE TABLE t5(x PRIMARY KEY);
1.852 + SELECT * FROM t5 WHERE x<10;
1.853 + }
1.854 +} {}
1.855 +do_test where-9.2 {
1.856 + execsql {
1.857 + SELECT * FROM t5 WHERE x<10 ORDER BY x DESC;
1.858 + }
1.859 +} {}
1.860 +do_test where-9.3 {
1.861 + execsql {
1.862 + SELECT * FROM t5 WHERE x=10;
1.863 + }
1.864 +} {}
1.865 +
1.866 +do_test where-10.1 {
1.867 + execsql {
1.868 + SELECT 1 WHERE abs(random())<0
1.869 + }
1.870 +} {}
1.871 +do_test where-10.2 {
1.872 + proc tclvar_func {vname} {return [set ::$vname]}
1.873 + db function tclvar tclvar_func
1.874 + set ::v1 0
1.875 + execsql {
1.876 + SELECT count(*) FROM t1 WHERE tclvar('v1');
1.877 + }
1.878 +} {0}
1.879 +do_test where-10.3 {
1.880 + set ::v1 1
1.881 + execsql {
1.882 + SELECT count(*) FROM t1 WHERE tclvar('v1');
1.883 + }
1.884 +} {100}
1.885 +do_test where-10.4 {
1.886 + set ::v1 1
1.887 + proc tclvar_func {vname} {
1.888 + upvar #0 $vname v
1.889 + set v [expr {!$v}]
1.890 + return $v
1.891 + }
1.892 + execsql {
1.893 + SELECT count(*) FROM t1 WHERE tclvar('v1');
1.894 + }
1.895 +} {50}
1.896 +
1.897 +# Ticket #1376. The query below was causing a segfault.
1.898 +# The problem was the age-old error of calling realloc() on an
1.899 +# array while there are still pointers to individual elements of
1.900 +# that array.
1.901 +#
1.902 +do_test where-11.1 {
1.903 + execsql {
1.904 + CREATE TABLE t99(Dte INT, X INT);
1.905 + DELETE FROM t99 WHERE (Dte = 2451337) OR (Dte = 2451339) OR
1.906 + (Dte BETWEEN 2451345 AND 2451347) OR (Dte = 2451351) OR
1.907 + (Dte BETWEEN 2451355 AND 2451356) OR (Dte = 2451358) OR
1.908 + (Dte = 2451362) OR (Dte = 2451365) OR (Dte = 2451367) OR
1.909 + (Dte BETWEEN 2451372 AND 2451376) OR (Dte BETWEEN 2451382 AND 2451384) OR
1.910 + (Dte = 2451387) OR (Dte BETWEEN 2451389 AND 2451391) OR
1.911 + (Dte BETWEEN 2451393 AND 2451395) OR (Dte = 2451400) OR
1.912 + (Dte = 2451402) OR (Dte = 2451404) OR (Dte BETWEEN 2451416 AND 2451418) OR
1.913 + (Dte = 2451422) OR (Dte = 2451426) OR (Dte BETWEEN 2451445 AND 2451446) OR
1.914 + (Dte = 2451456) OR (Dte = 2451458) OR (Dte BETWEEN 2451465 AND 2451467) OR
1.915 + (Dte BETWEEN 2451469 AND 2451471) OR (Dte = 2451474) OR
1.916 + (Dte BETWEEN 2451477 AND 2451501) OR (Dte BETWEEN 2451503 AND 2451509) OR
1.917 + (Dte BETWEEN 2451511 AND 2451514) OR (Dte BETWEEN 2451518 AND 2451521) OR
1.918 + (Dte BETWEEN 2451523 AND 2451531) OR (Dte BETWEEN 2451533 AND 2451537) OR
1.919 + (Dte BETWEEN 2451539 AND 2451544) OR (Dte BETWEEN 2451546 AND 2451551) OR
1.920 + (Dte BETWEEN 2451553 AND 2451555) OR (Dte = 2451557) OR
1.921 + (Dte BETWEEN 2451559 AND 2451561) OR (Dte = 2451563) OR
1.922 + (Dte BETWEEN 2451565 AND 2451566) OR (Dte BETWEEN 2451569 AND 2451571) OR
1.923 + (Dte = 2451573) OR (Dte = 2451575) OR (Dte = 2451577) OR (Dte = 2451581) OR
1.924 + (Dte BETWEEN 2451583 AND 2451586) OR (Dte BETWEEN 2451588 AND 2451592) OR
1.925 + (Dte BETWEEN 2451596 AND 2451598) OR (Dte = 2451600) OR
1.926 + (Dte BETWEEN 2451602 AND 2451603) OR (Dte = 2451606) OR (Dte = 2451611);
1.927 + }
1.928 +} {}
1.929 +
1.930 +# Ticket #2116: Make sure sorting by index works well with nn INTEGER PRIMARY
1.931 +# KEY.
1.932 +#
1.933 +do_test where-12.1 {
1.934 + execsql {
1.935 + CREATE TABLE t6(a INTEGER PRIMARY KEY, b TEXT);
1.936 + INSERT INTO t6 VALUES(1,'one');
1.937 + INSERT INTO t6 VALUES(4,'four');
1.938 + CREATE INDEX t6i1 ON t6(b);
1.939 + }
1.940 + cksort {
1.941 + SELECT * FROM t6 ORDER BY b;
1.942 + }
1.943 +} {4 four 1 one nosort}
1.944 +do_test where-12.2 {
1.945 + cksort {
1.946 + SELECT * FROM t6 ORDER BY b, a;
1.947 + }
1.948 +} {4 four 1 one nosort}
1.949 +do_test where-12.3 {
1.950 + cksort {
1.951 + SELECT * FROM t6 ORDER BY a;
1.952 + }
1.953 +} {1 one 4 four nosort}
1.954 +do_test where-12.4 {
1.955 + cksort {
1.956 + SELECT * FROM t6 ORDER BY a, b;
1.957 + }
1.958 +} {1 one 4 four nosort}
1.959 +do_test where-12.5 {
1.960 + cksort {
1.961 + SELECT * FROM t6 ORDER BY b DESC;
1.962 + }
1.963 +} {1 one 4 four nosort}
1.964 +do_test where-12.6 {
1.965 + cksort {
1.966 + SELECT * FROM t6 ORDER BY b DESC, a DESC;
1.967 + }
1.968 +} {1 one 4 four nosort}
1.969 +do_test where-12.7 {
1.970 + cksort {
1.971 + SELECT * FROM t6 ORDER BY b DESC, a ASC;
1.972 + }
1.973 +} {1 one 4 four sort}
1.974 +do_test where-12.8 {
1.975 + cksort {
1.976 + SELECT * FROM t6 ORDER BY b ASC, a DESC;
1.977 + }
1.978 +} {4 four 1 one sort}
1.979 +do_test where-12.9 {
1.980 + cksort {
1.981 + SELECT * FROM t6 ORDER BY a DESC;
1.982 + }
1.983 +} {4 four 1 one nosort}
1.984 +do_test where-12.10 {
1.985 + cksort {
1.986 + SELECT * FROM t6 ORDER BY a DESC, b DESC;
1.987 + }
1.988 +} {4 four 1 one nosort}
1.989 +do_test where-12.11 {
1.990 + cksort {
1.991 + SELECT * FROM t6 ORDER BY a DESC, b ASC;
1.992 + }
1.993 +} {4 four 1 one nosort}
1.994 +do_test where-12.12 {
1.995 + cksort {
1.996 + SELECT * FROM t6 ORDER BY a ASC, b DESC;
1.997 + }
1.998 +} {1 one 4 four nosort}
1.999 +do_test where-13.1 {
1.1000 + execsql {
1.1001 + CREATE TABLE t7(a INTEGER PRIMARY KEY, b TEXT);
1.1002 + INSERT INTO t7 VALUES(1,'one');
1.1003 + INSERT INTO t7 VALUES(4,'four');
1.1004 + CREATE INDEX t7i1 ON t7(b);
1.1005 + }
1.1006 + cksort {
1.1007 + SELECT * FROM t7 ORDER BY b;
1.1008 + }
1.1009 +} {4 four 1 one nosort}
1.1010 +do_test where-13.2 {
1.1011 + cksort {
1.1012 + SELECT * FROM t7 ORDER BY b, a;
1.1013 + }
1.1014 +} {4 four 1 one nosort}
1.1015 +do_test where-13.3 {
1.1016 + cksort {
1.1017 + SELECT * FROM t7 ORDER BY a;
1.1018 + }
1.1019 +} {1 one 4 four nosort}
1.1020 +do_test where-13.4 {
1.1021 + cksort {
1.1022 + SELECT * FROM t7 ORDER BY a, b;
1.1023 + }
1.1024 +} {1 one 4 four nosort}
1.1025 +do_test where-13.5 {
1.1026 + cksort {
1.1027 + SELECT * FROM t7 ORDER BY b DESC;
1.1028 + }
1.1029 +} {1 one 4 four nosort}
1.1030 +do_test where-13.6 {
1.1031 + cksort {
1.1032 + SELECT * FROM t7 ORDER BY b DESC, a DESC;
1.1033 + }
1.1034 +} {1 one 4 four nosort}
1.1035 +do_test where-13.7 {
1.1036 + cksort {
1.1037 + SELECT * FROM t7 ORDER BY b DESC, a ASC;
1.1038 + }
1.1039 +} {1 one 4 four sort}
1.1040 +do_test where-13.8 {
1.1041 + cksort {
1.1042 + SELECT * FROM t7 ORDER BY b ASC, a DESC;
1.1043 + }
1.1044 +} {4 four 1 one sort}
1.1045 +do_test where-13.9 {
1.1046 + cksort {
1.1047 + SELECT * FROM t7 ORDER BY a DESC;
1.1048 + }
1.1049 +} {4 four 1 one nosort}
1.1050 +do_test where-13.10 {
1.1051 + cksort {
1.1052 + SELECT * FROM t7 ORDER BY a DESC, b DESC;
1.1053 + }
1.1054 +} {4 four 1 one nosort}
1.1055 +do_test where-13.11 {
1.1056 + cksort {
1.1057 + SELECT * FROM t7 ORDER BY a DESC, b ASC;
1.1058 + }
1.1059 +} {4 four 1 one nosort}
1.1060 +do_test where-13.12 {
1.1061 + cksort {
1.1062 + SELECT * FROM t7 ORDER BY a ASC, b DESC;
1.1063 + }
1.1064 +} {1 one 4 four nosort}
1.1065 +
1.1066 +# Ticket #2211.
1.1067 +#
1.1068 +# When optimizing out ORDER BY clauses, make sure that trailing terms
1.1069 +# of the ORDER BY clause do not reference other tables in a join.
1.1070 +#
1.1071 +do_test where-14.1 {
1.1072 + execsql {
1.1073 + CREATE TABLE t8(a INTEGER PRIMARY KEY, b TEXT UNIQUE);
1.1074 + INSERT INTO t8 VALUES(1,'one');
1.1075 + INSERT INTO t8 VALUES(4,'four');
1.1076 + }
1.1077 + cksort {
1.1078 + SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b
1.1079 + }
1.1080 +} {1/4 1/1 4/4 4/1 sort}
1.1081 +do_test where-14.2 {
1.1082 + cksort {
1.1083 + SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b DESC
1.1084 + }
1.1085 +} {1/1 1/4 4/1 4/4 sort}
1.1086 +do_test where-14.3 {
1.1087 + cksort {
1.1088 + SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b
1.1089 + }
1.1090 +} {1/1 1/4 4/1 4/4 nosort}
1.1091 +do_test where-14.4 {
1.1092 + cksort {
1.1093 + SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b DESC
1.1094 + }
1.1095 +} {1/1 1/4 4/1 4/4 nosort}
1.1096 +do_test where-14.5 {
1.1097 + cksort {
1.1098 + SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b
1.1099 + }
1.1100 +} {4/1 4/4 1/1 1/4 nosort}
1.1101 +do_test where-14.6 {
1.1102 + cksort {
1.1103 + SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b DESC
1.1104 + }
1.1105 +} {4/1 4/4 1/1 1/4 nosort}
1.1106 +do_test where-14.7 {
1.1107 + cksort {
1.1108 + SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b
1.1109 + }
1.1110 +} {4/1 4/4 1/1 1/4 sort}
1.1111 +do_test where-14.7.1 {
1.1112 + cksort {
1.1113 + SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a, y.a||y.b
1.1114 + }
1.1115 +} {4/1 4/4 1/1 1/4 sort}
1.1116 +do_test where-14.7.2 {
1.1117 + cksort {
1.1118 + SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a, x.a||x.b
1.1119 + }
1.1120 +} {4/1 4/4 1/1 1/4 nosort}
1.1121 +do_test where-14.8 {
1.1122 + cksort {
1.1123 + SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b DESC
1.1124 + }
1.1125 +} {4/4 4/1 1/4 1/1 sort}
1.1126 +do_test where-14.9 {
1.1127 + cksort {
1.1128 + SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||y.b
1.1129 + }
1.1130 +} {4/4 4/1 1/4 1/1 sort}
1.1131 +do_test where-14.10 {
1.1132 + cksort {
1.1133 + SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||y.b DESC
1.1134 + }
1.1135 +} {4/1 4/4 1/1 1/4 sort}
1.1136 +do_test where-14.11 {
1.1137 + cksort {
1.1138 + SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||x.b
1.1139 + }
1.1140 +} {4/1 4/4 1/1 1/4 sort}
1.1141 +do_test where-14.12 {
1.1142 + cksort {
1.1143 + SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||x.b DESC
1.1144 + }
1.1145 +} {4/4 4/1 1/4 1/1 sort}
1.1146 +
1.1147 +# Ticket #2445.
1.1148 +#
1.1149 +# There was a crash that could occur when a where clause contains an
1.1150 +# alias for an expression in the result set, and that expression retrieves
1.1151 +# a column of the second or subsequent table in a join.
1.1152 +#
1.1153 +do_test where-15.1 {
1.1154 + execsql {
1.1155 + CREATE TEMP TABLE t1 (a, b, c, d, e);
1.1156 + CREATE TEMP TABLE t2 (f);
1.1157 + SELECT t1.e AS alias FROM t2, t1 WHERE alias = 1 ;
1.1158 + }
1.1159 +} {}
1.1160 +
1.1161 +integrity_check {where-99.0}
1.1162 +
1.1163 +finish_test