Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | More test case updates. Tests are all running now. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | rework-EQP |
Files: | files | file ages | folders |
SHA3-256: |
dab5e5294813891469660cceb211ac1a |
User & Date: | drh 2018-05-02 19:42:33.079 |
Context
2018-05-03
| ||
01:37 | Enhance EXPLAIN QUERY PLAN to report the generation of constant rows using VALUES or just a SELECT without FROM. (check-in: c75eee69fa user: drh tags: rework-EQP) | |
2018-05-02
| ||
19:42 | More test case updates. Tests are all running now. (check-in: dab5e52948 user: drh tags: rework-EQP) | |
18:00 | Fix test cases so that they work with the new EXPLAIN QUERY PLAN output format. Only some of the cases have been fixed. This is an incremental check-in. (check-in: 5f0e803e33 user: drh tags: rework-EQP) | |
Changes
Changes to ext/expert/expert1.test.
︙ | ︙ | |||
91 92 93 94 95 96 97 | eval $setup do_setup_rec_test $tn.1 { CREATE TABLE t1(a, b, c) } { SELECT * FROM t1 } { (no new indexes) | | | | | | | | | | | | | | | | | | | | | | | | | | | 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 | eval $setup do_setup_rec_test $tn.1 { CREATE TABLE t1(a, b, c) } { SELECT * FROM t1 } { (no new indexes) SCAN TABLE t1 } do_setup_rec_test $tn.2 { CREATE TABLE t1(a, b, c); } { SELECT * FROM t1 WHERE b>?; } { CREATE INDEX t1_idx_00000062 ON t1(b); SEARCH TABLE t1 USING INDEX t1_idx_00000062 (b>?) } do_setup_rec_test $tn.3 { CREATE TABLE t1(a, b, c); } { SELECT * FROM t1 WHERE b COLLATE nocase BETWEEN ? AND ? } { CREATE INDEX t1_idx_3e094c27 ON t1(b COLLATE NOCASE); SEARCH TABLE t1 USING INDEX t1_idx_3e094c27 (b>? AND b<?) } do_setup_rec_test $tn.4 { CREATE TABLE t1(a, b, c); } { SELECT a FROM t1 ORDER BY b; } { CREATE INDEX t1_idx_00000062 ON t1(b); SCAN TABLE t1 USING INDEX t1_idx_00000062 } do_setup_rec_test $tn.5 { CREATE TABLE t1(a, b, c); } { SELECT a FROM t1 WHERE a=? ORDER BY b; } { CREATE INDEX t1_idx_000123a7 ON t1(a, b); SEARCH TABLE t1 USING COVERING INDEX t1_idx_000123a7 (a=?) } do_setup_rec_test $tn.6 { CREATE TABLE t1(a, b, c); } { SELECT min(a) FROM t1 } { CREATE INDEX t1_idx_00000061 ON t1(a); SEARCH TABLE t1 USING COVERING INDEX t1_idx_00000061 } do_setup_rec_test $tn.7 { CREATE TABLE t1(a, b, c); } { SELECT * FROM t1 ORDER BY a, b, c; } { CREATE INDEX t1_idx_033e95fe ON t1(a, b, c); SCAN TABLE t1 USING COVERING INDEX t1_idx_033e95fe } #do_setup_rec_test $tn.1.8 { # CREATE TABLE t1(a, b, c); #} { # SELECT * FROM t1 ORDER BY a ASC, b COLLATE nocase DESC, c ASC; #} { # CREATE INDEX t1_idx_5be6e222 ON t1(a, b COLLATE NOCASE DESC, c); # 0|0|0|SCAN TABLE t1 USING COVERING INDEX t1_idx_5be6e222 #} do_setup_rec_test $tn.8.1 { CREATE TABLE t1(a COLLATE NOCase, b, c); } { SELECT * FROM t1 WHERE a=? } { CREATE INDEX t1_idx_00000061 ON t1(a); SEARCH TABLE t1 USING INDEX t1_idx_00000061 (a=?) } do_setup_rec_test $tn.8.2 { CREATE TABLE t1(a, b COLLATE nocase, c); } { SELECT * FROM t1 ORDER BY a ASC, b DESC, c ASC; } { CREATE INDEX t1_idx_5cb97285 ON t1(a, b DESC, c); SCAN TABLE t1 USING COVERING INDEX t1_idx_5cb97285 } # Tables with names that require quotes. # do_setup_rec_test $tn.9.1 { CREATE TABLE "t t"(a, b, c); } { SELECT * FROM "t t" WHERE a=? } { CREATE INDEX 't t_idx_00000061' ON 't t'(a); SEARCH TABLE t t USING INDEX t t_idx_00000061 (a=?) } do_setup_rec_test $tn.9.2 { CREATE TABLE "t t"(a, b, c); } { SELECT * FROM "t t" WHERE b BETWEEN ? AND ? } { CREATE INDEX 't t_idx_00000062' ON 't t'(b); SEARCH TABLE t t USING INDEX t t_idx_00000062 (b>? AND b<?) } # Columns with names that require quotes. # do_setup_rec_test $tn.10.1 { CREATE TABLE t3(a, "b b", c); } { SELECT * FROM t3 WHERE "b b" = ? } { CREATE INDEX t3_idx_00050c52 ON t3('b b'); SEARCH TABLE t3 USING INDEX t3_idx_00050c52 (b b=?) } do_setup_rec_test $tn.10.2 { CREATE TABLE t3(a, "b b", c); } { SELECT * FROM t3 ORDER BY "b b" } { CREATE INDEX t3_idx_00050c52 ON t3('b b'); SCAN TABLE t3 USING INDEX t3_idx_00050c52 } # Transitive constraints # do_setup_rec_test $tn.11.1 { CREATE TABLE t5(a, b); CREATE TABLE t6(c, d); } { SELECT * FROM t5, t6 WHERE a=? AND b=c AND c=? } { CREATE INDEX t5_idx_000123a7 ON t5(a, b); CREATE INDEX t6_idx_00000063 ON t6(c); SEARCH TABLE t6 USING INDEX t6_idx_00000063 (c=?) SEARCH TABLE t5 USING COVERING INDEX t5_idx_000123a7 (a=? AND b=?) } # OR terms. # do_setup_rec_test $tn.12.1 { CREATE TABLE t7(a, b); } { SELECT * FROM t7 WHERE a=? OR b=? } { CREATE INDEX t7_idx_00000062 ON t7(b); CREATE INDEX t7_idx_00000061 ON t7(a); SEARCH TABLE t7 USING INDEX t7_idx_00000061 (a=?) SEARCH TABLE t7 USING INDEX t7_idx_00000062 (b=?) } # rowid terms. # do_setup_rec_test $tn.13.1 { CREATE TABLE t8(a, b); } { SELECT * FROM t8 WHERE rowid=? } { (no new indexes) SEARCH TABLE t8 USING INTEGER PRIMARY KEY (rowid=?) } do_setup_rec_test $tn.13.2 { CREATE TABLE t8(a, b); } { SELECT * FROM t8 ORDER BY rowid } { (no new indexes) SCAN TABLE t8 } do_setup_rec_test $tn.13.3 { CREATE TABLE t8(a, b); } { SELECT * FROM t8 WHERE a=? ORDER BY rowid } { CREATE INDEX t8_idx_00000061 ON t8(a); SEARCH TABLE t8 USING INDEX t8_idx_00000061 (a=?) } # Triggers # do_setup_rec_test $tn.14 { CREATE TABLE t9(a, b, c); CREATE TABLE t10(a, b, c); CREATE TRIGGER t9t AFTER INSERT ON t9 BEGIN UPDATE t10 SET a=new.a WHERE b = new.b; END; } { INSERT INTO t9 VALUES(?, ?, ?); } { CREATE INDEX t10_idx_00000062 ON t10(b); -- TRIGGER t9t SEARCH TABLE t10 USING INDEX t10_idx_00000062 (b=?) } do_setup_rec_test $tn.15 { CREATE TABLE t1(a, b); CREATE TABLE t2(c, d); WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100) INSERT INTO t1 SELECT (i-1)/50, (i-1)/20 FROM s; WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100) INSERT INTO t2 SELECT (i-1)/20, (i-1)/5 FROM s; } { SELECT * FROM t2, t1 WHERE b=? AND d=? AND t2.rowid=t1.rowid } { CREATE INDEX t2_idx_00000064 ON t2(d); SEARCH TABLE t2 USING INDEX t2_idx_00000064 (d=?) SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) } do_setup_rec_test $tn.16 { CREATE TABLE t1(a, b); } { SELECT * FROM t1 WHERE b IS NOT NULL; } { (no new indexes) SCAN TABLE t1 } } proc do_candidates_test {tn sql res} { set res [squish [string trim $res]] |
︙ | ︙ |
Changes to ext/expert/sqlite3expert.c.
︙ | ︙ | |||
1119 1120 1121 1122 1123 1124 1125 | IdxHashEntry *pEntry; sqlite3_stmt *pExplain = 0; idxHashClear(&hIdx); rc = idxPrintfPrepareStmt(dbm, &pExplain, pzErr, "EXPLAIN QUERY PLAN %s", pStmt->zSql ); while( rc==SQLITE_OK && sqlite3_step(pExplain)==SQLITE_ROW ){ | | | | | 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 | IdxHashEntry *pEntry; sqlite3_stmt *pExplain = 0; idxHashClear(&hIdx); rc = idxPrintfPrepareStmt(dbm, &pExplain, pzErr, "EXPLAIN QUERY PLAN %s", pStmt->zSql ); while( rc==SQLITE_OK && sqlite3_step(pExplain)==SQLITE_ROW ){ /* int iId = sqlite3_column_int(pExplain, 0); */ /* int iParent = sqlite3_column_int(pExplain, 1); */ /* int iNotUsed = sqlite3_column_int(pExplain, 2); */ const char *zDetail = (const char*)sqlite3_column_text(pExplain, 3); int nDetail = STRLEN(zDetail); int i; for(i=0; i<nDetail; i++){ const char *zIdx = 0; if( memcmp(&zDetail[i], " USING INDEX ", 13)==0 ){ |
︙ | ︙ | |||
1148 1149 1150 1151 1152 1153 1154 | idxHashAdd(&rc, &hIdx, zSql, 0); if( rc ) goto find_indexes_out; } break; } } | | < < | 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 | idxHashAdd(&rc, &hIdx, zSql, 0); if( rc ) goto find_indexes_out; } break; } } pStmt->zEQP = idxAppendText(&rc, pStmt->zEQP, "%s\n", zDetail); } for(pEntry=hIdx.pFirst; pEntry; pEntry=pEntry->pNext){ pStmt->zIdx = idxAppendText(&rc, pStmt->zIdx, "%s;\n", pEntry->zKey); } idxFinalize(&rc, pExplain); |
︙ | ︙ |
Changes to ext/fts5/test/fts5plan.test.
︙ | ︙ | |||
56 57 58 59 60 61 62 | QUERY PLAN |--SCAN TABLE f1 VIRTUAL TABLE INDEX 0: `--USE TEMP B-TREE FOR ORDER BY } do_eqp_test 1.5 { SELECT * FROM f1 WHERE rank MATCH ? | | | 56 57 58 59 60 61 62 63 64 65 | QUERY PLAN |--SCAN TABLE f1 VIRTUAL TABLE INDEX 0: `--USE TEMP B-TREE FOR ORDER BY } do_eqp_test 1.5 { SELECT * FROM f1 WHERE rank MATCH ? } {SCAN TABLE f1 VIRTUAL TABLE INDEX 2:} finish_test |
Changes to ext/rtree/rtree6.test.
︙ | ︙ | |||
70 71 72 73 74 75 76 | do_test rtree6-1.5 { rtree_strategy {SELECT * FROM t1,t2 WHERE k=+ii AND x1<10} } {C0} do_eqp_test rtree6.2.1 { SELECT * FROM t1,t2 WHERE k=+ii AND x1<10 } { | > | | > | | > | | > | | > | | > | | | 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 | do_test rtree6-1.5 { rtree_strategy {SELECT * FROM t1,t2 WHERE k=+ii AND x1<10} } {C0} do_eqp_test rtree6.2.1 { SELECT * FROM t1,t2 WHERE k=+ii AND x1<10 } { QUERY PLAN |--SCAN TABLE t1 VIRTUAL TABLE INDEX 2:C0 `--SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) } do_eqp_test rtree6.2.2 { SELECT * FROM t1,t2 WHERE k=ii AND x1<10 } { QUERY PLAN |--SCAN TABLE t1 VIRTUAL TABLE INDEX 2:C0 `--SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) } do_eqp_test rtree6.2.3 { SELECT * FROM t1,t2 WHERE k=ii } { QUERY PLAN |--SCAN TABLE t1 VIRTUAL TABLE INDEX 2: `--SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) } do_eqp_test rtree6.2.4.1 { SELECT * FROM t1,t2 WHERE v=+ii and x1<10 and x2>10 } { QUERY PLAN |--SCAN TABLE t1 VIRTUAL TABLE INDEX 2:C0E1 `--SEARCH TABLE t2 USING AUTOMATIC COVERING INDEX (v=?) } do_eqp_test rtree6.2.4.2 { SELECT * FROM t1,t2 WHERE v=10 and x1<10 and x2>10 } { QUERY PLAN |--SCAN TABLE t1 VIRTUAL TABLE INDEX 2:C0E1 `--SEARCH TABLE t2 USING AUTOMATIC PARTIAL COVERING INDEX (v=?) } do_eqp_test rtree6.2.5 { SELECT * FROM t1,t2 WHERE k=ii AND x1<v } { QUERY PLAN |--SCAN TABLE t1 VIRTUAL TABLE INDEX 2: `--SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) } do_execsql_test rtree6-3.1 { CREATE VIRTUAL TABLE t3 USING rtree(id, x1, x2, y1, y2); INSERT INTO t3 VALUES(NULL, 1, 1, 2, 2); SELECT * FROM t3 WHERE x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND |
︙ | ︙ |
Changes to ext/rtree/rtreeC.test.
︙ | ︙ | |||
25 26 27 28 29 30 31 | CREATE TABLE t(x, y); } do_eqp_test 1.1 { SELECT * FROM r_tree, t WHERE t.x>=min_x AND t.x<=max_x AND t.y>=min_y AND t.x<=max_y } { | > | | > | | > | | > | | | 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 | CREATE TABLE t(x, y); } do_eqp_test 1.1 { SELECT * FROM r_tree, t WHERE t.x>=min_x AND t.x<=max_x AND t.y>=min_y AND t.x<=max_y } { QUERY PLAN |--SCAN TABLE t `--SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:D3B2D1B0 } do_eqp_test 1.2 { SELECT * FROM t, r_tree WHERE t.x>=min_x AND t.x<=max_x AND t.y>=min_y AND t.x<=max_y } { QUERY PLAN |--SCAN TABLE t `--SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:D3B2D1B0 } do_eqp_test 1.3 { SELECT * FROM t, r_tree WHERE t.x>=min_x AND t.x<=max_x AND t.y>=min_y AND ?<=max_y } { QUERY PLAN |--SCAN TABLE t `--SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:D3B2D1B0 } do_eqp_test 1.5 { SELECT * FROM t, r_tree } { QUERY PLAN |--SCAN TABLE r_tree VIRTUAL TABLE INDEX 2: `--SCAN TABLE t } do_execsql_test 2.0 { INSERT INTO t VALUES(0, 0); INSERT INTO t VALUES(0, 1); INSERT INTO t VALUES(0, 2); INSERT INTO t VALUES(0, 3); |
︙ | ︙ | |||
78 79 80 81 82 83 84 | db close sqlite3 db test.db do_eqp_test 2.1 { SELECT * FROM r_tree, t WHERE t.x>=min_x AND t.x<=max_x AND t.y>=min_y AND t.x<=max_y } { | > | | > | | > | | > | | > | | > | > > | | > | | | 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 | db close sqlite3 db test.db do_eqp_test 2.1 { SELECT * FROM r_tree, t WHERE t.x>=min_x AND t.x<=max_x AND t.y>=min_y AND t.x<=max_y } { QUERY PLAN |--SCAN TABLE t `--SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:D3B2D1B0 } do_eqp_test 2.2 { SELECT * FROM t, r_tree WHERE t.x>=min_x AND t.x<=max_x AND t.y>=min_y AND t.x<=max_y } { QUERY PLAN |--SCAN TABLE t `--SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:D3B2D1B0 } do_eqp_test 2.3 { SELECT * FROM t, r_tree WHERE t.x>=min_x AND t.x<=max_x AND t.y>=min_y AND ?<=max_y } { QUERY PLAN |--SCAN TABLE t `--SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:D3B2D1B0 } do_eqp_test 2.5 { SELECT * FROM t, r_tree } { QUERY PLAN |--SCAN TABLE r_tree VIRTUAL TABLE INDEX 2: `--SCAN TABLE t } #------------------------------------------------------------------------- # Test that the special CROSS JOIN handling works with rtree tables. # do_execsql_test 3.1 { CREATE TABLE t1(x); CREATE TABLE t2(y); CREATE VIRTUAL TABLE t3 USING rtree(z, x1,x2, y1,y2); } do_eqp_test 3.2.1 { SELECT * FROM t1 CROSS JOIN t2 } { QUERY PLAN |--SCAN TABLE t1 `--SCAN TABLE t2 } do_eqp_test 3.2.2 { SELECT * FROM t2 CROSS JOIN t1 } { QUERY PLAN |--SCAN TABLE t2 `--SCAN TABLE t1 } do_eqp_test 3.3.1 { SELECT * FROM t1 CROSS JOIN t3 } { QUERY PLAN |--SCAN TABLE t1 `--SCAN TABLE t3 VIRTUAL TABLE INDEX 2: } do_eqp_test 3.3.2 { SELECT * FROM t3 CROSS JOIN t1 } { QUERY PLAN |--SCAN TABLE t3 VIRTUAL TABLE INDEX 2: `--SCAN TABLE t1 } #-------------------------------------------------------------------- # Test that LEFT JOINs are not reordered if the right-hand-side is # a virtual table. # reset_db |
︙ | ︙ | |||
185 186 187 188 189 190 191 | # First test a query with no ANALYZE data at all. The outer loop is # real table "t1". # do_eqp_test 5.2 { SELECT * FROM t1, rt WHERE x==id; } { | > | | > | | > | | | 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 | # First test a query with no ANALYZE data at all. The outer loop is # real table "t1". # do_eqp_test 5.2 { SELECT * FROM t1, rt WHERE x==id; } { QUERY PLAN |--SCAN TABLE t1 `--SCAN TABLE rt VIRTUAL TABLE INDEX 1: } # Now create enough ANALYZE data to tell SQLite that virtual table "rt" # contains very few rows. This causes it to move "rt" to the outer loop. # do_execsql_test 5.3 { ANALYZE; DELETE FROM sqlite_stat1 WHERE tbl='t1'; } db close sqlite3 db test.db do_eqp_test 5.4 { SELECT * FROM t1, rt WHERE x==id; } { QUERY PLAN |--SCAN TABLE rt VIRTUAL TABLE INDEX 2: `--SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (x=?) } # Delete the ANALYZE data. "t1" should be the outer loop again. # do_execsql_test 5.5 { DROP TABLE sqlite_stat1; } db close sqlite3 db test.db do_eqp_test 5.6 { SELECT * FROM t1, rt WHERE x==id; } { QUERY PLAN |--SCAN TABLE t1 `--SCAN TABLE rt VIRTUAL TABLE INDEX 1: } # This time create and attach a database that contains ANALYZE data for # tables of the same names as those used internally by virtual table # "rt". Check that the rtree module is not fooled into using this data. # Table "t1" should remain the outer loop. # |
︙ | ︙ | |||
237 238 239 240 241 242 243 | db close sqlite3 db test.db execsql { ATTACH 'test.db2' AS aux; } } {} do_eqp_test 5.8 { SELECT * FROM t1, rt WHERE x==id; } { | > | | | 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 | db close sqlite3 db test.db execsql { ATTACH 'test.db2' AS aux; } } {} do_eqp_test 5.8 { SELECT * FROM t1, rt WHERE x==id; } { QUERY PLAN |--SCAN TABLE t1 `--SCAN TABLE rt VIRTUAL TABLE INDEX 1: } #-------------------------------------------------------------------- # Test that having a second connection drop the sqlite_stat1 table # before it is required by rtreeConnect() does not cause problems. # ifcapable rtree { |
︙ | ︙ | |||
295 296 297 298 299 300 301 | INSERT INTO rt VALUES(1, 2, 7, 12, 14); -- Not a hit INSERT INTO rt VALUES(2, 2, 7, 8, 12); -- A hit! INSERT INTO rt VALUES(3, 7, 11, 8, 12); -- Not a hit! INSERT INTO rt VALUES(4, 5, 5, 10, 10); -- A hit! } | | | | > | | | > > | | | | > | | | > > | | | > | 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 | INSERT INTO rt VALUES(1, 2, 7, 12, 14); -- Not a hit INSERT INTO rt VALUES(2, 2, 7, 8, 12); -- A hit! INSERT INTO rt VALUES(3, 7, 11, 8, 12); -- Not a hit! INSERT INTO rt VALUES(4, 5, 5, 10, 10); -- A hit! } proc do_eqp_execsql_test {tn sql res1 res2} { do_eqp_test $tn.1 $sql $res1 do_execsql_test $tn.2 $sql $res2 } do_eqp_execsql_test 7.1 { SELECT id FROM xdir, rt, ydir ON (y1 BETWEEN ymin AND ymax) WHERE (x1 BETWEEN xmin AND xmax); } { QUERY PLAN |--SCAN TABLE xdir |--SCAN TABLE ydir `--SCAN TABLE rt VIRTUAL TABLE INDEX 2:B2D3B0D1 } { 2 4 } do_eqp_execsql_test 7.2 { SELECT * FROM xdir, rt LEFT JOIN ydir ON (y1 BETWEEN ymin AND ymax) WHERE (x1 BETWEEN xmin AND xmax); } { QUERY PLAN |--SCAN TABLE xdir |--SCAN TABLE rt VIRTUAL TABLE INDEX 2:B0D1 `--SCAN TABLE ydir } { 5 1 2 7 12 14 {} 5 2 2 7 8 12 10 5 4 5 5 10 10 10 } do_eqp_execsql_test 7.3 { SELECT id FROM xdir, rt CROSS JOIN ydir ON (y1 BETWEEN ymin AND ymax) WHERE (x1 BETWEEN xmin AND xmax); } { QUERY PLAN |--SCAN TABLE xdir |--SCAN TABLE rt VIRTUAL TABLE INDEX 2:B0D1 `--SCAN TABLE ydir } { 2 4 } do_eqp_execsql_test 7.4 { SELECT id FROM rt, xdir CROSS JOIN ydir ON (y1 BETWEEN ymin AND ymax) WHERE (x1 BETWEEN xmin AND xmax); } { QUERY PLAN |--SCAN TABLE xdir |--SCAN TABLE rt VIRTUAL TABLE INDEX 2:B0D1 `--SCAN TABLE ydir } { 2 4 } finish_test |
Changes to test/analyze4.test.
︙ | ︙ | |||
34 35 36 37 38 39 40 | INSERT INTO t1 SELECT a+32, b FROM t1; INSERT INTO t1 SELECT a+64, b FROM t1; ANALYZE; } # Should choose the t1a index since it is more specific than t1b. db eval {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=5 AND b IS NULL} | | | 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | INSERT INTO t1 SELECT a+32, b FROM t1; INSERT INTO t1 SELECT a+64, b FROM t1; ANALYZE; } # Should choose the t1a index since it is more specific than t1b. db eval {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=5 AND b IS NULL} } {/*SEARCH TABLE t1 USING INDEX t1a (a=?)*/} # Verify that the t1b index shows that it does not narrow down the # search any at all. # do_test analyze4-1.1 { db eval { SELECT idx, stat FROM sqlite_stat1 WHERE tbl='t1' ORDER BY idx; |
︙ | ︙ |
Changes to test/analyze6.test.
︙ | ︙ | |||
57 58 59 60 61 62 63 | # The lowest cost plan is to scan CAT and for each integer there, do a single # lookup of the first corresponding entry in EV then read off the equal values # in EV. (Prior to the 2011-03-04 enhancement to where.c, this query would # have used EV for the outer loop instead of CAT - which was about 3x slower.) # do_test analyze6-1.1 { eqp {SELECT count(*) FROM ev, cat WHERE x=y} | | | | > > > | > | | | | | | | | | | 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 | # The lowest cost plan is to scan CAT and for each integer there, do a single # lookup of the first corresponding entry in EV then read off the equal values # in EV. (Prior to the 2011-03-04 enhancement to where.c, this query would # have used EV for the outer loop instead of CAT - which was about 3x slower.) # do_test analyze6-1.1 { eqp {SELECT count(*) FROM ev, cat WHERE x=y} } {/*SCAN TABLE cat USING COVERING INDEX catx*SEARCH TABLE ev USING COVERING INDEX evy (y=?)*/} # The same plan is chosen regardless of the order of the tables in the # FROM clause. # do_eqp_test analyze6-1.2 { SELECT count(*) FROM cat, ev WHERE x=y } { QUERY PLAN |--SCAN TABLE cat USING COVERING INDEX catx `--SEARCH TABLE ev USING COVERING INDEX evy (y=?) } # Ticket [83ea97620bd3101645138b7b0e71c12c5498fe3d] 2011-03-30 # If ANALYZE is run on an empty table, make sure indices are used # on the table. # do_test analyze6-2.1 { execsql { CREATE TABLE t201(x INTEGER PRIMARY KEY, y UNIQUE, z); CREATE INDEX t201z ON t201(z); ANALYZE; } eqp {SELECT * FROM t201 WHERE z=5} } {/*SEARCH TABLE t201 USING INDEX t201z (z=?)*/} do_test analyze6-2.2 { eqp {SELECT * FROM t201 WHERE y=5} } {/*SEARCH TABLE t201 USING INDEX sqlite_autoindex_t201_1 (y=?)*/} do_test analyze6-2.3 { eqp {SELECT * FROM t201 WHERE x=5} } {/*SEARCH TABLE t201 USING INTEGER PRIMARY KEY (rowid=?)*/} do_test analyze6-2.4 { execsql { INSERT INTO t201 VALUES(1,2,3),(2,3,4),(3,4,5); ANALYZE t201; } eqp {SELECT * FROM t201 WHERE z=5} } {/*SEARCH TABLE t201 USING INDEX t201z (z=?)*/} do_test analyze6-2.5 { eqp {SELECT * FROM t201 WHERE y=5} } {/*SEARCH TABLE t201 USING INDEX sqlite_autoindex_t201_1 (y=?)*/} do_test analyze6-2.6 { eqp {SELECT * FROM t201 WHERE x=5} } {/*SEARCH TABLE t201 USING INTEGER PRIMARY KEY (rowid=?)*/} do_test analyze6-2.7 { execsql { INSERT INTO t201 VALUES(4,5,7); INSERT INTO t201 SELECT x+100, y+100, z+100 FROM t201; INSERT INTO t201 SELECT x+200, y+200, z+200 FROM t201; INSERT INTO t201 SELECT x+400, y+400, z+400 FROM t201; ANALYZE t201; } eqp {SELECT * FROM t201 WHERE z=5} } {/*SEARCH TABLE t201 USING INDEX t201z (z=?)*/} do_test analyze6-2.8 { eqp {SELECT * FROM t201 WHERE y=5} } {/*SEARCH TABLE t201 USING INDEX sqlite_autoindex_t201_1 (y=?)*/} do_test analyze6-2.9 { eqp {SELECT * FROM t201 WHERE x=5} } {/*SEARCH TABLE t201 USING INTEGER PRIMARY KEY (rowid=?)*/} finish_test |
Changes to test/analyze7.test.
︙ | ︙ | |||
33 34 35 36 37 38 39 | CREATE INDEX t1b ON t1(b); CREATE INDEX t1cd ON t1(c,d); CREATE VIRTUAL TABLE nums USING wholenumber; INSERT INTO t1 SELECT value, value, value/100, value FROM nums WHERE value BETWEEN 1 AND 256; EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123; } | | | | | | | | | | | | | | | | | | 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 | CREATE INDEX t1b ON t1(b); CREATE INDEX t1cd ON t1(c,d); CREATE VIRTUAL TABLE nums USING wholenumber; INSERT INTO t1 SELECT value, value, value/100, value FROM nums WHERE value BETWEEN 1 AND 256; EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123; } } {/*SEARCH TABLE t1 USING INDEX t1a (a=?)*/} do_test analyze7-1.1 { execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;} } {/*SEARCH TABLE t1 USING INDEX t1b (b=?)*/} do_test analyze7-1.2 { execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;} } {/*SEARCH TABLE t1 USING INDEX t1cd (c=?)*/} # Run an analyze on one of the three indices. Verify that this # effects the row-count estimate on the one query that uses that # one index. # do_test analyze7-2.0 { execsql {ANALYZE t1a;} db cache flush execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123;} } {/*SEARCH TABLE t1 USING INDEX t1a (a=?)*/} do_test analyze7-2.1 { execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;} } {/*SEARCH TABLE t1 USING INDEX t1b (b=?)*/} do_test analyze7-2.2 { execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;} } {/*SEARCH TABLE t1 USING INDEX t1cd (c=?)*/} # Verify that since the query planner now things that t1a is more # selective than t1b, it prefers to use t1a. # do_test analyze7-2.3 { execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND b=123} } {/*SEARCH TABLE t1 USING INDEX t1a (a=?)*/} # Run an analysis on another of the three indices. Verify that this # new analysis works and does not disrupt the previous analysis. # do_test analyze7-3.0 { execsql {ANALYZE t1cd;} db cache flush; execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123;} } {/*SEARCH TABLE t1 USING INDEX t1a (a=?)*/} do_test analyze7-3.1 { execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;} } {/*SEARCH TABLE t1 USING INDEX t1b (b=?)*/} do_test analyze7-3.2.1 { execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=?;} } {/*SEARCH TABLE t1 USING INDEX t1cd (c=?)*/} ifcapable stat4||stat3 { # If ENABLE_STAT4 is defined, SQLite comes up with a different estimated # row count for (c=2) than it does for (c=?). do_test analyze7-3.2.2 { execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;} } {/*SEARCH TABLE t1 USING INDEX t1cd (c=?)*/} } else { # If ENABLE_STAT4 is not defined, the expected row count for (c=2) is the # same as that for (c=?). do_test analyze7-3.2.3 { execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;} } {/*SEARCH TABLE t1 USING INDEX t1cd (c=?)*/} } do_test analyze7-3.3 { execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND b=123} } {/*SEARCH TABLE t1 USING INDEX t1a (a=?)*/} ifcapable {!stat4 && !stat3} { do_test analyze7-3.4 { execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=123 AND b=123} } {/*SEARCH TABLE t1 USING INDEX t1b (b=?)*/} do_test analyze7-3.5 { execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND c=123} } {/*SEARCH TABLE t1 USING INDEX t1a (a=?)*/} } do_test analyze7-3.6 { execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=123 AND d=123 AND b=123} } {/*SEARCH TABLE t1 USING INDEX t1cd (c=? AND d=?)*/} finish_test |
Changes to test/analyze8.test.
︙ | ︙ | |||
57 58 59 60 61 62 63 | # with a==100. And so for those cases, choose the t1b index. # # Buf ro a==99 and a==101, there are far fewer rows so choose # the t1a index. # do_test 1.1 { eqp {SELECT * FROM t1 WHERE a=100 AND b=55} | | | | | | | | | | | | | 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 | # with a==100. And so for those cases, choose the t1b index. # # Buf ro a==99 and a==101, there are far fewer rows so choose # the t1a index. # do_test 1.1 { eqp {SELECT * FROM t1 WHERE a=100 AND b=55} } {/*SEARCH TABLE t1 USING INDEX t1b (b=?)*/} do_test 1.2 { eqp {SELECT * FROM t1 WHERE a=99 AND b=55} } {/*SEARCH TABLE t1 USING INDEX t1a (a=?)*/} do_test 1.3 { eqp {SELECT * FROM t1 WHERE a=101 AND b=55} } {/*SEARCH TABLE t1 USING INDEX t1a (a=?)*/} do_test 1.4 { eqp {SELECT * FROM t1 WHERE a=100 AND b=56} } {/*SEARCH TABLE t1 USING INDEX t1b (b=?)*/} do_test 1.5 { eqp {SELECT * FROM t1 WHERE a=99 AND b=56} } {/*SEARCH TABLE t1 USING INDEX t1a (a=?)*/} do_test 1.6 { eqp {SELECT * FROM t1 WHERE a=101 AND b=56} } {/*SEARCH TABLE t1 USING INDEX t1a (a=?)*/} do_test 2.1 { eqp {SELECT * FROM t1 WHERE a=100 AND b BETWEEN 50 AND 54} } {/*SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)*/} # There are many more values of c between 0 and 100000 than there are # between 800000 and 900000. So t1c is more selective for the latter # range. # # Test 3.2 is a little unstable. It depends on the planner estimating # that (b BETWEEN 30 AND 34) will match more rows than (c BETWEEN # 800000 AND 900000). Which is a pretty close call (50 vs. 32), so # the planner could get it wrong with an unlucky set of samples. This # case happens to work, but others ("b BETWEEN 40 AND 44" for example) # will fail. # do_execsql_test 3.0 { SELECT count(*) FROM t1 WHERE b BETWEEN 30 AND 34; SELECT count(*) FROM t1 WHERE c BETWEEN 0 AND 100000; SELECT count(*) FROM t1 WHERE c BETWEEN 800000 AND 900000; } {50 376 32} do_test 3.1 { eqp {SELECT * FROM t1 WHERE b BETWEEN 30 AND 34 AND c BETWEEN 0 AND 100000} } {/*SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)*/} do_test 3.2 { eqp {SELECT * FROM t1 WHERE b BETWEEN 30 AND 34 AND c BETWEEN 800000 AND 900000} } {/*SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)*/} do_test 3.3 { eqp {SELECT * FROM t1 WHERE a=100 AND c BETWEEN 0 AND 100000} } {/*SEARCH TABLE t1 USING INDEX t1a (a=?)*/} do_test 3.4 { eqp {SELECT * FROM t1 WHERE a=100 AND c BETWEEN 800000 AND 900000} } {/*SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)*/} finish_test |
Changes to test/autoindex1.test.
︙ | ︙ | |||
173 174 175 176 177 178 179 | # do_execsql_test autoindex1-500 { CREATE TABLE t501(a INTEGER PRIMARY KEY, b); CREATE TABLE t502(x INTEGER PRIMARY KEY, y); INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t501',null,'1000000'); INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t502',null,'1000'); ANALYZE sqlite_master; | < > > > | | | | < > | | | | < > | | | < | 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 | # do_execsql_test autoindex1-500 { CREATE TABLE t501(a INTEGER PRIMARY KEY, b); CREATE TABLE t502(x INTEGER PRIMARY KEY, y); INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t501',null,'1000000'); INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t502',null,'1000'); ANALYZE sqlite_master; } do_eqp_test autoindex1-500.1 { SELECT b FROM t501 WHERE t501.a IN (SELECT x FROM t502 WHERE y=?); } { QUERY PLAN |--SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?) `--LIST SUBQUERY `--SCAN TABLE t502 } do_eqp_test autoindex1-501 { SELECT b FROM t501 WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b); } { QUERY PLAN |--SCAN TABLE t501 `--CORRELATED LIST SUBQUERY `--SEARCH TABLE t502 USING AUTOMATIC COVERING INDEX (y=?) } do_eqp_test autoindex1-502 { SELECT b FROM t501 WHERE t501.a=123 AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b); } { QUERY PLAN |--SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?) `--CORRELATED LIST SUBQUERY `--SCAN TABLE t502 } # The following code checks a performance regression reported on the # mailing list on 2010-10-19. The problem is that the nRowEst field # of ephermeral tables was not being initialized correctly and so no # automatic index was being created for the emphemeral table when it was # used as part of a join. # |
︙ | ︙ | |||
253 254 255 256 257 258 259 | ON flock_owner (owner_change_date); CREATE INDEX fo_owner_person_id_index ON flock_owner (owner_person_id); CREATE INDEX sheep_org_flock_index ON sheep (originating_flock); CREATE INDEX sheep_reg_flock_index ON sheep (registering_flock); | < > > > > | | | | | | > > | > | | | 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 | ON flock_owner (owner_change_date); CREATE INDEX fo_owner_person_id_index ON flock_owner (owner_person_id); CREATE INDEX sheep_org_flock_index ON sheep (originating_flock); CREATE INDEX sheep_reg_flock_index ON sheep (registering_flock); } do_eqp_test autoindex1-600a { SELECT x.sheep_no, x.registering_flock, x.date_of_registration FROM sheep x LEFT JOIN (SELECT s.sheep_no, prev.flock_no, prev.owner_person_id, s.date_of_registration, prev.owner_change_date FROM sheep s JOIN flock_owner prev ON s.registering_flock = prev.flock_no AND (prev.owner_change_date <= s.date_of_registration || ' 00:00:00') WHERE NOT EXISTS (SELECT 'x' FROM flock_owner later WHERE prev.flock_no = later.flock_no AND later.owner_change_date > prev.owner_change_date AND later.owner_change_date <= s.date_of_registration||' 00:00:00') ) y ON x.sheep_no = y.sheep_no WHERE y.sheep_no IS NULL ORDER BY x.registering_flock; } { QUERY PLAN |--MATERIALIZE xxxxxx | |--SCAN TABLE sheep AS s | |--SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?) | `--CORRELATED SCALAR SUBQUERY | `--SEARCH TABLE flock_owner AS later USING COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date>? AND owner_change_date<?) |--SCAN TABLE sheep AS x USING INDEX sheep_reg_flock_index `--SEARCH SUBQUERY xxxxxx AS y USING AUTOMATIC COVERING INDEX (sheep_no=?) } do_execsql_test autoindex1-700 { CREATE TABLE t5(a, b, c); } do_eqp_test autoindex1-700a { SELECT a FROM t5 WHERE b=10 ORDER BY c; } { QUERY PLAN |--SCAN TABLE t5 `--USE TEMP B-TREE FOR ORDER BY } # The following checks a performance issue reported on the sqlite-dev # mailing list on 2013-01-10 # do_execsql_test autoindex1-800 { CREATE TABLE accounts( |
︙ | ︙ |
Changes to test/autoindex5.test.
︙ | ︙ | |||
80 81 82 83 84 85 86 | AND debian_bugs.note = package_notes.id ORDER BY debian_bugs.bug; } {} # The following query should use an automatic index for the view # in FROM clause of the subquery of the second result column. # | | < | | 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 | AND debian_bugs.note = package_notes.id ORDER BY debian_bugs.bug; } {} # The following query should use an automatic index for the view # in FROM clause of the subquery of the second result column. # do_eqp_test autoindex5-1.1 { SELECT st.bug_name, (SELECT ALL debian_cve.bug FROM debian_cve WHERE debian_cve.bug_name = st.bug_name ORDER BY debian_cve.bug), sp.release FROM source_package_status AS st, source_packages AS sp, bugs WHERE sp.rowid = st.package AND st.bug_name = bugs.name AND ( st.bug_name LIKE 'CVE-%' OR st.bug_name LIKE 'TEMP-%' ) AND ( sp.release = 'sid' OR sp.release = 'stretch' OR sp.release = 'jessie' OR sp.release = 'wheezy' OR sp.release = 'squeeze' ) ORDER BY sp.name, st.bug_name, sp.release, sp.subrelease; } {SEARCH SUBQUERY * USING AUTOMATIC COVERING INDEX (bug_name=?)} #------------------------------------------------------------------------- # Test that ticket [8a2adec1] has been fixed. # do_execsql_test 2.1 { CREATE TABLE one(o); INSERT INTO one DEFAULT VALUES; |
︙ | ︙ |
Changes to test/bestindex3.test.
︙ | ︙ | |||
141 142 143 144 145 146 147 | CREATE TABLE t2(x TEXT COLLATE nocase, y TEXT); CREATE INDEX t2x ON t2(x COLLATE nocase); CREATE INDEX t2y ON t2(y); } do_eqp_test 2.2 { SELECT * FROM t2 WHERE x LIKE 'abc%' OR y = 'def' | | | | | | | 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 | CREATE TABLE t2(x TEXT COLLATE nocase, y TEXT); CREATE INDEX t2x ON t2(x COLLATE nocase); CREATE INDEX t2y ON t2(y); } do_eqp_test 2.2 { SELECT * FROM t2 WHERE x LIKE 'abc%' OR y = 'def' } [string map {"\n " \n} { QUERY PLAN |--SEARCH TABLE t2 USING INDEX t2x (x>? AND x<?) `--SEARCH TABLE t2 USING INDEX t2y (y=?) }] } #------------------------------------------------------------------------- # Test that any PRIMARY KEY within a sqlite3_decl_vtab() CREATE TABLE # statement is currently ignored. # proc vvv_command {method args} { |
︙ | ︙ |
Changes to test/e_createtable.test.
︙ | ︙ | |||
1381 1382 1383 1384 1385 1386 1387 | # do_execsql_test 4.10.0 { CREATE TABLE t1(a, b PRIMARY KEY); CREATE TABLE t2(a, b, c, UNIQUE(b, c)); } do_createtable_tests 4.10 { 1 "EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 5" | | | | | 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 | # do_execsql_test 4.10.0 { CREATE TABLE t1(a, b PRIMARY KEY); CREATE TABLE t2(a, b, c, UNIQUE(b, c)); } do_createtable_tests 4.10 { 1 "EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 5" {/*SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (b=?)*/} 2 "EXPLAIN QUERY PLAN SELECT * FROM t2 ORDER BY b, c" {/*SCAN TABLE t2 USING INDEX sqlite_autoindex_t2_1*/} 3 "EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE b=10 AND c>10" {/*SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (b=? AND c>?)*/} } # EVIDENCE-OF: R-45493-35653 A CHECK constraint may be attached to a # column definition or specified as a table constraint. In practice it # makes no difference. # # All the tests that deal with CHECK constraints below (4.11.* and |
︙ | ︙ |
Changes to test/fts3aux1.test.
︙ | ︙ | |||
101 102 103 104 105 106 107 | db func rec rec # Use EQP to show that the WHERE expression "term='braid'" uses a different # index number (1) than "+term='braid'" (0). # do_execsql_test 2.1.1.1 { EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term='braid' | | | | 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 | db func rec rec # Use EQP to show that the WHERE expression "term='braid'" uses a different # index number (1) than "+term='braid'" (0). # do_execsql_test 2.1.1.1 { EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term='braid' } {/*SCAN TABLE terms VIRTUAL TABLE INDEX 1:*/} do_execsql_test 2.1.1.2 { EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term='braid' } {/*SCAN TABLE terms VIRTUAL TABLE INDEX 0:*/} # Now show that using "term='braid'" means the virtual table returns # only 1 row to SQLite, but "+term='braid'" means all 19 are returned. # do_test 2.1.2.1 { set cnt 0 execsql { SELECT * FROM terms_v WHERE rec('cnt', term) AND term='braid' } |
︙ | ︙ | |||
150 151 152 153 154 155 156 | # Special case: term=NULL # do_execsql_test 2.1.5 { SELECT * FROM terms WHERE term=NULL } {} do_execsql_test 2.2.1.1 { EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term>'brain' | | | | | | | | 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 | # Special case: term=NULL # do_execsql_test 2.1.5 { SELECT * FROM terms WHERE term=NULL } {} do_execsql_test 2.2.1.1 { EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term>'brain' } {/*SCAN TABLE terms VIRTUAL TABLE INDEX 2:*/} do_execsql_test 2.2.1.2 { EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term>'brain' } {/*SCAN TABLE terms VIRTUAL TABLE INDEX 0:*/} do_execsql_test 2.2.1.3 { EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term<'brain' } {/*SCAN TABLE terms VIRTUAL TABLE INDEX 4:*/} do_execsql_test 2.2.1.4 { EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term<'brain' } {/*SCAN TABLE terms VIRTUAL TABLE INDEX 0:*/} do_execsql_test 2.2.1.5 { EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term BETWEEN 'brags' AND 'brain' } {/*SCAN TABLE terms VIRTUAL TABLE INDEX 6:*/} do_execsql_test 2.2.1.6 { EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term BETWEEN 'brags' AND 'brain' } {/*SCAN TABLE terms VIRTUAL TABLE INDEX 0:*/} do_test 2.2.2.1 { set cnt 0 execsql { SELECT * FROM terms WHERE rec('cnt', term) AND term>'brain' } set cnt } {18} do_test 2.2.2.2 { |
︙ | ︙ | |||
331 332 333 334 335 336 337 | 5 1 "ORDER BY documents" 6 1 "ORDER BY documents DESC" 7 1 "ORDER BY occurrences ASC" 8 1 "ORDER BY occurrences" 9 1 "ORDER BY occurrences DESC" } { | | | > | 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 | 5 1 "ORDER BY documents" 6 1 "ORDER BY documents DESC" 7 1 "ORDER BY occurrences ASC" 8 1 "ORDER BY occurrences" 9 1 "ORDER BY occurrences DESC" } { set res {SCAN TABLE terms VIRTUAL TABLE INDEX 0:} if {$sort} { append res {*USE TEMP B-TREE FOR ORDER BY} } set res "/*$res*/" set sql "SELECT * FROM terms $orderby" do_execsql_test 2.3.1.$tn "EXPLAIN QUERY PLAN $sql" $res } #------------------------------------------------------------------------- # The next set of tests, fts3aux1-3.*, test error conditions in the |
︙ | ︙ | |||
399 400 401 402 403 404 405 | INSERT INTO x1 VALUES('f g h i j'); INSERT INTO x1 VALUES('k k l l a'); INSERT INTO x2 SELECT term FROM terms WHERE col = '*'; INSERT INTO x3 SELECT term FROM terms WHERE col = '*'; } | | > | > | | > > | | > > | | > > | | > | 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 | INSERT INTO x1 VALUES('f g h i j'); INSERT INTO x1 VALUES('k k l l a'); INSERT INTO x2 SELECT term FROM terms WHERE col = '*'; INSERT INTO x3 SELECT term FROM terms WHERE col = '*'; } proc do_plansql_test {tn sql r1 r2} { do_eqp_test $tn.eqp $sql $r1 do_execsql_test $tn $sql $r2 } do_plansql_test 4.2 { SELECT y FROM x2, terms WHERE y = term AND col = '*' } { QUERY PLAN |--SCAN TABLE x2 `--SCAN TABLE terms VIRTUAL TABLE INDEX 1: } { a b c d e f g h i j k l } do_plansql_test 4.3 { SELECT y FROM terms, x2 WHERE y = term AND col = '*' } { QUERY PLAN |--SCAN TABLE x2 `--SCAN TABLE terms VIRTUAL TABLE INDEX 1: } { a b c d e f g h i j k l } do_plansql_test 4.4 { SELECT y FROM x3, terms WHERE y = term AND col = '*' } { QUERY PLAN |--SCAN TABLE terms VIRTUAL TABLE INDEX 0: `--SEARCH TABLE x3 USING COVERING INDEX i1 (y=?) } { a b c d e f g h i j k l } do_plansql_test 4.5 { SELECT y FROM terms, x3 WHERE y = term AND occurrences>1 AND col = '*' } { QUERY PLAN |--SCAN TABLE terms VIRTUAL TABLE INDEX 0: `--SEARCH TABLE x3 USING COVERING INDEX i1 (y=?) } { a k l } #------------------------------------------------------------------------- # The following tests check that fts4aux can handle an fts table with an # odd name (one that requires quoting for use in SQL statements). And that # the argument to the fts4aux constructor is properly dequoted before use. |
︙ | ︙ |
Changes to test/selectD.test.
︙ | ︙ | |||
165 166 167 168 169 170 171 | SELECT * FROM t41 LEFT JOIN (SELECT count(*) AS cnt, x1.d FROM (t42 INNER JOIN t43 ON d=g) AS x1 WHERE x1.d>5 GROUP BY x1.d) AS x2 ON t41.b=x2.d; | | | 165 166 167 168 169 170 171 172 173 174 | SELECT * FROM t41 LEFT JOIN (SELECT count(*) AS cnt, x1.d FROM (t42 INNER JOIN t43 ON d=g) AS x1 WHERE x1.d>5 GROUP BY x1.d) AS x2 ON t41.b=x2.d; } {/*SEARCH SUBQUERY 0x* AS x2 USING AUTOMATIC*/} finish_test |
Changes to test/tkt-78e04e52ea.test.
︙ | ︙ | |||
37 38 39 40 41 42 43 | } {0 {} {} 0 {} 0 1 x CHAR(100) 0 {} 0} do_test tkt-78e04-1.3 { execsql { CREATE INDEX i1 ON ""("" COLLATE nocase); } } {} do_test tkt-78e04-1.4 { | < | < | | | | 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 | } {0 {} {} 0 {} 0 1 x CHAR(100) 0 {} 0} do_test tkt-78e04-1.3 { execsql { CREATE INDEX i1 ON ""("" COLLATE nocase); } } {} do_test tkt-78e04-1.4 { db eval {EXPLAIN QUERY PLAN SELECT "" FROM "" WHERE "" LIKE '1abc%';} } {/*SCAN TABLE USING COVERING INDEX i1*/} do_test tkt-78e04-1.5 { execsql { DROP TABLE ""; SELECT name FROM sqlite_master; } } {t2} do_test tkt-78e04-2.1 { execsql { CREATE INDEX "" ON t2(x); EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE x=5; } } {/*SEARCH TABLE t2 USING COVERING INDEX (x=?)*/} do_test tkt-78e04-2.2 { execsql { DROP INDEX ""; EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE x=2; } } {/*SCAN TABLE t2*/} finish_test |
Changes to test/tkt3442.test.
︙ | ︙ | |||
30 31 32 33 34 35 36 | id TEXT, node INTEGER ); CREATE UNIQUE INDEX ididx ON listhash(id); } } {} | < < < < < < < < < | | | | | | < < | | | | | 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 | id TEXT, node INTEGER ); CREATE UNIQUE INDEX ididx ON listhash(id); } } {} # These tests perform an EXPLAIN QUERY PLAN on both versions of the # SELECT referenced in ticket #3442 (both '5000' and "5000") # and verify that the query plan is the same. # do_eqp_test tkt3442-1.2 { SELECT node FROM listhash WHERE id='5000' LIMIT 1; } {SEARCH TABLE listhash USING INDEX ididx (id=?)} do_eqp_test tkt3442-1.3 { SELECT node FROM listhash WHERE id="5000" LIMIT 1; } {SEARCH TABLE listhash USING INDEX ididx (id=?)} # Some extra tests testing other permutations of 5000. # do_eqp_test tkt3442-1.4 { SELECT node FROM listhash WHERE id=5000 LIMIT 1; } {SEARCH TABLE listhash USING INDEX ididx (id=?)} do_test tkt3442-1.5 { catchsql { SELECT node FROM listhash WHERE id=[5000] LIMIT 1; } } {1 {no such column: 5000}} finish_test |
Changes to test/where3.test.
︙ | ︙ | |||
231 232 233 234 235 236 237 | CREATE TABLE t301(a INTEGER PRIMARY KEY,b,c); CREATE INDEX t301c ON t301(c); INSERT INTO t301 VALUES(1,2,3); INSERT INTO t301 VALUES(2,2,3); CREATE TABLE t302(x, y); INSERT INTO t302 VALUES(4,5); ANALYZE; | > > | > | | | < > | | | 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 | CREATE TABLE t301(a INTEGER PRIMARY KEY,b,c); CREATE INDEX t301c ON t301(c); INSERT INTO t301 VALUES(1,2,3); INSERT INTO t301 VALUES(2,2,3); CREATE TABLE t302(x, y); INSERT INTO t302 VALUES(4,5); ANALYZE; } do_eqp_test where3-3.0a { SELECT * FROM t302, t301 WHERE t302.x=5 AND t301.a=t302.y; } { QUERY PLAN |--SCAN TABLE t302 `--SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) } do_eqp_test where3-3.1 { SELECT * FROM t301, t302 WHERE t302.x=5 AND t301.a=t302.y; } { QUERY PLAN |--SCAN TABLE t302 `--SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) } do_execsql_test where3-3.2 { SELECT * FROM t301 WHERE c=3 AND a IS NULL; } {} do_execsql_test where3-3.3 { SELECT * FROM t301 WHERE c=3 AND a IS NOT NULL; } {1 2 3 2 2 3} |
︙ | ︙ | |||
304 305 306 307 308 309 310 | fk INTEGER DEFAULT NULL, parent INTEGER, position INTEGER, title LONGVARCHAR, keyword_id INTEGER, folder_type TEXT, dateAdded INTEGER, lastModified INTEGER); CREATE INDEX bbb_111 ON bbb (fk, type); CREATE INDEX bbb_222 ON bbb (parent, position); CREATE INDEX bbb_333 ON bbb (fk, lastModified); | | | > | | | | < > | | | | < > | | | | < > | | | | 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 | fk INTEGER DEFAULT NULL, parent INTEGER, position INTEGER, title LONGVARCHAR, keyword_id INTEGER, folder_type TEXT, dateAdded INTEGER, lastModified INTEGER); CREATE INDEX bbb_111 ON bbb (fk, type); CREATE INDEX bbb_222 ON bbb (parent, position); CREATE INDEX bbb_333 ON bbb (fk, lastModified); } do_eqp_test where3-5.0a { SELECT bbb.title AS tag_title FROM aaa JOIN bbb ON bbb.id = aaa.parent WHERE aaa.fk = 'constant' AND LENGTH(bbb.title) > 0 AND bbb.parent = 4 ORDER BY bbb.title COLLATE NOCASE ASC; } { QUERY PLAN |--SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) |--SEARCH TABLE bbb USING INTEGER PRIMARY KEY (rowid=?) `--USE TEMP B-TREE FOR ORDER BY } do_eqp_test where3-5.1 { SELECT bbb.title AS tag_title FROM aaa JOIN aaa AS bbb ON bbb.id = aaa.parent WHERE aaa.fk = 'constant' AND LENGTH(bbb.title) > 0 AND bbb.parent = 4 ORDER BY bbb.title COLLATE NOCASE ASC; } { QUERY PLAN |--SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) |--SEARCH TABLE aaa AS bbb USING INTEGER PRIMARY KEY (rowid=?) `--USE TEMP B-TREE FOR ORDER BY } do_eqp_test where3-5.2 { SELECT bbb.title AS tag_title FROM bbb JOIN aaa ON bbb.id = aaa.parent WHERE aaa.fk = 'constant' AND LENGTH(bbb.title) > 0 AND bbb.parent = 4 ORDER BY bbb.title COLLATE NOCASE ASC; } { QUERY PLAN |--SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) |--SEARCH TABLE bbb USING INTEGER PRIMARY KEY (rowid=?) `--USE TEMP B-TREE FOR ORDER BY } do_eqp_test where3-5.3 { SELECT bbb.title AS tag_title FROM aaa AS bbb JOIN aaa ON bbb.id = aaa.parent WHERE aaa.fk = 'constant' AND LENGTH(bbb.title) > 0 AND bbb.parent = 4 ORDER BY bbb.title COLLATE NOCASE ASC; } { QUERY PLAN |--SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) |--SEARCH TABLE aaa AS bbb USING INTEGER PRIMARY KEY (rowid=?) `--USE TEMP B-TREE FOR ORDER BY } # Name resolution with NATURAL JOIN and USING # do_test where3-6.setup { db eval { CREATE TABLE t6w(a, w); |
︙ | ︙ |