gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/in4.test (about) 1 # 2008 September 1 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 # $Id: in4.test,v 1.4 2009/06/05 17:09:12 drh Exp $ 13 14 set testdir [file dirname $argv0] 15 source $testdir/tester.tcl 16 set testprefix in4 17 18 do_test in4-1.1 { 19 execsql { 20 CREATE TABLE t1(a, b); 21 CREATE INDEX i1 ON t1(a); 22 } 23 } {} 24 do_test in4-1.2 { 25 execsql { 26 SELECT * FROM t1 WHERE a IN ('aaa', 'bbb', 'ccc'); 27 } 28 } {} 29 do_test in4-1.3 { 30 execsql { 31 INSERT INTO t1 VALUES('aaa', 1); 32 INSERT INTO t1 VALUES('ddd', 2); 33 INSERT INTO t1 VALUES('ccc', 3); 34 INSERT INTO t1 VALUES('eee', 4); 35 SELECT b FROM t1 WHERE a IN ('aaa', 'bbb', 'ccc'); 36 } 37 } {1 3} 38 do_test in4-1.4 { 39 execsql { 40 SELECT a FROM t1 WHERE rowid IN (1, 3); 41 } 42 } {aaa ccc} 43 do_test in4-1.5 { 44 execsql { 45 SELECT a FROM t1 WHERE rowid IN (); 46 } 47 } {} 48 do_test in4-1.6 { 49 execsql { 50 SELECT a FROM t1 WHERE a IN ('ddd'); 51 } 52 } {ddd} 53 54 do_test in4-2.1 { 55 execsql { 56 CREATE TABLE t2(a INTEGER PRIMARY KEY, b TEXT); 57 INSERT INTO t2 VALUES(-1, '-one'); 58 INSERT INTO t2 VALUES(0, 'zero'); 59 INSERT INTO t2 VALUES(1, 'one'); 60 INSERT INTO t2 VALUES(2, 'two'); 61 INSERT INTO t2 VALUES(3, 'three'); 62 } 63 } {} 64 65 do_test in4-2.2 { 66 execsql { SELECT b FROM t2 WHERE a IN (0, 2) } 67 } {zero two} 68 69 do_test in4-2.3 { 70 execsql { SELECT b FROM t2 WHERE a IN (2, 0) } 71 } {zero two} 72 73 do_test in4-2.4 { 74 execsql { SELECT b FROM t2 WHERE a IN (2, -1) } 75 } {-one two} 76 77 do_test in4-2.5 { 78 execsql { SELECT b FROM t2 WHERE a IN (NULL, 3) } 79 } {three} 80 81 do_test in4-2.6 { 82 execsql { SELECT b FROM t2 WHERE a IN (1.0, 2.1) } 83 } {one} 84 85 do_test in4-2.7 { 86 execsql { SELECT b FROM t2 WHERE a IN ('1', '2') } 87 } {one two} 88 89 do_test in4-2.8 { 90 execsql { SELECT b FROM t2 WHERE a IN ('', '0.0.0', '2') } 91 } {two} 92 93 # The following block of tests test expressions of the form: 94 # 95 # <expr> IN () 96 # 97 # i.e. IN expressions with a literal empty set. 98 # 99 # This has led to crashes on more than one occasion. Test case in4-3.2 100 # was added in reponse to a bug reported on the mailing list on 11/7/2008. 101 # See also tickets #3602 and #185. 102 # 103 do_test in4-3.1 { 104 execsql { 105 DROP TABLE IF EXISTS t1; 106 DROP TABLE IF EXISTS t2; 107 CREATE TABLE t1(x, id); 108 CREATE TABLE t2(x, id); 109 INSERT INTO t1 VALUES(NULL, NULL); 110 INSERT INTO t1 VALUES(0, NULL); 111 INSERT INTO t1 VALUES(1, 3); 112 INSERT INTO t1 VALUES(2, 4); 113 INSERT INTO t1 VALUES(3, 5); 114 INSERT INTO t1 VALUES(4, 6); 115 INSERT INTO t2 VALUES(0, NULL); 116 INSERT INTO t2 VALUES(4, 1); 117 INSERT INTO t2 VALUES(NULL, 1); 118 INSERT INTO t2 VALUES(NULL, NULL); 119 } 120 } {} 121 do_test in4-3.2 { 122 execsql { 123 SELECT x FROM t1 WHERE id IN () AND x IN (SELECT x FROM t2 WHERE id=1) 124 } 125 } {} 126 do_test in4-3.3 { 127 execsql { 128 CREATE TABLE t3(x, y, z); 129 CREATE INDEX t3i1 ON t3(x, y); 130 INSERT INTO t3 VALUES(1, 1, 1); 131 INSERT INTO t3 VALUES(10, 10, 10); 132 } 133 execsql { SELECT * FROM t3 WHERE x IN () } 134 } {} 135 do_test in4-3.4 { 136 execsql { SELECT * FROM t3 WHERE x = 10 AND y IN () } 137 } {} 138 do_test in4-3.5 { 139 execsql { SELECT * FROM t3 WHERE x IN () AND y = 10 } 140 } {} 141 do_test in4-3.6 { 142 execsql { SELECT * FROM t3 WHERE x IN () OR x = 10 } 143 } {10 10 10} 144 do_test in4-3.7 { 145 execsql { SELECT * FROM t3 WHERE y IN () } 146 } {} 147 do_test in4-3.8 { 148 execsql { SELECT x IN() AS a FROM t3 WHERE a } 149 } {} 150 do_test in4-3.9 { 151 execsql { SELECT x IN() AS a FROM t3 WHERE NOT a } 152 } {0 0} 153 do_test in4-3.10 { 154 execsql { SELECT * FROM t3 WHERE oid IN () } 155 } {} 156 do_test in4-3.11 { 157 execsql { SELECT * FROM t3 WHERE x IN (1, 2) OR y IN ()} 158 } {1 1 1} 159 do_test in4-3.12 { 160 execsql { SELECT * FROM t3 WHERE x IN (1, 2) AND y IN ()} 161 } {} 162 163 # Tests for "... IN (?)" and "... NOT IN (?)". In other words, tests 164 # for when the RHS of IN is a single expression. This should work the 165 # same as the == and <> operators. 166 # 167 do_execsql_test in4-3.21 { 168 SELECT * FROM t3 WHERE x=10 AND y IN (10); 169 } {10 10 10} 170 do_execsql_test in4-3.22 { 171 SELECT * FROM t3 WHERE x IN (10) AND y=10; 172 } {10 10 10} 173 do_execsql_test in4-3.23 { 174 SELECT * FROM t3 WHERE x IN (10) AND y IN (10); 175 } {10 10 10} 176 do_execsql_test in4-3.24 { 177 SELECT * FROM t3 WHERE x=1 AND y NOT IN (10); 178 } {1 1 1} 179 do_execsql_test in4-3.25 { 180 SELECT * FROM t3 WHERE x NOT IN (10) AND y=1; 181 } {1 1 1} 182 do_execsql_test in4-3.26 { 183 SELECT * FROM t3 WHERE x NOT IN (10) AND y NOT IN (10); 184 } {1 1 1} 185 186 # The query planner recognizes that "x IN (?)" only generates a 187 # single match and can use this information to optimize-out ORDER BY 188 # clauses. 189 # 190 do_execsql_test in4-3.31 { 191 DROP INDEX t3i1; 192 CREATE UNIQUE INDEX t3xy ON t3(x,y); 193 194 SELECT *, '|' FROM t3 A, t3 B 195 WHERE A.x=10 AND A.y IN (10) 196 AND B.x=1 AND B.y IN (1); 197 } {10 10 10 1 1 1 |} 198 do_execsql_test in4-3.32 { 199 EXPLAIN QUERY PLAN 200 SELECT *, '|' FROM t3 A, t3 B 201 WHERE A.x=10 AND A.y IN (10) 202 AND B.x=1 AND B.y IN (1); 203 } {~/B-TREE/} ;# No separate sorting pass 204 do_execsql_test in4-3.33 { 205 SELECT *, '|' FROM t3 A, t3 B 206 WHERE A.x IN (10) AND A.y=10 207 AND B.x IN (1) AND B.y=1; 208 } {10 10 10 1 1 1 |} 209 do_execsql_test in4-3.34 { 210 EXPLAIN QUERY PLAN 211 SELECT *, '|' FROM t3 A, t3 B 212 WHERE A.x IN (10) AND A.y=10 213 AND B.x IN (1) AND B.y=1; 214 } {~/B-TREE/} ;# No separate sorting pass 215 216 # An expression of the form "x IN (?,?)" creates an ephemeral table to 217 # hold the list of values on the RHS. But "x IN (?)" does not create 218 # an ephemeral table. 219 # 220 do_execsql_test in4-3.41 { 221 SELECT * FROM t3 WHERE x IN (10,11); 222 } {10 10 10} 223 do_execsql_test in4-3.42 { 224 EXPLAIN 225 SELECT * FROM t3 WHERE x IN (10,11); 226 } {/OpenEphemeral/} 227 do_execsql_test in4-3.43 { 228 SELECT * FROM t3 WHERE x IN (10); 229 } {10 10 10} 230 231 # This test would verify that the "X IN (Y)" -> "X==Y" optimization 232 # was working. But we have now taken that optimization out. 233 #do_execsql_test in4-3.44 { 234 # EXPLAIN 235 # SELECT * FROM t3 WHERE x IN (10); 236 #} {~/OpenEphemeral/} 237 do_execsql_test in4-3.45 { 238 SELECT * FROM t3 WHERE x NOT IN (10,11,99999); 239 } {1 1 1} 240 do_execsql_test in4-3.46 { 241 EXPLAIN 242 SELECT * FROM t3 WHERE x NOT IN (10,11,99999); 243 } {/OpenEphemeral/} 244 do_execsql_test in4-3.47 { 245 SELECT * FROM t3 WHERE x NOT IN (10); 246 } {1 1 1} 247 do_execsql_test in4-3.48 { 248 EXPLAIN 249 SELECT * FROM t3 WHERE x NOT IN (10); 250 } {~/OpenEphemeral/} 251 252 # Make sure that when "x IN (?)" is converted into "x==?" that collating 253 # sequence and affinity computations do not get messed up. 254 # 255 do_execsql_test in4-4.1 { 256 CREATE TABLE t4a(a TEXT, b TEXT COLLATE nocase, c); 257 INSERT INTO t4a VALUES('ABC','abc',1); 258 INSERT INTO t4a VALUES('def','xyz',2); 259 INSERT INTO t4a VALUES('ghi','ghi',3); 260 SELECT c FROM t4a WHERE a=b ORDER BY c; 261 } {3} 262 do_execsql_test in4-4.2 { 263 SELECT c FROM t4a WHERE b=a ORDER BY c; 264 } {1 3} 265 do_execsql_test in4-4.3 { 266 SELECT c FROM t4a WHERE (a||'')=b ORDER BY c; 267 } {1 3} 268 do_execsql_test in4-4.4 { 269 SELECT c FROM t4a WHERE (a||'')=(b||'') ORDER BY c; 270 } {3} 271 do_execsql_test in4-4.5 { 272 SELECT c FROM t4a WHERE a IN (b) ORDER BY c; 273 } {3} 274 do_execsql_test in4-4.6 { 275 SELECT c FROM t4a WHERE (a||'') IN (b) ORDER BY c; 276 } {3} 277 278 279 do_execsql_test in4-4.11 { 280 CREATE TABLE t4b(a TEXT, b NUMERIC, c); 281 INSERT INTO t4b VALUES('1.0',1,4); 282 SELECT c FROM t4b WHERE a=b; 283 } {4} 284 do_execsql_test in4-4.12 { 285 SELECT c FROM t4b WHERE b=a; 286 } {4} 287 do_execsql_test in4-4.13 { 288 SELECT c FROM t4b WHERE +a=b; 289 } {4} 290 do_execsql_test in4-4.14 { 291 SELECT c FROM t4b WHERE a=+b; 292 } {} 293 do_execsql_test in4-4.15 { 294 SELECT c FROM t4b WHERE +b=a; 295 } {} 296 do_execsql_test in4-4.16 { 297 SELECT c FROM t4b WHERE b=+a; 298 } {4} 299 do_execsql_test in4-4.17 { 300 SELECT c FROM t4b WHERE a IN (b); 301 } {} 302 do_execsql_test in4-4.18 { 303 SELECT c FROM t4b WHERE b IN (a); 304 } {4} 305 do_execsql_test in4-4.19 { 306 SELECT c FROM t4b WHERE +b IN (a); 307 } {} 308 309 do_execsql_test in4-5.1 { 310 CREATE TABLE t5(c INTEGER PRIMARY KEY, d TEXT COLLATE nocase); 311 INSERT INTO t5 VALUES(17, 'fuzz'); 312 SELECT 1 FROM t5 WHERE 'fuzz' IN (d); -- match 313 SELECT 2 FROM t5 WHERE 'FUZZ' IN (d); -- no match 314 SELECT 3 FROM t5 WHERE d IN ('fuzz'); -- match 315 SELECT 4 FROM t5 WHERE d IN ('FUZZ'); -- match 316 } {1 3 4} 317 318 # An expression of the form "x IN (y)" can be used as "x=y" by the 319 # query planner when computing transitive constraints or to run the 320 # query using an index on y. 321 # 322 do_execsql_test in4-6.1 { 323 CREATE TABLE t6a(a INTEGER PRIMARY KEY, b); 324 INSERT INTO t6a VALUES(1,2),(3,4),(5,6); 325 CREATE TABLE t6b(c INTEGER PRIMARY KEY, d); 326 INSERT INTO t6b VALUES(4,44),(5,55),(6,66); 327 328 SELECT * FROM t6a, t6b WHERE a=3 AND b IN (c); 329 } {3 4 4 44} 330 do_execsql_test in4-6.1-eqp { 331 EXPLAIN QUERY PLAN 332 SELECT * FROM t6a, t6b WHERE a=3 AND b IN (c); 333 } {~/SCAN t6a/} 334 do_execsql_test in4-6.2 { 335 SELECT * FROM t6a, t6b WHERE a=3 AND c IN (b); 336 } {3 4 4 44} 337 do_execsql_test in4-6.2-eqp { 338 EXPLAIN QUERY PLAN 339 SELECT * FROM t6a, t6b WHERE a=3 AND c IN (b); 340 } {~/SCAN/} 341 342 reset_db 343 do_execsql_test 7.0 { 344 CREATE TABLE t1(a, b, c); 345 CREATE TABLE t2(d, e); 346 CREATE INDEX t1bc ON t1(c, b); 347 INSERT INTO t2(e) VALUES(1); 348 INSERT INTO t1 VALUES(NULL, NULL, NULL); 349 } 350 351 do_execsql_test 7.1 { 352 SELECT * FROM t2 LEFT JOIN t1 ON c = d AND b IN (10,10,10); 353 } {{} 1 {} {} {}} 354 355 ifcapable rtree { 356 reset_db 357 do_execsql_test 7.2 { 358 CREATE VIRTUAL TABLE t1 USING rtree(a, b, c); 359 CREATE TABLE t2(d INTEGER, e INT); 360 INSERT INTO t2(e) VALUES(1); 361 } 362 363 do_execsql_test 7.3 { 364 SELECT * FROM t2 LEFT JOIN t1 ON c IN (d) AND b IN (10,10,10); 365 } {{} 1 {} {} {}} 366 } 367 368 #------------------------------------------------------------------------- 369 reset_db 370 do_execsql_test 8.0 { 371 CREATE TABLE t1(x INTEGER PRIMARY KEY, y); 372 CREATE UNIQUE INDEX t1y ON t1(y); 373 INSERT INTO t1 VALUES(111, 'AAA'),(222, 'BBB'),(333, 'CCC'); 374 CREATE TABLE t2(z); 375 INSERT INTO t2 VALUES('BBB'),('AAA'); 376 ANALYZE sqlite_schema; 377 INSERT INTO sqlite_stat1 VALUES('t1', 't1y','100 1'); 378 } 379 380 db close 381 sqlite3 db test.db 382 383 do_execsql_test 8.1 { 384 SELECT t1.x FROM t2 CROSS JOIN t1 WHERE t2.z = t1.y; 385 } {222 111} 386 387 do_execsql_test 8.2 { 388 SELECT t1.x FROM t2 CROSS JOIN t1 WHERE t2.z = t1.y AND +t1.x IN (111, 222); 389 } {222 111} 390 391 do_execsql_test 8.3 { 392 SELECT t1.x FROM t2 CROSS JOIN t1 WHERE t2.z = t1.y AND t1.x IN (111, 222); 393 } {222 111} 394 395 # 2021-06-02 forum post https://sqlite.org/forum/forumpost/b4fcb8a598 396 # OP_SeekScan changes from check-in 4a43430fd23f8835 on 2020-09-30 causes 397 # performance regression. 398 # 399 reset_db 400 do_execsql_test 9.0 { 401 CREATE TABLE node(node_id INTEGER PRIMARY KEY); 402 CREATE TABLE edge(node_from INT, node_to INT); 403 CREATE TABLE sub_nodes(node_id INTEGER PRIMARY KEY); 404 CREATE INDEX edge_from_to ON edge(node_from,node_to); 405 CREATE INDEX edge_to_from ON edge(node_to,node_from); 406 ANALYZE; 407 DELETE FROM sqlite_stat1; 408 INSERT INTO sqlite_stat1 VALUES 409 ('sub_nodes',NULL,'1000000'), 410 ('edge','edge_to_from','20000000 2 2'), 411 ('edge','edge_from_to','20000000 2 2'), 412 ('node',NULL,'10000000'); 413 ANALYZE sqlite_schema; 414 } {} 415 do_eqp_test 9.1 { 416 SELECT count(*) FROM edge 417 WHERE node_from IN sub_nodes AND node_to IN sub_nodes; 418 } { 419 QUERY PLAN 420 |--SEARCH edge USING COVERING INDEX edge_to_from (node_to=?) 421 |--USING ROWID SEARCH ON TABLE sub_nodes FOR IN-OPERATOR 422 `--USING ROWID SEARCH ON TABLE sub_nodes FOR IN-OPERATOR 423 } 424 # ^^^^^ the key to the above is that the index should only use a single 425 # term (node_to=?), not two terms (node_to=? AND node_from=). 426 427 # dbsqlfuzz case 428 # 429 reset_db 430 do_execsql_test 10.0 { 431 CREATE TABLE t1(a,b,c,d,PRIMARY KEY(a,b,c)) WITHOUT ROWID; 432 INSERT INTO t1(a,b,c,d) VALUES 433 (0,-2,2,3), 434 (0,2,3,4), 435 (0,5,8,10), 436 (1,7,11,13); 437 ANALYZE sqlite_schema; 438 INSERT INTO sqlite_stat1 VALUES('t1','t1','10 3 2 1'); 439 ANALYZE sqlite_schema; 440 PRAGMA reverse_unordered_selects(1); 441 SELECT d FROM t1 WHERE 0=a AND b IN (-17,-4,-3,1,5,25,7798); 442 } {10} 443 444 # 2021-06-13 dbsqlfuzz e41762333a4d6e90a49e628f488d0873b2dba4c5 445 # The opcode that preceeds OP_SeekScan is usually OP_IdxGT, but can 446 # sometimes be OP_IdxGE 447 # 448 reset_db 449 do_execsql_test 11.0 { 450 CREATE TABLE t1(a TEXT, b INT, c INT, d INT); 451 INSERT INTO t1 VALUES('abc',123,4,5); 452 INSERT INTO t1 VALUES('xyz',1,'abcdefxyz',99); 453 CREATE INDEX t1abc ON t1(b,b,c); 454 ANALYZE sqlite_schema; 455 INSERT INTO sqlite_stat1 VALUES('t1','t1abc','10000 5 00 2003 10'); 456 ANALYZE sqlite_schema; 457 } {} 458 do_execsql_test 11.1 { 459 SELECT * FROM t1 460 WHERE b IN (345, (SELECT 1 FROM t1 461 WHERE b IN (345 NOT GLOB 510) 462 AND c GLOB 'abc*xyz')) 463 AND c BETWEEN 'abc' AND 'xyz'; 464 } {xyz 1 abcdefxyz 99} 465 do_execsql_test 11.2 { 466 EXPLAIN SELECT * FROM t1 467 WHERE b IN (345, (SELECT 1 FROM t1 468 WHERE b IN (345 NOT GLOB 510) 469 AND c GLOB 'abc*xyz')) 470 AND c BETWEEN 'abc' AND 'xyz'; 471 } {/ SeekScan /} 472 473 # 2021-06-25 ticket 6dcbfd11cf666e21 474 # Another problem with OP_SeekScan 475 # 476 reset_db 477 do_execsql_test 12.0 { 478 CREATE TABLE t1(a,b,c); 479 CREATE INDEX t1abc ON t1(a,b,c); 480 CREATE INDEX t1bca on t1(b,c,a); 481 INSERT INTO t1 VALUES(56,1119,1115); 482 INSERT INTO t1 VALUES(57,1147,1137); 483 INSERT INTO t1 VALUES(100,1050,1023); 484 INSERT INTO t1 VALUES(101,1050,1023); 485 ANALYZE sqlite_schema; 486 INSERT INTO sqlite_stat1 VALUES('t1','t1abc','358677 2 2 1'); 487 INSERT INTO sqlite_stat1 VALUES('t1','t1bca','358677 4 2 1'); 488 ANALYZE sqlite_schema; 489 SELECT * FROM t1 NOT INDEXED 490 WHERE (b = 1137 AND c IN (97, 98)) 491 OR (b = 1119 AND c IN (1115, 1023)); 492 } {56 1119 1115} 493 do_execsql_test 12.1 { 494 SELECT * FROM t1 495 WHERE (b = 1137 AND c IN (97, 98)) 496 OR (b = 1119 AND c IN (1115, 1023)); 497 } {56 1119 1115} 498 499 # 2021-11-02 ticket 5981a8c041a3c2f3 500 # Another OP_SeekScan problem. 501 # 502 reset_db 503 do_execsql_test 13.0 { 504 CREATE TABLE t1(id INTEGER PRIMARY KEY, a INT, b INT, c INT); 505 INSERT INTO t1 VALUES(10,1,2,5); 506 INSERT INTO t1 VALUES(20,1,3,5); 507 INSERT INTO t1 VALUES(30,1,2,4); 508 INSERT INTO t1 VALUES(40,1,3,4); 509 ANALYZE sqlite_master; 510 INSERT INTO sqlite_stat1 VALUES('t1','t1x','84000 3 2 1'); 511 CREATE INDEX t1x ON t1(a,b,c); 512 PRAGMA writable_schema=RESET; 513 SELECT * FROM t1 514 WHERE a=1 515 AND b IN (2,3) 516 AND c BETWEEN 4 AND 5 517 ORDER BY +id; 518 } {10 1 2 5 20 1 3 5 30 1 2 4 40 1 3 4} 519 520 finish_test