gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/in5.test (about) 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 finish_test