AryaWu/sqlite
0
1# 2014-10-082#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# This file implements tests for using STAT4 information12# on a descending index in a range query.13#14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17set ::testprefix analyzeE18 19ifcapable {!stat4} {20 finish_test21 return22}23 24# Verify that range queries on an ASCENDING index will use the25# index only if the range covers only a small fraction of the26# entries.27#28do_execsql_test analyzeE-1.0 {29 CREATE TABLE t1(a,b);30 WITH RECURSIVE31 cnt(x) AS (VALUES(1000) UNION ALL SELECT x+1 FROM cnt WHERE x<2000)32 INSERT INTO t1(a,b) SELECT x, x FROM cnt;33 CREATE INDEX t1a ON t1(a);34 ANALYZE;35} {}36do_execsql_test analyzeE-1.1 {37 EXPLAIN QUERY PLAN38 SELECT * FROM t1 WHERE a BETWEEN 500 AND 2500;39} {/SCAN t1/}40do_execsql_test analyzeE-1.2 {41 EXPLAIN QUERY PLAN42 SELECT * FROM t1 WHERE a BETWEEN 2900 AND 3000;43} {/SEARCH t1 USING INDEX t1a/}44do_execsql_test analyzeE-1.3 {45 EXPLAIN QUERY PLAN46 SELECT * FROM t1 WHERE a BETWEEN 1700 AND 1750;47} {/SEARCH t1 USING INDEX t1a/}48do_execsql_test analyzeE-1.4 {49 EXPLAIN QUERY PLAN50 SELECT * FROM t1 WHERE a BETWEEN 1 AND 50051} {/SEARCH t1 USING INDEX t1a/}52do_execsql_test analyzeE-1.5 {53 EXPLAIN QUERY PLAN54 SELECT * FROM t1 WHERE a BETWEEN 3000 AND 300000055} {/SEARCH t1 USING INDEX t1a/}56do_execsql_test analyzeE-1.6 {57 EXPLAIN QUERY PLAN58 SELECT * FROM t1 WHERE a<50059} {/SEARCH t1 USING INDEX t1a/}60do_execsql_test analyzeE-1.7 {61 EXPLAIN QUERY PLAN62 SELECT * FROM t1 WHERE a>250063} {/SEARCH t1 USING INDEX t1a/}64do_execsql_test analyzeE-1.8 {65 EXPLAIN QUERY PLAN66 SELECT * FROM t1 WHERE a>190067} {/SEARCH t1 USING INDEX t1a/}68do_execsql_test analyzeE-1.9 {69 EXPLAIN QUERY PLAN70 SELECT * FROM t1 WHERE a>110071} {/SCAN t1/}72do_execsql_test analyzeE-1.10 {73 EXPLAIN QUERY PLAN74 SELECT * FROM t1 WHERE a<110075} {/SEARCH t1 USING INDEX t1a/}76do_execsql_test analyzeE-1.11 {77 EXPLAIN QUERY PLAN78 SELECT * FROM t1 WHERE a<190079} {/SCAN t1/}80 81# Verify that everything works the same on a DESCENDING index.82#83do_execsql_test analyzeE-2.0 {84 DROP INDEX t1a;85 CREATE INDEX t1a ON t1(a DESC);86 ANALYZE;87} {}88do_execsql_test analyzeE-2.1 {89 EXPLAIN QUERY PLAN90 SELECT * FROM t1 WHERE a BETWEEN 500 AND 2500;91} {/SCAN t1/}92do_execsql_test analyzeE-2.2 {93 EXPLAIN QUERY PLAN94 SELECT * FROM t1 WHERE a BETWEEN 2900 AND 3000;95} {/SEARCH t1 USING INDEX t1a/}96do_execsql_test analyzeE-2.3 {97 EXPLAIN QUERY PLAN98 SELECT * FROM t1 WHERE a BETWEEN 1700 AND 1750;99} {/SEARCH t1 USING INDEX t1a/}100do_execsql_test analyzeE-2.4 {101 EXPLAIN QUERY PLAN102 SELECT * FROM t1 WHERE a BETWEEN 1 AND 500103} {/SEARCH t1 USING INDEX t1a/}104do_execsql_test analyzeE-2.5 {105 EXPLAIN QUERY PLAN106 SELECT * FROM t1 WHERE a BETWEEN 3000 AND 3000000107} {/SEARCH t1 USING INDEX t1a/}108do_execsql_test analyzeE-2.6 {109 EXPLAIN QUERY PLAN110 SELECT * FROM t1 WHERE a<500111} {/SEARCH t1 USING INDEX t1a/}112do_execsql_test analyzeE-2.7 {113 EXPLAIN QUERY PLAN114 SELECT * FROM t1 WHERE a>2500115} {/SEARCH t1 USING INDEX t1a/}116do_execsql_test analyzeE-2.8 {117 EXPLAIN QUERY PLAN118 SELECT * FROM t1 WHERE a>1900119} {/SEARCH t1 USING INDEX t1a/}120do_execsql_test analyzeE-2.9 {121 EXPLAIN QUERY PLAN122 SELECT * FROM t1 WHERE a>1100123} {/SCAN t1/}124do_execsql_test analyzeE-2.10 {125 EXPLAIN QUERY PLAN126 SELECT * FROM t1 WHERE a<1100127} {/SEARCH t1 USING INDEX t1a/}128do_execsql_test analyzeE-2.11 {129 EXPLAIN QUERY PLAN130 SELECT * FROM t1 WHERE a<1900131} {/SCAN t1/}132 133# Now do a range query on the second term of an ASCENDING index134# where the first term is constrained by equality.135#136do_execsql_test analyzeE-3.0 {137 DROP TABLE t1;138 CREATE TABLE t1(a,b,c);139 WITH RECURSIVE140 cnt(x) AS (VALUES(1000) UNION ALL SELECT x+1 FROM cnt WHERE x<2000)141 INSERT INTO t1(a,b,c) SELECT x, x, 123 FROM cnt;142 CREATE INDEX t1ca ON t1(c,a);143 ANALYZE;144} {}145do_execsql_test analyzeE-3.1 {146 EXPLAIN QUERY PLAN147 SELECT * FROM t1 WHERE a BETWEEN 500 AND 2500 AND c=123;148} {/SCAN t1/}149do_execsql_test analyzeE-3.2 {150 EXPLAIN QUERY PLAN151 SELECT * FROM t1 WHERE a BETWEEN 2900 AND 3000 AND c=123;152} {/SEARCH t1 USING INDEX t1ca/}153do_execsql_test analyzeE-3.3 {154 EXPLAIN QUERY PLAN155 SELECT * FROM t1 WHERE a BETWEEN 1700 AND 1750 AND c=123;156} {/SEARCH t1 USING INDEX t1ca/}157do_execsql_test analyzeE-3.4 {158 EXPLAIN QUERY PLAN159 SELECT * FROM t1 WHERE a BETWEEN 1 AND 500 AND c=123160} {/SEARCH t1 USING INDEX t1ca/}161do_execsql_test analyzeE-3.5 {162 EXPLAIN QUERY PLAN163 SELECT * FROM t1 WHERE a BETWEEN 3000 AND 3000000 AND c=123164} {/SEARCH t1 USING INDEX t1ca/}165do_execsql_test analyzeE-3.6 {166 EXPLAIN QUERY PLAN167 SELECT * FROM t1 WHERE a<500 AND c=123168} {/SEARCH t1 USING INDEX t1ca/}169do_execsql_test analyzeE-3.7 {170 EXPLAIN QUERY PLAN171 SELECT * FROM t1 WHERE a>2500 AND c=123172} {/SEARCH t1 USING INDEX t1ca/}173do_execsql_test analyzeE-3.8 {174 EXPLAIN QUERY PLAN175 SELECT * FROM t1 WHERE a>1900 AND c=123176} {/SEARCH t1 USING INDEX t1ca/}177do_execsql_test analyzeE-3.9 {178 EXPLAIN QUERY PLAN179 SELECT * FROM t1 WHERE a>1100 AND c=123180} {/SCAN t1/}181do_execsql_test analyzeE-3.10 {182 EXPLAIN QUERY PLAN183 SELECT * FROM t1 WHERE a<1100 AND c=123184} {/SEARCH t1 USING INDEX t1ca/}185do_execsql_test analyzeE-3.11 {186 EXPLAIN QUERY PLAN187 SELECT * FROM t1 WHERE a<1900 AND c=123188} {/SCAN t1/}189 190# Repeat the 3.x tests using a DESCENDING index191#192do_execsql_test analyzeE-4.0 {193 DROP INDEX t1ca;194 CREATE INDEX t1ca ON t1(c ASC,a DESC);195 ANALYZE;196} {}197do_execsql_test analyzeE-4.1 {198 EXPLAIN QUERY PLAN199 SELECT * FROM t1 WHERE a BETWEEN 500 AND 2500 AND c=123;200} {/SCAN t1/}201do_execsql_test analyzeE-4.2 {202 EXPLAIN QUERY PLAN203 SELECT * FROM t1 WHERE a BETWEEN 2900 AND 3000 AND c=123;204} {/SEARCH t1 USING INDEX t1ca/}205do_execsql_test analyzeE-4.3 {206 EXPLAIN QUERY PLAN207 SELECT * FROM t1 WHERE a BETWEEN 1700 AND 1750 AND c=123;208} {/SEARCH t1 USING INDEX t1ca/}209do_execsql_test analyzeE-4.4 {210 EXPLAIN QUERY PLAN211 SELECT * FROM t1 WHERE a BETWEEN 1 AND 500 AND c=123212} {/SEARCH t1 USING INDEX t1ca/}213do_execsql_test analyzeE-4.5 {214 EXPLAIN QUERY PLAN215 SELECT * FROM t1 WHERE a BETWEEN 3000 AND 3000000 AND c=123216} {/SEARCH t1 USING INDEX t1ca/}217do_execsql_test analyzeE-4.6 {218 EXPLAIN QUERY PLAN219 SELECT * FROM t1 WHERE a<500 AND c=123220} {/SEARCH t1 USING INDEX t1ca/}221do_execsql_test analyzeE-4.7 {222 EXPLAIN QUERY PLAN223 SELECT * FROM t1 WHERE a>2500 AND c=123224} {/SEARCH t1 USING INDEX t1ca/}225do_execsql_test analyzeE-4.8 {226 EXPLAIN QUERY PLAN227 SELECT * FROM t1 WHERE a>1900 AND c=123228} {/SEARCH t1 USING INDEX t1ca/}229do_execsql_test analyzeE-4.9 {230 EXPLAIN QUERY PLAN231 SELECT * FROM t1 WHERE a>1100 AND c=123232} {/SCAN t1/}233do_execsql_test analyzeE-4.10 {234 EXPLAIN QUERY PLAN235 SELECT * FROM t1 WHERE a<1100 AND c=123236} {/SEARCH t1 USING INDEX t1ca/}237do_execsql_test analyzeE-4.11 {238 EXPLAIN QUERY PLAN239 SELECT * FROM t1 WHERE a<1900 AND c=123240} {/SCAN t1/}241 242# 2023-03-23 https://sqlite.org/forum/forumpost/dc4854437b243#244reset_db245do_execsql_test analyzeE-5.0 {246 PRAGMA encoding = 'UTF-16';247 CREATE TABLE t0 (c1 TEXT);248 INSERT INTO t0 VALUES ('');249 CREATE INDEX i0 ON t0(c1);250 ANALYZE;251 SELECT * FROM t0 WHERE t0.c1 BETWEEN '' AND (ABS(''));252} {{}}253 254# 2023-03-24 https://sqlite.org/forum/forumpost/bc39e531e5255#256reset_db257do_execsql_test analyzeE-6.0 {258 CREATE TABLE t1(x);259 CREATE INDEX i1 ON t1(x,x,x,x,x||2);260 CREATE INDEX i2 ON t1(1<2);261 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<1000)262 INSERT INTO t1(x) SELECT x FROM c;263 ANALYZE;264} {}265do_execsql_test analyzeE-6.1 {266 SELECT count(*)>1 FROM sqlite_stat4 WHERE idx='i2' AND neq='1000 1';267} 1268do_execsql_test analyzeE-6.2 {269 SELECT count(*) FROM sqlite_stat4 WHERE idx='i2' AND neq<>'1000 1';270} 0271do_execsql_test analyzeE-6.3 {272 SELECT count(*)>1 FROM sqlite_stat4 WHERE idx='i1' AND neq='1 1 1 1 1 1';273} 1274do_execsql_test analyzeE-6.4 {275 SELECT count(*) FROM sqlite_stat4 WHERE idx='i1' AND neq<>'1 1 1 1 1 1';276} 0277 278# 2023-03-25 https://sqlite.org/forum/forumpost/5275207102279# Correctly expand zeroblobs while processing STAT4 information280# during query planning.281#282reset_db283do_execsql_test analyzeE-7.0 {284 CREATE TABLE t1(a TEXT COLLATE binary);285 CREATE INDEX t1x ON t1(a);286 INSERT INTO t1(a) VALUES(0),('apple'),(NULL),(''),('banana');287 ANALYZE;288 SELECT format('(%s)',a) FROM t1 WHERE t1.a > CAST(zeroblob(5) AS TEXT);289} {(0) (apple) (banana)}290do_execsql_test analyzeE-7.1 {291 SELECT format('(%s)',a) FROM t1 WHERE t1.a <= CAST(zeroblob(5) AS TEXT);292} {()}293 294finish_test295