test/in5.test

   1 # 2012 September 18
   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 #
  12
  13 set testdir [file dirname $argv0]
  14 source $testdir/tester.tcl
  15 set testprefix in5
  16
  17 do_test in5-1.1 {
  18   execsql {
  19     CREATE TABLE t1x(x INTEGER PRIMARY KEY);
  20     INSERT INTO t1x VALUES(1),(3),(5),(7),(9);
  21     CREATE TABLE t1y(y INTEGER UNIQUE);
  22     INSERT INTO t1y VALUES(2),(4),(6),(8);
  23     CREATE TABLE t1z(z TEXT UNIQUE);
  24     INSERT INTO t1z VALUES('a'),('c'),('e'),('g');
  25     CREATE TABLE t2(a INTEGER, b INTEGER, c TEXT, d TEXT);
  26     INSERT INTO t2 VALUES(1,2,'a','12a'),(1,2,'b','12b'),
  27                          (2,3,'g','23g'),(3,5,'c','35c'),
  28                          (4,6,'h','46h'),(5,6,'e','56e');
  29     CREATE TABLE t3x AS SELECT x FROM t1x;
  30     CREATE TABLE t3y AS SELECT y FROM t1y;
  31     CREATE TABLE t3z AS SELECT z FROM t1z;
  32     SELECT d FROM t2 WHERE a IN t1x AND b IN t1y AND c IN t1z ORDER BY c;
  33   }
  34 } {12a 56e}
  35 do_test in5-1.2 {
  36   execsql {
  37     SELECT d FROM t2 WHERE a IN t1y AND b IN t1x AND c IN t1z ORDER BY d;
  38   }
  39 } {23g}
  40 do_test in5-1.3 {
  41   execsql {
  42     SELECT d FROM t2 WHERE a IN t3x AND b IN t3y AND c IN t3z ORDER BY d;
  43   }
  44 } {12a 56e}
  45
  46
  47 do_test in5-2.1 {
  48   execsql {
  49     CREATE INDEX t2abc ON t2(a,b,c);
  50     SELECT d FROM t2 WHERE a IN t1x AND b IN t1y AND c IN t1z ORDER BY d;
  51   }
  52 } {12a 56e}
  53 do_test in5-2.2 {
  54   execsql {
  55     SELECT d FROM t2 WHERE a IN t1y AND b IN t1x AND c IN t1z ORDER BY d;
  56   }
  57 } {23g}
  58 do_test in5-2.3 {
  59   regexp {OpenEphemeral} [db eval {
  60     EXPLAIN SELECT d FROM t2 WHERE a IN t1x AND b IN t1y AND c IN t1z
  61   }]
  62 } {0}
  63 do_test in5-2.4 {
  64   execsql {
  65     SELECT d FROM t2 WHERE a IN t3x AND b IN t3y AND c IN t3z ORDER BY d;
  66   }
  67 } {12a 56e}
  68 do_test in5-2.5.1 {
  69   regexp {OpenEphemeral} [db eval {
  70     EXPLAIN SELECT d FROM t2 WHERE a IN t3x AND b IN t1y AND c IN t1z
  71   }]
  72 } {1}
  73 do_test in5-2.5.2 {
  74   regexp {OpenEphemeral} [db eval {
  75     EXPLAIN SELECT d FROM t2 WHERE a IN t1x AND b IN t3y AND c IN t1z
  76   }]
  77 } {1}
  78 do_test in5-2.5.3 {
  79   regexp {OpenEphemeral} [db eval {
  80     EXPLAIN SELECT d FROM t2 WHERE a IN t1x AND b IN t1y AND c IN t3z
  81   }]
  82 } {1}
  83
  84 do_test in5-3.1 {
  85   execsql {
  86     DROP INDEX t2abc;
  87     CREATE INDEX t2ab ON t2(a,b);
  88     SELECT d FROM t2 WHERE a IN t1x AND b IN t1y AND c IN t1z ORDER BY d;
  89   }
  90 } {12a 56e}
  91 do_test in5-3.2 {
  92   execsql {
  93     SELECT d FROM t2 WHERE a IN t1y AND b IN t1x AND c IN t1z ORDER BY d;
  94   }
  95 } {23g}
  96 do_test in5-3.3 {
  97   regexp {OpenEphemeral} [db eval {
  98     EXPLAIN SELECT d FROM t2 WHERE a IN t1x AND b IN t1y AND c IN t1z
  99   }]
 100 } {0}
 101
 102 do_test in5-4.1 {
 103   execsql {
 104     DROP INDEX t2ab;
 105     CREATE INDEX t2abcd ON t2(a,b,c,d);
 106     SELECT d FROM t2 WHERE a IN t1x AND b IN t1y AND c IN t1z ORDER BY d;
 107   }
 108 } {12a 56e}
 109 do_test in5-4.2 {
 110   execsql {
 111     SELECT d FROM t2 WHERE a IN t1y AND b IN t1x AND c IN t1z ORDER BY d;
 112   }
 113 } {23g}
 114 do_test in5-4.3 {
 115   regexp {OpenEphemeral} [db eval {
 116     EXPLAIN SELECT d FROM t2 WHERE a IN t1x AND b IN t1y AND c IN t1z
 117   }]
 118 } {0}
 119
 120
 121 do_test in5-5.1 {
 122   execsql {
 123     DROP INDEX t2abcd;
 124     CREATE INDEX t2cbad ON t2(c,b,a,d);
 125     SELECT d FROM t2 WHERE a IN t1x AND b IN t1y AND c IN t1z ORDER BY d;
 126   }
 127 } {12a 56e}
 128 do_test in5-5.2 {
 129   execsql {
 130     SELECT d FROM t2 WHERE a IN t1y AND b IN t1x AND c IN t1z ORDER BY d;
 131   }
 132 } {23g}
 133 do_test in5-5.3 {
 134   regexp {OpenEphemeral} [db eval {
 135     EXPLAIN SELECT d FROM t2 WHERE a IN t1x AND b IN t1y AND c IN t1z
 136   }]
 137 } {0}
 138
 139 #-------------------------------------------------------------------------
 140 # At one point SQLite was removing the DISTINCT keyword from expressions
 141 # similar to:
 142 #
 143 #   <expr1> IN (SELECT DISTINCT <expr2> FROM...)
 144 #
 145 # However, there are a few obscure cases where this is incorrect. For
 146 # example, if the SELECT features a LIMIT clause, or if the collation
 147 # sequence or affinity used by the DISTINCT does not match the one used
 148 # by the IN(...) expression.
 149 #
 150 do_execsql_test 6.1.1 {
 151   CREATE TABLE t1(a COLLATE nocase);
 152   INSERT INTO t1 VALUES('one');
 153   INSERT INTO t1 VALUES('ONE');
 154 }
 155 do_execsql_test 6.1.2 {
 156   SELECT count(*) FROM t1 WHERE a COLLATE BINARY IN (SELECT DISTINCT a FROM t1)
 157 } {1}
 158
 159 do_execsql_test 6.2.1 {
 160   CREATE TABLE t3(a, b);
 161   INSERT INTO t3 VALUES(1, 1);
 162   INSERT INTO t3 VALUES(1, 2);
 163   INSERT INTO t3 VALUES(1, 3);
 164   INSERT INTO t3 VALUES(2, 4);
 165   INSERT INTO t3 VALUES(2, 5);
 166   INSERT INTO t3 VALUES(2, 6);
 167   INSERT INTO t3 VALUES(3, 7);
 168   INSERT INTO t3 VALUES(3, 8);
 169   INSERT INTO t3 VALUES(3, 9);
 170 }
 171 do_execsql_test 6.2.2 {
 172   SELECT count(*) FROM t3 WHERE b IN (SELECT DISTINCT a FROM t3 LIMIT 5);
 173 } {3}
 174 do_execsql_test 6.2.3 {
 175   SELECT count(*) FROM t3 WHERE b IN (SELECT          a FROM t3 LIMIT 5);
 176 } {2}
 177
 178 do_execsql_test 6.3.1 {
 179   CREATE TABLE x1(a);
 180   CREATE TABLE x2(b);
 181   INSERT INTO x1 VALUES(1), (1), (2);
 182   INSERT INTO x2 VALUES(1), (2);
 183   SELECT count(*) FROM x2 WHERE b IN (SELECT DISTINCT a FROM x1 LIMIT 2);
 184 } {2}
 185
 186 #-------------------------------------------------------------------------
 187 # Test to confirm that bug [5e3c886796e5] is fixed.
 188 #
 189 do_execsql_test 7.1 {
 190   CREATE TABLE y1(a, b);
 191   CREATE TABLE y2(c);
 192
 193   INSERT INTO y1 VALUES(1,     'one');
 194   INSERT INTO y1 VALUES('two', 'two');
 195   INSERT INTO y1 VALUES(3,     'three');
 196
 197   INSERT INTO y2 VALUES('one');
 198   INSERT INTO y2 VALUES('two');
 199   INSERT INTO y2 VALUES('three');
 200 } {}
 201
 202 do_execsql_test 7.2.1 {
 203   SELECT a FROM y1 WHERE b NOT IN (SELECT a FROM y2);
 204 } {1 3}
 205 do_execsql_test 7.2.2 {
 206   SELECT a FROM y1 WHERE b IN (SELECT a FROM y2);
 207 } {two}
 208
 209 do_execsql_test 7.3.1 {
 210   CREATE INDEX y2c ON y2(c);
 211   SELECT a FROM y1 WHERE b NOT IN (SELECT a FROM y2);
 212 } {1 3}
 213 do_execsql_test 7.3.2 {
 214   SELECT a FROM y1 WHERE b IN (SELECT a FROM y2);
 215 } {two}
 216
 217 #-------------------------------------------------------------------------
 218 # Tests to confirm that indexes on the rowid column do not confuse
 219 # the query planner. See ticket [0eab1ac7591f511d].
 220 #
 221 do_execsql_test 8.0 {
 222   CREATE TABLE n1(a INTEGER PRIMARY KEY, b VARCHAR(500));
 223   CREATE UNIQUE INDEX n1a ON n1(a);
 224 }
 225
 226 do_execsql_test 8.1 {
 227   SELECT count(*) FROM n1 WHERE a IN (1, 2, 3)
 228 } 0
 229 do_execsql_test 8.2 {
 230   SELECT count(*) FROM n1 WHERE a IN (SELECT +a FROM n1)
 231 } 0
 232 do_execsql_test 8.3 {
 233   INSERT INTO n1 VALUES(1, NULL), (2, NULL), (3, NULL);
 234   SELECT count(*) FROM n1 WHERE a IN (1, 2, 3)
 235 } 3
 236 do_execsql_test 8.4 {
 237   SELECT count(*) FROM n1 WHERE a IN (SELECT +a FROM n1)
 238 } 3
 239
 240 #-------------------------------------------------------------------------
 241 # Test that ticket 61fe97454c is fixed.
 242 #
 243 do_execsql_test 9.0 {
 244   CREATE TABLE t9(a INTEGER PRIMARY KEY);
 245   INSERT INTO t9 VALUES (44), (45);
 246 }
 247 do_execsql_test 9.1 {
 248   SELECT * FROM t9 WHERE a IN (44, 45, 44, 45)
 249 } {44 45}
 250
 251 #-------------------------------------------------------------------------
 252 # Test that ticket c7a117190 is fixed.
 253 #
 254 reset_db
 255 do_execsql_test 9.0 {
 256   CREATE TABLE t0(c0);
 257   CREATE VIEW v0(c0) AS SELECT LOWER(CAST('1e500' AS TEXT)) FROM t0;
 258   INSERT INTO t0(c0) VALUES (NULL);
 259 }
 260
 261 do_execsql_test 9.1 {
 262   SELECT lower('1e500') FROM t0 WHERE rowid NOT IN (0, 0, lower('1e500'));
 263 } {1e500}
 264
 265 do_execsql_test 9.2 {
 266   SELECT lower('1e500') FROM t0 WHERE rowid != lower('1e500');
 267 } {1e500}
 268
 269 #-------------------------------------------------------------------------
 270 #
 271 reset_db
 272
 273 do_execsql_test 10.0 {
 274   CREATE TABLE t1(a, b TEXT COLLATE NOCASE);
 275   INSERT INTO t1 VALUES('abc', 'def');
 276   INSERT INTO t1 VALUES('ghi', 'jkl');
 277 }
 278
 279 do_execsql_test 10.1 {
 280   SELECT rowid FROM t1 WHERE (a, b) IN ( VALUES('abc', 'def'), ('ghi', 'JKL') );
 281 } {1 2}
 282
 283 do_execsql_test 10.2 {
 284   CREATE INDEX i1 ON t1(a, b COLLATE BINARY);
 285 }
 286
 287 do_execsql_test 10.3 {
 288   SELECT rowid FROM t1 WHERE (a, b) IN ( VALUES('abc', 'def'), ('ghi', 'JKL') );
 289 } {1 2}
 290
 291 finish_test