Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Modifications to test cases to account for new EXPLAIN QUERY PLAN output. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | experimental |
| Files: | files | file ages | folders |
| SHA1: |
30904ef8412348464e893e9e1551ef22 |
| User & Date: | dan 2010-11-11 10:36:26 |
Context
|
2010-11-11
| ||
| 11:43 | Fix a bug in the EXPLAIN QUERY PLAN code. (check-in: 7ae06895 user: dan tags: experimental) | |
| 10:36 | Modifications to test cases to account for new EXPLAIN QUERY PLAN output. (check-in: 30904ef8 user: dan tags: experimental) | |
|
2010-11-09
| ||
| 17:49 | Merge with latest trunk changes. (check-in: 4b5c93bc user: dan tags: experimental) | |
Changes
Changes to test/autoindex1.test.
136 136 } 137 137 } {4087} 138 138 139 139 # Ticket [8011086c85c6c404014c947fcf3eb9f42b184a0d] from 2010-07-08 140 140 # Make sure automatic indices are not created for the RHS of an IN expression 141 141 # that is not a correlated subquery. 142 142 # 143 -do_test autoindex1-500 { 144 - db eval { 145 - CREATE TABLE t501(a INTEGER PRIMARY KEY, b); 146 - CREATE TABLE t502(x INTEGER PRIMARY KEY, y); 147 - EXPLAIN QUERY PLAN 148 - SELECT b FROM t501 149 - WHERE t501.a IN (SELECT x FROM t502 WHERE y=?); 150 - } 151 -} {0 0 {TABLE t501 USING PRIMARY KEY} 0 0 {TABLE t502}} 152 -do_test autoindex1-501 { 153 - db eval { 154 - EXPLAIN QUERY PLAN 155 - SELECT b FROM t501 156 - WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b); 157 - } 158 -} {0 0 {TABLE t501} 0 0 {TABLE t502 WITH AUTOMATIC INDEX}} 159 -do_test autoindex1-502 { 160 - db eval { 161 - EXPLAIN QUERY PLAN 162 - SELECT b FROM t501 163 - WHERE t501.a=123 164 - AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b); 165 - } 166 -} {0 0 {TABLE t501 USING PRIMARY KEY} 0 0 {TABLE t502}} 143 +do_execsql_test autoindex1-500 { 144 + CREATE TABLE t501(a INTEGER PRIMARY KEY, b); 145 + CREATE TABLE t502(x INTEGER PRIMARY KEY, y); 146 + EXPLAIN QUERY PLAN 147 + SELECT b FROM t501 148 + WHERE t501.a IN (SELECT x FROM t502 WHERE y=?); 149 +} { 150 + 0 0 0 {SCAN TABLE t501 BY INTEGER PRIMARY KEY (rowid=?) (~25 rows)} 151 + 0 0 0 {EXECUTE LIST SUBQUERY 1} 152 + 1 0 0 {SCAN TABLE t502 (~100000 rows)} 153 +} 154 +do_execsql_test autoindex1-501 { 155 + EXPLAIN QUERY PLAN 156 + SELECT b FROM t501 157 + WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b); 158 +} { 159 + 0 0 0 {SCAN TABLE t501 (~500000 rows)} 160 + 0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 161 + 1 0 0 {SCAN TABLE t502 BY AUTOMATIC COVERING INDEX (y=?) (~7 rows)} 162 +} 163 +do_execsql_test autoindex1-502 { 164 + EXPLAIN QUERY PLAN 165 + SELECT b FROM t501 166 + WHERE t501.a=123 167 + AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b); 168 +} { 169 + 0 0 0 {SCAN TABLE t501 BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 170 + 0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 171 + 1 0 0 {SCAN TABLE t502 (~100000 rows)} 172 +} 167 173 168 174 169 175 # The following code checks a performance regression reported on the 170 176 # mailing list on 2010-10-19. The problem is that the nRowEst field 171 177 # of ephermeral tables was not being initialized correctly and so no 172 178 # automatic index was being created for the emphemeral table when it was 173 179 # used as part of a join. 174 180 # 175 -do_test autoindex1-600 { 176 - db eval { 177 - CREATE TABLE flock_owner( 178 - owner_rec_id INTEGER CONSTRAINT flock_owner_key PRIMARY KEY, 179 - flock_no VARCHAR(6) NOT NULL REFERENCES flock (flock_no), 180 - owner_person_id INTEGER NOT NULL REFERENCES person (person_id), 181 - owner_change_date TEXT, last_changed TEXT NOT NULL, 182 - CONSTRAINT fo_owner_date UNIQUE (flock_no, owner_change_date) 183 - ); 184 - CREATE TABLE sheep ( 185 - Sheep_No char(7) NOT NULL, 186 - Date_of_Birth char(8), 187 - Sort_DoB text, 188 - Flock_Book_Vol char(2), 189 - Breeder_No char(6), 190 - Breeder_Person integer, 191 - Originating_Flock char(6), 192 - Registering_Flock char(6), 193 - Tag_Prefix char(9), 194 - Tag_No char(15), 195 - Sort_Tag_No integer, 196 - Breeders_Temp_Tag char(15), 197 - Sex char(1), 198 - Sheep_Name char(32), 199 - Sire_No char(7), 200 - Dam_No char(7), 201 - Register_Code char(1), 202 - Colour char(48), 203 - Colour_Code char(2), 204 - Pattern_Code char(8), 205 - Horns char(1), 206 - Litter_Size char(1), 207 - Coeff_of_Inbreeding real, 208 - Date_of_Registration text, 209 - Date_Last_Changed text, 210 - UNIQUE(Sheep_No)); 211 - CREATE INDEX fo_flock_no_index 212 - ON flock_owner (flock_no); 213 - CREATE INDEX fo_owner_change_date_index 214 - ON flock_owner (owner_change_date); 215 - CREATE INDEX fo_owner_person_id_index 216 - ON flock_owner (owner_person_id); 217 - CREATE INDEX sheep_org_flock_index 218 - ON sheep (originating_flock); 219 - CREATE INDEX sheep_reg_flock_index 220 - ON sheep (registering_flock); 221 - EXPLAIN QUERY PLAN 222 - SELECT x.sheep_no, x.registering_flock, x.date_of_registration 223 - FROM sheep x LEFT JOIN 224 - (SELECT s.sheep_no, prev.flock_no, prev.owner_person_id, 225 - s.date_of_registration, prev.owner_change_date 226 - FROM sheep s JOIN flock_owner prev ON s.registering_flock = 227 - prev.flock_no 228 - AND (prev.owner_change_date <= s.date_of_registration || ' 00:00:00') 229 - WHERE NOT EXISTS 230 - (SELECT 'x' FROM flock_owner later 231 - WHERE prev.flock_no = later.flock_no 232 - AND later.owner_change_date > prev.owner_change_date 233 - AND later.owner_change_date <= s.date_of_registration||' 00:00:00') 234 - ) y ON x.sheep_no = y.sheep_no 235 - WHERE y.sheep_no IS NULL 236 - ORDER BY x.registering_flock; 237 - } 238 -} {0 0 {TABLE sheep AS s} 1 1 {TABLE flock_owner AS prev WITH INDEX sqlite_autoindex_flock_owner_1} 0 0 {TABLE flock_owner AS later WITH INDEX sqlite_autoindex_flock_owner_1} 0 0 {TABLE sheep AS x WITH INDEX sheep_reg_flock_index ORDER BY} 1 1 {TABLE AS y WITH AUTOMATIC INDEX}} 181 +do_execsql_test autoindex1-600 { 182 + CREATE TABLE flock_owner( 183 + owner_rec_id INTEGER CONSTRAINT flock_owner_key PRIMARY KEY, 184 + flock_no VARCHAR(6) NOT NULL REFERENCES flock (flock_no), 185 + owner_person_id INTEGER NOT NULL REFERENCES person (person_id), 186 + owner_change_date TEXT, last_changed TEXT NOT NULL, 187 + CONSTRAINT fo_owner_date UNIQUE (flock_no, owner_change_date) 188 + ); 189 + CREATE TABLE sheep ( 190 + Sheep_No char(7) NOT NULL, 191 + Date_of_Birth char(8), 192 + Sort_DoB text, 193 + Flock_Book_Vol char(2), 194 + Breeder_No char(6), 195 + Breeder_Person integer, 196 + Originating_Flock char(6), 197 + Registering_Flock char(6), 198 + Tag_Prefix char(9), 199 + Tag_No char(15), 200 + Sort_Tag_No integer, 201 + Breeders_Temp_Tag char(15), 202 + Sex char(1), 203 + Sheep_Name char(32), 204 + Sire_No char(7), 205 + Dam_No char(7), 206 + Register_Code char(1), 207 + Colour char(48), 208 + Colour_Code char(2), 209 + Pattern_Code char(8), 210 + Horns char(1), 211 + Litter_Size char(1), 212 + Coeff_of_Inbreeding real, 213 + Date_of_Registration text, 214 + Date_Last_Changed text, 215 + UNIQUE(Sheep_No)); 216 + CREATE INDEX fo_flock_no_index 217 + ON flock_owner (flock_no); 218 + CREATE INDEX fo_owner_change_date_index 219 + ON flock_owner (owner_change_date); 220 + CREATE INDEX fo_owner_person_id_index 221 + ON flock_owner (owner_person_id); 222 + CREATE INDEX sheep_org_flock_index 223 + ON sheep (originating_flock); 224 + CREATE INDEX sheep_reg_flock_index 225 + ON sheep (registering_flock); 226 + EXPLAIN QUERY PLAN 227 + SELECT x.sheep_no, x.registering_flock, x.date_of_registration 228 + FROM sheep x LEFT JOIN 229 + (SELECT s.sheep_no, prev.flock_no, prev.owner_person_id, 230 + s.date_of_registration, prev.owner_change_date 231 + FROM sheep s JOIN flock_owner prev ON s.registering_flock = 232 + prev.flock_no 233 + AND (prev.owner_change_date <= s.date_of_registration || ' 00:00:00') 234 + WHERE NOT EXISTS 235 + (SELECT 'x' FROM flock_owner later 236 + WHERE prev.flock_no = later.flock_no 237 + AND later.owner_change_date > prev.owner_change_date 238 + AND later.owner_change_date <= s.date_of_registration||' 00:00:00') 239 + ) y ON x.sheep_no = y.sheep_no 240 + WHERE y.sheep_no IS NULL 241 + ORDER BY x.registering_flock; 242 +} { 243 + 1 0 0 {SCAN TABLE sheep AS s (~1000000 rows)} 244 + 1 1 1 {SCAN TABLE flock_owner AS prev BY INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND flock_no<?) (~2 rows)} 245 + 1 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2} 246 + 2 0 0 {SCAN TABLE flock_owner AS later BY COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND flock_no>? AND flock_no<?) (~1 rows)} 247 + 0 0 0 {SCAN TABLE sheep AS x BY INDEX sheep_reg_flock_index (~1000000 rows)} 248 + 0 1 1 {SCAN SUBQUERY 1 AS y BY AUTOMATIC COVERING INDEX (sheep_no=?) (~7 rows)} 249 +} 239 250 240 251 finish_test
Changes to test/e_createtable.test.
1369 1369 # 1370 1370 do_execsql_test 4.10.0 { 1371 1371 CREATE TABLE t1(a, b PRIMARY KEY); 1372 1372 CREATE TABLE t2(a, b, c, UNIQUE(b, c)); 1373 1373 } 1374 1374 do_createtable_tests 4.10 { 1375 1375 1 "EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 5" 1376 - {0 0 {TABLE t1 WITH INDEX sqlite_autoindex_t1_1}} 1376 + {0 0 0 {SCAN TABLE t1 BY INDEX sqlite_autoindex_t1_1 (b=?) (~1 rows)}} 1377 1377 1378 1378 2 "EXPLAIN QUERY PLAN SELECT * FROM t2 ORDER BY b, c" 1379 - {0 0 {TABLE t2 WITH INDEX sqlite_autoindex_t2_1 ORDER BY}} 1379 + {0 0 0 {SCAN TABLE t2 BY INDEX sqlite_autoindex_t2_1 (~1000000 rows)}} 1380 1380 1381 1381 3 "EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE b=10 AND c>10" 1382 - {0 0 {TABLE t2 WITH INDEX sqlite_autoindex_t2_1}} 1382 + {0 0 0 {SCAN TABLE t2 BY INDEX sqlite_autoindex_t2_1 (b=? AND b>?) (~3 rows)}} 1383 1383 } 1384 1384 1385 1385 # EVIDENCE-OF: R-45493-35653 A CHECK constraint may be attached to a 1386 1386 # column definition or specified as a table constraint. In practice it 1387 1387 # makes no difference. 1388 1388 # 1389 1389 # All the tests that deal with CHECK constraints below (4.11.* and
Changes to test/e_fkey.test.
964 964 trackid INTEGER, 965 965 trackname TEXT, 966 966 trackartist INTEGER, 967 967 FOREIGN KEY(trackartist) REFERENCES artist(artistid) 968 968 ); 969 969 } 970 970 } {} 971 -do_test e_fkey-25.2 { 972 - execsql { 973 - PRAGMA foreign_keys = OFF; 974 - EXPLAIN QUERY PLAN DELETE FROM artist WHERE 1; 975 - EXPLAIN QUERY PLAN SELECT rowid FROM track WHERE trackartist = ?; 976 - } 977 -} {0 0 {TABLE artist} 0 0 {TABLE track}} 978 -do_test e_fkey-25.3 { 979 - execsql { 980 - PRAGMA foreign_keys = ON; 981 - EXPLAIN QUERY PLAN DELETE FROM artist WHERE 1; 982 - } 983 -} {0 0 {TABLE artist} 0 0 {TABLE track}} 971 +do_execsql_test e_fkey-25.2 { 972 + PRAGMA foreign_keys = OFF; 973 + EXPLAIN QUERY PLAN DELETE FROM artist WHERE 1; 974 + EXPLAIN QUERY PLAN SELECT rowid FROM track WHERE trackartist = ?; 975 +} { 976 + 0 0 0 {SCAN TABLE artist (~1000000 rows)} 977 + 0 0 0 {SCAN TABLE track (~100000 rows)} 978 +} 979 +do_execsql_test e_fkey-25.3 { 980 + PRAGMA foreign_keys = ON; 981 + EXPLAIN QUERY PLAN DELETE FROM artist WHERE 1; 982 +} { 983 + 0 0 0 {SCAN TABLE artist (~1000000 rows)} 984 + 0 0 0 {SCAN TABLE track (~100000 rows)} 985 +} 984 986 do_test e_fkey-25.4 { 985 987 execsql { 986 988 INSERT INTO artist VALUES(5, 'artist 5'); 987 989 INSERT INTO artist VALUES(6, 'artist 6'); 988 990 INSERT INTO artist VALUES(7, 'artist 7'); 989 991 INSERT INTO track VALUES(1, 'track 1', 5); 990 992 INSERT INTO track VALUES(2, 'track 2', 6); ................................................................................ 1089 1091 ); 1090 1092 CREATE INDEX trackindex ON track(trackartist); 1091 1093 } 1092 1094 } {} 1093 1095 do_test e_fkey-27.2 { 1094 1096 eqp { INSERT INTO artist VALUES(?, ?) } 1095 1097 } {} 1096 -do_test e_fkey-27.3 { 1097 - eqp { UPDATE artist SET artistid = ?, artistname = ? } 1098 -} [list \ 1099 - 0 0 {TABLE artist} \ 1100 - 0 0 {TABLE track WITH INDEX trackindex} \ 1101 - 0 0 {TABLE track WITH INDEX trackindex} 1102 -] 1103 -do_test e_fkey-27.4 { 1104 - eqp { DELETE FROM artist } 1105 -} [list \ 1106 - 0 0 {TABLE artist} \ 1107 - 0 0 {TABLE track WITH INDEX trackindex} 1108 -] 1098 +do_execsql_test e_fkey-27.3 { 1099 + EXPLAIN QUERY PLAN UPDATE artist SET artistid = ?, artistname = ? 1100 +} { 1101 + 0 0 0 {SCAN TABLE artist (~1000000 rows)} 1102 + 0 0 0 {SCAN TABLE track BY COVERING INDEX trackindex (trackartist=?) (~10 rows)} 1103 + 0 0 0 {SCAN TABLE track BY COVERING INDEX trackindex (trackartist=?) (~10 rows)} 1104 +} 1105 +do_execsql_test e_fkey-27.4 { 1106 + EXPLAIN QUERY PLAN DELETE FROM artist 1107 +} { 1108 + 0 0 0 {SCAN TABLE artist (~1000000 rows)} 1109 + 0 0 0 {SCAN TABLE track BY COVERING INDEX trackindex (trackartist=?) (~10 rows)} 1110 +} 1109 1111 1110 1112 1111 1113 ########################################################################### 1112 1114 ### SECTION 4.1: Composite Foreign Key Constraints 1113 1115 ########################################################################### 1114 1116 1115 1117 #-------------------------------------------------------------------------
Changes to test/eqp.test.
183 183 1 0 0 {SCAN TABLE t1 (~1000000 rows)} 184 184 1 0 0 {USE TEMP B-TREE FOR ORDER BY} 185 185 2 0 0 {SCAN TABLE t2 BY INDEX t2i1 (~1000000 rows)} 186 186 0 0 0 {SCAN SUBQUERY 1 AS x1 (~1000000 rows)} 187 187 0 1 1 {SCAN SUBQUERY 2 AS x2 (~1000000 rows)} 188 188 0 0 0 {USE TEMP B-TREE FOR ORDER BY} 189 189 } 190 + 191 +det 3.3.1 { 192 + SELECT * FROM t1 WHERE y IN (SELECT y FROM t2) 193 +} { 194 + 0 0 0 {SCAN TABLE t1 (~100000 rows)} 195 + 0 0 0 {EXECUTE LIST SUBQUERY 1} 196 + 1 0 0 {SCAN TABLE t2 (~1000000 rows)} 197 +} 198 +det 3.3.2 { 199 + SELECT * FROM t1 WHERE y IN (SELECT y FROM t2 WHERE t1.x!=t2.x) 200 +} { 201 + 0 0 0 {SCAN TABLE t1 (~500000 rows)} 202 + 0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 203 + 1 0 0 {SCAN TABLE t2 (~500000 rows)} 204 +} 205 +det 3.3.3 { 206 + SELECT * FROM t1 WHERE EXISTS (SELECT y FROM t2 WHERE t1.x!=t2.x) 207 +} { 208 + 0 0 0 {SCAN TABLE t1 (~500000 rows)} 209 + 0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 1} 210 + 1 0 0 {SCAN TABLE t2 (~500000 rows)} 211 +} 190 212 191 213 #------------------------------------------------------------------------- 192 214 # Test cases eqp-4.* - tests for composite select statements. 193 215 # 194 216 do_eqp_test 4.1.1 { 195 217 SELECT * FROM t1 UNION ALL SELECT * FROM t2 196 218 } {
Changes to test/fts3matchinfo.test.
10 10 #*********************************************************************** 11 11 # This file implements regression tests for the FTS3 module. The focus 12 12 # of this file is tables created with the "matchinfo=fts3" option. 13 13 # 14 14 15 15 set testdir [file dirname $argv0] 16 16 source $testdir/tester.tcl 17 + 18 +# If SQLITE_ENABLE_FTS3 is not defined, omit this file. 19 +ifcapable !fts3 { finish_test ; return } 17 20 18 21 set testprefix fts3matchinfo 19 22 20 23 proc mit {blob} { 21 24 set scan(littleEndian) i* 22 25 set scan(bigEndian) I* 23 26 binary scan $blob $scan($::tcl_platform(byteOrder)) r
Changes to test/indexedby.test.
36 36 # 37 37 proc EQP {sql} { 38 38 uplevel "execsql {EXPLAIN QUERY PLAN $sql}" 39 39 } 40 40 41 41 # These tests are to check that "EXPLAIN QUERY PLAN" is working as expected. 42 42 # 43 -do_test indexedby-1.2 { 44 - EQP { select * from t1 WHERE a = 10; } 45 -} {0 0 {TABLE t1 WITH INDEX i1}} 46 -do_test indexedby-1.3 { 47 - EQP { select * from t1 ; } 48 -} {0 0 {TABLE t1}} 49 -do_test indexedby-1.4 { 50 - EQP { select * from t1, t2 WHERE c = 10; } 51 -} {0 1 {TABLE t2 WITH INDEX i3} 1 0 {TABLE t1}} 43 +do_execsql_test indexedby-1.2 { 44 + EXPLAIN QUERY PLAN select * from t1 WHERE a = 10; 45 +} {0 0 0 {SCAN TABLE t1 BY INDEX i1 (a=?) (~10 rows)}} 46 +do_execsql_test indexedby-1.3 { 47 + EXPLAIN QUERY PLAN select * from t1 ; 48 +} {0 0 0 {SCAN TABLE t1 (~1000000 rows)}} 49 +do_execsql_test indexedby-1.4 { 50 + EXPLAIN QUERY PLAN select * from t1, t2 WHERE c = 10; 51 +} { 52 + 0 0 1 {SCAN TABLE t2 BY INDEX i3 (c=?) (~10 rows)} 53 + 0 1 0 {SCAN TABLE t1 (~1000000 rows)} 54 +} 52 55 53 56 # Parser tests. Test that an INDEXED BY or NOT INDEX clause can be 54 57 # attached to a table in the FROM clause, but not to a sub-select or 55 58 # SQL view. Also test that specifying an index that does not exist or 56 59 # is attached to a different table is detected as an error. 57 60 # 58 61 do_test indexedby-2.1 { ................................................................................ 76 79 } {1 {near "WHERE": syntax error}} 77 80 do_test indexedby-2.7 { 78 81 catchsql { SELECT * FROM v1 INDEXED BY i1 WHERE a = 'one' } 79 82 } {1 {no such index: i1}} 80 83 81 84 # Tests for single table cases. 82 85 # 83 -do_test indexedby-3.1 { 84 - EQP { SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two'} 85 -} {0 0 {TABLE t1}} 86 -do_test indexedby-3.2 { 87 - EQP { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two'} 88 -} {0 0 {TABLE t1 WITH INDEX i1}} 89 -do_test indexedby-3.3 { 90 - EQP { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two'} 91 -} {0 0 {TABLE t1 WITH INDEX i2}} 86 +do_execsql_test indexedby-3.1 { 87 + EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two' 88 +} {0 0 0 {SCAN TABLE t1 (~10000 rows)}} 89 +do_execsql_test indexedby-3.2 { 90 + EXPLAIN QUERY PLAN 91 + SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two' 92 +} {0 0 0 {SCAN TABLE t1 BY INDEX i1 (a=?) (~2 rows)}} 93 +do_execsql_test indexedby-3.3 { 94 + EXPLAIN QUERY PLAN 95 + SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two' 96 +} {0 0 0 {SCAN TABLE t1 BY INDEX i2 (b=?) (~2 rows)}} 92 97 do_test indexedby-3.4 { 93 98 catchsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' } 94 99 } {1 {cannot use index: i2}} 95 100 do_test indexedby-3.5 { 96 101 catchsql { SELECT * FROM t1 INDEXED BY i2 ORDER BY a } 97 102 } {1 {cannot use index: i2}} 98 103 do_test indexedby-3.6 { 99 104 catchsql { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' } 100 105 } {0 {}} 101 106 do_test indexedby-3.7 { 102 107 catchsql { SELECT * FROM t1 INDEXED BY i1 ORDER BY a } 103 108 } {0 {}} 104 109 105 -do_test indexedby-3.8 { 106 - EQP { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 ORDER BY e } 107 -} {0 0 {TABLE t3 WITH INDEX sqlite_autoindex_t3_1 ORDER BY}} 108 -do_test indexedby-3.9 { 109 - EQP { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE e = 10 } 110 -} {0 0 {TABLE t3 WITH INDEX sqlite_autoindex_t3_1}} 110 +do_execsql_test indexedby-3.8 { 111 + EXPLAIN QUERY PLAN 112 + SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 ORDER BY e 113 +} {0 0 0 {SCAN TABLE t3 BY INDEX sqlite_autoindex_t3_1 (~1000000 rows)}} 114 +do_execsql_test indexedby-3.9 { 115 + EXPLAIN QUERY PLAN 116 + SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE e = 10 117 +} {0 0 0 {SCAN TABLE t3 BY INDEX sqlite_autoindex_t3_1 (e=?) (~1 rows)}} 111 118 do_test indexedby-3.10 { 112 119 catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE f = 10 } 113 120 } {1 {cannot use index: sqlite_autoindex_t3_1}} 114 121 do_test indexedby-3.11 { 115 122 catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_2 WHERE f = 10 } 116 123 } {1 {no such index: sqlite_autoindex_t3_2}} 117 124 118 125 # Tests for multiple table cases. 119 126 # 120 -do_test indexedby-4.1 { 121 - EQP { SELECT * FROM t1, t2 WHERE a = c } 122 -} {0 0 {TABLE t1} 1 1 {TABLE t2 WITH INDEX i3}} 123 -do_test indexedby-4.2 { 124 - EQP { SELECT * FROM t1 INDEXED BY i1, t2 WHERE a = c } 125 -} {0 1 {TABLE t2} 1 0 {TABLE t1 WITH INDEX i1}} 127 +do_execsql_test indexedby-4.1 { 128 + EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE a = c 129 +} { 130 + 0 0 0 {SCAN TABLE t1 (~1000000 rows)} 131 + 0 1 1 {SCAN TABLE t2 BY INDEX i3 (c=?) (~10 rows)} 132 +} 133 +do_execsql_test indexedby-4.2 { 134 + EXPLAIN QUERY PLAN SELECT * FROM t1 INDEXED BY i1, t2 WHERE a = c 135 +} { 136 + 0 0 1 {SCAN TABLE t2 (~1000000 rows)} 137 + 0 1 0 {SCAN TABLE t1 BY INDEX i1 (a=?) (~10 rows)} 138 +} 126 139 do_test indexedby-4.3 { 127 140 catchsql { 128 141 SELECT * FROM t1 INDEXED BY i1, t2 INDEXED BY i3 WHERE a=c 129 142 } 130 143 } {1 {cannot use index: i1}} 131 144 do_test indexedby-4.4 { 132 145 catchsql { ................................................................................ 134 147 } 135 148 } {1 {cannot use index: i3}} 136 149 137 150 # Test embedding an INDEXED BY in a CREATE VIEW statement. This block 138 151 # also tests that nothing bad happens if an index refered to by 139 152 # a CREATE VIEW statement is dropped and recreated. 140 153 # 141 -do_test indexedby-5.1 { 142 - execsql { 143 - CREATE VIEW v2 AS SELECT * FROM t1 INDEXED BY i1 WHERE a > 5; 144 - } 145 - EQP { SELECT * FROM v2 } 146 -} {0 0 {TABLE t1 WITH INDEX i1}} 147 -do_test indexedby-5.2 { 148 - EQP { SELECT * FROM v2 WHERE b = 10 } 149 -} {0 0 {TABLE t1 WITH INDEX i1}} 154 +do_execsql_test indexedby-5.1 { 155 + CREATE VIEW v2 AS SELECT * FROM t1 INDEXED BY i1 WHERE a > 5; 156 + EXPLAIN QUERY PLAN SELECT * FROM v2 157 +} {0 0 0 {SCAN TABLE t1 BY INDEX i1 (a>?) (~330000 rows)}} 158 +do_execsql_test indexedby-5.2 { 159 + EXPLAIN QUERY PLAN SELECT * FROM v2 WHERE b = 10 160 +} {0 0 0 {SCAN TABLE t1 BY INDEX i1 (a>?) (~33000 rows)}} 150 161 do_test indexedby-5.3 { 151 162 execsql { DROP INDEX i1 } 152 163 catchsql { SELECT * FROM v2 } 153 164 } {1 {no such index: i1}} 154 165 do_test indexedby-5.4 { 155 166 # Recreate index i1 in such a way as it cannot be used by the view query. 156 167 execsql { CREATE INDEX i1 ON t1(b) } ................................................................................ 161 172 # be used by the query. 162 173 execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(a) } 163 174 catchsql { SELECT * FROM v2 } 164 175 } {0 {}} 165 176 166 177 # Test that "NOT INDEXED" may use the rowid index, but not others. 167 178 # 168 -do_test indexedby-6.1 { 169 - EQP { SELECT * FROM t1 WHERE b = 10 ORDER BY rowid } 170 -} {0 0 {TABLE t1 WITH INDEX i2 ORDER BY}} 171 -do_test indexedby-6.2 { 172 - EQP { SELECT * FROM t1 NOT INDEXED WHERE b = 10 ORDER BY rowid } 173 -} {0 0 {TABLE t1 USING PRIMARY KEY ORDER BY}} 179 +do_execsql_test indexedby-6.1 { 180 + EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 10 ORDER BY rowid 181 +} {0 0 0 {SCAN TABLE t1 BY INDEX i2 (b=?) (~10 rows)}} 182 +do_execsql_test indexedby-6.2 { 183 + EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE b = 10 ORDER BY rowid 184 +} {0 0 0 {SCAN TABLE t1 BY INTEGER PRIMARY KEY (~100000 rows)}} 174 185 175 186 # Test that "INDEXED BY" can be used in a DELETE statement. 176 187 # 177 -do_test indexedby-7.1 { 178 - EQP { DELETE FROM t1 WHERE a = 5 } 179 -} {0 0 {TABLE t1 WITH INDEX i1}} 180 -do_test indexedby-7.2 { 181 - EQP { DELETE FROM t1 NOT INDEXED WHERE a = 5 } 182 -} {0 0 {TABLE t1}} 183 -do_test indexedby-7.3 { 184 - EQP { DELETE FROM t1 INDEXED BY i1 WHERE a = 5 } 185 -} {0 0 {TABLE t1 WITH INDEX i1}} 186 -do_test indexedby-7.4 { 187 - EQP { DELETE FROM t1 INDEXED BY i1 WHERE a = 5 AND b = 10} 188 -} {0 0 {TABLE t1 WITH INDEX i1}} 189 -do_test indexedby-7.5 { 190 - EQP { DELETE FROM t1 INDEXED BY i2 WHERE a = 5 AND b = 10} 191 -} {0 0 {TABLE t1 WITH INDEX i2}} 188 +do_execsql_test indexedby-7.1 { 189 + EXPLAIN QUERY PLAN DELETE FROM t1 WHERE a = 5 190 +} {0 0 0 {SCAN TABLE t1 BY COVERING INDEX i1 (a=?) (~10 rows)}} 191 +do_execsql_test indexedby-7.2 { 192 + EXPLAIN QUERY PLAN DELETE FROM t1 NOT INDEXED WHERE a = 5 193 +} {0 0 0 {SCAN TABLE t1 (~100000 rows)}} 194 +do_execsql_test indexedby-7.3 { 195 + EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5 196 +} {0 0 0 {SCAN TABLE t1 BY COVERING INDEX i1 (a=?) (~10 rows)}} 197 +do_execsql_test indexedby-7.4 { 198 + EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5 AND b = 10 199 +} {0 0 0 {SCAN TABLE t1 BY INDEX i1 (a=?) (~2 rows)}} 200 +do_execsql_test indexedby-7.5 { 201 + EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i2 WHERE a = 5 AND b = 10 202 +} {0 0 0 {SCAN TABLE t1 BY INDEX i2 (b=?) (~2 rows)}} 192 203 do_test indexedby-7.6 { 193 204 catchsql { DELETE FROM t1 INDEXED BY i2 WHERE a = 5} 194 205 } {1 {cannot use index: i2}} 195 206 196 207 # Test that "INDEXED BY" can be used in an UPDATE statement. 197 208 # 198 -do_test indexedby-8.1 { 199 - EQP { UPDATE t1 SET rowid=rowid+1 WHERE a = 5 } 200 -} {0 0 {TABLE t1 WITH INDEX i1}} 201 -do_test indexedby-8.2 { 202 - EQP { UPDATE t1 NOT INDEXED SET rowid=rowid+1 WHERE a = 5 } 203 -} {0 0 {TABLE t1}} 204 -do_test indexedby-8.3 { 205 - EQP { UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 } 206 -} {0 0 {TABLE t1 WITH INDEX i1}} 207 -do_test indexedby-8.4 { 208 - EQP { UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 AND b = 10} 209 -} {0 0 {TABLE t1 WITH INDEX i1}} 210 -do_test indexedby-8.5 { 211 - EQP { UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5 AND b = 10} 212 -} {0 0 {TABLE t1 WITH INDEX i2}} 209 +do_execsql_test indexedby-8.1 { 210 + EXPLAIN QUERY PLAN UPDATE t1 SET rowid=rowid+1 WHERE a = 5 211 +} {0 0 0 {SCAN TABLE t1 BY COVERING INDEX i1 (a=?) (~10 rows)}} 212 +do_execsql_test indexedby-8.2 { 213 + EXPLAIN QUERY PLAN UPDATE t1 NOT INDEXED SET rowid=rowid+1 WHERE a = 5 214 +} {0 0 0 {SCAN TABLE t1 (~100000 rows)}} 215 +do_execsql_test indexedby-8.3 { 216 + EXPLAIN QUERY PLAN UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 217 +} {0 0 0 {SCAN TABLE t1 BY COVERING INDEX i1 (a=?) (~10 rows)}} 218 +do_execsql_test indexedby-8.4 { 219 + EXPLAIN QUERY PLAN 220 + UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 AND b = 10 221 +} {0 0 0 {SCAN TABLE t1 BY INDEX i1 (a=?) (~2 rows)}} 222 +do_execsql_test indexedby-8.5 { 223 + EXPLAIN QUERY PLAN 224 + UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5 AND b = 10 225 +} {0 0 0 {SCAN TABLE t1 BY INDEX i2 (b=?) (~2 rows)}} 213 226 do_test indexedby-8.6 { 214 227 catchsql { UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5} 215 228 } {1 {cannot use index: i2}} 216 229 217 230 # Test that bug #3560 is fixed. 218 231 # 219 232 do_test indexedby-9.1 {
Changes to test/tester.tcl.
345 345 } { 346 346 set testname "${::testprefix}-$testname" 347 347 } 348 348 } 349 349 350 350 proc do_execsql_test {testname sql {result {}}} { 351 351 fix_testname testname 352 - uplevel do_test $testname [list "execsql {$sql}"] [list $result] 352 + uplevel do_test $testname [list "execsql {$sql}"] [list [list {*}$result]] 353 353 } 354 354 proc do_catchsql_test {testname sql result} { 355 355 fix_testname testname 356 356 uplevel do_test $testname [list "catchsql {$sql}"] [list $result] 357 357 } 358 358 359 359 #-------------------------------------------------------------------------
Changes to test/tkt-78e04e52ea.test.
40 40 CREATE INDEX i1 ON ""("" COLLATE nocase); 41 41 } 42 42 } {} 43 43 do_test tkt-78e04-1.4 { 44 44 execsql { 45 45 EXPLAIN QUERY PLAN SELECT * FROM "" WHERE "" LIKE 'abc%'; 46 46 } 47 -} {0 0 {TABLE }} 47 +} {0 0 0 {SCAN TABLE (~500000 rows)}} 48 48 do_test tkt-78e04-1.5 { 49 49 execsql { 50 50 DROP TABLE ""; 51 51 SELECT name FROM sqlite_master; 52 52 } 53 53 } {t2} 54 54 55 55 do_test tkt-78e04-2.1 { 56 56 execsql { 57 57 CREATE INDEX "" ON t2(x); 58 58 EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE x=5; 59 59 } 60 -} {0 0 {TABLE t2 WITH INDEX }} 60 +} {0 0 0 {SCAN TABLE t2 BY COVERING INDEX (x=?) (~10 rows)}} 61 61 do_test tkt-78e04-2.2 { 62 62 execsql { 63 63 DROP INDEX ""; 64 64 EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE x=2; 65 65 } 66 -} {0 0 {TABLE t2}} 66 +} {0 0 0 {SCAN TABLE t2 (~100000 rows)}} 67 67 68 68 finish_test
Changes to test/tkt3442.test.
45 45 # These tests perform an EXPLAIN QUERY PLAN on both versions of the 46 46 # SELECT referenced in ticket #3442 (both '5000' and "5000") 47 47 # and verify that the query plan is the same. 48 48 # 49 49 ifcapable explain { 50 50 do_test tkt3442-1.2 { 51 51 EQP { SELECT node FROM listhash WHERE id='5000' LIMIT 1; } 52 - } {0 0 {TABLE listhash WITH INDEX ididx}} 52 + } {0 0 0 {SCAN TABLE listhash BY INDEX ididx (id=?) (~1 rows)}} 53 53 do_test tkt3442-1.3 { 54 54 EQP { SELECT node FROM listhash WHERE id="5000" LIMIT 1; } 55 - } {0 0 {TABLE listhash WITH INDEX ididx}} 55 + } {0 0 0 {SCAN TABLE listhash BY INDEX ididx (id=?) (~1 rows)}} 56 56 } 57 57 58 58 59 59 # Some extra tests testing other permutations of 5000. 60 60 # 61 61 ifcapable explain { 62 62 do_test tkt3442-1.4 { 63 63 EQP { SELECT node FROM listhash WHERE id=5000 LIMIT 1; } 64 - } {0 0 {TABLE listhash WITH INDEX ididx}} 64 + } {0 0 0 {SCAN TABLE listhash BY INDEX ididx (id=?) (~1 rows)}} 65 65 } 66 66 do_test tkt3442-1.5 { 67 67 catchsql { 68 68 SELECT node FROM listhash WHERE id=[5000] LIMIT 1; 69 69 } 70 70 } {1 {no such column: 5000}} 71 71 72 72 finish_test
Changes to test/where3.test.
213 213 } {tB {} tC * tA * tD *} 214 214 215 215 # Ticket [13f033c865f878953] 216 216 # If the outer loop must be a full table scan, do not let ANALYZE trick 217 217 # the planner into use a table for the outer loop that might be indexable 218 218 # if held until an inner loop. 219 219 # 220 -do_test where3-3.0 { 221 - execsql { 222 - CREATE TABLE t301(a INTEGER PRIMARY KEY,b,c); 223 - CREATE INDEX t301c ON t301(c); 224 - INSERT INTO t301 VALUES(1,2,3); 225 - CREATE TABLE t302(x, y); 226 - ANALYZE; 227 - explain query plan 228 - SELECT * FROM t302, t301 WHERE t302.x=5 AND t301.a=t302.y; 229 - } 230 -} {0 0 {TABLE t302} 1 1 {TABLE t301 USING PRIMARY KEY}} 231 -do_test where3-3.1 { 232 - execsql { 233 - explain query plan 234 - SELECT * FROM t301, t302 WHERE t302.x=5 AND t301.a=t302.y; 235 - } 236 -} {0 1 {TABLE t302} 1 0 {TABLE t301 USING PRIMARY KEY}} 220 +do_execsql_test where3-3.0 { 221 + CREATE TABLE t301(a INTEGER PRIMARY KEY,b,c); 222 + CREATE INDEX t301c ON t301(c); 223 + INSERT INTO t301 VALUES(1,2,3); 224 + CREATE TABLE t302(x, y); 225 + ANALYZE; 226 + explain query plan SELECT * FROM t302, t301 WHERE t302.x=5 AND t301.a=t302.y; 227 +} { 228 + 0 0 0 {SCAN TABLE t302 (~0 rows)} 229 + 0 1 1 {SCAN TABLE t301 BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 230 +} 231 +do_execsql_test where3-3.1 { 232 + explain query plan 233 + SELECT * FROM t301, t302 WHERE t302.x=5 AND t301.a=t302.y; 234 +} { 235 + 0 0 1 {SCAN TABLE t302 (~0 rows)} 236 + 0 1 0 {SCAN TABLE t301 BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 237 +} 237 238 238 239 # Verify that when there are multiple tables in a join which must be 239 240 # full table scans that the query planner attempts put the table with 240 241 # the fewest number of output rows as the outer loop. 241 242 # 242 -do_test where3-4.0 { 243 - execsql { 244 - CREATE TABLE t400(a INTEGER PRIMARY KEY, b, c); 245 - CREATE TABLE t401(p INTEGER PRIMARY KEY, q, r); 246 - CREATE TABLE t402(x INTEGER PRIMARY KEY, y, z); 247 - EXPLAIN QUERY PLAN 248 - SELECT * FROM t400, t401, t402 WHERE t402.z GLOB 'abc*'; 249 - } 250 -} {0 2 {TABLE t402} 1 0 {TABLE t400} 2 1 {TABLE t401}} 251 -do_test where3-4.1 { 252 - execsql { 253 - EXPLAIN QUERY PLAN 254 - SELECT * FROM t400, t401, t402 WHERE t401.r GLOB 'abc*'; 255 - } 256 -} {0 1 {TABLE t401} 1 0 {TABLE t400} 2 2 {TABLE t402}} 257 -do_test where3-4.2 { 258 - execsql { 259 - EXPLAIN QUERY PLAN 260 - SELECT * FROM t400, t401, t402 WHERE t400.c GLOB 'abc*'; 261 - } 262 -} {0 0 {TABLE t400} 1 1 {TABLE t401} 2 2 {TABLE t402}} 243 +do_execsql_test where3-4.0 { 244 + CREATE TABLE t400(a INTEGER PRIMARY KEY, b, c); 245 + CREATE TABLE t401(p INTEGER PRIMARY KEY, q, r); 246 + CREATE TABLE t402(x INTEGER PRIMARY KEY, y, z); 247 + EXPLAIN QUERY PLAN 248 + SELECT * FROM t400, t401, t402 WHERE t402.z GLOB 'abc*'; 249 +} { 250 + 0 0 2 {SCAN TABLE t402 (~500000 rows)} 251 + 0 1 0 {SCAN TABLE t400 (~1000000 rows)} 252 + 0 2 1 {SCAN TABLE t401 (~1000000 rows)} 253 +} 254 +do_execsql_test where3-4.1 { 255 + EXPLAIN QUERY PLAN 256 + SELECT * FROM t400, t401, t402 WHERE t401.r GLOB 'abc*'; 257 +} { 258 + 0 0 1 {SCAN TABLE t401 (~500000 rows)} 259 + 0 1 0 {SCAN TABLE t400 (~1000000 rows)} 260 + 0 2 2 {SCAN TABLE t402 (~1000000 rows)} 261 +} 262 +do_execsql_test where3-4.2 { 263 + EXPLAIN QUERY PLAN 264 + SELECT * FROM t400, t401, t402 WHERE t400.c GLOB 'abc*'; 265 +} { 266 + 0 0 0 {SCAN TABLE t400 (~500000 rows)} 267 + 0 1 1 {SCAN TABLE t401 (~1000000 rows)} 268 + 0 2 2 {SCAN TABLE t402 (~1000000 rows)} 269 +} 263 270 264 271 # Verify that a performance regression encountered by firefox 265 272 # has been fixed. 266 273 # 267 -do_test where3-5.0 { 268 - execsql { 269 - CREATE TABLE aaa (id INTEGER PRIMARY KEY, type INTEGER, 270 - fk INTEGER DEFAULT NULL, parent INTEGER, 271 - position INTEGER, title LONGVARCHAR, 272 - keyword_id INTEGER, folder_type TEXT, 273 - dateAdded INTEGER, lastModified INTEGER); 274 - CREATE INDEX aaa_111 ON aaa (fk, type); 275 - CREATE INDEX aaa_222 ON aaa (parent, position); 276 - CREATE INDEX aaa_333 ON aaa (fk, lastModified); 277 - CREATE TABLE bbb (id INTEGER PRIMARY KEY, type INTEGER, 278 - fk INTEGER DEFAULT NULL, parent INTEGER, 279 - position INTEGER, title LONGVARCHAR, 280 - keyword_id INTEGER, folder_type TEXT, 281 - dateAdded INTEGER, lastModified INTEGER); 282 - CREATE INDEX bbb_111 ON bbb (fk, type); 283 - CREATE INDEX bbb_222 ON bbb (parent, position); 284 - CREATE INDEX bbb_333 ON bbb (fk, lastModified); 285 - } 274 +do_execsql_test where3-5.0 { 275 + CREATE TABLE aaa (id INTEGER PRIMARY KEY, type INTEGER, 276 + fk INTEGER DEFAULT NULL, parent INTEGER, 277 + position INTEGER, title LONGVARCHAR, 278 + keyword_id INTEGER, folder_type TEXT, 279 + dateAdded INTEGER, lastModified INTEGER); 280 + CREATE INDEX aaa_111 ON aaa (fk, type); 281 + CREATE INDEX aaa_222 ON aaa (parent, position); 282 + CREATE INDEX aaa_333 ON aaa (fk, lastModified); 283 + CREATE TABLE bbb (id INTEGER PRIMARY KEY, type INTEGER, 284 + fk INTEGER DEFAULT NULL, parent INTEGER, 285 + position INTEGER, title LONGVARCHAR, 286 + keyword_id INTEGER, folder_type TEXT, 287 + dateAdded INTEGER, lastModified INTEGER); 288 + CREATE INDEX bbb_111 ON bbb (fk, type); 289 + CREATE INDEX bbb_222 ON bbb (parent, position); 290 + CREATE INDEX bbb_333 ON bbb (fk, lastModified); 286 291 287 - execsql { 288 - EXPLAIN QUERY PLAN 289 - SELECT bbb.title AS tag_title 290 - FROM aaa JOIN bbb ON bbb.id = aaa.parent 291 - WHERE aaa.fk = 'constant' 292 - AND LENGTH(bbb.title) > 0 293 - AND bbb.parent = 4 294 - ORDER BY bbb.title COLLATE NOCASE ASC; 295 - } 296 -} {0 0 {TABLE aaa WITH INDEX aaa_333} 1 1 {TABLE bbb USING PRIMARY KEY}} 297 -do_test where3-5.1 { 298 - execsql { 299 - EXPLAIN QUERY PLAN 300 - SELECT bbb.title AS tag_title 301 - FROM aaa JOIN aaa AS bbb ON bbb.id = aaa.parent 302 - WHERE aaa.fk = 'constant' 303 - AND LENGTH(bbb.title) > 0 304 - AND bbb.parent = 4 305 - ORDER BY bbb.title COLLATE NOCASE ASC; 306 - } 307 -} {0 0 {TABLE aaa WITH INDEX aaa_333} 1 1 {TABLE aaa AS bbb USING PRIMARY KEY}} 308 -do_test where3-5.2 { 309 - execsql { 310 - EXPLAIN QUERY PLAN 311 - SELECT bbb.title AS tag_title 312 - FROM bbb JOIN aaa ON bbb.id = aaa.parent 313 - WHERE aaa.fk = 'constant' 314 - AND LENGTH(bbb.title) > 0 315 - AND bbb.parent = 4 316 - ORDER BY bbb.title COLLATE NOCASE ASC; 317 - } 318 -} {0 1 {TABLE aaa WITH INDEX aaa_333} 1 0 {TABLE bbb USING PRIMARY KEY}} 319 -do_test where3-5.3 { 320 - execsql { 321 - EXPLAIN QUERY PLAN 322 - SELECT bbb.title AS tag_title 323 - FROM aaa AS bbb JOIN aaa ON bbb.id = aaa.parent 324 - WHERE aaa.fk = 'constant' 325 - AND LENGTH(bbb.title) > 0 326 - AND bbb.parent = 4 327 - ORDER BY bbb.title COLLATE NOCASE ASC; 328 - } 329 -} {0 1 {TABLE aaa WITH INDEX aaa_333} 1 0 {TABLE aaa AS bbb USING PRIMARY KEY}} 292 + EXPLAIN QUERY PLAN 293 + SELECT bbb.title AS tag_title 294 + FROM aaa JOIN bbb ON bbb.id = aaa.parent 295 + WHERE aaa.fk = 'constant' 296 + AND LENGTH(bbb.title) > 0 297 + AND bbb.parent = 4 298 + ORDER BY bbb.title COLLATE NOCASE ASC; 299 +} { 300 + 0 0 0 {SCAN TABLE aaa BY INDEX aaa_333 (fk=?) (~10 rows)} 301 + 0 1 1 {SCAN TABLE bbb BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 302 + 0 0 0 {USE TEMP B-TREE FOR ORDER BY} 303 +} 304 +do_execsql_test where3-5.1 { 305 + EXPLAIN QUERY PLAN 306 + SELECT bbb.title AS tag_title 307 + FROM aaa JOIN aaa AS bbb ON bbb.id = aaa.parent 308 + WHERE aaa.fk = 'constant' 309 + AND LENGTH(bbb.title) > 0 310 + AND bbb.parent = 4 311 + ORDER BY bbb.title COLLATE NOCASE ASC; 312 +} { 313 + 0 0 0 {SCAN TABLE aaa BY INDEX aaa_333 (fk=?) (~10 rows)} 314 + 0 1 1 {SCAN TABLE aaa AS bbb BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 315 + 0 0 0 {USE TEMP B-TREE FOR ORDER BY} 316 +} 317 +do_execsql_test where3-5.2 { 318 + EXPLAIN QUERY PLAN 319 + SELECT bbb.title AS tag_title 320 + FROM bbb JOIN aaa ON bbb.id = aaa.parent 321 + WHERE aaa.fk = 'constant' 322 + AND LENGTH(bbb.title) > 0 323 + AND bbb.parent = 4 324 + ORDER BY bbb.title COLLATE NOCASE ASC; 325 +} { 326 + 0 0 1 {SCAN TABLE aaa BY INDEX aaa_333 (fk=?) (~10 rows)} 327 + 0 1 0 {SCAN TABLE bbb BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 328 + 0 0 0 {USE TEMP B-TREE FOR ORDER BY} 329 +} 330 +do_execsql_test where3-5.3 { 331 + EXPLAIN QUERY PLAN 332 + SELECT bbb.title AS tag_title 333 + FROM aaa AS bbb JOIN aaa ON bbb.id = aaa.parent 334 + WHERE aaa.fk = 'constant' 335 + AND LENGTH(bbb.title) > 0 336 + AND bbb.parent = 4 337 + ORDER BY bbb.title COLLATE NOCASE ASC; 338 +} { 339 + 0 0 1 {SCAN TABLE aaa BY INDEX aaa_333 (fk=?) (~10 rows)} 340 + 0 1 0 {SCAN TABLE aaa AS bbb BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 341 + 0 0 0 {USE TEMP B-TREE FOR ORDER BY} 342 +} 330 343 331 344 332 345 finish_test
Changes to test/where7.test.
23303 23303 # test case for the performance regression fixed by 23304 23304 # check-in 28ba6255282b on 2010-10-21 02:05:06 23305 23305 # 23306 23306 # The test case that follows is code from an actual 23307 23307 # application with identifiers change and unused columns 23308 23308 # remove. 23309 23309 # 23310 -do_test where7-3.1 { 23311 - db eval { 23312 - CREATE TABLE t301 ( 23313 - c8 INTEGER PRIMARY KEY, 23314 - c6 INTEGER, 23315 - c4 INTEGER, 23316 - c7 INTEGER, 23317 - FOREIGN KEY (c4) REFERENCES series(c4) 23318 - ); 23319 - CREATE INDEX t301_c6 on t301(c6); 23320 - CREATE INDEX t301_c4 on t301(c4); 23321 - CREATE INDEX t301_c7 on t301(c7); 23322 - 23323 - CREATE TABLE t302 ( 23324 - c1 INTEGER PRIMARY KEY, 23325 - c8 INTEGER, 23326 - c5 INTEGER, 23327 - c3 INTEGER, 23328 - c2 INTEGER, 23329 - c4 INTEGER, 23330 - FOREIGN KEY (c8) REFERENCES t301(c8) 23331 - ); 23332 - CREATE INDEX t302_c3 on t302(c3); 23333 - CREATE INDEX t302_c8_c3 on t302(c8, c3); 23334 - CREATE INDEX t302_c5 on t302(c5); 23335 - 23336 - EXPLAIN QUERY PLAN 23337 - SELECT t302.c1 23338 - FROM t302 JOIN t301 ON t302.c8 = t301.c8 23339 - WHERE t302.c2 = 19571 23340 - AND t302.c3 > 1287603136 23341 - AND (t301.c4 = 1407449685622784 23342 - OR t301.c8 = 1407424651264000) 23343 - ORDER BY t302.c5 LIMIT 200; 23344 - } 23345 -} {0 1 {TABLE t301 VIA MULTI-INDEX UNION} 1 0 {TABLE t302 WITH INDEX t302_c8_c3} 0 0 {TABLE t301 WITH INDEX t301_c4} 0 0 {TABLE t301 USING PRIMARY KEY}} 23310 +do_execsql_test where7-3.1 { 23311 + CREATE TABLE t301 ( 23312 + c8 INTEGER PRIMARY KEY, 23313 + c6 INTEGER, 23314 + c4 INTEGER, 23315 + c7 INTEGER, 23316 + FOREIGN KEY (c4) REFERENCES series(c4) 23317 + ); 23318 + CREATE INDEX t301_c6 on t301(c6); 23319 + CREATE INDEX t301_c4 on t301(c4); 23320 + CREATE INDEX t301_c7 on t301(c7); 23321 + 23322 + CREATE TABLE t302 ( 23323 + c1 INTEGER PRIMARY KEY, 23324 + c8 INTEGER, 23325 + c5 INTEGER, 23326 + c3 INTEGER, 23327 + c2 INTEGER, 23328 + c4 INTEGER, 23329 + FOREIGN KEY (c8) REFERENCES t301(c8) 23330 + ); 23331 + CREATE INDEX t302_c3 on t302(c3); 23332 + CREATE INDEX t302_c8_c3 on t302(c8, c3); 23333 + CREATE INDEX t302_c5 on t302(c5); 23334 + 23335 + EXPLAIN QUERY PLAN 23336 + SELECT t302.c1 23337 + FROM t302 JOIN t301 ON t302.c8 = t301.c8 23338 + WHERE t302.c2 = 19571 23339 + AND t302.c3 > 1287603136 23340 + AND (t301.c4 = 1407449685622784 23341 + OR t301.c8 = 1407424651264000) 23342 + ORDER BY t302.c5 LIMIT 200; 23343 +} { 23344 + 0 0 1 {SCAN TABLE t301 BY COVERING INDEX t301_c4 (c4=?) (~10 rows)} 23345 + 0 0 1 {SCAN TABLE t301 BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 23346 + 0 1 0 {SCAN TABLE t302 BY INDEX t302_c8_c3 (c8=? AND c8>?) (~2 rows)} 23347 + 0 0 0 {USE TEMP B-TREE FOR ORDER BY} 23348 +} 23346 23349 23347 23350 finish_test
Changes to test/where9.test.
354 354 WHERE t1.a=t3.y OR t1.b=t3.y*11 OR (t1.c=27027 AND round(t1.d)==80) 355 355 ORDER BY 1, 2, 3 356 356 } 357 357 } {1 80 2 1 80 28 1 80 54 1 80 80 2 80 2 2 80 28 2 80 54 2 80 80 scan 1 sort 1} 358 358 359 359 360 360 ifcapable explain { 361 - do_test where9-3.1 { 362 - set r [db eval { 363 - EXPLAIN QUERY PLAN 364 - SELECT t2.a FROM t1, t2 365 - WHERE t1.a=80 366 - AND ((t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f) 367 - }] 368 - set a [expr {[lsearch $r {TABLE t2 VIA MULTI-INDEX UNION}]>=0}] 369 - set b [expr {[lsearch $r {TABLE t2 WITH INDEX t2f}]>=0}] 370 - set c [expr {([lsearch $r {TABLE t2 WITH INDEX t2c}]>=0)+ 371 - [lsearch $r {TABLE t2 WITH INDEX t2d}]>=0}] 372 - concat $a $b $c 373 - } {1 1 1} 374 - do_test where9-3.2 { 375 - set r [db eval { 376 - EXPLAIN QUERY PLAN 377 - SELECT coalesce(t2.a,9999) 378 - FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f 379 - WHERE t1.a=80 380 - }] 381 - set a [expr {[lsearch $r {TABLE t2 VIA MULTI-INDEX UNION}]>=0}] 382 - set b [expr {[lsearch $r {TABLE t2 WITH INDEX t2f}]>=0}] 383 - set c [expr {([lsearch $r {TABLE t2 WITH INDEX t2c}]>=0)+ 384 - [lsearch $r {TABLE t2 WITH INDEX t2d}]>=0}] 385 - concat $a $b $c 386 - } {1 1 1} 361 + do_execsql_test where9-3.1 { 362 + EXPLAIN QUERY PLAN 363 + SELECT t2.a FROM t1, t2 364 + WHERE t1.a=80 AND ((t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f) 365 + } { 366 + 0 0 0 {SCAN TABLE t1 BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 367 + 0 1 1 {SCAN TABLE t2 BY INDEX t2d (d=?) (~2 rows)} 368 + 0 1 1 {SCAN TABLE t2 BY COVERING INDEX t2f (f=?) (~10 rows)} 369 + } 370 + do_execsql_test where9-3.2 { 371 + EXPLAIN QUERY PLAN 372 + SELECT coalesce(t2.a,9999) 373 + FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f 374 + WHERE t1.a=80 375 + } { 376 + 0 0 0 {SCAN TABLE t1 BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 377 + 0 1 1 {SCAN TABLE t2 BY INDEX t2d (d=?) (~2 rows)} 378 + 0 1 1 {SCAN TABLE t2 BY COVERING INDEX t2f (f=?) (~10 rows)} 379 + } 387 380 } 388 381 389 382 # Make sure that INDEXED BY and multi-index OR clauses play well with 390 383 # one another. 391 384 # 392 385 do_test where9-4.1 { 393 386 count_steps { ................................................................................ 454 447 } 455 448 } {1 {cannot use index: t1d}} 456 449 457 450 ifcapable explain { 458 451 # The (c=31031 OR d IS NULL) clause is preferred over b>1000 because 459 452 # the former is an equality test which is expected to return fewer rows. 460 453 # 461 - do_test where9-5.1 { 462 - set r [db eval { 463 - EXPLAIN QUERY PLAN 464 - SELECT a FROM t1 465 - WHERE b>1000 466 - AND (c=31031 OR d IS NULL) 467 - }] 468 - set a [expr {[lsearch $r {TABLE t1 VIA MULTI-INDEX UNION}]>=0}] 469 - set b [expr {[lsearch $r {TABLE t1 WITH INDEX t1b}]>=0}] 470 - concat $a $b 471 - } {1 0} 454 + do_execsql_test where9-5.1 { 455 + EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c=31031 OR d IS NULL) 456 + } { 457 + 0 0 0 {SCAN TABLE t1 BY INDEX t1c (c=?) (~10 rows)} 458 + 0 0 0 {SCAN TABLE t1 BY INDEX t1d (d=?) (~10 rows)} 459 + } 472 460 473 461 # In contrast, b=1000 is preferred over any OR-clause. 474 462 # 475 - do_test where9-5.2 { 476 - set r [db eval { 477 - EXPLAIN QUERY PLAN 478 - SELECT a FROM t1 479 - WHERE b=1000 480 - AND (c=31031 OR d IS NULL) 481 - }] 482 - set a [expr {[lsearch $r {TABLE t1 VIA MULTI-INDEX UNION}]>=0}] 483 - set b [expr {[lsearch $r {TABLE t1 WITH INDEX t1b}]>=0}] 484 - concat $a $b 485 - } {0 1} 463 + do_execsql_test where9-5.2 { 464 + EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b=1000 AND (c=31031 OR d IS NULL) 465 + } { 466 + 0 0 0 {SCAN TABLE t1 BY INDEX t1b (b=?) (~5 rows)} 467 + } 486 468 487 469 # Likewise, inequalities in an AND are preferred over inequalities in 488 470 # an OR. 489 471 # 490 - do_test where9-5.3 { 491 - set r [db eval { 492 - EXPLAIN QUERY PLAN 493 - SELECT a FROM t1 494 - WHERE b>1000 495 - AND (c>=31031 OR d IS NULL) 496 - }] 497 - set a [expr {[lsearch $r {TABLE t1 VIA MULTI-INDEX UNION}]>=0}] 498 - set b [expr {[lsearch $r {TABLE t1 WITH INDEX t1b}]>=0}] 499 - concat $a $b 500 - } {0 1} 472 + do_execsql_test where9-5.3 { 473 + EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c>=31031 OR d IS NULL) 474 + } { 475 + 0 0 0 {SCAN TABLE t1 BY INDEX t1b (a>?) (~165000 rows)} 476 + } 501 477 } 502 478 503 479 ############################################################################ 504 480 # Make sure OR-clauses work correctly on UPDATE and DELETE statements. 505 481 506 482 do_test where9-6.2.1 { 507 483 db eval {SELECT count(*) FROM t1 UNION ALL SELECT a FROM t1 WHERE a>=85}