Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Partially reenable automatic indexes. Six legacy tests cases in autoindex1.test are still failing as of this commit. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
2244abfac27e7671b5d2d8cd500e6d76 |
User & Date: | dan 2013-07-25 20:02:41.591 |
Context
2013-07-25
| ||
23:26 | Add the kvreplace(K,V) pragma for use in testing. check-in: b5ceafc26f user: drh tags: trunk | |
20:02 | Partially reenable automatic indexes. Six legacy tests cases in autoindex1.test are still failing as of this commit. check-in: 2244abfac2 user: dan tags: trunk | |
15:13 | Reenable the whereShortCut() optimization. check-in: 97c7b5580f user: dan tags: trunk | |
Changes
Changes to main.mk.
︙ | ︙ | |||
48 49 50 51 52 53 54 | OPTS += -DHAVE_GMTIME_R OPTS += -DHAVE_LOCALTIME_R OPTS += -DHAVE_MALLOC_USABLE_SIZE OPTS += -DHAVE_USLEEP #OPTS += -DSQLITE4_MEMDEBUG=1 #OPTS += -DSQLITE4_NO_SYNC=1 -DLSM_NO_SYNC=1 #OPTS += -DSQLITE4_OMIT_ANALYZE | | | 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 | OPTS += -DHAVE_GMTIME_R OPTS += -DHAVE_LOCALTIME_R OPTS += -DHAVE_MALLOC_USABLE_SIZE OPTS += -DHAVE_USLEEP #OPTS += -DSQLITE4_MEMDEBUG=1 #OPTS += -DSQLITE4_NO_SYNC=1 -DLSM_NO_SYNC=1 #OPTS += -DSQLITE4_OMIT_ANALYZE #OPTS += -DSQLITE4_OMIT_AUTOMATIC_INDEX OPTS += -DSQLITE4_OMIT_VIRTUALTABLE=1 OPTS += -DSQLITE4_OMIT_XFER_OPT OPTS += -DSQLITE4_THREADSAFE=0 # This is how we compile # TCCX = $(TCC) $(OPTS) -I. -I$(TOP)/src -I$(TOP) |
︙ | ︙ |
Changes to src/build.c.
︙ | ︙ | |||
2239 2240 2241 2242 2243 2244 2245 | /* ** Cursor iPkCsr is open on a primary key index. This function generates ** code that creates the corresponding covering index record required ** by pIdx by reading values from this cursor. The record is stored in ** register regOut. */ | | | 2239 2240 2241 2242 2243 2244 2245 2246 2247 2248 2249 2250 2251 2252 2253 | /* ** Cursor iPkCsr is open on a primary key index. This function generates ** code that creates the corresponding covering index record required ** by pIdx by reading values from this cursor. The record is stored in ** register regOut. */ void sqlite4EncodeIndexValue( Parse *pParse, /* Parse context */ int iPkCsr, /* Cursor open on primary key */ Index *pIdx, /* Index to create record for */ int regOut /* Register to write record to */ ){ Vdbe *v = pParse->pVdbe; /* Generate code into this virtual machine */ Table *pTab = pIdx->pTable; /* The table that is indexed */ |
︙ | ︙ | |||
2328 2329 2330 2331 2332 2333 2334 | addrTest = sqlite4VdbeAddOp4Int(v, OP_IsUnique, iIdx, 0, regKey, 0); sqlite4HaltConstraint(pParse, OE_Abort, (char *)zErr, P4_STATIC); sqlite4VdbeJumpHere(v, addrTest); } if( pIdx->nCover>0 ){ regData = regKey+1; | | | 2328 2329 2330 2331 2332 2333 2334 2335 2336 2337 2338 2339 2340 2341 2342 | addrTest = sqlite4VdbeAddOp4Int(v, OP_IsUnique, iIdx, 0, regKey, 0); sqlite4HaltConstraint(pParse, OE_Abort, (char *)zErr, P4_STATIC); sqlite4VdbeJumpHere(v, addrTest); } if( pIdx->nCover>0 ){ regData = regKey+1; sqlite4EncodeIndexValue(pParse, iTab, pIdx, regData); } sqlite4VdbeAddOp3(v, OP_IdxInsert, iIdx, regData, regKey); sqlite4ReleaseTempRange(pParse, regKey, 2); } sqlite4VdbeAddOp2(v, OP_Next, iTab, addr1+1); sqlite4VdbeJumpHere(v, addr1); |
︙ | ︙ |
Changes to src/env.c.
︙ | ︙ | |||
175 176 177 178 179 180 181 | sqlite4_mutex_free(pEnv->pFactoryMutex); sqlite4_mutex_free(pEnv->pPrngMutex); sqlite4_mutex_free(pEnv->pMemMutex); pEnv->pMemMutex = 0; while( (pMkr = pEnv->pFactory)!=0 && pMkr->isPerm==0 ){ KVFactory *pNext = pMkr->pNext; sqlite4_free(pEnv, pMkr); | | | 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 | sqlite4_mutex_free(pEnv->pFactoryMutex); sqlite4_mutex_free(pEnv->pPrngMutex); sqlite4_mutex_free(pEnv->pMemMutex); pEnv->pMemMutex = 0; while( (pMkr = pEnv->pFactory)!=0 && pMkr->isPerm==0 ){ KVFactory *pNext = pMkr->pNext; sqlite4_free(pEnv, pMkr); pEnv->pFactory = pNext; } sqlite4MutexEnd(pEnv); sqlite4MallocEnd(pEnv); pEnv->isInit = 0; } return SQLITE4_OK; } |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
14 15 16 17 18 19 20 | */ #ifndef _SQLITEINT_H_ #define _SQLITEINT_H_ #define SQLITE4_OMIT_PROGRESS_CALLBACK 1 #define SQLITE4_OMIT_VIRTUALTABLE 1 #define SQLITE4_OMIT_LOCALTIME 1 | < | 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | */ #ifndef _SQLITEINT_H_ #define _SQLITEINT_H_ #define SQLITE4_OMIT_PROGRESS_CALLBACK 1 #define SQLITE4_OMIT_VIRTUALTABLE 1 #define SQLITE4_OMIT_LOCALTIME 1 /* ** These #defines should enable >2GB file support on POSIX if the ** underlying operating system supports it. If the OS lacks ** large file support, or if the OS is windows, these should be no-ops. ** ** Ticket #2739: The _LARGEFILE_SOURCE macro must appear before any |
︙ | ︙ | |||
2791 2792 2793 2794 2795 2796 2797 2798 2799 2800 2801 2802 2803 2804 | int sqlite4ExprIsInteger(Expr*, int*); int sqlite4ExprCanBeNull(const Expr*); void sqlite4ExprCodeIsNullJump(Vdbe*, const Expr*, int, int); int sqlite4ExprNeedsNoAffinityChange(const Expr*, char); void sqlite4GenerateRowDelete(Parse*, Table*, int, int, int, Trigger *, int); void sqlite4GenerateRowIndexDelete(Parse*, Table*, int, int, int*); void sqlite4EncodeIndexKey(Parse *, Index *, int, Index *, int, int, int); void sqlite4GenerateConstraintChecks(Parse*,Table*,int,int, int*,int,int,int,int,int*); void sqlite4CompleteInsertion(Parse*, Table*, int, int, int*, int, int, int); int sqlite4OpenTableAndIndices(Parse*, Table*, int, int); void sqlite4BeginWriteOperation(Parse*, int, int); void sqlite4MultiWrite(Parse*); void sqlite4MayAbort(Parse*); | > | 2790 2791 2792 2793 2794 2795 2796 2797 2798 2799 2800 2801 2802 2803 2804 | int sqlite4ExprIsInteger(Expr*, int*); int sqlite4ExprCanBeNull(const Expr*); void sqlite4ExprCodeIsNullJump(Vdbe*, const Expr*, int, int); int sqlite4ExprNeedsNoAffinityChange(const Expr*, char); void sqlite4GenerateRowDelete(Parse*, Table*, int, int, int, Trigger *, int); void sqlite4GenerateRowIndexDelete(Parse*, Table*, int, int, int*); void sqlite4EncodeIndexKey(Parse *, Index *, int, Index *, int, int, int); void sqlite4EncodeIndexValue(Parse*, int, Index*, int); void sqlite4GenerateConstraintChecks(Parse*,Table*,int,int, int*,int,int,int,int,int*); void sqlite4CompleteInsertion(Parse*, Table*, int, int, int*, int, int, int); int sqlite4OpenTableAndIndices(Parse*, Table*, int, int); void sqlite4BeginWriteOperation(Parse*, int, int); void sqlite4MultiWrite(Parse*); void sqlite4MayAbort(Parse*); |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
967 968 969 970 971 972 973 | } /* ** Return the total number of fields in the index pIdx, including any ** trailing primary key fields. */ static int idxColumnCount(Index *pIdx, Index *pPk){ | | | 967 968 969 970 971 972 973 974 975 976 977 978 979 980 981 | } /* ** Return the total number of fields in the index pIdx, including any ** trailing primary key fields. */ static int idxColumnCount(Index *pIdx, Index *pPk){ return (pIdx->nColumn + ((pPk==0 || pIdx==pPk) ? 0 : pPk->nColumn)); } /* ** Initialize a WHERE clause scanner object. Return a pointer to the ** first match. Return NULL if there are no matches. ** ** The scanner will be searching the WHERE clause pWC. It will look |
︙ | ︙ | |||
2108 2109 2110 2111 2112 2113 2114 | int nByte; /* Byte of memory needed for pIdx */ Index *pIdx; /* Object describing the transient index */ Vdbe *v; /* Prepared statement under construction */ int addrInit; /* Address of the initialization bypass jump */ Table *pTable; /* The table being indexed */ KeyInfo *pKeyinfo; /* Key information for the index */ int addrTop; /* Top of the index fill loop */ | | > > | 2108 2109 2110 2111 2112 2113 2114 2115 2116 2117 2118 2119 2120 2121 2122 2123 2124 2125 2126 2127 | int nByte; /* Byte of memory needed for pIdx */ Index *pIdx; /* Object describing the transient index */ Vdbe *v; /* Prepared statement under construction */ int addrInit; /* Address of the initialization bypass jump */ Table *pTable; /* The table being indexed */ KeyInfo *pKeyinfo; /* Key information for the index */ int addrTop; /* Top of the index fill loop */ int regRecord; /* Register holding an index value */ int regKey; /* Register holding an index key */ int n; /* Column counter */ int i; /* Loop counter */ int mxBitCol; /* Maximum column in pSrc->colUsed */ int iPkCsr; /* PK cursor number */ CollSeq *pColl; /* Collating sequence to on a column */ WhereLoop *pLoop; /* The Loop object */ Bitmask idxCols; /* Bitmap of columns used for indexing */ Bitmask extraCols; /* Bitmap of additional columns */ u8 sentWarning = 0; /* True if a warnning has been issued */ /* Generate code to skip over the creation and initialization of the |
︙ | ︙ | |||
2138 2139 2140 2141 2142 2143 2144 | for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){ if( termCanDriveIndex(pTerm, pSrc, notReady) ){ int iCol = pTerm->u.leftColumn; Bitmask cMask = iCol>=BMS ? MASKBIT(BMS-1) : MASKBIT(iCol); testcase( iCol==BMS ); testcase( iCol==BMS-1 ); if( !sentWarning ){ | | | 2140 2141 2142 2143 2144 2145 2146 2147 2148 2149 2150 2151 2152 2153 2154 | for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){ if( termCanDriveIndex(pTerm, pSrc, notReady) ){ int iCol = pTerm->u.leftColumn; Bitmask cMask = iCol>=BMS ? MASKBIT(BMS-1) : MASKBIT(iCol); testcase( iCol==BMS ); testcase( iCol==BMS-1 ); if( !sentWarning ){ sqlite4_log(pParse->db->pEnv, SQLITE4_WARNING_AUTOINDEX, "automatic index on %s(%s)", pTable->zName, pTable->aCol[iCol].zName); sentWarning = 1; } if( (idxCols & cMask)==0 ){ if( whereLoopResize(pParse->db, pLoop, nColumn+1) ) return; pLoop->aLTerm[nColumn++] = pTerm; |
︙ | ︙ | |||
2189 2190 2191 2192 2193 2194 2195 2196 2197 2198 2199 2200 2201 2202 | pLoop->u.btree.pIndex = pIdx; pIdx->azColl = (char**)&pIdx[1]; pIdx->aiColumn = (int*)&pIdx->azColl[nColumn]; pIdx->aSortOrder = (u8*)&pIdx->aiColumn[nColumn]; pIdx->zName = "auto-index"; pIdx->nColumn = nColumn; pIdx->pTable = pTable; n = 0; idxCols = 0; for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){ if( termCanDriveIndex(pTerm, pSrc, notReady) ){ int iCol = pTerm->u.leftColumn; Bitmask cMask = iCol>=BMS ? MASKBIT(BMS-1) : MASKBIT(iCol); testcase( iCol==BMS-1 ); | > > > | 2191 2192 2193 2194 2195 2196 2197 2198 2199 2200 2201 2202 2203 2204 2205 2206 2207 | pLoop->u.btree.pIndex = pIdx; pIdx->azColl = (char**)&pIdx[1]; pIdx->aiColumn = (int*)&pIdx->azColl[nColumn]; pIdx->aSortOrder = (u8*)&pIdx->aiColumn[nColumn]; pIdx->zName = "auto-index"; pIdx->nColumn = nColumn; pIdx->pTable = pTable; pIdx->aiCover = pIdx->aiColumn; pIdx->nCover = pIdx->nColumn; pIdx->eIndexType = SQLITE4_INDEX_TEMP; n = 0; idxCols = 0; for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){ if( termCanDriveIndex(pTerm, pSrc, notReady) ){ int iCol = pTerm->u.leftColumn; Bitmask cMask = iCol>=BMS ? MASKBIT(BMS-1) : MASKBIT(iCol); testcase( iCol==BMS-1 ); |
︙ | ︙ | |||
2236 2237 2238 2239 2240 2241 2242 | assert( pLevel->iIdxCur>=0 ); pLevel->iIdxCur = pParse->nTab++; sqlite4VdbeAddOp4(v, OP_OpenAutoindex, pLevel->iIdxCur, nColumn+1, 0, (char*)pKeyinfo, P4_KEYINFO_HANDOFF); VdbeComment((v, "for %s", pTable->zName)); /* Fill the automatic index with content */ | > | | > | > | | | | 2241 2242 2243 2244 2245 2246 2247 2248 2249 2250 2251 2252 2253 2254 2255 2256 2257 2258 2259 2260 2261 2262 2263 2264 2265 2266 | assert( pLevel->iIdxCur>=0 ); pLevel->iIdxCur = pParse->nTab++; sqlite4VdbeAddOp4(v, OP_OpenAutoindex, pLevel->iIdxCur, nColumn+1, 0, (char*)pKeyinfo, P4_KEYINFO_HANDOFF); VdbeComment((v, "for %s", pTable->zName)); /* Fill the automatic index with content */ iPkCsr = pLevel->iTabCur; addrTop = sqlite4VdbeAddOp1(v, OP_Rewind, iPkCsr); regRecord = sqlite4GetTempRange(pParse, 2); regKey = regRecord + 1; sqlite4EncodeIndexKey(pParse, 0, iPkCsr, pIdx, pLevel->iIdxCur, 1, regKey); sqlite4EncodeIndexValue(pParse, iPkCsr, pIdx, regRecord); sqlite4VdbeAddOp3(v, OP_IdxInsert, pLevel->iIdxCur, regRecord, regKey); /* sqlite4VdbeChangeP5(v, OPFLAG_USESEEKRESULT); */ sqlite4VdbeAddOp2(v, OP_Next, pLevel->iTabCur, addrTop+1); sqlite4VdbeChangeP5(v, SQLITE4_STMTSTATUS_AUTOINDEX); sqlite4VdbeJumpHere(v, addrTop); sqlite4ReleaseTempRange(pParse, regRecord, 2); /* Jump here when skipping the initialization */ sqlite4VdbeJumpHere(v, addrInit); } #endif /* SQLITE4_OMIT_AUTOMATIC_INDEX */ #ifndef SQLITE4_OMIT_VIRTUALTABLE |
︙ | ︙ |
Changes to test/autoindex1.test.
︙ | ︙ | |||
18 19 20 21 22 23 24 25 26 27 28 29 30 31 | # If the library is not compiled with automatic index support then # skip all tests in this file. # ifcapable {!autoindex} { finish_test return } # With automatic index turned off, we do a full scan of the T2 table do_test autoindex1-100 { db eval { CREATE TABLE t1(a,b); INSERT INTO t1 VALUES(1,11); INSERT INTO t1 VALUES(2,22); | > > > > > > > > | 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | # If the library is not compiled with automatic index support then # skip all tests in this file. # ifcapable {!autoindex} { finish_test return } # Setup for logging #db close #sqlite4_shutdown #test_sqlite4_log [list lappend ::log] #set ::log [list] #sqlite4 db test.db # With automatic index turned off, we do a full scan of the T2 table do_test autoindex1-100 { db eval { CREATE TABLE t1(a,b); INSERT INTO t1 VALUES(1,11); INSERT INTO t1 VALUES(2,22); |
︙ | ︙ | |||
44 45 46 47 48 49 50 | } {63} do_test autoindex1-102 { db status autoindex } {0} # With autoindex turned on, we build an index once and then use that index # to find T2 values. | < > > > > > > > > > > > > > > > > > > | | 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 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 | } {63} do_test autoindex1-102 { db status autoindex } {0} # With autoindex turned on, we build an index once and then use that index # to find T2 values. do_test autoindex1-110 { db eval { PRAGMA automatic_index=ON; SELECT b, d FROM t1 JOIN t2 ON a=c ORDER BY b; } } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988} do_test autoindex1-111 { db status step } {7} do_test autoindex1-112 { db status autoindex } {7} #do_test autoindex1-113 { #set ::log #} {SQLITE_WARNING_AUTOINDEX {automatic index on t2(c)}} #db close #sqlite4_shutdown #test_sqlite4_log #sqlite4_initialize #sqlite4 db test.db # The same test as above, but this time the T2 query is a subquery rather # than a join. do_test autoindex1-200 { db eval { PRAGMA automatic_index=OFF; SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1; } } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988} do_test autoindex1-201 { db status step } {35} do_test autoindex1-202 { db status autoindex } {0} do_test autoindex1-210 { db eval { PRAGMA automatic_index=ON; ANALYZE; UPDATE sqlite_stat1 SET stat='10000' WHERE tbl='t1'; ANALYZE sqlite_master; SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1; } } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988} do_test autoindex1-211 { db status step } {7} do_test autoindex1-212 { db status autoindex } {7} # Modify the second table of the join while the join is in progress # do_execsql_test autoindex1-299 { UPDATE sqlite_stat1 SET stat='10000' WHERE tbl='t2'; ANALYZE sqlite_master; EXPLAIN QUERY PLAN SELECT b, d FROM t1 CROSS JOIN t2 ON (c=a); } {/AUTOMATIC COVERING INDEX/} do_test autoindex1-300 { set r {} db eval {SELECT b, d FROM t1 CROSS JOIN t2 ON (c=a)} { lappend r $b $d db eval {UPDATE t2 SET d=d+1} } set r } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988} do_test autoindex1-310 { db eval {SELECT d FROM t2 ORDER BY d} |
︙ | ︙ | |||
140 141 142 143 144 145 146 147 148 149 150 | # Ticket [8011086c85c6c404014c947fcf3eb9f42b184a0d] from 2010-07-08 # Make sure automatic indices are not created for the RHS of an IN expression # that is not a correlated subquery. # do_execsql_test autoindex1-500 { CREATE TABLE t501(a INTEGER PRIMARY KEY, b); CREATE TABLE t502(x INTEGER PRIMARY KEY, y); EXPLAIN QUERY PLAN SELECT b FROM t501 WHERE t501.a IN (SELECT x FROM t502 WHERE y=?); } { | > > > | | | | | | | 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 | # Ticket [8011086c85c6c404014c947fcf3eb9f42b184a0d] from 2010-07-08 # Make sure automatic indices are not created for the RHS of an IN expression # that is not a correlated subquery. # 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; EXPLAIN QUERY PLAN SELECT b FROM t501 WHERE t501.a IN (SELECT x FROM t502 WHERE y=?); } { 0 0 0 {SEARCH TABLE t501 USING INDEX t501 (a=?)} 0 0 0 {EXECUTE LIST SUBQUERY 1} 1 0 0 {SCAN TABLE t502 USING INDEX t502} } do_execsql_test autoindex1-501 { EXPLAIN QUERY PLAN SELECT b FROM t501 WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b); } { 0 0 0 {SCAN TABLE t501 USING INDEX t501} 0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 1 0 0 {SEARCH TABLE t502 USING AUTOMATIC COVERING INDEX (y=?)} } do_execsql_test autoindex1-502 { EXPLAIN QUERY PLAN SELECT b FROM t501 WHERE t501.a=123 AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b); } { 0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?)} 0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 1 0 0 {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 |
︙ | ︙ | |||
237 238 239 240 241 242 243 | 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; } { | | | | | | | > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 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 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 | 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; } { 1 0 0 {SCAN TABLE sheep AS s} 1 1 1 {SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?)} 1 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2} 2 0 0 {SEARCH TABLE flock_owner AS later USING COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date>? AND owner_change_date<?)} 0 0 0 {SCAN TABLE sheep AS x USING INDEX sheep_reg_flock_index} 0 1 1 {SEARCH SUBQUERY 1 AS y USING AUTOMATIC COVERING INDEX (sheep_no=?)} } do_execsql_test autoindex1-700 { CREATE TABLE t5(a, b, c); EXPLAIN QUERY PLAN SELECT a FROM t5 WHERE b=10 ORDER BY c; } { 0 0 0 {SCAN TABLE t5} 0 0 0 {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( _id INTEGER PRIMARY KEY AUTOINCREMENT, account_name TEXT, account_type TEXT, data_set TEXT ); CREATE TABLE data( _id INTEGER PRIMARY KEY AUTOINCREMENT, package_id INTEGER REFERENCES package(_id), mimetype_id INTEGER REFERENCES mimetype(_id) NOT NULL, raw_contact_id INTEGER REFERENCES raw_contacts(_id) NOT NULL, is_read_only INTEGER NOT NULL DEFAULT 0, is_primary INTEGER NOT NULL DEFAULT 0, is_super_primary INTEGER NOT NULL DEFAULT 0, data_version INTEGER NOT NULL DEFAULT 0, data1 TEXT, data2 TEXT, data3 TEXT, data4 TEXT, data5 TEXT, data6 TEXT, data7 TEXT, data8 TEXT, data9 TEXT, data10 TEXT, data11 TEXT, data12 TEXT, data13 TEXT, data14 TEXT, data15 TEXT, data_sync1 TEXT, data_sync2 TEXT, data_sync3 TEXT, data_sync4 TEXT ); CREATE TABLE mimetypes( _id INTEGER PRIMARY KEY AUTOINCREMENT, mimetype TEXT NOT NULL ); CREATE TABLE raw_contacts( _id INTEGER PRIMARY KEY AUTOINCREMENT, account_id INTEGER REFERENCES accounts(_id), sourceid TEXT, raw_contact_is_read_only INTEGER NOT NULL DEFAULT 0, version INTEGER NOT NULL DEFAULT 1, dirty INTEGER NOT NULL DEFAULT 0, deleted INTEGER NOT NULL DEFAULT 0, contact_id INTEGER REFERENCES contacts(_id), aggregation_mode INTEGER NOT NULL DEFAULT 0, aggregation_needed INTEGER NOT NULL DEFAULT 1, custom_ringtone TEXT, send_to_voicemail INTEGER NOT NULL DEFAULT 0, times_contacted INTEGER NOT NULL DEFAULT 0, last_time_contacted INTEGER, starred INTEGER NOT NULL DEFAULT 0, display_name TEXT, display_name_alt TEXT, display_name_source INTEGER NOT NULL DEFAULT 0, phonetic_name TEXT, phonetic_name_style TEXT, sort_key TEXT, sort_key_alt TEXT, name_verified INTEGER NOT NULL DEFAULT 0, sync1 TEXT, sync2 TEXT, sync3 TEXT, sync4 TEXT, sync_uid TEXT, sync_version INTEGER NOT NULL DEFAULT 1, has_calendar_event INTEGER NOT NULL DEFAULT 0, modified_time INTEGER, is_restricted INTEGER DEFAULT 0, yp_source TEXT, method_selected INTEGER DEFAULT 0, custom_vibration_type INTEGER DEFAULT 0, custom_ringtone_path TEXT, message_notification TEXT, message_notification_path TEXT ); CREATE INDEX data_mimetype_data1_index ON data (mimetype_id,data1); CREATE INDEX data_raw_contact_id ON data (raw_contact_id); CREATE UNIQUE INDEX mime_type ON mimetypes (mimetype); CREATE INDEX raw_contact_sort_key1_index ON raw_contacts (sort_key); CREATE INDEX raw_contact_sort_key2_index ON raw_contacts (sort_key_alt); CREATE INDEX raw_contacts_contact_id_index ON raw_contacts (contact_id); CREATE INDEX raw_contacts_source_id_account_id_index ON raw_contacts (sourceid, account_id); ANALYZE sqlite_master; INSERT INTO sqlite_stat1 VALUES('raw_contacts','raw_contact_sort_key2_index','1600 4'); INSERT INTO sqlite_stat1 VALUES('raw_contacts','raw_contact_sort_key1_index','1600 4'); INSERT INTO sqlite_stat1 VALUES('raw_contacts','raw_contacts_source_id_account_id_index', '1600 1600 1600'); INSERT INTO sqlite_stat1 VALUES('raw_contacts','raw_contacts_contact_id_index','1600 1'); INSERT INTO sqlite_stat1 VALUES('mimetypes','mime_type','12 1'); INSERT INTO sqlite_stat1 VALUES('data','data_mimetype_data1_index','9819 2455 3'); INSERT INTO sqlite_stat1 VALUES('data','data_raw_contact_id','9819 7'); INSERT INTO sqlite_stat1 VALUES('accounts',NULL,'1'); DROP TABLE IF EXISTS sqlite_stat3; ANALYZE sqlite_master; EXPLAIN QUERY PLAN SELECT * FROM data JOIN mimetypes ON (data.mimetype_id=mimetypes._id) JOIN raw_contacts ON (data.raw_contact_id=raw_contacts._id) JOIN accounts ON (raw_contacts.account_id=accounts._id) WHERE mimetype_id=10 AND data14 IS NOT NULL; } {/SEARCH TABLE data .*SEARCH TABLE raw_contacts/} do_execsql_test autoindex1-801 { EXPLAIN QUERY PLAN SELECT * FROM data JOIN mimetypes ON (data.mimetype_id=mimetypes._id) JOIN raw_contacts ON (data.raw_contact_id=raw_contacts._id) JOIN accounts ON (raw_contacts.account_id=accounts._id) WHERE mimetypes._id=10 AND data14 IS NOT NULL; } {/SEARCH TABLE data .*SEARCH TABLE raw_contacts/} finish_test |
Changes to test/collate2.test.
︙ | ︙ | |||
637 638 639 640 641 642 643 | # Test that when one side has a default collation type and the other # does not, the collation type is used. do_test collate2-4.3 { execsql { SELECT collate2t1.a FROM collate2t1, collate2t3 WHERE collate2t1.b = collate2t3.b||''; } | | | | 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 | # Test that when one side has a default collation type and the other # does not, the collation type is used. do_test collate2-4.3 { execsql { SELECT collate2t1.a FROM collate2t1, collate2t3 WHERE collate2t1.b = collate2t3.b||''; } } {AA Aa aA aa} do_test collate2-4.4 { execsql { SELECT collate2t1.a FROM collate2t1, collate2t3 WHERE collate2t3.b||'' = collate2t1.b; } } {AA Aa aA aa} do_test collate2-4.5 { execsql { DROP TABLE collate2t3; } } {} |
︙ | ︙ |