AryaWu/sqlite
0
1# 2008 September 12#3# The author disclaims copyright to this source code. In place of4# 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 14set testdir [file dirname $argv0]15source $testdir/tester.tcl16set testprefix in417 18do_test in4-1.1 {19 execsql {20 CREATE TABLE t1(a, b);21 CREATE INDEX i1 ON t1(a);22 }23} {}24do_test in4-1.2 {25 execsql {26 SELECT * FROM t1 WHERE a IN ('aaa', 'bbb', 'ccc');27 }28} {}29do_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}38do_test in4-1.4 {39 execsql {40 SELECT a FROM t1 WHERE rowid IN (1, 3);41 }42} {aaa ccc}43do_test in4-1.5 {44 execsql {45 SELECT a FROM t1 WHERE rowid IN ();46 }47} {}48do_test in4-1.6 {49 execsql {50 SELECT a FROM t1 WHERE a IN ('ddd');51 }52} {ddd}53 54do_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 65do_test in4-2.2 {66 execsql { SELECT b FROM t2 WHERE a IN (0, 2) }67} {zero two}68 69do_test in4-2.3 {70 execsql { SELECT b FROM t2 WHERE a IN (2, 0) }71} {zero two}72 73do_test in4-2.4 {74 execsql { SELECT b FROM t2 WHERE a IN (2, -1) }75} {-one two}76 77do_test in4-2.5 {78 execsql { SELECT b FROM t2 WHERE a IN (NULL, 3) }79} {three}80 81do_test in4-2.6 {82 execsql { SELECT b FROM t2 WHERE a IN (1.0, 2.1) }83} {one}84 85do_test in4-2.7 {86 execsql { SELECT b FROM t2 WHERE a IN ('1', '2') }87} {one two}88 89do_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#103do_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} {}121do_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} {}126do_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} {}135do_test in4-3.4 {136 execsql { SELECT * FROM t3 WHERE x = 10 AND y IN () }137} {}138do_test in4-3.5 {139 execsql { SELECT * FROM t3 WHERE x IN () AND y = 10 }140} {}141do_test in4-3.6 {142 execsql { SELECT * FROM t3 WHERE x IN () OR x = 10 }143} {10 10 10}144do_test in4-3.7 {145 execsql { SELECT * FROM t3 WHERE y IN () }146} {}147do_test in4-3.8 {148 execsql { SELECT x IN() AS a FROM t3 WHERE a }149} {}150do_test in4-3.9 {151 execsql { SELECT x IN() AS a FROM t3 WHERE NOT a }152} {0 0}153do_test in4-3.10 {154 execsql { SELECT * FROM t3 WHERE oid IN () }155} {}156do_test in4-3.11 {157 execsql { SELECT * FROM t3 WHERE x IN (1, 2) OR y IN ()}158} {1 1 1}159do_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, tests164# for when the RHS of IN is a single expression. This should work the165# same as the == and <> operators.166#167do_execsql_test in4-3.21 {168 SELECT * FROM t3 WHERE x=10 AND y IN (10);169} {10 10 10}170do_execsql_test in4-3.22 {171 SELECT * FROM t3 WHERE x IN (10) AND y=10;172} {10 10 10}173do_execsql_test in4-3.23 {174 SELECT * FROM t3 WHERE x IN (10) AND y IN (10);175} {10 10 10}176do_execsql_test in4-3.24 {177 SELECT * FROM t3 WHERE x=1 AND y NOT IN (10);178} {1 1 1}179do_execsql_test in4-3.25 {180 SELECT * FROM t3 WHERE x NOT IN (10) AND y=1;181} {1 1 1}182do_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 a187# single match and can use this information to optimize-out ORDER BY188# clauses.189#190do_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 B195 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 |}198do_execsql_test in4-3.32 {199 EXPLAIN QUERY PLAN200 SELECT *, '|' FROM t3 A, t3 B201 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 pass204do_execsql_test in4-3.33 {205 SELECT *, '|' FROM t3 A, t3 B206 WHERE A.x IN (10) AND A.y=10207 AND B.x IN (1) AND B.y=1;208} {10 10 10 1 1 1 |}209do_execsql_test in4-3.34 {210 EXPLAIN QUERY PLAN211 SELECT *, '|' FROM t3 A, t3 B212 WHERE A.x IN (10) AND A.y=10213 AND B.x IN (1) AND B.y=1;214} {~/B-TREE/} ;# No separate sorting pass215 216# An expression of the form "x IN (?,?)" creates an ephemeral table to217# hold the list of values on the RHS. But "x IN (?)" does not create218# an ephemeral table.219#220do_execsql_test in4-3.41 {221 SELECT * FROM t3 WHERE x IN (10,11);222} {10 10 10}223do_execsql_test in4-3.42 {224 EXPLAIN225 SELECT * FROM t3 WHERE x IN (10,11);226} {/OpenEphemeral/}227do_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" optimization232# was working. But we have now taken that optimization out.233#do_execsql_test in4-3.44 {234# EXPLAIN235# SELECT * FROM t3 WHERE x IN (10);236#} {~/OpenEphemeral/}237do_execsql_test in4-3.45 {238 SELECT * FROM t3 WHERE x NOT IN (10,11,99999);239} {1 1 1}240do_execsql_test in4-3.46 {241 EXPLAIN242 SELECT * FROM t3 WHERE x NOT IN (10,11,99999);243} {/OpenEphemeral/}244do_execsql_test in4-3.47 {245 SELECT * FROM t3 WHERE x NOT IN (10);246} {1 1 1}247do_execsql_test in4-3.48 {248 EXPLAIN249 SELECT * FROM t3 WHERE x NOT IN (10);250} {~/OpenEphemeral/}251 252# Make sure that when "x IN (?)" is converted into "x==?" that collating253# sequence and affinity computations do not get messed up.254#255do_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}262do_execsql_test in4-4.2 {263 SELECT c FROM t4a WHERE b=a ORDER BY c;264} {1 3}265do_execsql_test in4-4.3 {266 SELECT c FROM t4a WHERE (a||'')=b ORDER BY c;267} {1 3}268do_execsql_test in4-4.4 {269 SELECT c FROM t4a WHERE (a||'')=(b||'') ORDER BY c;270} {3}271do_execsql_test in4-4.5 {272 SELECT c FROM t4a WHERE a IN (b) ORDER BY c;273} {3}274do_execsql_test in4-4.6 {275 SELECT c FROM t4a WHERE (a||'') IN (b) ORDER BY c;276} {3}277 278 279do_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}284do_execsql_test in4-4.12 {285 SELECT c FROM t4b WHERE b=a;286} {4}287do_execsql_test in4-4.13 {288 SELECT c FROM t4b WHERE +a=b;289} {4}290do_execsql_test in4-4.14 {291 SELECT c FROM t4b WHERE a=+b;292} {}293do_execsql_test in4-4.15 {294 SELECT c FROM t4b WHERE +b=a;295} {}296do_execsql_test in4-4.16 {297 SELECT c FROM t4b WHERE b=+a;298} {4}299do_execsql_test in4-4.17 {300 SELECT c FROM t4b WHERE a IN (b);301} {}302do_execsql_test in4-4.18 {303 SELECT c FROM t4b WHERE b IN (a);304} {4}305do_execsql_test in4-4.19 {306 SELECT c FROM t4b WHERE +b IN (a);307} {}308 309do_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); -- match313 SELECT 2 FROM t5 WHERE 'FUZZ' IN (d); -- no match314 SELECT 3 FROM t5 WHERE d IN ('fuzz'); -- match315 SELECT 4 FROM t5 WHERE d IN ('FUZZ'); -- match316} {1 3 4}317 318# An expression of the form "x IN (y)" can be used as "x=y" by the319# query planner when computing transitive constraints or to run the320# query using an index on y.321#322do_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}330do_execsql_test in4-6.1-eqp {331 EXPLAIN QUERY PLAN332 SELECT * FROM t6a, t6b WHERE a=3 AND b IN (c);333} {~/SCAN t6a/}334do_execsql_test in4-6.2 {335 SELECT * FROM t6a, t6b WHERE a=3 AND c IN (b);336} {3 4 4 44}337do_execsql_test in4-6.2-eqp {338 EXPLAIN QUERY PLAN339 SELECT * FROM t6a, t6b WHERE a=3 AND c IN (b);340} {~/SCAN/}341 342reset_db343do_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 351do_execsql_test 7.1 {352 SELECT * FROM t2 LEFT JOIN t1 ON c = d AND b IN (10,10,10);353} {{} 1 {} {} {}}354 355ifcapable rtree {356 reset_db357 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#-------------------------------------------------------------------------369reset_db370do_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 380db close381sqlite3 db test.db382 383do_execsql_test 8.1 {384 SELECT t1.x FROM t2 CROSS JOIN t1 WHERE t2.z = t1.y;385} {222 111}386 387do_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 391do_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/b4fcb8a598396# OP_SeekScan changes from check-in 4a43430fd23f8835 on 2020-09-30 causes397# performance regression.398#399reset_db400do_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 VALUES409 ('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} {}415do_eqp_test 9.1 {416SELECT count(*) FROM edge417 WHERE node_from IN sub_nodes AND node_to IN sub_nodes;418} {419 QUERY PLAN420 |--SEARCH edge USING COVERING INDEX edge_to_from (node_to=?)421 |--USING ROWID SEARCH ON TABLE sub_nodes FOR IN-OPERATOR422 `--USING ROWID SEARCH ON TABLE sub_nodes FOR IN-OPERATOR423}424# ^^^^^ the key to the above is that the index should only use a single425# term (node_to=?), not two terms (node_to=? AND node_from=).426 427# dbsqlfuzz case428#429reset_db430do_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) VALUES433 (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 e41762333a4d6e90a49e628f488d0873b2dba4c5445# The opcode that precedes OP_SeekScan is usually OP_IdxGT, but can446# sometimes be OP_IdxGE447#448reset_db449do_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} {}458do_execsql_test 11.1 {459 SELECT * FROM t1460 WHERE b IN (345, (SELECT 1 FROM t1 461 WHERE b IN (coalesce(1,random()))462 AND c GLOB 'abc*xyz'))463 AND c BETWEEN 'abc' AND 'xyz';464} {xyz 1 abcdefxyz 99}465do_execsql_test 11.2 {466 EXPLAIN SELECT * FROM t1467 WHERE b IN (345, (SELECT 1 FROM t1 468 WHERE b IN (coalesce(1,random()))469 AND c GLOB 'abc*xyz'))470 AND c BETWEEN 'abc' AND 'xyz';471} {/ SeekScan /}472 473# 2021-06-25 ticket 6dcbfd11cf666e21474# Another problem with OP_SeekScan475#476reset_db477do_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 INDEXED490 WHERE (b = 1137 AND c IN (97, 98))491 OR (b = 1119 AND c IN (1115, 1023));492} {56 1119 1115}493do_execsql_test 12.1 {494 SELECT * FROM t1495 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 5981a8c041a3c2f3500# Another OP_SeekScan problem.501#502reset_db503do_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 t1514 WHERE a=1515 AND b IN (2,3)516 AND c BETWEEN 4 AND 5517 ORDER BY +id;518} {10 1 2 5 20 1 3 5 30 1 2 4 40 1 3 4}519 520finish_test521