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 13set testdir [file dirname $argv0] 14source $testdir/tester.tcl 15set testprefix in5 16 17do_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} 35do_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} 40do_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 47do_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} 53do_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} 58do_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} 63do_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} 68do_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} 73do_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} 78do_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 84do_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} 91do_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} 96do_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 102do_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} 109do_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} 114do_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 121do_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} 128do_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} 133do_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# 150do_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} 155do_execsql_test 6.1.2 { 156 SELECT count(*) FROM t1 WHERE a COLLATE BINARY IN (SELECT DISTINCT a FROM t1) 157} {1} 158 159do_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} 171do_execsql_test 6.2.2 { 172 SELECT count(*) FROM t3 WHERE b IN (SELECT DISTINCT a FROM t3 LIMIT 5); 173} {3} 174do_execsql_test 6.2.3 { 175 SELECT count(*) FROM t3 WHERE b IN (SELECT a FROM t3 LIMIT 5); 176} {2} 177 178do_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# 189do_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 202do_execsql_test 7.2.1 { 203 SELECT a FROM y1 WHERE b NOT IN (SELECT a FROM y2); 204} {1 3} 205do_execsql_test 7.2.2 { 206 SELECT a FROM y1 WHERE b IN (SELECT a FROM y2); 207} {two} 208 209do_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} 213do_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# 221do_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 226do_execsql_test 8.1 { 227 SELECT count(*) FROM n1 WHERE a IN (1, 2, 3) 228} 0 229do_execsql_test 8.2 { 230 SELECT count(*) FROM n1 WHERE a IN (SELECT +a FROM n1) 231} 0 232do_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 236do_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# 243do_execsql_test 9.0 { 244 CREATE TABLE t9(a INTEGER PRIMARY KEY); 245 INSERT INTO t9 VALUES (44), (45); 246} 247do_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# 254reset_db 255do_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 261do_execsql_test 9.1 { 262 SELECT lower('1e500') FROM t0 WHERE rowid NOT IN (0, 0, lower('1e500')); 263} {1e500} 264 265do_execsql_test 9.2 { 266 SELECT lower('1e500') FROM t0 WHERE rowid != lower('1e500'); 267} {1e500} 268 269finish_test 270