AryaWu/sqlite
0
1# 2012 September 182#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 13set testdir [file dirname $argv0]14source $testdir/tester.tcl15set testprefix in516 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 t1z61 }]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 t1z71 }]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 t1z76 }]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 t3z81 }]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 t1z99 }]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 t1z117 }]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 t1z136 }]137} {0}138 139#-------------------------------------------------------------------------140# At one point SQLite was removing the DISTINCT keyword from expressions141# similar to:142#143# <expr1> IN (SELECT DISTINCT <expr2> FROM...)144#145# However, there are a few obscure cases where this is incorrect. For146# example, if the SELECT features a LIMIT clause, or if the collation147# sequence or affinity used by the DISTINCT does not match the one used148# 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 confuse219# 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} 0229do_execsql_test 8.2 {230 SELECT count(*) FROM n1 WHERE a IN (SELECT +a FROM n1)231} 0232do_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} 3236do_execsql_test 8.4 {237 SELECT count(*) FROM n1 WHERE a IN (SELECT +a FROM n1)238} 3239 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_db255do_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 269#-------------------------------------------------------------------------270#271reset_db272 273do_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 279do_execsql_test 10.1 {280 SELECT rowid FROM t1 WHERE (a, b) IN ( VALUES('abc', 'def'), ('ghi', 'JKL') );281} {1 2}282 283do_execsql_test 10.2 {284 CREATE INDEX i1 ON t1(a, b COLLATE BINARY);285}286 287do_execsql_test 10.3 {288 SELECT rowid FROM t1 WHERE (a, b) IN ( VALUES('abc', 'def'), ('ghi', 'JKL') );289} {1 2}290 291finish_test292