Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
| Comment: | Fix problems with handling constraints on the rowid column in sqlite3expert.c. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | schemalint |
| Files: | files | file ages | folders |
| SHA3-256: |
2e6308798ae2db30564deb35ba389659 |
| User & Date: | dan 2017-04-15 16:52:12 |
|
2017-04-17
| ||
| 17:03 | Add support for analyzing trigger programs to the sqlite3_expert code. (check-in: 159e8022 user: dan tags: schemalint) | |
|
2017-04-15
| ||
| 16:52 | Fix problems with handling constraints on the rowid column in sqlite3expert.c. (check-in: 2e630879 user: dan tags: schemalint) | |
| 15:47 | Merge latest trunk changes into this branch. (check-in: 2d0c458e user: dan tags: schemalint) | |
Changes to ext/expert/expert1.test.
74 74 } 75 75 } 76 76 } { 77 77 78 78 eval $setup 79 79 80 80 81 -do_setup_rec_test $tn.1.1 { CREATE TABLE t1(a, b, c) } { 81 +do_setup_rec_test $tn.1 { CREATE TABLE t1(a, b, c) } { 82 82 SELECT * FROM t1 83 83 } { 84 84 (no new indexes) 85 85 0|0|0|SCAN TABLE t1 86 86 } 87 87 88 -do_setup_rec_test $tn.1.2 { 88 +do_setup_rec_test $tn.2 { 89 89 CREATE TABLE t1(a, b, c); 90 90 } { 91 91 SELECT * FROM t1 WHERE b>?; 92 92 } { 93 93 CREATE INDEX t1_idx_00000062 ON t1(b); 94 94 0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_00000062 (b>?) 95 95 } 96 96 97 -do_setup_rec_test $tn.1.3 { 97 +do_setup_rec_test $tn.3 { 98 98 CREATE TABLE t1(a, b, c); 99 99 } { 100 100 SELECT * FROM t1 WHERE b COLLATE nocase BETWEEN ? AND ? 101 101 } { 102 102 CREATE INDEX t1_idx_3e094c27 ON t1(b COLLATE NOCASE); 103 103 0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_3e094c27 (b>? AND b<?) 104 104 } 105 105 106 -do_setup_rec_test $tn.1.4 { 106 +do_setup_rec_test $tn.4 { 107 107 CREATE TABLE t1(a, b, c); 108 108 } { 109 109 SELECT a FROM t1 ORDER BY b; 110 110 } { 111 111 CREATE INDEX t1_idx_00000062 ON t1(b); 112 112 0|0|0|SCAN TABLE t1 USING INDEX t1_idx_00000062 113 113 } 114 114 115 -do_setup_rec_test $tn.1.5 { 115 +do_setup_rec_test $tn.5 { 116 116 CREATE TABLE t1(a, b, c); 117 117 } { 118 118 SELECT a FROM t1 WHERE a=? ORDER BY b; 119 119 } { 120 120 CREATE INDEX t1_idx_000123a7 ON t1(a, b); 121 121 0|0|0|SEARCH TABLE t1 USING COVERING INDEX t1_idx_000123a7 (a=?) 122 122 } 123 123 124 -do_setup_rec_test $tn.1.6 { 124 +do_setup_rec_test $tn.6 { 125 125 CREATE TABLE t1(a, b, c); 126 126 } { 127 127 SELECT min(a) FROM t1 128 128 } { 129 129 CREATE INDEX t1_idx_00000061 ON t1(a); 130 130 0|0|0|SEARCH TABLE t1 USING COVERING INDEX t1_idx_00000061 131 131 } 132 132 133 -do_setup_rec_test $tn.1.7 { 133 +do_setup_rec_test $tn.7 { 134 134 CREATE TABLE t1(a, b, c); 135 135 } { 136 136 SELECT * FROM t1 ORDER BY a, b, c; 137 137 } { 138 138 CREATE INDEX t1_idx_033e95fe ON t1(a, b, c); 139 139 0|0|0|SCAN TABLE t1 USING COVERING INDEX t1_idx_033e95fe 140 140 } ................................................................................ 144 144 #} { 145 145 # SELECT * FROM t1 ORDER BY a ASC, b COLLATE nocase DESC, c ASC; 146 146 #} { 147 147 # CREATE INDEX t1_idx_5be6e222 ON t1(a, b COLLATE NOCASE DESC, c); 148 148 # 0|0|0|SCAN TABLE t1 USING COVERING INDEX t1_idx_5be6e222 149 149 #} 150 150 151 -do_setup_rec_test $tn.1.9 { 151 +do_setup_rec_test $tn.8.1 { 152 152 CREATE TABLE t1(a COLLATE NOCase, b, c); 153 153 } { 154 154 SELECT * FROM t1 WHERE a=? 155 155 } { 156 156 CREATE INDEX t1_idx_00000061 ON t1(a); 157 157 0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_00000061 (a=?) 158 158 } 159 - 160 -do_setup_rec_test $tn.1.10 { 159 +do_setup_rec_test $tn.8.2 { 161 160 CREATE TABLE t1(a, b COLLATE nocase, c); 162 161 } { 163 162 SELECT * FROM t1 ORDER BY a ASC, b DESC, c ASC; 164 163 } { 165 164 CREATE INDEX t1_idx_5cb97285 ON t1(a, b DESC, c); 166 165 0|0|0|SCAN TABLE t1 USING COVERING INDEX t1_idx_5cb97285 167 166 } 168 167 169 168 170 169 # Tables with names that require quotes. 171 170 # 172 -do_setup_rec_test $tn.8.1 { 171 +do_setup_rec_test $tn.9.1 { 173 172 CREATE TABLE "t t"(a, b, c); 174 173 } { 175 174 SELECT * FROM "t t" WHERE a=? 176 175 } { 177 176 CREATE INDEX 't t_idx_00000061' ON 't t'(a); 178 177 0|0|0|SEARCH TABLE t t USING INDEX t t_idx_00000061 (a=?) 179 178 } 180 179 181 -do_setup_rec_test $tn.8.2 { 180 +do_setup_rec_test $tn.9.2 { 182 181 CREATE TABLE "t t"(a, b, c); 183 182 } { 184 183 SELECT * FROM "t t" WHERE b BETWEEN ? AND ? 185 184 } { 186 185 CREATE INDEX 't t_idx_00000062' ON 't t'(b); 187 186 0|0|0|SEARCH TABLE t t USING INDEX t t_idx_00000062 (b>? AND b<?) 188 187 } 189 188 190 189 # Columns with names that require quotes. 191 190 # 192 -do_setup_rec_test $tn.9.1 { 191 +do_setup_rec_test $tn.10.1 { 193 192 CREATE TABLE t3(a, "b b", c); 194 193 } { 195 194 SELECT * FROM t3 WHERE "b b" = ? 196 195 } { 197 196 CREATE INDEX t3_idx_00050c52 ON t3('b b'); 198 197 0|0|0|SEARCH TABLE t3 USING INDEX t3_idx_00050c52 (b b=?) 199 198 } 200 199 201 -do_setup_rec_test $tn.9.2 { 200 +do_setup_rec_test $tn.10.2 { 202 201 CREATE TABLE t3(a, "b b", c); 203 202 } { 204 203 SELECT * FROM t3 ORDER BY "b b" 205 204 } { 206 205 CREATE INDEX t3_idx_00050c52 ON t3('b b'); 207 206 0|0|0|SCAN TABLE t3 USING INDEX t3_idx_00050c52 208 207 } 209 208 210 209 # Transitive constraints 211 210 # 212 -do_setup_rec_test $tn.10.1 { 211 +do_setup_rec_test $tn.11.1 { 213 212 CREATE TABLE t5(a, b); 214 213 CREATE TABLE t6(c, d); 215 214 } { 216 215 SELECT * FROM t5, t6 WHERE a=? AND b=c AND c=? 217 216 } { 218 217 CREATE INDEX t5_idx_000123a7 ON t5(a, b); 219 218 CREATE INDEX t6_idx_00000063 ON t6(c); 220 219 0|0|1|SEARCH TABLE t6 USING INDEX t6_idx_00000063 (c=?) 221 220 0|1|0|SEARCH TABLE t5 USING COVERING INDEX t5_idx_000123a7 (a=? AND b=?) 222 221 } 223 222 224 223 # OR terms. 225 224 # 226 -do_setup_rec_test $tn.11.1 { 225 +do_setup_rec_test $tn.12.1 { 227 226 CREATE TABLE t7(a, b); 228 227 } { 229 228 SELECT * FROM t7 WHERE a=? OR b=? 230 229 } { 231 230 CREATE INDEX t7_idx_00000062 ON t7(b); 232 231 CREATE INDEX t7_idx_00000061 ON t7(a); 233 232 0|0|0|SEARCH TABLE t7 USING INDEX t7_idx_00000061 (a=?) 234 233 0|0|0|SEARCH TABLE t7 USING INDEX t7_idx_00000062 (b=?) 235 234 } 235 + 236 +# rowid terms. 237 +# 238 +do_setup_rec_test $tn.13.1 { 239 + CREATE TABLE t8(a, b); 240 +} { 241 + SELECT * FROM t8 WHERE rowid=? 242 +} { 243 + (no new indexes) 244 + 0|0|0|SEARCH TABLE t8 USING INTEGER PRIMARY KEY (rowid=?) 245 +} 246 +do_setup_rec_test $tn.13.2 { 247 + CREATE TABLE t8(a, b); 248 +} { 249 + SELECT * FROM t8 ORDER BY rowid 250 +} { 251 + (no new indexes) 252 + 0|0|0|SCAN TABLE t8 253 +} 254 +do_setup_rec_test $tn.13.3 { 255 + CREATE TABLE t8(a, b); 256 +} { 257 + SELECT * FROM t8 WHERE a=? ORDER BY rowid 258 +} { 259 + CREATE INDEX t8_idx_00000061 ON t8(a); 260 + 0|0|0|SEARCH TABLE t8 USING INDEX t8_idx_00000061 (a=?) 261 +} 236 262 237 263 } 238 264 239 265 finish_test 240 266
Changes to ext/expert/sqlite3expert.c.
361 361 pScan->pTab = p->pTab; 362 362 pScan->pNextScan = p->pExpert->pScan; 363 363 p->pExpert->pScan = pScan; 364 364 365 365 /* Add the constraints to the IdxScan object */ 366 366 for(i=0; i<pIdxInfo->nConstraint; i++){ 367 367 struct sqlite3_index_constraint *pCons = &pIdxInfo->aConstraint[i]; 368 - if( pCons->usable && (pCons->op & opmask) ){ 368 + if( pCons->usable && pCons->iColumn>=0 && (pCons->op & opmask) ){ 369 369 IdxConstraint *pNew; 370 370 const char *zColl = sqlite3_vtab_collation(dbv, i); 371 371 pNew = idxNewConstraint(&rc, zColl); 372 372 if( pNew ){ 373 373 pNew->iCol = pCons->iColumn; 374 374 if( pCons->op==SQLITE_INDEX_CONSTRAINT_EQ ){ 375 375 pNew->pNext = pScan->pEq; ................................................................................ 384 384 pIdxInfo->aConstraintUsage[i].argvIndex = n; 385 385 } 386 386 } 387 387 388 388 /* Add the ORDER BY to the IdxScan object */ 389 389 for(i=pIdxInfo->nOrderBy-1; i>=0; i--){ 390 390 int iCol = pIdxInfo->aOrderBy[i].iColumn; 391 - IdxConstraint *pNew = idxNewConstraint(&rc, p->pTab->aCol[iCol].zColl); 392 - if( pNew ){ 393 - pNew->iCol = iCol; 394 - pNew->bDesc = pIdxInfo->aOrderBy[i].desc; 395 - pNew->pNext = pScan->pOrder; 396 - pNew->pLink = pScan->pOrder; 397 - pScan->pOrder = pNew; 398 - n++; 391 + if( iCol>=0 ){ 392 + IdxConstraint *pNew = idxNewConstraint(&rc, p->pTab->aCol[iCol].zColl); 393 + if( pNew ){ 394 + pNew->iCol = iCol; 395 + pNew->bDesc = pIdxInfo->aOrderBy[i].desc; 396 + pNew->pNext = pScan->pOrder; 397 + pNew->pLink = pScan->pOrder; 398 + pScan->pOrder = pNew; 399 + n++; 400 + } 399 401 } 400 402 } 401 403 } 402 404 403 405 pIdxInfo->estimatedCost = 1000000.0 / n; 404 406 return rc; 405 407 }