github.com/jdgcs/sqlite3@v1.12.1-0.20210908114423-bc5f96e4dd51/testdata/tcl/analyzeE.test (about) 1 # 2014-10-08 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 # This file implements tests for using STAT4 information 12 # on a descending index in a range query. 13 # 14 15 set testdir [file dirname $argv0] 16 source $testdir/tester.tcl 17 set ::testprefix analyzeE 18 19 ifcapable {!stat4} { 20 finish_test 21 return 22 } 23 24 # Verify that range queries on an ASCENDING index will use the 25 # index only if the range covers only a small fraction of the 26 # entries. 27 # 28 do_execsql_test analyzeE-1.0 { 29 CREATE TABLE t1(a,b); 30 WITH RECURSIVE 31 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 } {} 36 do_execsql_test analyzeE-1.1 { 37 EXPLAIN QUERY PLAN 38 SELECT * FROM t1 WHERE a BETWEEN 500 AND 2500; 39 } {/SCAN t1/} 40 do_execsql_test analyzeE-1.2 { 41 EXPLAIN QUERY PLAN 42 SELECT * FROM t1 WHERE a BETWEEN 2900 AND 3000; 43 } {/SEARCH t1 USING INDEX t1a/} 44 do_execsql_test analyzeE-1.3 { 45 EXPLAIN QUERY PLAN 46 SELECT * FROM t1 WHERE a BETWEEN 1700 AND 1750; 47 } {/SEARCH t1 USING INDEX t1a/} 48 do_execsql_test analyzeE-1.4 { 49 EXPLAIN QUERY PLAN 50 SELECT * FROM t1 WHERE a BETWEEN 1 AND 500 51 } {/SEARCH t1 USING INDEX t1a/} 52 do_execsql_test analyzeE-1.5 { 53 EXPLAIN QUERY PLAN 54 SELECT * FROM t1 WHERE a BETWEEN 3000 AND 3000000 55 } {/SEARCH t1 USING INDEX t1a/} 56 do_execsql_test analyzeE-1.6 { 57 EXPLAIN QUERY PLAN 58 SELECT * FROM t1 WHERE a<500 59 } {/SEARCH t1 USING INDEX t1a/} 60 do_execsql_test analyzeE-1.7 { 61 EXPLAIN QUERY PLAN 62 SELECT * FROM t1 WHERE a>2500 63 } {/SEARCH t1 USING INDEX t1a/} 64 do_execsql_test analyzeE-1.8 { 65 EXPLAIN QUERY PLAN 66 SELECT * FROM t1 WHERE a>1900 67 } {/SEARCH t1 USING INDEX t1a/} 68 do_execsql_test analyzeE-1.9 { 69 EXPLAIN QUERY PLAN 70 SELECT * FROM t1 WHERE a>1100 71 } {/SCAN t1/} 72 do_execsql_test analyzeE-1.10 { 73 EXPLAIN QUERY PLAN 74 SELECT * FROM t1 WHERE a<1100 75 } {/SEARCH t1 USING INDEX t1a/} 76 do_execsql_test analyzeE-1.11 { 77 EXPLAIN QUERY PLAN 78 SELECT * FROM t1 WHERE a<1900 79 } {/SCAN t1/} 80 81 # Verify that everything works the same on a DESCENDING index. 82 # 83 do_execsql_test analyzeE-2.0 { 84 DROP INDEX t1a; 85 CREATE INDEX t1a ON t1(a DESC); 86 ANALYZE; 87 } {} 88 do_execsql_test analyzeE-2.1 { 89 EXPLAIN QUERY PLAN 90 SELECT * FROM t1 WHERE a BETWEEN 500 AND 2500; 91 } {/SCAN t1/} 92 do_execsql_test analyzeE-2.2 { 93 EXPLAIN QUERY PLAN 94 SELECT * FROM t1 WHERE a BETWEEN 2900 AND 3000; 95 } {/SEARCH t1 USING INDEX t1a/} 96 do_execsql_test analyzeE-2.3 { 97 EXPLAIN QUERY PLAN 98 SELECT * FROM t1 WHERE a BETWEEN 1700 AND 1750; 99 } {/SEARCH t1 USING INDEX t1a/} 100 do_execsql_test analyzeE-2.4 { 101 EXPLAIN QUERY PLAN 102 SELECT * FROM t1 WHERE a BETWEEN 1 AND 500 103 } {/SEARCH t1 USING INDEX t1a/} 104 do_execsql_test analyzeE-2.5 { 105 EXPLAIN QUERY PLAN 106 SELECT * FROM t1 WHERE a BETWEEN 3000 AND 3000000 107 } {/SEARCH t1 USING INDEX t1a/} 108 do_execsql_test analyzeE-2.6 { 109 EXPLAIN QUERY PLAN 110 SELECT * FROM t1 WHERE a<500 111 } {/SEARCH t1 USING INDEX t1a/} 112 do_execsql_test analyzeE-2.7 { 113 EXPLAIN QUERY PLAN 114 SELECT * FROM t1 WHERE a>2500 115 } {/SEARCH t1 USING INDEX t1a/} 116 do_execsql_test analyzeE-2.8 { 117 EXPLAIN QUERY PLAN 118 SELECT * FROM t1 WHERE a>1900 119 } {/SEARCH t1 USING INDEX t1a/} 120 do_execsql_test analyzeE-2.9 { 121 EXPLAIN QUERY PLAN 122 SELECT * FROM t1 WHERE a>1100 123 } {/SCAN t1/} 124 do_execsql_test analyzeE-2.10 { 125 EXPLAIN QUERY PLAN 126 SELECT * FROM t1 WHERE a<1100 127 } {/SEARCH t1 USING INDEX t1a/} 128 do_execsql_test analyzeE-2.11 { 129 EXPLAIN QUERY PLAN 130 SELECT * FROM t1 WHERE a<1900 131 } {/SCAN t1/} 132 133 # Now do a range query on the second term of an ASCENDING index 134 # where the first term is constrained by equality. 135 # 136 do_execsql_test analyzeE-3.0 { 137 DROP TABLE t1; 138 CREATE TABLE t1(a,b,c); 139 WITH RECURSIVE 140 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 } {} 145 do_execsql_test analyzeE-3.1 { 146 EXPLAIN QUERY PLAN 147 SELECT * FROM t1 WHERE a BETWEEN 500 AND 2500 AND c=123; 148 } {/SCAN t1/} 149 do_execsql_test analyzeE-3.2 { 150 EXPLAIN QUERY PLAN 151 SELECT * FROM t1 WHERE a BETWEEN 2900 AND 3000 AND c=123; 152 } {/SEARCH t1 USING INDEX t1ca/} 153 do_execsql_test analyzeE-3.3 { 154 EXPLAIN QUERY PLAN 155 SELECT * FROM t1 WHERE a BETWEEN 1700 AND 1750 AND c=123; 156 } {/SEARCH t1 USING INDEX t1ca/} 157 do_execsql_test analyzeE-3.4 { 158 EXPLAIN QUERY PLAN 159 SELECT * FROM t1 WHERE a BETWEEN 1 AND 500 AND c=123 160 } {/SEARCH t1 USING INDEX t1ca/} 161 do_execsql_test analyzeE-3.5 { 162 EXPLAIN QUERY PLAN 163 SELECT * FROM t1 WHERE a BETWEEN 3000 AND 3000000 AND c=123 164 } {/SEARCH t1 USING INDEX t1ca/} 165 do_execsql_test analyzeE-3.6 { 166 EXPLAIN QUERY PLAN 167 SELECT * FROM t1 WHERE a<500 AND c=123 168 } {/SEARCH t1 USING INDEX t1ca/} 169 do_execsql_test analyzeE-3.7 { 170 EXPLAIN QUERY PLAN 171 SELECT * FROM t1 WHERE a>2500 AND c=123 172 } {/SEARCH t1 USING INDEX t1ca/} 173 do_execsql_test analyzeE-3.8 { 174 EXPLAIN QUERY PLAN 175 SELECT * FROM t1 WHERE a>1900 AND c=123 176 } {/SEARCH t1 USING INDEX t1ca/} 177 do_execsql_test analyzeE-3.9 { 178 EXPLAIN QUERY PLAN 179 SELECT * FROM t1 WHERE a>1100 AND c=123 180 } {/SCAN t1/} 181 do_execsql_test analyzeE-3.10 { 182 EXPLAIN QUERY PLAN 183 SELECT * FROM t1 WHERE a<1100 AND c=123 184 } {/SEARCH t1 USING INDEX t1ca/} 185 do_execsql_test analyzeE-3.11 { 186 EXPLAIN QUERY PLAN 187 SELECT * FROM t1 WHERE a<1900 AND c=123 188 } {/SCAN t1/} 189 190 # Repeat the 3.x tests using a DESCENDING index 191 # 192 do_execsql_test analyzeE-4.0 { 193 DROP INDEX t1ca; 194 CREATE INDEX t1ca ON t1(c ASC,a DESC); 195 ANALYZE; 196 } {} 197 do_execsql_test analyzeE-4.1 { 198 EXPLAIN QUERY PLAN 199 SELECT * FROM t1 WHERE a BETWEEN 500 AND 2500 AND c=123; 200 } {/SCAN t1/} 201 do_execsql_test analyzeE-4.2 { 202 EXPLAIN QUERY PLAN 203 SELECT * FROM t1 WHERE a BETWEEN 2900 AND 3000 AND c=123; 204 } {/SEARCH t1 USING INDEX t1ca/} 205 do_execsql_test analyzeE-4.3 { 206 EXPLAIN QUERY PLAN 207 SELECT * FROM t1 WHERE a BETWEEN 1700 AND 1750 AND c=123; 208 } {/SEARCH t1 USING INDEX t1ca/} 209 do_execsql_test analyzeE-4.4 { 210 EXPLAIN QUERY PLAN 211 SELECT * FROM t1 WHERE a BETWEEN 1 AND 500 AND c=123 212 } {/SEARCH t1 USING INDEX t1ca/} 213 do_execsql_test analyzeE-4.5 { 214 EXPLAIN QUERY PLAN 215 SELECT * FROM t1 WHERE a BETWEEN 3000 AND 3000000 AND c=123 216 } {/SEARCH t1 USING INDEX t1ca/} 217 do_execsql_test analyzeE-4.6 { 218 EXPLAIN QUERY PLAN 219 SELECT * FROM t1 WHERE a<500 AND c=123 220 } {/SEARCH t1 USING INDEX t1ca/} 221 do_execsql_test analyzeE-4.7 { 222 EXPLAIN QUERY PLAN 223 SELECT * FROM t1 WHERE a>2500 AND c=123 224 } {/SEARCH t1 USING INDEX t1ca/} 225 do_execsql_test analyzeE-4.8 { 226 EXPLAIN QUERY PLAN 227 SELECT * FROM t1 WHERE a>1900 AND c=123 228 } {/SEARCH t1 USING INDEX t1ca/} 229 do_execsql_test analyzeE-4.9 { 230 EXPLAIN QUERY PLAN 231 SELECT * FROM t1 WHERE a>1100 AND c=123 232 } {/SCAN t1/} 233 do_execsql_test analyzeE-4.10 { 234 EXPLAIN QUERY PLAN 235 SELECT * FROM t1 WHERE a<1100 AND c=123 236 } {/SEARCH t1 USING INDEX t1ca/} 237 do_execsql_test analyzeE-4.11 { 238 EXPLAIN QUERY PLAN 239 SELECT * FROM t1 WHERE a<1900 AND c=123 240 } {/SCAN t1/} 241 242 finish_test