test/where4.test

   1 # 2006 October 27
   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 clauses.
  13 # This file was created when support for optimizing IS NULL phrases
  14 # was added.  And so the principle purpose of this file is to test
  15 # that IS NULL phrases are correctly optimized.  But you can never
  16 # have too many tests, so some other tests are thrown in as well.
  17 #
  18 # $Id: where4.test,v 1.6 2007/12/10 05:03:48 danielk1977 Exp $
  19
  20 set testdir [file dirname $argv0]
  21 source $testdir/tester.tcl
  22 set testprefix where4
  23
  24 ifcapable !tclvar||!bloblit {
  25   finish_test
  26   return
  27 }
  28
  29 # Build some test data
  30 #
  31 do_test where4-1.0 {
  32   execsql {
  33     CREATE TABLE t1(w, x, y);
  34     CREATE INDEX i1wxy ON t1(w,x,y);
  35     INSERT INTO t1 VALUES(1,2,3);
  36     INSERT INTO t1 VALUES(1,NULL,3);
  37     INSERT INTO t1 VALUES('a','b','c');
  38     INSERT INTO t1 VALUES('a',NULL,'c');
  39     INSERT INTO t1 VALUES(X'78',x'79',x'7a');
  40     INSERT INTO t1 VALUES(X'78',NULL,X'7A');
  41     INSERT INTO t1 VALUES(NULL,NULL,NULL);
  42     SELECT count(*) FROM t1;
  43   }
  44 } {7}
  45
  46 # Do an SQL statement.  Append the search count to the end of the result.
  47 #
  48 proc count sql {
  49   set ::sqlite_search_count 0
  50   return [concat [execsql $sql] $::sqlite_search_count]
  51 }
  52
  53 # Verify that queries use an index.  We are using the special variable
  54 # "sqlite_search_count" which tallys the number of executions of MoveTo
  55 # and Next operators in the VDBE.  By verifing that the search count is
  56 # small we can be assured that indices are being used properly.
  57 #
  58 do_test where4-1.1 {
  59   count {SELECT rowid FROM t1 WHERE w IS NULL}
  60 } {7 2}
  61 do_test where4-1.1b {
  62   unset -nocomplain null
  63   count {SELECT rowid FROM t1 WHERE w IS $null}
  64 } {7 2}
  65 do_test where4-1.2 {
  66   count {SELECT rowid FROM t1 WHERE +w IS NULL}
  67 } {7 6}
  68 do_test where4-1.3 {
  69   count {SELECT rowid FROM t1 WHERE w=1 AND x IS NULL}
  70 } {2 2}
  71 do_test where4-1.4 {
  72   count {SELECT rowid FROM t1 WHERE w=1 AND +x IS NULL}
  73 } {2 3}
  74 do_test where4-1.5 {
  75   count {SELECT rowid FROM t1 WHERE w=1 AND x>0}
  76 } {1 2}
  77 do_test where4-1.6 {
  78   count {SELECT rowid FROM t1 WHERE w=1 AND x<9}
  79 } {1 2}
  80 do_test where4-1.7 {
  81   count {SELECT rowid FROM t1 WHERE w=1 AND x IS NULL AND y=3}
  82 } {2 2}
  83 do_test where4-1.8 {
  84   count {SELECT rowid FROM t1 WHERE w=1 AND x IS NULL AND y>2}
  85 } {2 2}
  86 do_test where4-1.9 {
  87   count {SELECT rowid FROM t1 WHERE w='a' AND x IS NULL AND y='c'}
  88 } {4 2}
  89 do_test where4-1.10 {
  90   count {SELECT rowid FROM t1 WHERE w=x'78' AND x IS NULL}
  91 } {6 2}
  92 do_test where4-1.11 {
  93   count {SELECT rowid FROM t1 WHERE w=x'78' AND x IS NULL AND y=123}
  94 } {0}
  95 do_test where4-1.12 {
  96   count {SELECT rowid FROM t1 WHERE w=x'78' AND x IS NULL AND y=x'7A'}
  97 } {6 2}
  98 do_test where4-1.13 {
  99   count {SELECT rowid FROM t1 WHERE w IS NULL AND x IS NULL}
 100 } {7 2}
 101 do_test where4-1.14 {
 102   count {SELECT rowid FROM t1 WHERE w IS NULL AND x IS NULL AND y IS NULL}
 103 } {7 2}
 104 do_test where4-1.15 {
 105   count {SELECT rowid FROM t1 WHERE w IS NULL AND x IS NULL AND y<0}
 106 } {1}
 107 do_test where4-1.16 {
 108   count {SELECT rowid FROM t1 WHERE w IS NULL AND x IS NULL AND y>=0}
 109 } {1}
 110
 111 do_test where4-2.1 {
 112   execsql {SELECT rowid FROM t1 ORDER BY w, x, y}
 113 } {7 2 1 4 3 6 5}
 114 do_test where4-2.2 {
 115   execsql {SELECT rowid FROM t1 ORDER BY w DESC, x, y}
 116 } {6 5 4 3 2 1 7}
 117 do_test where4-2.3 {
 118   execsql {SELECT rowid FROM t1 ORDER BY w, x DESC, y}
 119 } {7 1 2 3 4 5 6}
 120
 121
 122 # Ticket #2177
 123 #
 124 # Suppose you have a left join where the right table of the left
 125 # join (the one that can be NULL) has an index on two columns.
 126 # The first indexed column is used in the ON clause of the join.
 127 # The second indexed column is used in the WHERE clause with an IS NULL
 128 # constraint.  It is not allowed to use the IS NULL optimization to
 129 # optimize the query because the second column might be NULL because
 130 # the right table did not match - something the index does not know
 131 # about.
 132 #
 133 do_test where4-3.1 {
 134   execsql {
 135     CREATE TABLE t2(a);
 136     INSERT INTO t2 VALUES(1);
 137     INSERT INTO t2 VALUES(2);
 138     INSERT INTO t2 VALUES(3);
 139     CREATE TABLE t3(x,y,UNIQUE("x",'y' ASC)); -- Goofy syntax allowed
 140     INSERT INTO t3 VALUES(1,11);
 141     INSERT INTO t3 VALUES(2,NULL);
 142
 143     SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE +y IS NULL;
 144   }
 145 } {2 2 {} 3 {} {}}
 146 do_test where4-3.2 {
 147   execsql {
 148     SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE y IS NULL;
 149   }
 150 } {2 2 {} 3 {} {}}
 151 do_test where4-3.3 {
 152   execsql {
 153     SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE NULL is y;
 154   }
 155 } {2 2 {} 3 {} {}}
 156 do_test where4-3.4 {
 157   unset -nocomplain null
 158   execsql {
 159     SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE y IS $null;
 160   }
 161 } {2 2 {} 3 {} {}}
 162
 163 # Ticket #2189.  Probably the same bug as #2177.
 164 #
 165 do_test where4-4.1 {
 166   execsql {
 167     CREATE TABLE test(col1 TEXT PRIMARY KEY);
 168     INSERT INTO test(col1) values('a');
 169     INSERT INTO test(col1) values('b');
 170     INSERT INTO test(col1) values('c');
 171     CREATE TABLE test2(col1 TEXT PRIMARY KEY);
 172     INSERT INTO test2(col1) values('a');
 173     INSERT INTO test2(col1) values('b');
 174     INSERT INTO test2(col1) values('c');
 175     SELECT * FROM test t1 LEFT OUTER JOIN test2 t2 ON t1.col1 = t2.col1
 176       WHERE +t2.col1 IS NULL;
 177   }
 178 } {}
 179 do_test where4-4.2 {
 180   execsql {
 181     SELECT * FROM test t1 LEFT OUTER JOIN test2 t2 ON t1.col1 = t2.col1
 182       WHERE t2.col1 IS NULL;
 183   }
 184 } {}
 185 do_test where4-4.3 {
 186   execsql {
 187     SELECT * FROM test t1 LEFT OUTER JOIN test2 t2 ON t1.col1 = t2.col1
 188       WHERE +t1.col1 IS NULL;
 189   }
 190 } {}
 191 do_test where4-4.4 {
 192   execsql {
 193     SELECT * FROM test t1 LEFT OUTER JOIN test2 t2 ON t1.col1 = t2.col1
 194       WHERE t1.col1 IS NULL;
 195   }
 196 } {}
 197
 198 # Ticket #2273.  Problems with IN operators and NULLs.
 199 #
 200 ifcapable subquery {
 201 do_test where4-5.1 {
 202   execsql {
 203     -- Allow the 'x' syntax for backwards compatibility
 204     CREATE TABLE t4(x,y,z,PRIMARY KEY('x' ASC, "y" ASC));
 205   }
 206   execsql {
 207     SELECT *
 208       FROM t2 LEFT JOIN t4 b1
 209               LEFT JOIN t4 b2 ON b2.x=b1.x AND b2.y IN (b1.y);
 210   }
 211 } {1 {} {} {} {} {} {} 2 {} {} {} {} {} {} 3 {} {} {} {} {} {}}
 212 do_test where4-5.2 {
 213   execsql {
 214     INSERT INTO t4 VALUES(1,1,11);
 215     INSERT INTO t4 VALUES(1,2,12);
 216     INSERT INTO t4 VALUES(1,3,13);
 217     INSERT INTO t4 VALUES(2,2,22);
 218     SELECT rowid FROM t4 WHERE x IN (1,9,2,5) AND y IN (1,3,NULL,2) AND z!=13;
 219   }
 220 } {1 2 4}
 221 do_test where4-5.3 {
 222   execsql {
 223     SELECT rowid FROM t4 WHERE x IN (1,9,NULL,2) AND y IN (1,3,2) AND z!=13;
 224   }
 225 } {1 2 4}
 226 do_test where4-6.1 {
 227   execsql {
 228     CREATE TABLE t5(a,b,c,d,e,f,UNIQUE(a,b,c,d,e,f));
 229     INSERT INTO t5 VALUES(1,1,1,1,1,11111);
 230     INSERT INTO t5 VALUES(2,2,2,2,2,22222);
 231     INSERT INTO t5 VALUES(1,2,3,4,5,12345);
 232     INSERT INTO t5 VALUES(2,3,4,5,6,23456);
 233   }
 234   execsql {
 235     SELECT rowid FROM t5
 236      WHERE a IN (1,9,2) AND b=2 AND c IN (1,2,3,4) AND d>0
 237   }
 238 } {3 2}
 239 do_test where4-6.2 {
 240   execsql {
 241     SELECT rowid FROM t5
 242      WHERE a IN (1,NULL,2) AND b=2 AND c IN (1,2,3,4) AND d>0
 243   }
 244 } {3 2}
 245 do_test where4-7.1 {
 246   execsql {
 247     CREATE TABLE t6(y,z,PRIMARY KEY(y,z));
 248   }
 249   execsql {
 250     SELECT * FROM t6 WHERE y=NULL AND z IN ('hello');
 251   }
 252 } {}
 253
 254 integrity_check {where4-99.0}
 255
 256 do_test where4-7.1 {
 257   execsql {
 258     BEGIN;
 259     CREATE TABLE t8(a, b, c, d);
 260     CREATE INDEX t8_i ON t8(a, b, c);
 261     CREATE TABLE t7(i);
 262
 263     INSERT INTO t7 VALUES(1);
 264     INSERT INTO t7 SELECT i*2 FROM t7;
 265     INSERT INTO t7 SELECT i*2 FROM t7;
 266     INSERT INTO t7 SELECT i*2 FROM t7;
 267     INSERT INTO t7 SELECT i*2 FROM t7;
 268     INSERT INTO t7 SELECT i*2 FROM t7;
 269     INSERT INTO t7 SELECT i*2 FROM t7;
 270
 271     COMMIT;
 272   }
 273 } {}
 274
 275 # At one point the sub-select inside the aggregate sum() function in the
 276 # following query was leaking a couple of stack entries. This query
 277 # runs the SELECT in a loop enough times that an assert() fails. Or rather,
 278 # did fail before the bug was fixed.
 279 #
 280 do_test where4-7.2 {
 281   execsql {
 282     SELECT sum((
 283       SELECT d FROM t8 WHERE a = i AND b = i AND c < NULL
 284     )) FROM t7;
 285   }
 286 } {{}}
 287
 288 }; #ifcapable subquery
 289
 290 #-------------------------------------------------------------------------
 291 # Verify that "IS ?" with a NULL bound to the variable also functions
 292 # correctly.
 293
 294 unset -nocomplain null
 295
 296 do_execsql_test 8.1 {
 297   CREATE TABLE u9(a UNIQUE, b);
 298   INSERT INTO u9 VALUES(NULL, 1);
 299   INSERT INTO u9 VALUES(NULL, 2);
 300 }
 301 do_execsql_test 8.2 { SELECT * FROM u9 WHERE a IS NULL  } {{} 1 {} 2}
 302 do_execsql_test 8.2 { SELECT * FROM u9 WHERE a IS $null } {{} 1 {} 2}
 303
 304
 305
 306
 307 finish_test