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