test/where.test

   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");  -- Verify quoted identifier names
  46     CREATE INDEX i1xy ON t1(`x`,'y' ASC); -- Old MySQL compatibility
  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_test where-1.1.1b {
  69   count {SELECT x, y, w FROM t1 WHERE w IS 10}
  70 } {3 121 10 3}
  71 do_eqp_test where-1.1.2 {
  72   SELECT x, y, w FROM t1 WHERE w=10
  73 } {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
  74 do_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=?)*}
  77 do_test where-1.1.3 {
  78   db status step
  79 } {0}
  80 do_test where-1.1.4 {
  81   db eval {SELECT x, y, w FROM t1 WHERE +w=10}
  82 } {3 121 10}
  83 do_test where-1.1.5 {
  84   db status step
  85 } {99}
  86 do_eqp_test where-1.1.6 {
  87   SELECT x, y, w FROM t1 WHERE +w=10
  88 } {*SCAN TABLE t1*}
  89 do_test where-1.1.7 {
  90   count {SELECT x, y, w AS abc FROM t1 WHERE abc=10}
  91 } {3 121 10 3}
  92 do_eqp_test where-1.1.8 {
  93   SELECT x, y, w AS abc FROM t1 WHERE abc=10
  94 } {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
  95 do_test where-1.1.9 {
  96   db status step
  97 } {0}
  98 do_test where-1.2.1 {
  99   count {SELECT x, y, w FROM t1 WHERE w=11}
 100 } {3 144 11 3}
 101 do_test where-1.2.2 {
 102   count {SELECT x, y, w AS abc FROM t1 WHERE abc=11}
 103 } {3 144 11 3}
 104 do_test where-1.3.1 {
 105   count {SELECT x, y, w AS abc FROM t1 WHERE 11=w}
 106 } {3 144 11 3}
 107 do_test where-1.3.2 {
 108   count {SELECT x, y, w AS abc FROM t1 WHERE 11=abc}
 109 } {3 144 11 3}
 110 do_test where-1.3.3 {
 111   count {SELECT x, y, w AS abc FROM t1 WHERE 11 IS abc}
 112 } {3 144 11 3}
 113 do_test where-1.4.1 {
 114   count {SELECT w, x, y FROM t1 WHERE 11=w AND x>2}
 115 } {11 3 144 3}
 116 do_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}
 119 do_eqp_test where-1.4.2 {
 120   SELECT w, x, y FROM t1 WHERE 11=w AND x>2
 121 } {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
 122 do_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=?)*}
 125 do_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}
 128 do_eqp_test where-1.4.4 {
 129   SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2
 130 } {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
 131 do_test where-1.5 {
 132   count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2}
 133 } {3 144 3}
 134 do_eqp_test where-1.5.2 {
 135   SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2
 136 } {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
 137 do_test where-1.6 {
 138   count {SELECT x, y FROM t1 WHERE y<200 AND x>2 AND w=11}
 139 } {3 144 3}
 140 do_test where-1.7 {
 141   count {SELECT x, y FROM t1 WHERE w=11 AND y<200 AND x>2}
 142 } {3 144 3}
 143 do_test where-1.8 {
 144   count {SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3}
 145 } {3 144 3}
 146 do_eqp_test where-1.8.2 {
 147   SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3
 148 } {*SEARCH TABLE t1 USING INDEX i1xy (x=? AND y=?)*}
 149 do_eqp_test where-1.8.3 {
 150   SELECT x, y FROM t1 WHERE y=144 AND x=3
 151 } {*SEARCH TABLE t1 USING COVERING INDEX i1xy (x=? AND y=?)*}
 152 do_test where-1.9 {
 153   count {SELECT x, y FROM t1 WHERE y=144 AND w>10 AND x=3}
 154 } {3 144 3}
 155 do_test where-1.10 {
 156   count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121}
 157 } {3 121 3}
 158 do_test where-1.11 {
 159   count {SELECT x, y FROM t1 WHERE x=3 AND y=100 AND w<10}
 160 } {3 100 3}
 161 do_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}
 164
 165 # New for SQLite version 2.1: Verify that that inequality constraints
 166 # are used correctly.
 167 #
 168 do_test where-1.12 {
 169   count {SELECT w FROM t1 WHERE x=3 AND y<100}
 170 } {8 3}
 171 do_test where-1.12b {
 172   count {SELECT w FROM t1 WHERE x IS 3 AND y<100}
 173 } {8 3}
 174 do_test where-1.13 {
 175   count {SELECT w FROM t1 WHERE x=3 AND 100>y}
 176 } {8 3}
 177 do_test where-1.14 {
 178   count {SELECT w FROM t1 WHERE 3=x AND y<100}
 179 } {8 3}
 180 do_test where-1.14b {
 181   count {SELECT w FROM t1 WHERE 3 IS x AND y<100}
 182 } {8 3}
 183 do_test where-1.15 {
 184   count {SELECT w FROM t1 WHERE 3=x AND 100>y}
 185 } {8 3}
 186 do_test where-1.16 {
 187   count {SELECT w FROM t1 WHERE x=3 AND y<=100}
 188 } {8 9 5}
 189 do_test where-1.17 {
 190   count {SELECT w FROM t1 WHERE x=3 AND 100>=y}
 191 } {8 9 5}
 192 do_test where-1.18 {
 193   count {SELECT w FROM t1 WHERE x=3 AND y>225}
 194 } {15 3}
 195 do_test where-1.18b {
 196   count {SELECT w FROM t1 WHERE x IS 3 AND y>225}
 197 } {15 3}
 198 do_test where-1.19 {
 199   count {SELECT w FROM t1 WHERE x=3 AND 225<y}
 200 } {15 3}
 201 do_test where-1.20 {
 202   count {SELECT w FROM t1 WHERE x=3 AND y>=225}
 203 } {14 15 5}
 204 do_test where-1.21 {
 205   count {SELECT w FROM t1 WHERE x=3 AND 225<=y}
 206 } {14 15 5}
 207 do_test where-1.22 {
 208   count {SELECT w FROM t1 WHERE x=3 AND y>121 AND y<196}
 209 } {11 12 5}
 210 do_test where-1.22b {
 211   count {SELECT w FROM t1 WHERE x IS 3 AND y>121 AND y<196}
 212 } {11 12 5}
 213 do_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}
 216 do_test where-1.24 {
 217   count {SELECT w FROM t1 WHERE x=3 AND 121<y AND 196>y}
 218 } {11 12 5}
 219 do_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
 229 do_test where-1.27 {
 230   count {SELECT w FROM t1 WHERE x=3 AND y+1==122}
 231 } {10 10}
 232
 233 do_test where-1.28 {
 234   count {SELECT w FROM t1 WHERE x+1=4 AND y+1==122}
 235 } {10 99}
 236 do_test where-1.29 {
 237   count {SELECT w FROM t1 WHERE y==121}
 238 } {10 99}
 239
 240
 241 do_test where-1.30 {
 242   count {SELECT w FROM t1 WHERE w>97}
 243 } {98 99 100 3}
 244 do_test where-1.31 {
 245   count {SELECT w FROM t1 WHERE w>=97}
 246 } {97 98 99 100 4}
 247 do_test where-1.33 {
 248   count {SELECT w FROM t1 WHERE w==97}
 249 } {97 2}
 250 do_test where-1.33.1  {
 251   count {SELECT w FROM t1 WHERE w<=97 AND w==97}
 252 } {97 2}
 253 do_test where-1.33.2  {
 254   count {SELECT w FROM t1 WHERE w<98 AND w==97}
 255 } {97 2}
 256 do_test where-1.33.3  {
 257   count {SELECT w FROM t1 WHERE w>=97 AND w==97}
 258 } {97 2}
 259 do_test where-1.33.4  {
 260   count {SELECT w FROM t1 WHERE w>96 AND w==97}
 261 } {97 2}
 262 do_test where-1.33.5  {
 263   count {SELECT w FROM t1 WHERE w==97 AND w==97}
 264 } {97 2}
 265 do_test where-1.34 {
 266   count {SELECT w FROM t1 WHERE w+1==98}
 267 } {97 99}
 268 do_test where-1.35 {
 269   count {SELECT w FROM t1 WHERE w<3}
 270 } {1 2 3}
 271 do_test where-1.36 {
 272   count {SELECT w FROM t1 WHERE w<=3}
 273 } {1 2 3 4}
 274 do_test where-1.37 {
 275   count {SELECT w FROM t1 WHERE w+1<=4 ORDER BY w}
 276 } {1 2 3 99}
 277
 278 do_test where-1.38 {
 279   count {SELECT (w) FROM t1 WHERE (w)>(97)}
 280 } {98 99 100 3}
 281 do_test where-1.39 {
 282   count {SELECT (w) FROM t1 WHERE (w)>=(97)}
 283 } {97 98 99 100 4}
 284 do_test where-1.40 {
 285   count {SELECT (w) FROM t1 WHERE (w)==(97)}
 286 } {97 2}
 287 do_test where-1.41 {
 288   count {SELECT (w) FROM t1 WHERE ((w)+(1))==(98)}
 289 } {97 99}
 290
 291
 292 # Do the same kind of thing except use a join as the data source.
 293 #
 294 do_test where-2.1 {
 295   count {
 296     SELECT w, p FROM t2, t1
 297     WHERE x=q AND y=s AND r=8977
 298   }
 299 } {34 67 6}
 300 do_test where-2.2 {
 301   count {
 302     SELECT w, p FROM t2, t1
 303     WHERE x=q AND s=y AND r=8977
 304   }
 305 } {34 67 6}
 306 do_test where-2.3 {
 307   count {
 308     SELECT w, p FROM t2, t1
 309     WHERE x=q AND s=y AND r=8977 AND w>10
 310   }
 311 } {34 67 6}
 312 do_test where-2.4 {
 313   count {
 314     SELECT w, p FROM t2, t1
 315     WHERE p<80 AND x=q AND s=y AND r=8977 AND w>10
 316   }
 317 } {34 67 6}
 318 do_test where-2.5 {
 319   count {
 320     SELECT w, p FROM t2, t1
 321     WHERE p<80 AND x=q AND 8977=r AND s=y AND w>10
 322   }
 323 } {34 67 6}
 324 do_test where-2.6 {
 325   count {
 326     SELECT w, p FROM t2, t1
 327     WHERE x=q AND p=77 AND s=y AND w>5
 328   }
 329 } {24 77 6}
 330 do_test where-2.7 {
 331   count {
 332     SELECT w, p FROM t1, t2
 333     WHERE x=q AND p>77 AND s=y AND w=5
 334   }
 335 } {5 96 6}
 336
 337 # Lets do a 3-way join.
 338 #
 339 do_test where-3.1 {
 340   count {
 341     SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
 342     WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=11
 343   }
 344 } {11 90 11 8}
 345 do_test where-3.2 {
 346   count {
 347     SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
 348     WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=12
 349   }
 350 } {12 89 12 8}
 351 do_test where-3.3 {
 352   count {
 353     SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
 354     WHERE A.w=15 AND B.p=C.w AND B.r=10202-A.y
 355   }
 356 } {15 86 86 8}
 357
 358 # Test to see that the special case of a constant WHERE clause is
 359 # handled.
 360 #
 361 do_test where-4.1 {
 362   count {
 363     SELECT * FROM t1 WHERE 0
 364   }
 365 } {0}
 366 do_test where-4.2 {
 367   count {
 368     SELECT * FROM t1 WHERE 1 LIMIT 1
 369   }
 370 } {1 0 4 0}
 371 do_test where-4.3 {
 372   execsql {
 373     SELECT 99 WHERE 0
 374   }
 375 } {}
 376 do_test where-4.4 {
 377   execsql {
 378     SELECT 99 WHERE 1
 379   }
 380 } {99}
 381 do_test where-4.5 {
 382   execsql {
 383     SELECT 99 WHERE 0.1
 384   }
 385 } {99}
 386 do_test where-4.6 {
 387   execsql {
 388     SELECT 99 WHERE 0.0
 389   }
 390 } {}
 391 do_test where-4.7 {
 392   execsql {
 393     SELECT count(*) FROM t1 WHERE t1.w
 394   }
 395 } {100}
 396
 397 # Verify that IN operators in a WHERE clause are handled correctly.
 398 # Omit these tests if the build is not capable of sub-queries.
 399 #
 400 ifcapable subquery {
 401   do_test where-5.1 {
 402     count {
 403       SELECT * FROM t1 WHERE rowid IN (1,2,3,1234) order by 1;
 404     }
 405   } {1 0 4 2 1 9 3 1 16 4}
 406   do_test where-5.2 {
 407     count {
 408       SELECT * FROM t1 WHERE rowid+0 IN (1,2,3,1234) order by 1;
 409     }
 410   } {1 0 4 2 1 9 3 1 16 102}
 411   do_test where-5.3a {
 412     count {
 413       SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1;
 414     }
 415   } {1 0 4 2 1 9 3 1 16 12}
 416   do_test where-5.3b {
 417     count {
 418       SELECT * FROM t1 WHERE w IN (3,-1,1,2) order by 1;
 419     }
 420   } {1 0 4 2 1 9 3 1 16 12}
 421   do_test where-5.3c {
 422     count {
 423       SELECT * FROM t1 WHERE w IN (3,2,-1,1,2) order by 1;
 424     }
 425   } {1 0 4 2 1 9 3 1 16 12}
 426   do_test where-5.3d {
 427     count {
 428       SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1 DESC;
 429     }
 430   } {3 1 16 2 1 9 1 0 4 11}
 431   do_test where-5.4 {
 432     count {
 433       SELECT * FROM t1 WHERE w+0 IN (-1,1,2,3) order by 1;
 434     }
 435   } {1 0 4 2 1 9 3 1 16 102}
 436   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     }
 449   } {2 1 9 4 2 25 103}
 450   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     }
 463   } {2 1 9 4 2 25 103}
 464   do_test where-5.9 {
 465     count {
 466       SELECT * FROM t1 WHERE x IN (1,7) ORDER BY 1;
 467     }
 468   } {2 1 9 3 1 16 6}
 469   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     }
 483   } {79 6 6400 89 6 8100 7}
 484   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     }
 488   } {2 1 9 3 1 16 6}
 489   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     }
 493   } {2 1 9 4}
 494   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     }
 498   } {2 1 9 3 1 16 8}
 499   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}
 523 }
 524
 525 # 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.
 531 #
 532 proc cksort {sql} {
 533   set data [execsql $sql]
 534   if {[db status sort]} {set x sort} {set x nosort}
 535   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 #
 541 do_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   }
 550 } {100 5050 5050 348550}
 551 do_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}
 556 do_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}
 561 do_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}
 566 do_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}
 571 do_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}
 576 do_test where-6.7.1 {
 577   cksort {
 578     SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 10
 579   }
 580 } {/1 100 4 2 99 9 3 98 16 .* nosort/}
 581 do_test where-6.7.2 {
 582   cksort {
 583     SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 1
 584   }
 585 } {1 100 4 nosort}
 586 ifcapable subquery {
 587   do_test where-6.8a {
 588     cksort {
 589       SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a LIMIT 3
 590     }
 591   } {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     }
 596   } {9 92 100 7 94 64 5 96 36 nosort}
 597 }
 598 do_test where-6.9.1 {
 599   cksort {
 600     SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3
 601   }
 602 } {1 100 4 nosort}
 603 do_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}
 608 do_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}
 613 do_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}
 618 do_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}
 623 do_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}
 628 do_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}
 633 do_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}
 638 do_test where-6.9.7 {
 639   cksort {
 640     SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c,a LIMIT 3
 641   }
 642 } {1 100 4 nosort}
 643 do_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   }
 647 } {1 100 4 nosort}
 648 do_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   }
 652 } {1 100 4 nosort}
 653 do_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}
 658 do_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}
 663 do_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}
 668 do_test where-6.13 {
 669   cksort {
 670     SELECT * FROM t3 WHERE a>0 ORDER BY a DESC LIMIT 3
 671   }
 672 } {100 1 10201 99 2 10000 98 3 9801 nosort}
 673 do_test where-6.13.1 {
 674   cksort {
 675     SELECT * FROM t3 WHERE a>0 ORDER BY -a LIMIT 3
 676   }
 677 } {100 1 10201 99 2 10000 98 3 9801 sort}
 678 do_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}
 683 do_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}
 688 do_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}
 693 do_test where-6.19 {
 694   cksort {
 695     SELECT y FROM t1 ORDER BY w LIMIT 3;
 696   }
 697 } {4 9 16 nosort}
 698 do_test where-6.20 {
 699   cksort {
 700     SELECT y FROM t1 ORDER BY rowid LIMIT 3;
 701   }
 702 } {4 9 16 nosort}
 703 do_test where-6.21 {
 704   cksort {
 705     SELECT y FROM t1 ORDER BY rowid, y LIMIT 3;
 706   }
 707 } {4 9 16 nosort}
 708 do_test where-6.22 {
 709   cksort {
 710     SELECT y FROM t1 ORDER BY rowid, y DESC LIMIT 3;
 711   }
 712 } {4 9 16 nosort}
 713 do_test where-6.23 {
 714   cksort {
 715     SELECT y FROM t1 WHERE y>4 ORDER BY rowid, w, x LIMIT 3;
 716   }
 717 } {9 16 25 nosort}
 718 do_test where-6.24 {
 719   cksort {
 720     SELECT y FROM t1 WHERE y>=9 ORDER BY rowid, x DESC, w LIMIT 3;
 721   }
 722 } {9 16 25 nosort}
 723 do_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}
 728 do_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}
 733 do_test where-6.27 {
 734   cksort {
 735     SELECT y FROM t1 WHERE y<=25 ORDER BY _rowid_, w+y;
 736   }
 737 } {4 9 16 25 nosort}
 738
 739
 740 # Tests for reverse-order sorting.
 741 #
 742 do_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}
 747 do_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}
 752 do_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}
 757 do_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}
 762 do_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}
 767 do_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}
 772 do_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}
 777 do_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}
 782 do_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}
 787 do_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}
 792 do_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}
 797 do_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}
 802 do_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}
 807 do_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}
 812 do_test where-7.15 {
 813   cksort {
 814     SELECT w FROM t1 WHERE x=3 AND y<81 ORDER BY y;
 815   }
 816 } {nosort}
 817 do_test where-7.16 {
 818   cksort {
 819     SELECT w FROM t1 WHERE x=3 AND y<=81 ORDER BY y;
 820   }
 821 } {8 nosort}
 822 do_test where-7.17 {
 823   cksort {
 824     SELECT w FROM t1 WHERE x=3 AND y>256 ORDER BY y;
 825   }
 826 } {nosort}
 827 do_test where-7.18 {
 828   cksort {
 829     SELECT w FROM t1 WHERE x=3 AND y>=256 ORDER BY y;
 830   }
 831 } {15 nosort}
 832 do_test where-7.19 {
 833   cksort {
 834     SELECT w FROM t1 WHERE x=3 AND y<81 ORDER BY y DESC;
 835   }
 836 } {nosort}
 837 do_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}
 842 do_test where-7.21 {
 843   cksort {
 844     SELECT w FROM t1 WHERE x=3 AND y>256 ORDER BY y DESC;
 845   }
 846 } {nosort}
 847 do_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}
 852 do_test where-7.23 {
 853   cksort {
 854     SELECT w FROM t1 WHERE x=0 AND y<4 ORDER BY y;
 855   }
 856 } {nosort}
 857 do_test where-7.24 {
 858   cksort {
 859     SELECT w FROM t1 WHERE x=0 AND y<=4 ORDER BY y;
 860   }
 861 } {1 nosort}
 862 do_test where-7.25 {
 863   cksort {
 864     SELECT w FROM t1 WHERE x=6 AND y>10201 ORDER BY y;
 865   }
 866 } {nosort}
 867 do_test where-7.26 {
 868   cksort {
 869     SELECT w FROM t1 WHERE x=6 AND y>=10201 ORDER BY y;
 870   }
 871 } {100 nosort}
 872 do_test where-7.27 {
 873   cksort {
 874     SELECT w FROM t1 WHERE x=0 AND y<4 ORDER BY y DESC;
 875   }
 876 } {nosort}
 877 do_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}
 882 do_test where-7.29 {
 883   cksort {
 884     SELECT w FROM t1 WHERE x=6 AND y>10201 ORDER BY y DESC;
 885   }
 886 } {nosort}
 887 do_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}
 892 do_test where-7.31 {
 893   cksort {
 894     SELECT y FROM t1 ORDER BY rowid DESC LIMIT 3
 895   }
 896 } {10201 10000 9801 nosort}
 897 do_test where-7.32 {
 898   cksort {
 899     SELECT y FROM t1 WHERE y<25 ORDER BY rowid DESC
 900   }
 901 } {16 9 4 nosort}
 902 do_test where-7.33 {
 903   cksort {
 904     SELECT y FROM t1 WHERE y<=25 ORDER BY rowid DESC
 905   }
 906 } {25 16 9 4 nosort}
 907 do_test where-7.34 {
 908   cksort {
 909     SELECT y FROM t1 WHERE y<25 AND y>4 ORDER BY rowid DESC, y DESC
 910   }
 911 } {16 9 nosort}
 912 do_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}
 917
 918 do_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}
 927 do_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
 936 # Make sure searches with an index work with an empty table.
 937 #
 938 do_test where-9.1 {
 939   execsql {
 940     CREATE TABLE t5(x PRIMARY KEY);
 941     SELECT * FROM t5 WHERE x<10;
 942   }
 943 } {}
 944 do_test where-9.2 {
 945   execsql {
 946     SELECT * FROM t5 WHERE x<10 ORDER BY x DESC;
 947   }
 948 } {}
 949 do_test where-9.3 {
 950   execsql {
 951     SELECT * FROM t5 WHERE x=10;
 952   }
 953 } {}
 954
 955 do_test where-10.1 {
 956   execsql {
 957     SELECT 1 WHERE abs(random())<0
 958   }
 959 } {}
 960 do_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}
 968 do_test where-10.3 {
 969   set ::v1 1
 970   execsql {
 971     SELECT count(*) FROM t1 WHERE tclvar('v1');
 972   }
 973 } {100}
 974 do_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
 986 # 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 #
 991 do_test where-11.1 {
 992   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
1019 # Ticket #2116:  Make sure sorting by index works well with nn INTEGER PRIMARY
1020 # KEY.
1021 #
1022 do_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}
1033 do_test where-12.2 {
1034   cksort {
1035     SELECT * FROM t6 ORDER BY b, a;
1036   }
1037 } {4 four 1 one nosort}
1038 do_test where-12.3 {
1039   cksort {
1040     SELECT * FROM t6 ORDER BY a;
1041   }
1042 } {1 one 4 four nosort}
1043 do_test where-12.4 {
1044   cksort {
1045     SELECT * FROM t6 ORDER BY a, b;
1046   }
1047 } {1 one 4 four nosort}
1048 do_test where-12.5 {
1049   cksort {
1050     SELECT * FROM t6 ORDER BY b DESC;
1051   }
1052 } {1 one 4 four nosort}
1053 do_test where-12.6 {
1054   cksort {
1055     SELECT * FROM t6 ORDER BY b DESC, a DESC;
1056   }
1057 } {1 one 4 four nosort}
1058 do_test where-12.7 {
1059   cksort {
1060     SELECT * FROM t6 ORDER BY b DESC, a ASC;
1061   }
1062 } {1 one 4 four sort}
1063 do_test where-12.8 {
1064   cksort {
1065     SELECT * FROM t6 ORDER BY b ASC, a DESC;
1066   }
1067 } {4 four 1 one sort}
1068 do_test where-12.9 {
1069   cksort {
1070     SELECT * FROM t6 ORDER BY a DESC;
1071   }
1072 } {4 four 1 one nosort}
1073 do_test where-12.10 {
1074   cksort {
1075     SELECT * FROM t6 ORDER BY a DESC, b DESC;
1076   }
1077 } {4 four 1 one nosort}
1078 do_test where-12.11 {
1079   cksort {
1080     SELECT * FROM t6 ORDER BY a DESC, b ASC;
1081   }
1082 } {4 four 1 one nosort}
1083 do_test where-12.12 {
1084   cksort {
1085     SELECT * FROM t6 ORDER BY a ASC, b DESC;
1086   }
1087 } {1 one 4 four nosort}
1088 do_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}
1099 do_test where-13.2 {
1100   cksort {
1101     SELECT * FROM t7 ORDER BY b, a;
1102   }
1103 } {4 four 1 one nosort}
1104 do_test where-13.3 {
1105   cksort {
1106     SELECT * FROM t7 ORDER BY a;
1107   }
1108 } {1 one 4 four nosort}
1109 do_test where-13.4 {
1110   cksort {
1111     SELECT * FROM t7 ORDER BY a, b;
1112   }
1113 } {1 one 4 four nosort}
1114 do_test where-13.5 {
1115   cksort {
1116     SELECT * FROM t7 ORDER BY b DESC;
1117   }
1118 } {1 one 4 four nosort}
1119 do_test where-13.6 {
1120   cksort {
1121     SELECT * FROM t7 ORDER BY b DESC, a DESC;
1122   }
1123 } {1 one 4 four nosort}
1124 do_test where-13.7 {
1125   cksort {
1126     SELECT * FROM t7 ORDER BY b DESC, a ASC;
1127   }
1128 } {1 one 4 four sort}
1129 do_test where-13.8 {
1130   cksort {
1131     SELECT * FROM t7 ORDER BY b ASC, a DESC;
1132   }
1133 } {4 four 1 one sort}
1134 do_test where-13.9 {
1135   cksort {
1136     SELECT * FROM t7 ORDER BY a DESC;
1137   }
1138 } {4 four 1 one nosort}
1139 do_test where-13.10 {
1140   cksort {
1141     SELECT * FROM t7 ORDER BY a DESC, b DESC;
1142   }
1143 } {4 four 1 one nosort}
1144 do_test where-13.11 {
1145   cksort {
1146     SELECT * FROM t7 ORDER BY a DESC, b ASC;
1147   }
1148 } {4 four 1 one nosort}
1149 do_test where-13.12 {
1150   cksort {
1151     SELECT * FROM t7 ORDER BY a ASC, b DESC;
1152   }
1153 } {1 one 4 four nosort}
1154
1155 # 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 #
1160 if {[permutation] != "no_optimization"} {
1161 do_test where-14.1 {
1162   execsql {
1163     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');
1166   }
1167   cksort {
1168     SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b
1169   }
1170 } {1/4 1/1 4/4 4/1 nosort}
1171 do_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   }
1175 } {1/1 1/4 4/1 4/4 nosort}
1176 do_test where-14.3 {
1177   cksort {
1178     SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b
1179   }
1180 } {1/4 1/1 4/4 4/1 nosort}
1181 do_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   }
1185 } {1/4 1/1 4/4 4/1 nosort}
1186 do_test where-14.5 {
1187   # This test case changed from "nosort" to "sort". See ticket 2a5629202f.
1188   cksort {
1189     SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b
1190   }
1191 } {/4/[14] 4/[14] 1/[14] 1/[14] sort/}
1192 do_test where-14.6 {
1193   # This test case changed from "nosort" to "sort". See ticket 2a5629202f.
1194   cksort {
1195     SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b DESC
1196   }
1197 } {/4/[14] 4/[14] 1/[14] 1/[14] sort/}
1198 do_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}
1203 do_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}
1208 do_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   }
1212 } {4/4 4/1 1/4 1/1 nosort}
1213 do_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}
1218 do_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}
1223 do_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}
1228 do_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}
1233 do_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}
1238 } ;# {permutation != "no_optimization"}
1239
1240 # 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 #
1246 do_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 } {}
1253
1254 # 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 #
1261 do_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 } {}
1271 do_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.
1278 do_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 } {}
1292 do_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
1298 integrity_check {where-99.0}
1299
1300 #---------------------------------------------------------------------
1301 # These tests test that a bug surrounding the use of ForceInt has been
1302 # fixed in where.c.
1303 #
1304 do_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 } {}
1314 do_test where-17.2 {
1315   execsql {
1316     SELECT a.id
1317     FROM tbooking AS a
1318     WHERE a.eventtype=3;
1319   }
1320 } {42}
1321 do_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}
1328 do_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}
1334 do_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
1342 # Ticket [be84e357c035d068135f20bcfe82761bbf95006b]  2013-09-03
1343 # Segfault during query involving LEFT JOIN column in the ORDER BY clause.
1344 #
1345 do_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}
1351 do_execsql_test where-18.2 {
1352   SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +c;
1353 } {1}
1354 do_execsql_test where-18.3 {
1355   SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c;
1356 } {1}
1357 do_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}
1361 do_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}
1365 do_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
1370 # Make sure the OR optimization works on a JOIN
1371 #
1372 do_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
1382 # 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 #
1385 do_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
1394 do_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 }
1407 do_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 }
1415
1416 # 2018-11-05: ticket [https://www.sqlite.org/src/tktview/65eb38f6e46de8c75e188a]
1417 # Incorrect result in LEFT JOIN when STAT4 is enabled.
1418 #
1419 sqlite3 db :memory:
1420 do_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
1428
1429 finish_test