sqlite3/test/in.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 IN and BETWEEN operator.
  13 #
  14 # $Id: in.test,v 1.13 2005/01/21 03:12:16 danielk1977 Exp $
  15
  16 set testdir [file dirname $argv0]
  17 source $testdir/tester.tcl
  18
  19 # Generate the test data we will need for the first squences of tests.
  20 #
  21 do_test in-1.0 {
  22   execsql {
  23     BEGIN;
  24     CREATE TABLE t1(a int, b int);
  25   }
  26   for {set i 1} {$i<=10} {incr i} {
  27     execsql "INSERT INTO t1 VALUES($i,[expr {int(pow(2,$i))}])"
  28   }
  29   execsql {
  30     COMMIT;
  31     SELECT count(*) FROM t1;
  32   }
  33 } {10}
  34
  35 # Do basic testing of BETWEEN.
  36 #
  37 do_test in-1.1 {
  38   execsql {SELECT a FROM t1 WHERE b BETWEEN 10 AND 50 ORDER BY a}
  39 } {4 5}
  40 do_test in-1.2 {
  41   execsql {SELECT a FROM t1 WHERE b NOT BETWEEN 10 AND 50 ORDER BY a}
  42 } {1 2 3 6 7 8 9 10}
  43 do_test in-1.3 {
  44   execsql {SELECT a FROM t1 WHERE b BETWEEN a AND a*5 ORDER BY a}
  45 } {1 2 3 4}
  46 do_test in-1.4 {
  47   execsql {SELECT a FROM t1 WHERE b NOT BETWEEN a AND a*5 ORDER BY a}
  48 } {5 6 7 8 9 10}
  49 do_test in-1.6 {
  50   execsql {SELECT a FROM t1 WHERE b BETWEEN a AND a*5 OR b=512 ORDER BY a}
  51 } {1 2 3 4 9}
  52 do_test in-1.7 {
  53   execsql {SELECT a+ 100*(a BETWEEN 1 and 3) FROM t1 ORDER BY b}
  54 } {101 102 103 4 5 6 7 8 9 10}
  55
  56 # The rest of this file concentrates on testing the IN operator.
  57 # Skip this if the library is compiled with SQLITE_OMIT_SUBQUERY
  58 # (because the IN operator is unavailable).
  59 #
  60 ifcapable !subquery {
  61   finish_test
  62   return
  63 }
  64
  65 # Testing of the IN operator using static lists on the right-hand side.
  66 #
  67 do_test in-2.1 {
  68   execsql {SELECT a FROM t1 WHERE b IN (8,12,16,24,32) ORDER BY a}
  69 } {3 4 5}
  70 do_test in-2.2 {
  71   execsql {SELECT a FROM t1 WHERE b NOT IN (8,12,16,24,32) ORDER BY a}
  72 } {1 2 6 7 8 9 10}
  73 do_test in-2.3 {
  74   execsql {SELECT a FROM t1 WHERE b IN (8,12,16,24,32) OR b=512 ORDER BY a}
  75 } {3 4 5 9}
  76 do_test in-2.4 {
  77   execsql {SELECT a FROM t1 WHERE b NOT IN (8,12,16,24,32) OR b=512 ORDER BY a}
  78 } {1 2 6 7 8 9 10}
  79 do_test in-2.5 {
  80   execsql {SELECT a+100*(b IN (8,16,24)) FROM t1 ORDER BY b}
  81 } {1 2 103 104 5 6 7 8 9 10}
  82
  83 do_test in-2.6 {
  84   set v [catch {execsql {SELECT a FROM t1 WHERE b IN (b+10,20)}} msg]
  85   lappend v $msg
  86 } {1 {right-hand side of IN operator must be constant}}
  87 do_test in-2.7 {
  88   set v [catch {execsql {SELECT a FROM t1 WHERE b IN (max(5,10,b),20)}} msg]
  89   lappend v $msg
  90 } {1 {right-hand side of IN operator must be constant}}
  91 do_test in-2.8 {
  92   execsql {SELECT a FROM t1 WHERE b IN (8*2,64/2) ORDER BY b}
  93 } {4 5}
  94 do_test in-2.9 {
  95   set v [catch {execsql {SELECT a FROM t1 WHERE b IN (max(5,10),20)}} msg]
  96   lappend v $msg
  97 } {1 {right-hand side of IN operator must be constant}}
  98 do_test in-2.10 {
  99   set v [catch {execsql {SELECT a FROM t1 WHERE min(0,b IN (a,30))}} msg]
 100   lappend v $msg
 101 } {1 {right-hand side of IN operator must be constant}}
 102 do_test in-2.11 {
 103   set v [catch {execsql {SELECT a FROM t1 WHERE c IN (10,20)}} msg]
 104   lappend v $msg
 105 } {1 {no such column: c}}
 106
 107 # Testing the IN operator where the right-hand side is a SELECT
 108 #
 109 do_test in-3.1 {
 110   execsql {
 111     SELECT a FROM t1
 112     WHERE b IN (SELECT b FROM t1 WHERE a<5)
 113     ORDER BY a
 114   }
 115 } {1 2 3 4}
 116 do_test in-3.2 {
 117   execsql {
 118     SELECT a FROM t1
 119     WHERE b IN (SELECT b FROM t1 WHERE a<5) OR b==512
 120     ORDER BY a
 121   }
 122 } {1 2 3 4 9}
 123 do_test in-3.3 {
 124   execsql {
 125     SELECT a + 100*(b IN (SELECT b FROM t1 WHERE a<5)) FROM t1 ORDER BY b
 126   }
 127 } {101 102 103 104 5 6 7 8 9 10}
 128
 129 # Make sure the UPDATE and DELETE commands work with IN-SELECT
 130 #
 131 do_test in-4.1 {
 132   execsql {
 133     UPDATE t1 SET b=b*2
 134     WHERE b IN (SELECT b FROM t1 WHERE a>8)
 135   }
 136   execsql {SELECT b FROM t1 ORDER BY b}
 137 } {2 4 8 16 32 64 128 256 1024 2048}
 138 do_test in-4.2 {
 139   execsql {
 140     DELETE FROM t1 WHERE b IN (SELECT b FROM t1 WHERE a>8)
 141   }
 142   execsql {SELECT a FROM t1 ORDER BY a}
 143 } {1 2 3 4 5 6 7 8}
 144 do_test in-4.3 {
 145   execsql {
 146     DELETE FROM t1 WHERE b NOT IN (SELECT b FROM t1 WHERE a>4)
 147   }
 148   execsql {SELECT a FROM t1 ORDER BY a}
 149 } {5 6 7 8}
 150
 151 # Do an IN with a constant RHS but where the RHS has many, many
 152 # elements.  We need to test that collisions in the hash table
 153 # are resolved properly.
 154 #
 155 do_test in-5.1 {
 156   execsql {
 157     INSERT INTO t1 VALUES('hello', 'world');
 158     SELECT * FROM t1
 159     WHERE a IN (
 160        'Do','an','IN','with','a','constant','RHS','but','where','the',
 161        'has','many','elements','We','need','to','test','that',
 162        'collisions','hash','table','are','resolved','properly',
 163        'This','in-set','contains','thirty','one','entries','hello');
 164   }
 165 } {hello world}
 166
 167 # Make sure the IN operator works with INTEGER PRIMARY KEY fields.
 168 #
 169 do_test in-6.1 {
 170   execsql {
 171     CREATE TABLE ta(a INTEGER PRIMARY KEY, b);
 172     INSERT INTO ta VALUES(1,1);
 173     INSERT INTO ta VALUES(2,2);
 174     INSERT INTO ta VALUES(3,3);
 175     INSERT INTO ta VALUES(4,4);
 176     INSERT INTO ta VALUES(6,6);
 177     INSERT INTO ta VALUES(8,8);
 178     INSERT INTO ta VALUES(10,
 179        'This is a key that is long enough to require a malloc in the VDBE');
 180     SELECT * FROM ta WHERE a<10;
 181   }
 182 } {1 1 2 2 3 3 4 4 6 6 8 8}
 183 do_test in-6.2 {
 184   execsql {
 185     CREATE TABLE tb(a INTEGER PRIMARY KEY, b);
 186     INSERT INTO tb VALUES(1,1);
 187     INSERT INTO tb VALUES(2,2);
 188     INSERT INTO tb VALUES(3,3);
 189     INSERT INTO tb VALUES(5,5);
 190     INSERT INTO tb VALUES(7,7);
 191     INSERT INTO tb VALUES(9,9);
 192     INSERT INTO tb VALUES(11,
 193        'This is a key that is long enough to require a malloc in the VDBE');
 194     SELECT * FROM tb WHERE a<10;
 195   }
 196 } {1 1 2 2 3 3 5 5 7 7 9 9}
 197 do_test in-6.3 {
 198   execsql {
 199     SELECT a FROM ta WHERE b IN (SELECT a FROM tb);
 200   }
 201 } {1 2 3}
 202 do_test in-6.4 {
 203   execsql {
 204     SELECT a FROM ta WHERE b NOT IN (SELECT a FROM tb);
 205   }
 206 } {4 6 8 10}
 207 do_test in-6.5 {
 208   execsql {
 209     SELECT a FROM ta WHERE b IN (SELECT b FROM tb);
 210   }
 211 } {1 2 3 10}
 212 do_test in-6.6 {
 213   execsql {
 214     SELECT a FROM ta WHERE b NOT IN (SELECT b FROM tb);
 215   }
 216 } {4 6 8}
 217 do_test in-6.7 {
 218   execsql {
 219     SELECT a FROM ta WHERE a IN (SELECT a FROM tb);
 220   }
 221 } {1 2 3}
 222 do_test in-6.8 {
 223   execsql {
 224     SELECT a FROM ta WHERE a NOT IN (SELECT a FROM tb);
 225   }
 226 } {4 6 8 10}
 227 do_test in-6.9 {
 228   execsql {
 229     SELECT a FROM ta WHERE a IN (SELECT b FROM tb);
 230   }
 231 } {1 2 3}
 232 do_test in-6.10 {
 233   execsql {
 234     SELECT a FROM ta WHERE a NOT IN (SELECT b FROM tb);
 235   }
 236 } {4 6 8 10}
 237
 238 # Tests of IN operator against empty sets.  (Ticket #185)
 239 #
 240 do_test in-7.1 {
 241   execsql {
 242     SELECT a FROM t1 WHERE a IN ();
 243   }
 244 } {}
 245 do_test in-7.2 {
 246   execsql {
 247     SELECT a FROM t1 WHERE a IN (5);
 248   }
 249 } {5}
 250 do_test in-7.3 {
 251   execsql {
 252     SELECT a FROM t1 WHERE a NOT IN () ORDER BY a;
 253   }
 254 } {5 6 7 8 hello}
 255 do_test in-7.4 {
 256   execsql {
 257     SELECT a FROM t1 WHERE a IN (5) AND b IN ();
 258   }
 259 } {}
 260 do_test in-7.5 {
 261   execsql {
 262     SELECT a FROM t1 WHERE a IN (5) AND b NOT IN ();
 263   }
 264 } {5}
 265 do_test in-7.6 {
 266   execsql {
 267     SELECT a FROM ta WHERE a IN ();
 268   }
 269 } {}
 270 do_test in-7.7 {
 271   execsql {
 272     SELECT a FROM ta WHERE a NOT IN ();
 273   }
 274 } {1 2 3 4 6 8 10}
 275
 276 do_test in-8.1 {
 277   execsql {
 278     SELECT b FROM t1 WHERE a IN ('hello','there')
 279   }
 280 } {world}
 281 do_test in-8.2 {
 282   execsql {
 283     SELECT b FROM t1 WHERE a IN ("hello",'there')
 284   }
 285 } {world}
 286
 287 # Test constructs of the form:  expr IN tablename
 288 #
 289 do_test in-9.1 {
 290   execsql {
 291     CREATE TABLE t4 AS SELECT a FROM tb;
 292     SELECT * FROM t4;
 293   }
 294 } {1 2 3 5 7 9 11}
 295 do_test in-9.2 {
 296   execsql {
 297     SELECT b FROM t1 WHERE a IN t4;
 298   }
 299 } {32 128}
 300 do_test in-9.3 {
 301   execsql {
 302     SELECT b FROM t1 WHERE a NOT IN t4;
 303   }
 304 } {64 256 world}
 305 do_test in-9.4 {
 306   catchsql {
 307     SELECT b FROM t1 WHERE a NOT IN tb;
 308   }
 309 } {1 {only a single result allowed for a SELECT that is part of an expression}}
 310
 311 finish_test