AryaWu/sqlite
0
1# 2023 September 232#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#13 14set testdir [file dirname $argv0]15source $testdir/tester.tcl16set testprefix indexA17 18do_execsql_test 1.0 {19 CREATE TABLE t1(a TEXT, b, c);20 CREATE INDEX i1 ON t1(b, c) WHERE a='abc';21 INSERT INTO t1 VALUES('abc', 1, 2);22}23 24do_execsql_test 1.1 {25 SELECT * FROM t1 WHERE a='abc'26} {abc 1 2}27 28do_eqp_test 1.2 {29 SELECT * FROM t1 WHERE a='abc'30} {USING COVERING INDEX i1}31 32do_execsql_test 1.3 {33 CREATE INDEX i2 ON t1(b, c) WHERE a=5;34 INSERT INTO t1 VALUES(5, 4, 3);35 36 SELECT a, typeof(a), b, c FROM t1 WHERE a=5;37} {5 text 4 3}38 39do_execsql_test 1.4 {40 CREATE TABLE t2(x);41 INSERT INTO t2 VALUES('v');42}43 44do_execsql_test 1.5 {45 SELECT x, a, b, c FROM t2 LEFT JOIN t1 ON (a=5 AND b=x)46} {v {} {} {}}47 48do_execsql_test 1.6 {49 SELECT x, a, b, c FROM t2 RIGHT JOIN t1 ON (t1.a=5 AND t1.b=t2.x)50} {{} abc 1 2 {} 5 4 3}51 52do_eqp_test 1.7 {53 SELECT x, a, b, c FROM t2 RIGHT JOIN t1 ON (t1.a=5 AND t1.b=t2.x)54} {USING INDEX i2}55 56#-------------------------------------------------------------------------57reset_db58 59do_execsql_test 2.0 {60 CREATE TABLE x1(a TEXT, b, c);61 INSERT INTO x1 VALUES('2', 'two', 'ii');62 INSERT INTO x1 VALUES('2.0', 'twopointoh', 'ii.0');63 64 CREATE TABLE x2(a NUMERIC, b, c);65 INSERT INTO x2 VALUES('2', 'two', 'ii');66 INSERT INTO x2 VALUES('2.0', 'twopointoh', 'ii.0');67 68 CREATE TABLE x3(a REAL, b, c);69 INSERT INTO x3 VALUES('2', 'two', 'ii');70 INSERT INTO x3 VALUES('2.0', 'twopointoh', 'ii.0');71}72 73foreach {tn idx} {74 0 {75 }76 1 {77 CREATE INDEX i1 ON x1(b, c) WHERE a=2;78 CREATE INDEX i2 ON x2(b, c) WHERE a=2;79 CREATE INDEX i3 ON x3(b, c) WHERE a=2;80 }81 2 {82 CREATE INDEX i1 ON x1(b, c) WHERE a=2.0;83 CREATE INDEX i2 ON x2(b, c) WHERE a=2.0;84 CREATE INDEX i3 ON x3(b, c) WHERE a=2.0;85 }86 3 {87 CREATE INDEX i1 ON x1(b, c) WHERE a='2.0';88 CREATE INDEX i2 ON x2(b, c) WHERE a='2.0';89 CREATE INDEX i3 ON x3(b, c) WHERE a='2.0';90 }91 4 {92 CREATE INDEX i1 ON x1(b, c) WHERE a='2';93 CREATE INDEX i2 ON x2(b, c) WHERE a='2';94 CREATE INDEX i3 ON x3(b, c) WHERE a='2';95 }96} {97 execsql { DROP INDEX IF EXISTS i1 }98 execsql { DROP INDEX IF EXISTS i2 }99 execsql { DROP INDEX IF EXISTS i3 }100 101 execsql $idx102 do_execsql_test 2.1.$tn.1 {103 SELECT *, typeof(a) FROM x1 WHERE a=2104 } {2 two ii text}105 do_execsql_test 2.1.$tn.2 {106 SELECT *, typeof(a) FROM x1 WHERE a=2.0107 } {2.0 twopointoh ii.0 text}108 do_execsql_test 2.1.$tn.3 {109 SELECT *, typeof(a) FROM x1 WHERE a='2'110 } {2 two ii text}111 do_execsql_test 2.1.$tn.4 {112 SELECT *, typeof(a) FROM x1 WHERE a='2.0'113 } {2.0 twopointoh ii.0 text}114 115 do_execsql_test 2.1.$tn.5 {116 SELECT *, typeof(a) FROM x2 WHERE a=2117 } {2 two ii integer 2 twopointoh ii.0 integer}118 do_execsql_test 2.1.$tn.6 {119 SELECT *, typeof(a) FROM x2 WHERE a=2.0120 } {2 two ii integer 2 twopointoh ii.0 integer}121 do_execsql_test 2.1.$tn.7 {122 SELECT *, typeof(a) FROM x2 WHERE a='2'123 } {2 two ii integer 2 twopointoh ii.0 integer}124 do_execsql_test 2.1.$tn.8 {125 SELECT *, typeof(a) FROM x2 WHERE a='2.0'126 } {2 two ii integer 2 twopointoh ii.0 integer}127 128 do_execsql_test 2.1.$tn.9 {129 SELECT *, typeof(a) FROM x3 WHERE a=2130 } {2.0 two ii real 2.0 twopointoh ii.0 real}131 do_execsql_test 2.1.$tn.10 {132 SELECT *, typeof(a) FROM x3 WHERE a=2.0133 } {2.0 two ii real 2.0 twopointoh ii.0 real}134 do_execsql_test 2.1.$tn.11 {135 SELECT *, typeof(a) FROM x3 WHERE a='2'136 } {2.0 two ii real 2.0 twopointoh ii.0 real}137 do_execsql_test 2.1.$tn.12 {138 SELECT *, typeof(a) FROM x3 WHERE a='2.0'139 } {2.0 two ii real 2.0 twopointoh ii.0 real}140 141}142 143reset_db144do_execsql_test 3.0 {145 CREATE TABLE x1(a TEXT, d PRIMARY KEY, b, c) WITHOUT ROWID;146 INSERT INTO x1 VALUES('2', 1, 'two', 'ii');147 INSERT INTO x1 VALUES('2.0', 2, 'twopointoh', 'ii.0');148 149 CREATE TABLE x2(a NUMERIC, b, c, d PRIMARY KEY) WITHOUT ROWID;150 INSERT INTO x2 VALUES('2', 'two', 'ii', 1);151 INSERT INTO x2 VALUES('2.0', 'twopointoh', 'ii.0', 2);152 153 CREATE TABLE x3(d PRIMARY KEY, a REAL, b, c) WITHOUT ROWID;154 INSERT INTO x3 VALUES(34, '2', 'two', 'ii');155 INSERT INTO x3 VALUES(35, '2.0', 'twopointoh', 'ii.0');156}157 158foreach {tn idx} {159 0 {160 }161 1 {162 CREATE INDEX i1 ON x1(b, c) WHERE a=2;163 CREATE INDEX i2 ON x2(b, c) WHERE a=2;164 CREATE INDEX i3 ON x3(b, c) WHERE a=2;165 }166 2 {167 CREATE INDEX i1 ON x1(b, c) WHERE a=2.0;168 CREATE INDEX i2 ON x2(b, c) WHERE a=2.0;169 CREATE INDEX i3 ON x3(b, c) WHERE a=2.0;170 }171 3 {172 CREATE INDEX i1 ON x1(b, c) WHERE a='2.0';173 CREATE INDEX i2 ON x2(b, c) WHERE a='2.0';174 CREATE INDEX i3 ON x3(b, c) WHERE a='2.0';175 }176 4 {177 CREATE INDEX i1 ON x1(b, c) WHERE a='2';178 CREATE INDEX i2 ON x2(b, c) WHERE a='2';179 CREATE INDEX i3 ON x3(b, c) WHERE a='2';180 }181} {182 execsql { DROP INDEX IF EXISTS i1 }183 execsql { DROP INDEX IF EXISTS i2 }184 execsql { DROP INDEX IF EXISTS i3 }185 186 execsql $idx187 do_execsql_test 3.1.$tn.1 {188 SELECT a, b, c, typeof(a) FROM x1 WHERE a=2189 } {2 two ii text}190 do_execsql_test 3.1.$tn.2 {191 SELECT a, b, c, typeof(a) FROM x1 WHERE a=2.0192 } {2.0 twopointoh ii.0 text}193 do_execsql_test 3.1.$tn.3 {194 SELECT a, b, c, typeof(a) FROM x1 WHERE a='2'195 } {2 two ii text}196 do_execsql_test 3.1.$tn.4 {197 SELECT a, b, c, typeof(a) FROM x1 WHERE a='2.0'198 } {2.0 twopointoh ii.0 text}199 200 do_execsql_test 3.1.$tn.5 {201 SELECT a, b, c, typeof(a) FROM x2 WHERE a=2202 } {2 two ii integer 2 twopointoh ii.0 integer}203 do_execsql_test 3.1.$tn.6 {204 SELECT a, b, c, typeof(a) FROM x2 WHERE a=2.0205 } {2 two ii integer 2 twopointoh ii.0 integer}206 do_execsql_test 3.1.$tn.7 {207 SELECT a, b, c, typeof(a) FROM x2 WHERE a='2'208 } {2 two ii integer 2 twopointoh ii.0 integer}209 do_execsql_test 3.1.$tn.8 {210 SELECT a, b, c, typeof(a) FROM x2 WHERE a='2.0'211 } {2 two ii integer 2 twopointoh ii.0 integer}212 213 do_execsql_test 3.1.$tn.9 {214 SELECT a, b, c, typeof(a) FROM x3 WHERE a=2215 } {2.0 two ii real 2.0 twopointoh ii.0 real}216 do_execsql_test 3.1.$tn.10 {217 SELECT a, b, c, typeof(a) FROM x3 WHERE a=2.0218 } {2.0 two ii real 2.0 twopointoh ii.0 real}219 do_execsql_test 3.1.$tn.11 {220 SELECT a, b, c, typeof(a) FROM x3 WHERE a='2'221 } {2.0 two ii real 2.0 twopointoh ii.0 real}222 do_execsql_test 3.1.$tn.12 {223 SELECT a, b, c, typeof(a) FROM x3 WHERE a='2.0'224 } {2.0 two ii real 2.0 twopointoh ii.0 real}225}226 227#-------------------------------------------------------------------------228reset_db229do_execsql_test 4.0 {230 CREATE TABLE t2(a INTEGER, b TEXT);231 INSERT INTO t2 VALUES(1, 'two');232 INSERT INTO t2 VALUES(2, 'two');233 INSERT INTO t2 VALUES(3, 'two');234 INSERT INTO t2 VALUES(1, 'three');235 INSERT INTO t2 VALUES(2, 'three');236 INSERT INTO t2 VALUES(3, 'three');237 238 CREATE INDEX t2a_two ON t2(a) WHERE b='two';239}240 241# explain_i { SELECT sum(a), b FROM t2 WHERE b='two' }242do_execsql_test 4.1.1 {243 SELECT sum(a), b FROM t2 WHERE b='two'244} {6 two}245do_eqp_test 4.1.2 {246 SELECT sum(a), b FROM t2 WHERE b='two'247} {USING COVERING INDEX t2a_two}248 249#-------------------------------------------------------------------------250reset_db251do_execsql_test 5.0 {252 CREATE TABLE t1(a INTEGER PRIMQRY KEY, b, c);253}254do_catchsql_test 5.1 {255 CREATE INDEX ex1 ON t1(c) WHERE b IS 'abc' COLLATE g;256} {1 {no such collation sequence: g}}257 258proc xyz {lhs rhs} {259 return [string compare $lhs $rhs]260}261db collate xyz xyz262do_execsql_test 5.2 {263 CREATE INDEX ex1 ON t1(c) WHERE b IS 'abc' COLLATE xyz;264}265db close266sqlite3 db test.db267do_execsql_test 5.3 {268 SELECT * FROM t1269}270 271#-------------------------------------------------------------------------272reset_db273do_execsql_test 6.0 {274 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);275 CREATE TABLE t2(x INTEGER PRIMARY KEY, y INTEGER, z INTEGER);276 INSERT INTO t1 VALUES(1, 1, 1);277 INSERT INTO t1 VALUES(2, 1, 2);278 INSERT INTO t2 VALUES(1, 5, 1);279 INSERT INTO t2 VALUES(2, 5, 2);280 281 CREATE INDEX t2z ON t2(z) WHERE y=5;282}283 284do_execsql_test 6.1 {285 ANALYZE;286 UPDATE sqlite_stat1 SET stat = '50 1' WHERE idx='t2z';287 UPDATE sqlite_stat1 SET stat = '50' WHERE tbl='t2' AND idx IS NULL;288 UPDATE sqlite_stat1 SET stat = '5000' WHERE tbl='t1' AND idx IS NULL;289 ANALYZE sqlite_schema;290}291 292do_execsql_test 6.2 {293 SELECT * FROM t1, t2 WHERE b=1 AND z=c AND y=5;294} {295 1 1 1 1 5 1296 2 1 2 2 5 2297}298 299do_eqp_test 6.3 {300 SELECT * FROM t1, t2 WHERE b=1 AND z=c AND y=5;301} {BLOOM FILTER ON t2}302 303do_execsql_test 6.4 {304 SELECT * FROM t1 LEFT JOIN t2 ON (y=5) WHERE b=1 AND z IS c;305} {306 1 1 1 1 5 1307 2 1 2 2 5 2308}309 310do_eqp_test 6.5 {311 SELECT * FROM t1 LEFT JOIN t2 ON (y=5) WHERE b=1 AND z IS c;312} {BLOOM FILTER ON t2}313 314do_execsql_test 6.6 {315 CREATE INDEX t2yz ON t2(y, z) WHERE y=5;316}317 318do_execsql_test 6.7 {319 SELECT * FROM t1 LEFT JOIN t2 ON (y=5) WHERE b=1 AND z IS c;320} {321 1 1 1 1 5 1322 2 1 2 2 5 2323}324 325#-------------------------------------------------------------------------326reset_db327do_execsql_test 7.0 {328 CREATE TABLE t1(i INTEGER PRIMARY KEY, b TEXT, c TEXT);329 CREATE INDEX i1 ON t1(c) WHERE b='abc' AND i=5;330 INSERT INTO t1 VALUES(5, 'abc', 'xyz');331 SELECT * FROM t1 INDEXED BY i1 WHERE b='abc' AND i=5 ORDER BY c;332} {5 abc xyz}333 334#-------------------------------------------------------------------------335reset_db336do_execsql_test 8.0 {337 CREATE TABLE t1(a, b, c);338 CREATE INDEX ex2 ON t1(a, 4);339 CREATE INDEX ex1 ON t1(a) WHERE 4=b;340 INSERT INTO t1 VALUES(1, 4, 1);341 INSERT INTO t1 VALUES(1, 5, 1);342 INSERT INTO t1 VALUES(2, 4, 2);343}344do_execsql_test 8.1 {345 SELECT * FROM t1 WHERE b=4;346} {347 1 4 1 2 4 2348}349 350finish_test351