Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch stat4-without-rowid Excluding Merge-Ins
This is equivalent to a diff from 6f86d89b88 to 053a210e31
2014-06-30
| ||
18:57 | Fix for ticket [b2fa5424e6fcb15]: Better define the format of the sqlite_stat4 file for WITHOUT ROWID tables and make sure the ANALYZE command generates a file in the appropriate format. Use the sqlite_stat4 data to enable the use of WHERE terms that cover all indexed columns plus some prefix of columns in the primary key. (check-in: bc2de8095f user: drh tags: trunk) | |
18:02 | Fix a problem in where.c with using the stat4 sample data of an index on a WITHOUT ROWID table. (Closed-Leaf check-in: 053a210e31 user: dan tags: stat4-without-rowid) | |
17:07 | Fix the STAT4 information for WITHOUT ROWID tables. (check-in: 5d8628fdff user: drh tags: stat4-without-rowid) | |
13:32 | Generate complete samples for sqlite_stat4 on WITHOUT ROWID tables. Ticket [b2fa5424e6fcb15b5] (check-in: 8cb43eddab user: drh tags: stat4-without-rowid) | |
11:14 | Add makefile targets for various diagnostic tools, such as showstat4. Fix harmless compiler warnings in diagnostic tools. (check-in: 6f86d89b88 user: drh tags: trunk) | |
2014-06-26
| ||
22:17 | Add some more IN operator tests. (check-in: fb32e374b7 user: mistachkin tags: trunk) | |
Changes to src/analyze.c.
︙ | ︙ | |||
242 243 244 245 246 247 248 249 250 251 252 253 254 255 | } /* Open the sqlite_stat[134] tables for writing. */ for(i=0; aTable[i].zCols; i++){ assert( i<ArraySize(aTable) ); sqlite3VdbeAddOp4Int(v, OP_OpenWrite, iStatCur+i, aRoot[i], iDb, 3); sqlite3VdbeChangeP5(v, aCreateTbl[i]); } } /* ** Recommended number of samples for sqlite_stat4 */ #ifndef SQLITE_STAT4_SAMPLES | > | 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 | } /* Open the sqlite_stat[134] tables for writing. */ for(i=0; aTable[i].zCols; i++){ assert( i<ArraySize(aTable) ); sqlite3VdbeAddOp4Int(v, OP_OpenWrite, iStatCur+i, aRoot[i], iDb, 3); sqlite3VdbeChangeP5(v, aCreateTbl[i]); VdbeComment((v, aTable[i].zName)); } } /* ** Recommended number of samples for sqlite_stat4 */ #ifndef SQLITE_STAT4_SAMPLES |
︙ | ︙ | |||
277 278 279 280 281 282 283 | int iCol; /* If !isPSample, the reason for inclusion */ u32 iHash; /* Tiebreaker hash */ #endif }; struct Stat4Accum { tRowcnt nRow; /* Number of rows in the entire table */ tRowcnt nPSample; /* How often to do a periodic sample */ | | > | 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 | int iCol; /* If !isPSample, the reason for inclusion */ u32 iHash; /* Tiebreaker hash */ #endif }; struct Stat4Accum { tRowcnt nRow; /* Number of rows in the entire table */ tRowcnt nPSample; /* How often to do a periodic sample */ int nCol; /* Number of columns in index + pk/rowid */ int nKeyCol; /* Number of index columns w/o the pk/rowid */ int mxSample; /* Maximum number of samples to accumulate */ Stat4Sample current; /* Current row as a Stat4Sample */ u32 iPrn; /* Pseudo-random number used for sampling */ Stat4Sample *aBest; /* Array of nCol best samples */ int iMin; /* Index in a[] of entry with minimum score */ int nSample; /* Current number of samples */ int iGet; /* Index of current sample accessed by stat_get() */ |
︙ | ︙ | |||
363 364 365 366 367 368 369 | for(i=0; i<p->mxSample; i++) sampleClear(p->db, p->a+i); sampleClear(p->db, &p->current); #endif sqlite3DbFree(p->db, p); } /* | | > > > | > | > > > > > | > > > | 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 415 416 417 | for(i=0; i<p->mxSample; i++) sampleClear(p->db, p->a+i); sampleClear(p->db, &p->current); #endif sqlite3DbFree(p->db, p); } /* ** Implementation of the stat_init(N,K,C) SQL function. The three parameters ** are: ** N: The number of columns in the index including the rowid/pk ** K: The number of columns in the index excluding the rowid/pk ** C: The number of rows in the index ** ** C is only used for STAT3 and STAT4. ** ** For ordinary rowid tables, N==K+1. But for WITHOUT ROWID tables, ** N=K+P where P is the number of columns in the primary key. For the ** covering index that implements the original WITHOUT ROWID table, N==K. ** ** This routine allocates the Stat4Accum object in heap memory. The return ** value is a pointer to the the Stat4Accum object encoded as a blob (i.e. ** the size of the blob is sizeof(void*) bytes). */ static void statInit( sqlite3_context *context, int argc, sqlite3_value **argv ){ Stat4Accum *p; int nCol; /* Number of columns in index being sampled */ int nKeyCol; /* Number of key columns */ int nColUp; /* nCol rounded up for alignment */ int n; /* Bytes of space to allocate */ sqlite3 *db; /* Database connection */ #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 int mxSample = SQLITE_STAT4_SAMPLES; #endif /* Decode the three function arguments */ UNUSED_PARAMETER(argc); nCol = sqlite3_value_int(argv[0]); assert( nCol>0 ); nColUp = sizeof(tRowcnt)<8 ? (nCol+1)&~1 : nCol; nKeyCol = sqlite3_value_int(argv[1]); assert( nKeyCol<=nCol ); assert( nKeyCol>0 ); /* Allocate the space required for the Stat4Accum object */ n = sizeof(*p) + sizeof(tRowcnt)*nColUp /* Stat4Accum.anEq */ + sizeof(tRowcnt)*nColUp /* Stat4Accum.anDLt */ #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 + sizeof(tRowcnt)*nColUp /* Stat4Accum.anLt */ |
︙ | ︙ | |||
411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 | sqlite3_result_error_nomem(context); return; } p->db = db; p->nRow = 0; p->nCol = nCol; p->current.anDLt = (tRowcnt*)&p[1]; p->current.anEq = &p->current.anDLt[nColUp]; #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 { u8 *pSpace; /* Allocated space not yet assigned */ int i; /* Used to iterate through p->aSample[] */ p->iGet = -1; p->mxSample = mxSample; | > | | | 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 | sqlite3_result_error_nomem(context); return; } p->db = db; p->nRow = 0; p->nCol = nCol; p->nKeyCol = nKeyCol; p->current.anDLt = (tRowcnt*)&p[1]; p->current.anEq = &p->current.anDLt[nColUp]; #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 { u8 *pSpace; /* Allocated space not yet assigned */ int i; /* Used to iterate through p->aSample[] */ p->iGet = -1; p->mxSample = mxSample; p->nPSample = (tRowcnt)(sqlite3_value_int64(argv[2])/(mxSample/3+1) + 1); p->current.anLt = &p->current.anEq[nColUp]; p->iPrn = nCol*0x689e962d ^ sqlite3_value_int(argv[2])*0xd0944565; /* Set up the Stat4Accum.a[] and aBest[] arrays */ p->a = (struct Stat4Sample*)&p->current.anLt[nColUp]; p->aBest = &p->a[mxSample]; pSpace = (u8*)(&p->a[mxSample+nCol]); for(i=0; i<(mxSample+nCol); i++){ p->a[i].anEq = (tRowcnt *)pSpace; pSpace += (sizeof(tRowcnt) * nColUp); |
︙ | ︙ | |||
446 447 448 449 450 451 452 | } #endif /* Return a pointer to the allocated object to the caller */ sqlite3_result_blob(context, p, sizeof(p), stat4Destructor); } static const FuncDef statInitFuncdef = { | | | 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 | } #endif /* Return a pointer to the allocated object to the caller */ sqlite3_result_blob(context, p, sizeof(p), stat4Destructor); } static const FuncDef statInitFuncdef = { 2+IsStat34, /* nArg */ SQLITE_UTF8, /* funcFlags */ 0, /* pUserData */ 0, /* pNext */ statInit, /* xFunc */ 0, /* xStep */ 0, /* xFinalize */ "stat_init", /* zName */ |
︙ | ︙ | |||
687 688 689 690 691 692 693 | /* The three function arguments */ Stat4Accum *p = (Stat4Accum*)sqlite3_value_blob(argv[0]); int iChng = sqlite3_value_int(argv[1]); UNUSED_PARAMETER( argc ); UNUSED_PARAMETER( context ); | | | 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 | /* The three function arguments */ Stat4Accum *p = (Stat4Accum*)sqlite3_value_blob(argv[0]); int iChng = sqlite3_value_int(argv[1]); UNUSED_PARAMETER( argc ); UNUSED_PARAMETER( context ); assert( p->nCol>0 ); assert( iChng<p->nCol ); if( p->nRow==0 ){ /* This is the first call to this function. Do initialization. */ for(i=0; i<p->nCol; i++) p->current.anEq[i] = 1; }else{ /* Second and subsequent calls get processed here */ |
︙ | ︙ | |||
815 816 817 818 819 820 821 | ** rows, then each estimate is computed as: ** ** I = (K+D-1)/D */ char *z; int i; | | | | 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 | ** rows, then each estimate is computed as: ** ** I = (K+D-1)/D */ char *z; int i; char *zRet = sqlite3MallocZero( (p->nKeyCol+1)*25 ); if( zRet==0 ){ sqlite3_result_error_nomem(context); return; } sqlite3_snprintf(24, zRet, "%llu", (u64)p->nRow); z = zRet + sqlite3Strlen30(zRet); for(i=0; i<p->nKeyCol; i++){ u64 nDistinct = p->current.anDLt[i] + 1; u64 iVal = (p->nRow + nDistinct - 1) / nDistinct; sqlite3_snprintf(24, z, " %llu", iVal); z += sqlite3Strlen30(z); assert( p->current.anEq[i] ); } assert( z[0]=='\0' && z>zRet ); |
︙ | ︙ | |||
992 993 994 995 996 997 998 | int addrRewind; /* Address of "OP_Rewind iIdxCur" */ int addrGotoChng0; /* Address of "Goto addr_chng_0" */ int addrNextRow; /* Address of "next_row:" */ const char *zIdxName; /* Name of the index */ if( pOnlyIdx && pOnlyIdx!=pIdx ) continue; if( pIdx->pPartIdxWhere==0 ) needTableCnt = 0; | | | > > > > > < < < < < > | 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 | int addrRewind; /* Address of "OP_Rewind iIdxCur" */ int addrGotoChng0; /* Address of "Goto addr_chng_0" */ int addrNextRow; /* Address of "next_row:" */ const char *zIdxName; /* Name of the index */ if( pOnlyIdx && pOnlyIdx!=pIdx ) continue; if( pIdx->pPartIdxWhere==0 ) needTableCnt = 0; if( !HasRowid(pTab) && IsPrimaryKeyIndex(pIdx) ){ nCol = pIdx->nKeyCol; zIdxName = pTab->zName; }else{ nCol = pIdx->nColumn; zIdxName = pIdx->zName; } aGotoChng = sqlite3DbMallocRaw(db, sizeof(int)*(nCol+1)); if( aGotoChng==0 ) continue; /* Populate the register containing the index name. */ sqlite3VdbeAddOp4(v, OP_String8, 0, regIdxname, 0, zIdxName, 0); VdbeComment((v, "Analysis for %s.%s", pTab->zName, zIdxName)); /* ** Pseudo-code for loop that calls stat_push(): ** ** Rewind csr ** if eof(csr) goto end_of_scan; ** regChng = 0 |
︙ | ︙ | |||
1057 1058 1059 1060 1061 1062 1063 | ** ** (1) the number of columns in the index including the rowid, ** (2) the number of rows in the index, ** ** The second argument is only used for STAT3 and STAT4 */ #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 | | | > | | 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 | ** ** (1) the number of columns in the index including the rowid, ** (2) the number of rows in the index, ** ** The second argument is only used for STAT3 and STAT4 */ #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 sqlite3VdbeAddOp2(v, OP_Count, iIdxCur, regStat4+3); #endif sqlite3VdbeAddOp2(v, OP_Integer, nCol, regStat4+1); sqlite3VdbeAddOp2(v, OP_Integer, pIdx->nKeyCol, regStat4+2); sqlite3VdbeAddOp3(v, OP_Function, 0, regStat4+1, regStat4); sqlite3VdbeChangeP4(v, -1, (char*)&statInitFuncdef, P4_FUNCDEF); sqlite3VdbeChangeP5(v, 2+IsStat34); /* Implementation of the following: ** ** Rewind csr ** if eof(csr) goto end_of_scan; ** regChng = 0 ** goto next_push_0; |
︙ | ︙ | |||
1164 1165 1166 1167 1168 1169 1170 | int regSample = regStat1+3; int regCol = regStat1+4; int regSampleRowid = regCol + nCol; int addrNext; int addrIsNull; u8 seekOp = HasRowid(pTab) ? OP_NotExists : OP_NotFound; | | | 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 | int regSample = regStat1+3; int regCol = regStat1+4; int regSampleRowid = regCol + nCol; int addrNext; int addrIsNull; u8 seekOp = HasRowid(pTab) ? OP_NotExists : OP_NotFound; pParse->nMem = MAX(pParse->nMem, regCol+nCol); addrNext = sqlite3VdbeCurrentAddr(v); callStatGet(v, regStat4, STAT_GET_ROWID, regSampleRowid); addrIsNull = sqlite3VdbeAddOp1(v, OP_IsNull, regSampleRowid); VdbeCoverage(v); callStatGet(v, regStat4, STAT_GET_NEQ, regEq); callStatGet(v, regStat4, STAT_GET_NLT, regLt); |
︙ | ︙ | |||
1186 1187 1188 1189 1190 1191 1192 | sqlite3ExprCodeGetColumnOfTable(v, pTab, iTabCur, pIdx->aiColumn[0], regSample); #else for(i=0; i<nCol; i++){ i16 iCol = pIdx->aiColumn[i]; sqlite3ExprCodeGetColumnOfTable(v, pTab, iTabCur, iCol, regCol+i); } | | | 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 | sqlite3ExprCodeGetColumnOfTable(v, pTab, iTabCur, pIdx->aiColumn[0], regSample); #else for(i=0; i<nCol; i++){ i16 iCol = pIdx->aiColumn[i]; sqlite3ExprCodeGetColumnOfTable(v, pTab, iTabCur, iCol, regCol+i); } sqlite3VdbeAddOp3(v, OP_MakeRecord, regCol, nCol, regSample); #endif sqlite3VdbeAddOp3(v, OP_MakeRecord, regTabname, 6, regTemp); sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur+1, regNewRowid); sqlite3VdbeAddOp3(v, OP_Insert, iStatCur+1, regTemp, regNewRowid); sqlite3VdbeAddOp2(v, OP_Goto, 1, addrNext); /* P1==1 for end-of-loop */ sqlite3VdbeJumpHere(v, addrIsNull); } |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
540 541 542 543 544 545 546 | /* memset(pScan, 0, sizeof(*pScan)); */ pScan->pOrigWC = pWC; pScan->pWC = pWC; if( pIdx && iColumn>=0 ){ pScan->idxaff = pIdx->pTable->aCol[iColumn].affinity; for(j=0; pIdx->aiColumn[j]!=iColumn; j++){ | | | 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 | /* memset(pScan, 0, sizeof(*pScan)); */ pScan->pOrigWC = pWC; pScan->pWC = pWC; if( pIdx && iColumn>=0 ){ pScan->idxaff = pIdx->pTable->aCol[iColumn].affinity; for(j=0; pIdx->aiColumn[j]!=iColumn; j++){ if( NEVER(j>pIdx->nColumn) ) return 0; } pScan->zCollName = pIdx->azColl[j]; }else{ pScan->idxaff = 0; pScan->zCollName = 0; } pScan->opMask = opMask; |
︙ | ︙ | |||
2197 2198 2199 2200 2201 2202 2203 | UnpackedRecord *pRec = pBuilder->pRec; u8 aff; /* Column affinity */ int rc; /* Subfunction return code */ tRowcnt a[2]; /* Statistics */ int bOk; assert( nEq>=1 ); | | | | 2197 2198 2199 2200 2201 2202 2203 2204 2205 2206 2207 2208 2209 2210 2211 2212 2213 2214 2215 2216 2217 2218 2219 2220 2221 2222 2223 2224 | UnpackedRecord *pRec = pBuilder->pRec; u8 aff; /* Column affinity */ int rc; /* Subfunction return code */ tRowcnt a[2]; /* Statistics */ int bOk; assert( nEq>=1 ); assert( nEq<=p->nColumn ); assert( p->aSample!=0 ); assert( p->nSample>0 ); assert( pBuilder->nRecValid<nEq ); /* If values are not available for all fields of the index to the left ** of this one, no estimate can be made. Return SQLITE_NOTFOUND. */ if( pBuilder->nRecValid<(nEq-1) ){ return SQLITE_NOTFOUND; } /* This is an optimization only. The call to sqlite3Stat4ProbeSetValue() ** below would return the same value. */ if( nEq>=p->nColumn ){ *pnRow = 1; return SQLITE_OK; } aff = p->pTable->aCol[p->aiColumn[nEq-1]].affinity; rc = sqlite3Stat4ProbeSetValue(pParse, p, &pRec, pExpr, aff, nEq-1, &bOk); pBuilder->pRec = pRec; |
︙ | ︙ | |||
2641 2642 2643 2644 2645 2646 2647 | if( nEq==0 && (pLoop->wsFlags & (WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))==0 ){ return 0; } sqlite3StrAccumInit(&txt, 0, 0, SQLITE_MAX_LENGTH); txt.db = db; sqlite3StrAccumAppend(&txt, " (", 2); for(i=0; i<nEq; i++){ | | | | | 2641 2642 2643 2644 2645 2646 2647 2648 2649 2650 2651 2652 2653 2654 2655 2656 2657 2658 2659 2660 2661 2662 2663 2664 2665 2666 2667 2668 2669 2670 2671 2672 | if( nEq==0 && (pLoop->wsFlags & (WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))==0 ){ return 0; } sqlite3StrAccumInit(&txt, 0, 0, SQLITE_MAX_LENGTH); txt.db = db; sqlite3StrAccumAppend(&txt, " (", 2); for(i=0; i<nEq; i++){ char *z = aiColumn[i] < 0 ? "rowid" : aCol[aiColumn[i]].zName; if( i>=nSkip ){ explainAppendTerm(&txt, i, z, "="); }else{ if( i ) sqlite3StrAccumAppend(&txt, " AND ", 5); sqlite3StrAccumAppend(&txt, "ANY(", 4); sqlite3StrAccumAppendAll(&txt, z); sqlite3StrAccumAppend(&txt, ")", 1); } } j = i; if( pLoop->wsFlags&WHERE_BTM_LIMIT ){ char *z = aiColumn[j] < 0 ? "rowid" : aCol[aiColumn[j]].zName; explainAppendTerm(&txt, i++, z, ">"); } if( pLoop->wsFlags&WHERE_TOP_LIMIT ){ char *z = aiColumn[j] < 0 ? "rowid" : aCol[aiColumn[j]].zName; explainAppendTerm(&txt, i, z, "<"); } sqlite3StrAccumAppend(&txt, ")", 1); return sqlite3StrAccumFinish(&txt); } /* |
︙ | ︙ | |||
4160 4161 4162 4163 4164 4165 4166 | }else if( pProbe->tnum<=0 || (pSrc->jointype & JT_LEFT)!=0 ){ opMask = WO_EQ|WO_IN|WO_GT|WO_GE|WO_LT|WO_LE; }else{ opMask = WO_EQ|WO_IN|WO_ISNULL|WO_GT|WO_GE|WO_LT|WO_LE; } if( pProbe->bUnordered ) opMask &= ~(WO_GT|WO_GE|WO_LT|WO_LE); | | < | < < | | 4160 4161 4162 4163 4164 4165 4166 4167 4168 4169 4170 4171 4172 4173 4174 4175 4176 | }else if( pProbe->tnum<=0 || (pSrc->jointype & JT_LEFT)!=0 ){ opMask = WO_EQ|WO_IN|WO_GT|WO_GE|WO_LT|WO_LE; }else{ opMask = WO_EQ|WO_IN|WO_ISNULL|WO_GT|WO_GE|WO_LT|WO_LE; } if( pProbe->bUnordered ) opMask &= ~(WO_GT|WO_GE|WO_LT|WO_LE); assert( pNew->u.btree.nEq<pProbe->nColumn ); iCol = pProbe->aiColumn[pNew->u.btree.nEq]; pTerm = whereScanInit(&scan, pBuilder->pWC, pSrc->iCursor, iCol, opMask, pProbe); saved_nEq = pNew->u.btree.nEq; saved_nSkip = pNew->u.btree.nSkip; saved_nLTerm = pNew->nLTerm; saved_wsFlags = pNew->wsFlags; saved_prereq = pNew->prereq; |
︙ | ︙ | |||
4355 4356 4357 4358 4359 4360 4361 | if( pNew->wsFlags & WHERE_COLUMN_RANGE ){ pNew->nOut = saved_nOut; }else{ pNew->nOut = nOutUnadjusted; } if( (pNew->wsFlags & WHERE_TOP_LIMIT)==0 | | | 4352 4353 4354 4355 4356 4357 4358 4359 4360 4361 4362 4363 4364 4365 4366 | if( pNew->wsFlags & WHERE_COLUMN_RANGE ){ pNew->nOut = saved_nOut; }else{ pNew->nOut = nOutUnadjusted; } if( (pNew->wsFlags & WHERE_TOP_LIMIT)==0 && pNew->u.btree.nEq<pProbe->nColumn ){ whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, nInMul+nIn); } pNew->nOut = saved_nOut; #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 pBuilder->nRecValid = nRecValid; #endif |
︙ | ︙ | |||
4502 4503 4504 4505 4506 4507 4508 4509 4510 4511 4512 4513 4514 4515 | /* There is no INDEXED BY clause. Create a fake Index object in local ** variable sPk to represent the rowid primary key index. Make this ** fake index the first in a chain of Index objects with all of the real ** indices to follow */ Index *pFirst; /* First of real indices on the table */ memset(&sPk, 0, sizeof(Index)); sPk.nKeyCol = 1; sPk.aiColumn = &aiColumnPk; sPk.aiRowLogEst = aiRowEstPk; sPk.onError = OE_Replace; sPk.pTable = pTab; sPk.szIdxRow = pTab->szTabRow; aiRowEstPk[0] = pTab->nRowLogEst; aiRowEstPk[1] = 0; | > | 4499 4500 4501 4502 4503 4504 4505 4506 4507 4508 4509 4510 4511 4512 4513 | /* There is no INDEXED BY clause. Create a fake Index object in local ** variable sPk to represent the rowid primary key index. Make this ** fake index the first in a chain of Index objects with all of the real ** indices to follow */ Index *pFirst; /* First of real indices on the table */ memset(&sPk, 0, sizeof(Index)); sPk.nKeyCol = 1; sPk.nColumn = 1; sPk.aiColumn = &aiColumnPk; sPk.aiRowLogEst = aiRowEstPk; sPk.onError = OE_Replace; sPk.pTable = pTab; sPk.szIdxRow = pTab->szTabRow; aiRowEstPk[0] = pTab->nRowLogEst; aiRowEstPk[1] = 0; |
︙ | ︙ |
Changes to test/analyze9.test.
︙ | ︙ | |||
948 949 950 951 952 953 954 955 956 | for {set i 0} {$i<16} {incr i} { set val "$i $i $i $i" do_execsql_test 20.3.$i { SELECT count(*) FROM sqlite_stat4 WHERE lrange(test_decode(sample), 0, 3)=$val } {1} } finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 974 975 976 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 | for {set i 0} {$i<16} {incr i} { set val "$i $i $i $i" do_execsql_test 20.3.$i { SELECT count(*) FROM sqlite_stat4 WHERE lrange(test_decode(sample), 0, 3)=$val } {1} } #------------------------------------------------------------------------- # reset_db do_execsql_test 21.0 { CREATE TABLE t2(a, b); CREATE INDEX i2 ON t2(a); } do_test 21.1 { for {set i 1} {$i < 100} {incr i} { execsql { INSERT INTO t2 VALUES(CASE WHEN $i < 80 THEN 'one' ELSE 'two' END, $i) } } execsql ANALYZE } {} # Condition (a='one') matches 80% of the table. (rowid<10) reduces this to # 10%, but (rowid<50) only reduces it to 50%. So in the first case below # the index is used. In the second, it is not. # do_eqp_test 21.2 { SELECT * FROM t2 WHERE a='one' AND rowid < 10 } {/*USING INDEX i2 (a=? AND rowid<?)*/} do_eqp_test 21.3 { SELECT * FROM t2 WHERE a='one' AND rowid < 50 } {/*USING INTEGER PRIMARY KEY*/} #------------------------------------------------------------------------- # reset_db do_execsql_test 22.0 { CREATE TABLE t3(a, b, c, d, PRIMARY KEY(a, b)) WITHOUT ROWID; } do_execsql_test 22.1 { WITH r(x) AS ( SELECT 1 UNION ALL SELECT x+1 FROM r WHERE x<=100 ) INSERT INTO t3 SELECT CASE WHEN (x>45 AND x<96) THEN 'B' ELSE 'A' END, /* Column "a" */ x, /* Column "b" */ CASE WHEN (x<51) THEN 'one' ELSE 'two' END, /* Column "c" */ x /* Column "d" */ FROM r; CREATE INDEX i3 ON t3(c); CREATE INDEX i4 ON t3(d); ANALYZE; } # Expression (c='one' AND a='B') matches 5 table rows. But (c='one' AND a=A') # matches 45. Expression (d<?) matches 20. Neither index is a covering index. # # Therefore, with stat4 data, SQLite prefers (c='one' AND a='B') over (d<20), # and (d<20) over (c='one' AND a='A'). foreach {tn where res} { 1 "c='one' AND a='B' AND d < 20" {/*INDEX i3 (c=? AND a=?)*/} 2 "c='one' AND a='A' AND d < 20" {/*INDEX i4 (d<?)*/} } { do_eqp_test 22.2.$tn "SELECT * FROM t3 WHERE $where" $res } finish_test |
Changes to test/without_rowid1.test.
︙ | ︙ | |||
209 210 211 212 213 214 215 216 217 | CREATE TABLE t42(x); INSERT INTO t42 VALUES('xyz'); SELECT t42.rowid FROM t41, t42; } {1} do_execsql_test 4.2 { SELECT t42.rowid FROM t42, t41; } {1} finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | CREATE TABLE t42(x); INSERT INTO t42 VALUES('xyz'); SELECT t42.rowid FROM t41, t42; } {1} do_execsql_test 4.2 { SELECT t42.rowid FROM t42, t41; } {1} #-------------------------------------------------------------------------- # The following tests verify that the trailing PK fields added to each # entry in an index on a WITHOUT ROWID table are used correctly. # do_execsql_test 5.0 { CREATE TABLE t45(a PRIMARY KEY, b, c) WITHOUT ROWID; CREATE INDEX i45 ON t45(b); INSERT INTO t45 VALUES(2, 'one', 'x'); INSERT INTO t45 VALUES(4, 'one', 'x'); INSERT INTO t45 VALUES(6, 'one', 'x'); INSERT INTO t45 VALUES(8, 'one', 'x'); INSERT INTO t45 VALUES(10, 'one', 'x'); INSERT INTO t45 VALUES(1, 'two', 'x'); INSERT INTO t45 VALUES(3, 'two', 'x'); INSERT INTO t45 VALUES(5, 'two', 'x'); INSERT INTO t45 VALUES(7, 'two', 'x'); INSERT INTO t45 VALUES(9, 'two', 'x'); } do_eqp_test 5.1 { SELECT * FROM t45 WHERE b=? AND a>? } {/*USING INDEX i45 (b=? AND a>?)*/} do_execsql_test 5.2 { SELECT * FROM t45 WHERE b='two' AND a>4 } {5 two x 7 two x 9 two x} do_execsql_test 5.3 { SELECT * FROM t45 WHERE b='one' AND a<8 } { 2 one x 4 one x 6 one x } do_execsql_test 5.4 { CREATE TABLE t46(a, b, c, d, PRIMARY KEY(a, b)) WITHOUT ROWID; WITH r(x) AS ( SELECT 1 UNION ALL SELECT x+1 FROM r WHERE x<100 ) INSERT INTO t46 SELECT x / 20, x % 20, x % 10, x FROM r; } set queries { 1 2 "c = 5 AND a = 1" {/*i46 (c=? AND a=?)*/} 2 6 "c = 4 AND a < 3" {/*i46 (c=? AND a<?)*/} 3 4 "c = 2 AND a >= 3" {/*i46 (c=? AND a>?)*/} 4 1 "c = 2 AND a = 1 AND b<10" {/*i46 (c=? AND a=? AND b<?)*/} 5 1 "c = 0 AND a = 0 AND b>5" {/*i46 (c=? AND a=? AND b>?)*/} } foreach {tn cnt where eqp} $queries { do_execsql_test 5.5.$tn.1 "SELECT count(*) FROM t46 WHERE $where" $cnt } do_execsql_test 5.6 { CREATE INDEX i46 ON t46(c); } foreach {tn cnt where eqp} $queries { do_execsql_test 5.7.$tn.1 "SELECT count(*) FROM t46 WHERE $where" $cnt do_eqp_test 5.7.$tn.2 "SELECT count(*) FROM t46 WHERE $where" $eqp } finish_test |