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.47 2008/09/01 15:52:11 drh 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_test where-1.1.2 {

    69   set sqlite_query_plan

    70 } {t1 i1w}

    71 do_test where-1.1.3 {

    72   count {SELECT x, y, w AS abc FROM t1 WHERE abc=10}

    73 } {3 121 10 3}

    74 do_test where-1.1.4 {

    75   set sqlite_query_plan

    76 } {t1 i1w}

    77 do_test where-1.2.1 {

    78   count {SELECT x, y, w FROM t1 WHERE w=11}

    79 } {3 144 11 3}

    80 do_test where-1.2.2 {

    81   count {SELECT x, y, w AS abc FROM t1 WHERE abc=11}

    82 } {3 144 11 3}

    83 do_test where-1.3.1 {

    84   count {SELECT x, y, w AS abc FROM t1 WHERE 11=w}

    85 } {3 144 11 3}

    86 do_test where-1.3.2 {

    87   count {SELECT x, y, w AS abc FROM t1 WHERE 11=abc}

    88 } {3 144 11 3}

    89 do_test where-1.4.1 {

    90   count {SELECT w, x, y FROM t1 WHERE 11=w AND x>2}

    91 } {11 3 144 3}

    92 do_test where-1.4.2 {

    93   set sqlite_query_plan

    94 } {t1 i1w}

    95 do_test where-1.4.3 {

    96   count {SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2}

    97 } {11 3 144 3}

    98 do_test where-1.4.4 {

    99   set sqlite_query_plan

   100 } {t1 i1w}

   101 do_test where-1.5 {

   102   count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2}

   103 } {3 144 3}

   104 do_test where-1.5.2 {

   105   set sqlite_query_plan

   106 } {t1 i1w}

   107 do_test where-1.6 {

   108   count {SELECT x, y FROM t1 WHERE y<200 AND x>2 AND w=11}

   109 } {3 144 3}

   110 do_test where-1.7 {

   111   count {SELECT x, y FROM t1 WHERE w=11 AND y<200 AND x>2}

   112 } {3 144 3}

   113 do_test where-1.8 {

   114   count {SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3}

   115 } {3 144 3}

   116 do_test where-1.8.2 {

   117   set sqlite_query_plan

   118 } {t1 i1xy}

   119 do_test where-1.8.3 {

   120   count {SELECT x, y FROM t1 WHERE y=144 AND x=3}

   121   set sqlite_query_plan

   122 } {{} i1xy}

   123 do_test where-1.9 {

   124   count {SELECT x, y FROM t1 WHERE y=144 AND w>10 AND x=3}

   125 } {3 144 3}

   126 do_test where-1.10 {

   127   count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121}

   128 } {3 121 3}

   129 do_test where-1.11 {

   130   count {SELECT x, y FROM t1 WHERE x=3 AND y=100 AND w<10}

   131 } {3 100 3}

   132 

   133 # New for SQLite version 2.1: Verify that that inequality constraints

   134 # are used correctly.

   135 #

   136 do_test where-1.12 {

   137   count {SELECT w FROM t1 WHERE x=3 AND y<100}

   138 } {8 3}

   139 do_test where-1.13 {

   140   count {SELECT w FROM t1 WHERE x=3 AND 100>y}

   141 } {8 3}

   142 do_test where-1.14 {

   143   count {SELECT w FROM t1 WHERE 3=x AND y<100}

   144 } {8 3}

   145 do_test where-1.15 {

   146   count {SELECT w FROM t1 WHERE 3=x AND 100>y}

   147 } {8 3}

   148 do_test where-1.16 {

   149   count {SELECT w FROM t1 WHERE x=3 AND y<=100}

   150 } {8 9 5}

   151 do_test where-1.17 {

   152   count {SELECT w FROM t1 WHERE x=3 AND 100>=y}

   153 } {8 9 5}

   154 do_test where-1.18 {

   155   count {SELECT w FROM t1 WHERE x=3 AND y>225}

   156 } {15 3}

   157 do_test where-1.19 {

   158   count {SELECT w FROM t1 WHERE x=3 AND 225<y}

   159 } {15 3}

   160 do_test where-1.20 {

   161   count {SELECT w FROM t1 WHERE x=3 AND y>=225}

   162 } {14 15 5}

   163 do_test where-1.21 {

   164   count {SELECT w FROM t1 WHERE x=3 AND 225<=y}

   165 } {14 15 5}

   166 do_test where-1.22 {

   167   count {SELECT w FROM t1 WHERE x=3 AND y>121 AND y<196}

   168 } {11 12 5}

   169 do_test where-1.23 {

   170   count {SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196}

   171 } {10 11 12 13 9}

   172 do_test where-1.24 {

   173   count {SELECT w FROM t1 WHERE x=3 AND 121<y AND 196>y}

   174 } {11 12 5}

   175 do_test where-1.25 {

   176   count {SELECT w FROM t1 WHERE x=3 AND 121<=y AND 196>=y}

   177 } {10 11 12 13 9}

   178 

   179 # Need to work on optimizing the BETWEEN operator.  

   180 #

   181 # do_test where-1.26 {

   182 #   count {SELECT w FROM t1 WHERE x=3 AND y BETWEEN 121 AND 196}

   183 # } {10 11 12 13 9}

   184 

   185 do_test where-1.27 {

   186   count {SELECT w FROM t1 WHERE x=3 AND y+1==122}

   187 } {10 10}

   188 

   189 do_test where-1.28 {

   190   count {SELECT w FROM t1 WHERE x+1=4 AND y+1==122}

   191 } {10 99}

   192 do_test where-1.29 {

   193   count {SELECT w FROM t1 WHERE y==121}

   194 } {10 99}

   195 

   196 

   197 do_test where-1.30 {

   198   count {SELECT w FROM t1 WHERE w>97}

   199 } {98 99 100 3}

   200 do_test where-1.31 {

   201   count {SELECT w FROM t1 WHERE w>=97}

   202 } {97 98 99 100 4}

   203 do_test where-1.33 {

   204   count {SELECT w FROM t1 WHERE w==97}

   205 } {97 2}

   206 do_test where-1.33.1  {

   207   count {SELECT w FROM t1 WHERE w<=97 AND w==97}

   208 } {97 2}

   209 do_test where-1.33.2  {

   210   count {SELECT w FROM t1 WHERE w<98 AND w==97}

   211 } {97 2}

   212 do_test where-1.33.3  {

   213   count {SELECT w FROM t1 WHERE w>=97 AND w==97}

   214 } {97 2}

   215 do_test where-1.33.4  {

   216   count {SELECT w FROM t1 WHERE w>96 AND w==97}

   217 } {97 2}

   218 do_test where-1.33.5  {

   219   count {SELECT w FROM t1 WHERE w==97 AND w==97}

   220 } {97 2}

   221 do_test where-1.34 {

   222   count {SELECT w FROM t1 WHERE w+1==98}

   223 } {97 99}

   224 do_test where-1.35 {

   225   count {SELECT w FROM t1 WHERE w<3}

   226 } {1 2 2}

   227 do_test where-1.36 {

   228   count {SELECT w FROM t1 WHERE w<=3}

   229 } {1 2 3 3}

   230 do_test where-1.37 {

   231   count {SELECT w FROM t1 WHERE w+1<=4 ORDER BY w}

   232 } {1 2 3 99}

   233 

   234 do_test where-1.38 {

   235   count {SELECT (w) FROM t1 WHERE (w)>(97)}

   236 } {98 99 100 3}

   237 do_test where-1.39 {

   238   count {SELECT (w) FROM t1 WHERE (w)>=(97)}

   239 } {97 98 99 100 4}

   240 do_test where-1.40 {

   241   count {SELECT (w) FROM t1 WHERE (w)==(97)}

   242 } {97 2}

   243 do_test where-1.41 {

   244   count {SELECT (w) FROM t1 WHERE ((w)+(1))==(98)}

   245 } {97 99}

   246 

   247 

   248 # Do the same kind of thing except use a join as the data source.

   249 #

   250 do_test where-2.1 {

   251   count {

   252     SELECT w, p FROM t2, t1

   253     WHERE x=q AND y=s AND r=8977

   254   }

   255 } {34 67 6}

   256 do_test where-2.2 {

   257   count {

   258     SELECT w, p FROM t2, t1

   259     WHERE x=q AND s=y AND r=8977

   260   }

   261 } {34 67 6}

   262 do_test where-2.3 {

   263   count {

   264     SELECT w, p FROM t2, t1

   265     WHERE x=q AND s=y AND r=8977 AND w>10

   266   }

   267 } {34 67 6}

   268 do_test where-2.4 {

   269   count {

   270     SELECT w, p FROM t2, t1

   271     WHERE p<80 AND x=q AND s=y AND r=8977 AND w>10

   272   }

   273 } {34 67 6}

   274 do_test where-2.5 {

   275   count {

   276     SELECT w, p FROM t2, t1

   277     WHERE p<80 AND x=q AND 8977=r AND s=y AND w>10

   278   }

   279 } {34 67 6}

   280 do_test where-2.6 {

   281   count {

   282     SELECT w, p FROM t2, t1

   283     WHERE x=q AND p=77 AND s=y AND w>5

   284   }

   285 } {24 77 6}

   286 do_test where-2.7 {

   287   count {

   288     SELECT w, p FROM t1, t2

   289     WHERE x=q AND p>77 AND s=y AND w=5

   290   }

   291 } {5 96 6}

   292 

   293 # Lets do a 3-way join.

   294 #

   295 do_test where-3.1 {

   296   count {

   297     SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C

   298     WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=11

   299   }

   300 } {11 90 11 8}

   301 do_test where-3.2 {

   302   count {

   303     SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C

   304     WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=12

   305   }

   306 } {12 89 12 8}

   307 do_test where-3.3 {

   308   count {

   309     SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C

   310     WHERE A.w=15 AND B.p=C.w AND B.r=10202-A.y

   311   }

   312 } {15 86 86 8}

   313 

   314 # Test to see that the special case of a constant WHERE clause is

   315 # handled.

   316 #

   317 do_test where-4.1 {

   318   count {

   319     SELECT * FROM t1 WHERE 0

   320   }

   321 } {0}

   322 do_test where-4.2 {

   323   count {

   324     SELECT * FROM t1 WHERE 1 LIMIT 1

   325   }

   326 } {1 0 4 0}

   327 do_test where-4.3 {

   328   execsql {

   329     SELECT 99 WHERE 0

   330   }

   331 } {}

   332 do_test where-4.4 {

   333   execsql {

   334     SELECT 99 WHERE 1

   335   }

   336 } {99}

   337 do_test where-4.5 {

   338   execsql {

   339     SELECT 99 WHERE 0.1

   340   }

   341 } {99}

   342 do_test where-4.6 {

   343   execsql {

   344     SELECT 99 WHERE 0.0

   345   }

   346 } {}

   347 do_test where-4.7 {

   348   execsql {

   349     SELECT count(*) FROM t1 WHERE t1.w

   350   }

   351 } {100}

   352 

   353 # Verify that IN operators in a WHERE clause are handled correctly.

   354 # Omit these tests if the build is not capable of sub-queries.

   355 #

   356 ifcapable subquery {

   357   do_test where-5.1 {

   358     count {

   359       SELECT * FROM t1 WHERE rowid IN (1,2,3,1234) order by 1;

   360     }

   361   } {1 0 4 2 1 9 3 1 16 4}

   362   do_test where-5.2 {

   363     count {

   364       SELECT * FROM t1 WHERE rowid+0 IN (1,2,3,1234) order by 1;

   365     }

   366   } {1 0 4 2 1 9 3 1 16 102}

   367   do_test where-5.3 {

   368     count {

   369       SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1;

   370     }

   371   } {1 0 4 2 1 9 3 1 16 14}

   372   do_test where-5.4 {

   373     count {

   374       SELECT * FROM t1 WHERE w+0 IN (-1,1,2,3) order by 1;

   375     }

   376   } {1 0 4 2 1 9 3 1 16 102}

   377   do_test where-5.5 {

   378     count {

   379       SELECT * FROM t1 WHERE rowid IN 

   380          (select rowid from t1 where rowid IN (-1,2,4))

   381       ORDER BY 1;

   382     }

   383   } {2 1 9 4 2 25 3}

   384   do_test where-5.6 {

   385     count {

   386       SELECT * FROM t1 WHERE rowid+0 IN 

   387          (select rowid from t1 where rowid IN (-1,2,4))

   388       ORDER BY 1;

   389     }

   390   } {2 1 9 4 2 25 103}

   391   do_test where-5.7 {

   392     count {

   393       SELECT * FROM t1 WHERE w IN 

   394          (select rowid from t1 where rowid IN (-1,2,4))

   395       ORDER BY 1;

   396     }

   397   } {2 1 9 4 2 25 9}

   398   do_test where-5.8 {

   399     count {

   400       SELECT * FROM t1 WHERE w+0 IN 

   401          (select rowid from t1 where rowid IN (-1,2,4))

   402       ORDER BY 1;

   403     }

   404   } {2 1 9 4 2 25 103}

   405   do_test where-5.9 {

   406     count {

   407       SELECT * FROM t1 WHERE x IN (1,7) ORDER BY 1;

   408     }

   409   } {2 1 9 3 1 16 7}

   410   do_test where-5.10 {

   411     count {

   412       SELECT * FROM t1 WHERE x+0 IN (1,7) ORDER BY 1;

   413     }

   414   } {2 1 9 3 1 16 199}

   415   do_test where-5.11 {

   416     count {

   417       SELECT * FROM t1 WHERE y IN (6400,8100) ORDER BY 1;

   418     }

   419   } {79 6 6400 89 6 8100 199}

   420   do_test where-5.12 {

   421     count {

   422       SELECT * FROM t1 WHERE x=6 AND y IN (6400,8100) ORDER BY 1;

   423     }

   424   } {79 6 6400 89 6 8100 7}

   425   do_test where-5.13 {

   426     count {

   427       SELECT * FROM t1 WHERE x IN (1,7) AND y NOT IN (6400,8100) ORDER BY 1;

   428     }

   429   } {2 1 9 3 1 16 7}

   430   do_test where-5.14 {

   431     count {

   432       SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,10) ORDER BY 1;

   433     }

   434   } {2 1 9 8}

   435   do_test where-5.15 {

   436     count {

   437       SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,16) ORDER BY 1;

   438     }

   439   } {2 1 9 3 1 16 11}

   440 }

   441 

   442 # This procedure executes the SQL.  Then it checks to see if the OP_Sort

   443 # opcode was executed.  If an OP_Sort did occur, then "sort" is appended

   444 # to the result.  If no OP_Sort happened, then "nosort" is appended.

   445 #

   446 # This procedure is used to check to make sure sorting is or is not

   447 # occurring as expected.

   448 #

   449 proc cksort {sql} {

   450   set ::sqlite_sort_count 0

   451   set data [execsql $sql]

   452   if {$::sqlite_sort_count} {set x sort} {set x nosort}

   453   lappend data $x

   454   return $data

   455 }

   456 # Check out the logic that attempts to implement the ORDER BY clause

   457 # using an index rather than by sorting.

   458 #

   459 do_test where-6.1 {

   460   execsql {

   461     CREATE TABLE t3(a,b,c);

   462     CREATE INDEX t3a ON t3(a);

   463     CREATE INDEX t3bc ON t3(b,c);

   464     CREATE INDEX t3acb ON t3(a,c,b);

   465     INSERT INTO t3 SELECT w, 101-w, y FROM t1;

   466     SELECT count(*), sum(a), sum(b), sum(c) FROM t3;

   467   }

   468 } {100 5050 5050 348550}

   469 do_test where-6.2 {

   470   cksort {

   471     SELECT * FROM t3 ORDER BY a LIMIT 3

   472   }

   473 } {1 100 4 2 99 9 3 98 16 nosort}

   474 do_test where-6.3 {

   475   cksort {

   476     SELECT * FROM t3 ORDER BY a+1 LIMIT 3

   477   }

   478 } {1 100 4 2 99 9 3 98 16 sort}

   479 do_test where-6.4 {

   480   cksort {

   481     SELECT * FROM t3 WHERE a<10 ORDER BY a LIMIT 3

   482   }

   483 } {1 100 4 2 99 9 3 98 16 nosort}

   484 do_test where-6.5 {

   485   cksort {

   486     SELECT * FROM t3 WHERE a>0 AND a<10 ORDER BY a LIMIT 3

   487   }

   488 } {1 100 4 2 99 9 3 98 16 nosort}

   489 do_test where-6.6 {

   490   cksort {

   491     SELECT * FROM t3 WHERE a>0 ORDER BY a LIMIT 3

   492   }

   493 } {1 100 4 2 99 9 3 98 16 nosort}

   494 do_test where-6.7 {

   495   cksort {

   496     SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 3

   497   }

   498 } {1 100 4 2 99 9 3 98 16 nosort}

   499 ifcapable subquery {

   500   do_test where-6.8 {

   501     cksort {

   502       SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a LIMIT 3

   503     }

   504   } {1 100 4 2 99 9 3 98 16 sort}

   505 }

   506 do_test where-6.9.1 {

   507   cksort {

   508     SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3

   509   }

   510 } {1 100 4 nosort}

   511 do_test where-6.9.1.1 {

   512   cksort {

   513     SELECT * FROM t3 WHERE a>=1 AND a=1 AND c>0 ORDER BY a LIMIT 3

   514   }

   515 } {1 100 4 nosort}

   516 do_test where-6.9.1.2 {

   517   cksort {

   518     SELECT * FROM t3 WHERE a<2 AND a=1 AND c>0 ORDER BY a LIMIT 3

   519   }

   520 } {1 100 4 nosort}

   521 do_test where-6.9.2 {

   522   cksort {

   523     SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c LIMIT 3

   524   }

   525 } {1 100 4 nosort}

   526 do_test where-6.9.3 {

   527   cksort {

   528     SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c LIMIT 3

   529   }

   530 } {1 100 4 nosort}

   531 do_test where-6.9.4 {

   532   cksort {

   533     SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC LIMIT 3

   534   }

   535 } {1 100 4 nosort}

   536 do_test where-6.9.5 {

   537   cksort {

   538     SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC, c DESC LIMIT 3

   539   }

   540 } {1 100 4 nosort}

   541 do_test where-6.9.6 {

   542   cksort {

   543     SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c DESC LIMIT 3

   544   }

   545 } {1 100 4 nosort}

   546 do_test where-6.9.7 {

   547   cksort {

   548     SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c,a LIMIT 3

   549   }

   550 } {1 100 4 sort}

   551 do_test where-6.9.8 {

   552   cksort {

   553     SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC, c ASC LIMIT 3

   554   }

   555 } {1 100 4 nosort}

   556 do_test where-6.9.9 {

   557   cksort {

   558     SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a ASC, c DESC LIMIT 3

   559   }

   560 } {1 100 4 nosort}

   561 do_test where-6.10 {

   562   cksort {

   563     SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3

   564   }

   565 } {1 100 4 nosort}

   566 do_test where-6.11 {

   567   cksort {

   568     SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c LIMIT 3

   569   }

   570 } {1 100 4 nosort}

   571 do_test where-6.12 {

   572   cksort {

   573     SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c,b LIMIT 3

   574   }

   575 } {1 100 4 nosort}

   576 do_test where-6.13 {

   577   cksort {

   578     SELECT * FROM t3 WHERE a>0 ORDER BY a DESC LIMIT 3

   579   }

   580 } {100 1 10201 99 2 10000 98 3 9801 nosort}

   581 do_test where-6.13.1 {

   582   cksort {

   583     SELECT * FROM t3 WHERE a>0 ORDER BY -a LIMIT 3

   584   }

   585 } {100 1 10201 99 2 10000 98 3 9801 sort}

   586 do_test where-6.14 {

   587   cksort {

   588     SELECT * FROM t3 ORDER BY b LIMIT 3

   589   }

   590 } {100 1 10201 99 2 10000 98 3 9801 nosort}

   591 do_test where-6.15 {

   592   cksort {

   593     SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t3.a LIMIT 3

   594   }

   595 } {1 0 2 1 3 1 nosort}

   596 do_test where-6.16 {

   597   cksort {

   598     SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t1.x, t3.a LIMIT 3

   599   }

   600 } {1 0 2 1 3 1 sort}

   601 do_test where-6.19 {

   602   cksort {

   603     SELECT y FROM t1 ORDER BY w LIMIT 3;

   604   }

   605 } {4 9 16 nosort}

   606 do_test where-6.20 {

   607   cksort {

   608     SELECT y FROM t1 ORDER BY rowid LIMIT 3;

   609   }

   610 } {4 9 16 nosort}

   611 do_test where-6.21 {

   612   cksort {

   613     SELECT y FROM t1 ORDER BY rowid, y LIMIT 3;

   614   }

   615 } {4 9 16 nosort}

   616 do_test where-6.22 {

   617   cksort {

   618     SELECT y FROM t1 ORDER BY rowid, y DESC LIMIT 3;

   619   }

   620 } {4 9 16 nosort}

   621 do_test where-6.23 {

   622   cksort {

   623     SELECT y FROM t1 WHERE y>4 ORDER BY rowid, w, x LIMIT 3;

   624   }

   625 } {9 16 25 nosort}

   626 do_test where-6.24 {

   627   cksort {

   628     SELECT y FROM t1 WHERE y>=9 ORDER BY rowid, x DESC, w LIMIT 3;

   629   }

   630 } {9 16 25 nosort}

   631 do_test where-6.25 {

   632   cksort {

   633     SELECT y FROM t1 WHERE y>4 AND y<25 ORDER BY rowid;

   634   }

   635 } {9 16 nosort}

   636 do_test where-6.26 {

   637   cksort {

   638     SELECT y FROM t1 WHERE y>=4 AND y<=25 ORDER BY oid;

   639   }

   640 } {4 9 16 25 nosort}

   641 do_test where-6.27 {

   642   cksort {

   643     SELECT y FROM t1 WHERE y<=25 ORDER BY _rowid_, w+y;

   644   }

   645 } {4 9 16 25 nosort}

   646 

   647 

   648 # Tests for reverse-order sorting.

   649 #

   650 do_test where-7.1 {

   651   cksort {

   652     SELECT w FROM t1 WHERE x=3 ORDER BY y;

   653   }

   654 } {8 9 10 11 12 13 14 15 nosort}

   655 do_test where-7.2 {

   656   cksort {

   657     SELECT w FROM t1 WHERE x=3 ORDER BY y DESC;

   658   }

   659 } {15 14 13 12 11 10 9 8 nosort}

   660 do_test where-7.3 {

   661   cksort {

   662     SELECT w FROM t1 WHERE x=3 AND y>100 ORDER BY y LIMIT 3;

   663   }

   664 } {10 11 12 nosort}

   665 do_test where-7.4 {

   666   cksort {

   667     SELECT w FROM t1 WHERE x=3 AND y>100 ORDER BY y DESC LIMIT 3;

   668   }

   669 } {15 14 13 nosort}

   670 do_test where-7.5 {

   671   cksort {

   672     SELECT w FROM t1 WHERE x=3 AND y>121 ORDER BY y DESC;

   673   }

   674 } {15 14 13 12 11 nosort}

   675 do_test where-7.6 {

   676   cksort {

   677     SELECT w FROM t1 WHERE x=3 AND y>=121 ORDER BY y DESC;

   678   }

   679 } {15 14 13 12 11 10 nosort}

   680 do_test where-7.7 {

   681   cksort {

   682     SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<196 ORDER BY y DESC;

   683   }

   684 } {12 11 10 nosort}

   685 do_test where-7.8 {

   686   cksort {

   687     SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196 ORDER BY y DESC;

   688   }

   689 } {13 12 11 10 nosort}

   690 do_test where-7.9 {

   691   cksort {

   692     SELECT w FROM t1 WHERE x=3 AND y>121 AND y<=196 ORDER BY y DESC;

   693   }

   694 } {13 12 11 nosort}

   695 do_test where-7.10 {

   696   cksort {

   697     SELECT w FROM t1 WHERE x=3 AND y>100 AND y<196 ORDER BY y DESC;

   698   }

   699 } {12 11 10 nosort}

   700 do_test where-7.11 {

   701   cksort {

   702     SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<196 ORDER BY y;

   703   }

   704 } {10 11 12 nosort}

   705 do_test where-7.12 {

   706   cksort {

   707     SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196 ORDER BY y;

   708   }

   709 } {10 11 12 13 nosort}

   710 do_test where-7.13 {

   711   cksort {

   712     SELECT w FROM t1 WHERE x=3 AND y>121 AND y<=196 ORDER BY y;

   713   }

   714 } {11 12 13 nosort}

   715 do_test where-7.14 {

   716   cksort {

   717     SELECT w FROM t1 WHERE x=3 AND y>100 AND y<196 ORDER BY y;

   718   }

   719 } {10 11 12 nosort}

   720 do_test where-7.15 {

   721   cksort {

   722     SELECT w FROM t1 WHERE x=3 AND y<81 ORDER BY y;

   723   }

   724 } {nosort}

   725 do_test where-7.16 {

   726   cksort {

   727     SELECT w FROM t1 WHERE x=3 AND y<=81 ORDER BY y;

   728   }

   729 } {8 nosort}

   730 do_test where-7.17 {

   731   cksort {

   732     SELECT w FROM t1 WHERE x=3 AND y>256 ORDER BY y;

   733   }

   734 } {nosort}

   735 do_test where-7.18 {

   736   cksort {

   737     SELECT w FROM t1 WHERE x=3 AND y>=256 ORDER BY y;

   738   }

   739 } {15 nosort}

   740 do_test where-7.19 {

   741   cksort {

   742     SELECT w FROM t1 WHERE x=3 AND y<81 ORDER BY y DESC;

   743   }

   744 } {nosort}

   745 do_test where-7.20 {

   746   cksort {

   747     SELECT w FROM t1 WHERE x=3 AND y<=81 ORDER BY y DESC;

   748   }

   749 } {8 nosort}

   750 do_test where-7.21 {

   751   cksort {

   752     SELECT w FROM t1 WHERE x=3 AND y>256 ORDER BY y DESC;

   753   }

   754 } {nosort}

   755 do_test where-7.22 {

   756   cksort {

   757     SELECT w FROM t1 WHERE x=3 AND y>=256 ORDER BY y DESC;

   758   }

   759 } {15 nosort}

   760 do_test where-7.23 {

   761   cksort {

   762     SELECT w FROM t1 WHERE x=0 AND y<4 ORDER BY y;

   763   }

   764 } {nosort}

   765 do_test where-7.24 {

   766   cksort {

   767     SELECT w FROM t1 WHERE x=0 AND y<=4 ORDER BY y;

   768   }

   769 } {1 nosort}

   770 do_test where-7.25 {

   771   cksort {

   772     SELECT w FROM t1 WHERE x=6 AND y>10201 ORDER BY y;

   773   }

   774 } {nosort}

   775 do_test where-7.26 {

   776   cksort {

   777     SELECT w FROM t1 WHERE x=6 AND y>=10201 ORDER BY y;

   778   }

   779 } {100 nosort}

   780 do_test where-7.27 {

   781   cksort {

   782     SELECT w FROM t1 WHERE x=0 AND y<4 ORDER BY y DESC;

   783   }

   784 } {nosort}

   785 do_test where-7.28 {

   786   cksort {

   787     SELECT w FROM t1 WHERE x=0 AND y<=4 ORDER BY y DESC;

   788   }

   789 } {1 nosort}

   790 do_test where-7.29 {

   791   cksort {

   792     SELECT w FROM t1 WHERE x=6 AND y>10201 ORDER BY y DESC;

   793   }

   794 } {nosort}

   795 do_test where-7.30 {

   796   cksort {

   797     SELECT w FROM t1 WHERE x=6 AND y>=10201 ORDER BY y DESC;

   798   }

   799 } {100 nosort}

   800 do_test where-7.31 {

   801   cksort {

   802     SELECT y FROM t1 ORDER BY rowid DESC LIMIT 3

   803   }

   804 } {10201 10000 9801 nosort}

   805 do_test where-7.32 {

   806   cksort {

   807     SELECT y FROM t1 WHERE y<25 ORDER BY rowid DESC

   808   }

   809 } {16 9 4 nosort}

   810 do_test where-7.33 {

   811   cksort {

   812     SELECT y FROM t1 WHERE y<=25 ORDER BY rowid DESC

   813   }

   814 } {25 16 9 4 nosort}

   815 do_test where-7.34 {

   816   cksort {

   817     SELECT y FROM t1 WHERE y<25 AND y>4 ORDER BY rowid DESC, y DESC

   818   }

   819 } {16 9 nosort}

   820 do_test where-7.35 {

   821   cksort {

   822     SELECT y FROM t1 WHERE y<25 AND y>=4 ORDER BY rowid DESC

   823   }

   824 } {16 9 4 nosort}

   825 

   826 do_test where-8.1 {

   827   execsql {

   828     CREATE TABLE t4 AS SELECT * FROM t1;

   829     CREATE INDEX i4xy ON t4(x,y);

   830   }

   831   cksort {

   832     SELECT w FROM t4 WHERE x=4 and y<1000 ORDER BY y DESC limit 3;

   833   }

   834 } {30 29 28 nosort}

   835 do_test where-8.2 {

   836   execsql {

   837     DELETE FROM t4;

   838   }

   839   cksort {

   840     SELECT w FROM t4 WHERE x=4 and y<1000 ORDER BY y DESC limit 3;

   841   }

   842 } {nosort}

   843 

   844 # Make sure searches with an index work with an empty table.

   845 #

   846 do_test where-9.1 {

   847   execsql {

   848     CREATE TABLE t5(x PRIMARY KEY);

   849     SELECT * FROM t5 WHERE x<10;

   850   }

   851 } {}

   852 do_test where-9.2 {

   853   execsql {

   854     SELECT * FROM t5 WHERE x<10 ORDER BY x DESC;

   855   }

   856 } {}

   857 do_test where-9.3 {

   858   execsql {

   859     SELECT * FROM t5 WHERE x=10;

   860   }

   861 } {}

   862 

   863 do_test where-10.1 {

   864   execsql {

   865     SELECT 1 WHERE abs(random())<0

   866   }

   867 } {}

   868 do_test where-10.2 {

   869   proc tclvar_func {vname} {return [set ::$vname]}

   870   db function tclvar tclvar_func

   871   set ::v1 0

   872   execsql {

   873     SELECT count(*) FROM t1 WHERE tclvar('v1');

   874   }

   875 } {0}

   876 do_test where-10.3 {

   877   set ::v1 1

   878   execsql {

   879     SELECT count(*) FROM t1 WHERE tclvar('v1');

   880   }

   881 } {100}

   882 do_test where-10.4 {

   883   set ::v1 1

   884   proc tclvar_func {vname} {

   885     upvar #0 $vname v

   886     set v [expr {!$v}]

   887     return $v

   888   }

   889   execsql {

   890     SELECT count(*) FROM t1 WHERE tclvar('v1');

   891   }

   892 } {50}

   893 

   894 # Ticket #1376.  The query below was causing a segfault.

   895 # The problem was the age-old error of calling realloc() on an

   896 # array while there are still pointers to individual elements of

   897 # that array.

   898 #

   899 do_test where-11.1 {

   900   execsql {

   901    CREATE TABLE t99(Dte INT, X INT);

   902    DELETE FROM t99 WHERE (Dte = 2451337) OR (Dte = 2451339) OR

   903      (Dte BETWEEN 2451345 AND 2451347) OR (Dte = 2451351) OR 

   904      (Dte BETWEEN 2451355 AND 2451356) OR (Dte = 2451358) OR

   905      (Dte = 2451362) OR (Dte = 2451365) OR (Dte = 2451367) OR

   906      (Dte BETWEEN 2451372 AND 2451376) OR (Dte BETWEEN 2451382 AND 2451384) OR

   907      (Dte = 2451387) OR (Dte BETWEEN 2451389 AND 2451391) OR 

   908      (Dte BETWEEN 2451393 AND 2451395) OR (Dte = 2451400) OR 

   909      (Dte = 2451402) OR (Dte = 2451404) OR (Dte BETWEEN 2451416 AND 2451418) OR 

   910      (Dte = 2451422) OR (Dte = 2451426) OR (Dte BETWEEN 2451445 AND 2451446) OR

   911      (Dte = 2451456) OR (Dte = 2451458) OR (Dte BETWEEN 2451465 AND 2451467) OR

   912      (Dte BETWEEN 2451469 AND 2451471) OR (Dte = 2451474) OR

   913      (Dte BETWEEN 2451477 AND 2451501) OR (Dte BETWEEN 2451503 AND 2451509) OR

   914      (Dte BETWEEN 2451511 AND 2451514) OR (Dte BETWEEN 2451518 AND 2451521) OR

   915      (Dte BETWEEN 2451523 AND 2451531) OR (Dte BETWEEN 2451533 AND 2451537) OR

   916      (Dte BETWEEN 2451539 AND 2451544) OR (Dte BETWEEN 2451546 AND 2451551) OR

   917      (Dte BETWEEN 2451553 AND 2451555) OR (Dte = 2451557) OR

   918      (Dte BETWEEN 2451559 AND 2451561) OR (Dte = 2451563) OR

   919      (Dte BETWEEN 2451565 AND 2451566) OR (Dte BETWEEN 2451569 AND 2451571) OR 

   920      (Dte = 2451573) OR (Dte = 2451575) OR (Dte = 2451577) OR (Dte = 2451581) OR

   921      (Dte BETWEEN 2451583 AND 2451586) OR (Dte BETWEEN 2451588 AND 2451592) OR 

   922      (Dte BETWEEN 2451596 AND 2451598) OR (Dte = 2451600) OR

   923      (Dte BETWEEN 2451602 AND 2451603) OR (Dte = 2451606) OR (Dte = 2451611);

   924   }

   925 } {}

   926 

   927 # Ticket #2116:  Make sure sorting by index works well with nn INTEGER PRIMARY

   928 # KEY.

   929 #

   930 do_test where-12.1 {

   931   execsql {

   932     CREATE TABLE t6(a INTEGER PRIMARY KEY, b TEXT);

   933     INSERT INTO t6 VALUES(1,'one');

   934     INSERT INTO t6 VALUES(4,'four');

   935     CREATE INDEX t6i1 ON t6(b);

   936   }

   937   cksort {

   938     SELECT * FROM t6 ORDER BY b;

   939   }

   940 } {4 four 1 one nosort}

   941 do_test where-12.2 {

   942   cksort {

   943     SELECT * FROM t6 ORDER BY b, a;

   944   }

   945 } {4 four 1 one nosort}

   946 do_test where-12.3 {

   947   cksort {

   948     SELECT * FROM t6 ORDER BY a;

   949   }

   950 } {1 one 4 four nosort}

   951 do_test where-12.4 {

   952   cksort {

   953     SELECT * FROM t6 ORDER BY a, b;

   954   }

   955 } {1 one 4 four nosort}

   956 do_test where-12.5 {

   957   cksort {

   958     SELECT * FROM t6 ORDER BY b DESC;

   959   }

   960 } {1 one 4 four nosort}

   961 do_test where-12.6 {

   962   cksort {

   963     SELECT * FROM t6 ORDER BY b DESC, a DESC;

   964   }

   965 } {1 one 4 four nosort}

   966 do_test where-12.7 {

   967   cksort {

   968     SELECT * FROM t6 ORDER BY b DESC, a ASC;

   969   }

   970 } {1 one 4 four sort}

   971 do_test where-12.8 {

   972   cksort {

   973     SELECT * FROM t6 ORDER BY b ASC, a DESC;

   974   }

   975 } {4 four 1 one sort}

   976 do_test where-12.9 {

   977   cksort {

   978     SELECT * FROM t6 ORDER BY a DESC;

   979   }

   980 } {4 four 1 one nosort}

   981 do_test where-12.10 {

   982   cksort {

   983     SELECT * FROM t6 ORDER BY a DESC, b DESC;

   984   }

   985 } {4 four 1 one nosort}

   986 do_test where-12.11 {

   987   cksort {

   988     SELECT * FROM t6 ORDER BY a DESC, b ASC;

   989   }

   990 } {4 four 1 one nosort}

   991 do_test where-12.12 {

   992   cksort {

   993     SELECT * FROM t6 ORDER BY a ASC, b DESC;

   994   }

   995 } {1 one 4 four nosort}

   996 do_test where-13.1 {

   997   execsql {

   998     CREATE TABLE t7(a INTEGER PRIMARY KEY, b TEXT);

   999     INSERT INTO t7 VALUES(1,'one');

  1000     INSERT INTO t7 VALUES(4,'four');

  1001     CREATE INDEX t7i1 ON t7(b);

  1002   }

  1003   cksort {

  1004     SELECT * FROM t7 ORDER BY b;

  1005   }

  1006 } {4 four 1 one nosort}

  1007 do_test where-13.2 {

  1008   cksort {

  1009     SELECT * FROM t7 ORDER BY b, a;

  1010   }

  1011 } {4 four 1 one nosort}

  1012 do_test where-13.3 {

  1013   cksort {

  1014     SELECT * FROM t7 ORDER BY a;

  1015   }

  1016 } {1 one 4 four nosort}

  1017 do_test where-13.4 {

  1018   cksort {

  1019     SELECT * FROM t7 ORDER BY a, b;

  1020   }

  1021 } {1 one 4 four nosort}

  1022 do_test where-13.5 {

  1023   cksort {

  1024     SELECT * FROM t7 ORDER BY b DESC;

  1025   }

  1026 } {1 one 4 four nosort}

  1027 do_test where-13.6 {

  1028   cksort {

  1029     SELECT * FROM t7 ORDER BY b DESC, a DESC;

  1030   }

  1031 } {1 one 4 four nosort}

  1032 do_test where-13.7 {

  1033   cksort {

  1034     SELECT * FROM t7 ORDER BY b DESC, a ASC;

  1035   }

  1036 } {1 one 4 four sort}

  1037 do_test where-13.8 {

  1038   cksort {

  1039     SELECT * FROM t7 ORDER BY b ASC, a DESC;

  1040   }

  1041 } {4 four 1 one sort}

  1042 do_test where-13.9 {

  1043   cksort {

  1044     SELECT * FROM t7 ORDER BY a DESC;

  1045   }

  1046 } {4 four 1 one nosort}

  1047 do_test where-13.10 {

  1048   cksort {

  1049     SELECT * FROM t7 ORDER BY a DESC, b DESC;

  1050   }

  1051 } {4 four 1 one nosort}

  1052 do_test where-13.11 {

  1053   cksort {

  1054     SELECT * FROM t7 ORDER BY a DESC, b ASC;

  1055   }

  1056 } {4 four 1 one nosort}

  1057 do_test where-13.12 {

  1058   cksort {

  1059     SELECT * FROM t7 ORDER BY a ASC, b DESC;

  1060   }

  1061 } {1 one 4 four nosort}

  1062 

  1063 # Ticket #2211.

  1064 #

  1065 # When optimizing out ORDER BY clauses, make sure that trailing terms

  1066 # of the ORDER BY clause do not reference other tables in a join.

  1067 #

  1068 do_test where-14.1 {

  1069   execsql {

  1070     CREATE TABLE t8(a INTEGER PRIMARY KEY, b TEXT UNIQUE);

  1071     INSERT INTO t8 VALUES(1,'one');

  1072     INSERT INTO t8 VALUES(4,'four');

  1073   }

  1074   cksort {

  1075     SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b

  1076   } 

  1077 } {1/4 1/1 4/4 4/1 sort}

  1078 do_test where-14.2 {

  1079   cksort {

  1080     SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b DESC

  1081   } 

  1082 } {1/1 1/4 4/1 4/4 sort}

  1083 do_test where-14.3 {

  1084   cksort {

  1085     SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b

  1086   } 

  1087 } {1/1 1/4 4/1 4/4 nosort}

  1088 do_test where-14.4 {

  1089   cksort {

  1090     SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b DESC

  1091   } 

  1092 } {1/1 1/4 4/1 4/4 nosort}

  1093 do_test where-14.5 {

  1094   cksort {

  1095     SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b

  1096   } 

  1097 } {4/1 4/4 1/1 1/4 nosort}

  1098 do_test where-14.6 {

  1099   cksort {

  1100     SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b DESC

  1101   } 

  1102 } {4/1 4/4 1/1 1/4 nosort}

  1103 do_test where-14.7 {

  1104   cksort {

  1105     SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b

  1106   } 

  1107 } {4/1 4/4 1/1 1/4 sort}

  1108 do_test where-14.7.1 {

  1109   cksort {

  1110     SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a, y.a||y.b

  1111   } 

  1112 } {4/1 4/4 1/1 1/4 sort}

  1113 do_test where-14.7.2 {

  1114   cksort {

  1115     SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a, x.a||x.b

  1116   } 

  1117 } {4/1 4/4 1/1 1/4 nosort}

  1118 do_test where-14.8 {

  1119   cksort {

  1120     SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b DESC

  1121   } 

  1122 } {4/4 4/1 1/4 1/1 sort}

  1123 do_test where-14.9 {

  1124   cksort {

  1125     SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||y.b

  1126   } 

  1127 } {4/4 4/1 1/4 1/1 sort}

  1128 do_test where-14.10 {

  1129   cksort {

  1130     SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||y.b DESC

  1131   } 

  1132 } {4/1 4/4 1/1 1/4 sort}

  1133 do_test where-14.11 {

  1134   cksort {

  1135     SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||x.b