Index: src/analyze.c ================================================================== --- src/analyze.c +++ src/analyze.c @@ -244,10 +244,11 @@ /* Open the sqlite_stat[134] tables for writing. */ for(i=0; aTable[i].zCols; i++){ assert( idb, p); } /* -** Implementation of the stat_init(N,C) SQL function. The two parameters -** are the number of rows in the table or index (C) and the number of columns -** in the index (N). The second argument (C) is only used for STAT3 and STAT4. +** 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). */ @@ -380,10 +390,11 @@ 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; @@ -390,12 +401,15 @@ #endif /* Decode the three function arguments */ UNUSED_PARAMETER(argc); nCol = sqlite3_value_int(argv[0]); - assert( nCol>1 ); /* >1 because it includes the rowid column */ + 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 */ @@ -413,10 +427,11 @@ } 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 { @@ -423,13 +438,13 @@ 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[1])/(mxSample/3+1) + 1); + 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[1])*0xd0944565; + 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]); @@ -448,11 +463,11 @@ /* Return a pointer to the allocated object to the caller */ sqlite3_result_blob(context, p, sizeof(p), stat4Destructor); } static const FuncDef statInitFuncdef = { - 1+IsStat34, /* nArg */ + 2+IsStat34, /* nArg */ SQLITE_UTF8, /* funcFlags */ 0, /* pUserData */ 0, /* pNext */ statInit, /* xFunc */ 0, /* xStep */ @@ -689,11 +704,11 @@ Stat4Accum *p = (Stat4Accum*)sqlite3_value_blob(argv[0]); int iChng = sqlite3_value_int(argv[1]); UNUSED_PARAMETER( argc ); UNUSED_PARAMETER( context ); - assert( p->nCol>1 ); /* Includes rowid field */ + assert( p->nCol>0 ); assert( iChngnCol ); if( p->nRow==0 ){ /* This is the first call to this function. Do initialization. */ for(i=0; inCol; i++) p->current.anEq[i] = 1; @@ -817,19 +832,19 @@ ** I = (K+D-1)/D */ char *z; int i; - char *zRet = sqlite3MallocZero(p->nCol * 25); + 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->nCol-1); i++){ + for(i=0; inKeyCol; 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] ); @@ -994,22 +1009,23 @@ int addrNextRow; /* Address of "next_row:" */ const char *zIdxName; /* Name of the index */ if( pOnlyIdx && pOnlyIdx!=pIdx ) continue; if( pIdx->pPartIdxWhere==0 ) needTableCnt = 0; - VdbeNoopComment((v, "Begin analysis of %s", pIdx->zName)); - nCol = pIdx->nKeyCol; + 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. */ - if( IsPrimaryKeyIndex(pIdx) && !HasRowid(pTab) ){ - zIdxName = pTab->zName; - }else{ - zIdxName = pIdx->zName; - } 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 @@ -1059,16 +1075,17 @@ ** (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+2); + sqlite3VdbeAddOp2(v, OP_Count, iIdxCur, regStat4+3); #endif - sqlite3VdbeAddOp2(v, OP_Integer, nCol+1, regStat4+1); + 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, 1+IsStat34); + sqlite3VdbeChangeP5(v, 2+IsStat34); /* Implementation of the following: ** ** Rewind csr ** if eof(csr) goto end_of_scan; @@ -1166,11 +1183,11 @@ int regSampleRowid = regCol + nCol; int addrNext; int addrIsNull; u8 seekOp = HasRowid(pTab) ? OP_NotExists : OP_NotFound; - pParse->nMem = MAX(pParse->nMem, regCol+nCol+1); + 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); @@ -1188,11 +1205,11 @@ #else for(i=0; iaiColumn[i]; sqlite3ExprCodeGetColumnOfTable(v, pTab, iTabCur, iCol, regCol+i); } - sqlite3VdbeAddOp3(v, OP_MakeRecord, regCol, nCol+1, regSample); + 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 */ Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -542,11 +542,11 @@ 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->nKeyCol) ) return 0; + if( NEVER(j>pIdx->nColumn) ) return 0; } pScan->zCollName = pIdx->azColl[j]; }else{ pScan->idxaff = 0; pScan->zCollName = 0; @@ -2199,11 +2199,11 @@ int rc; /* Subfunction return code */ tRowcnt a[2]; /* Statistics */ int bOk; assert( nEq>=1 ); - assert( nEq<=(p->nKeyCol+1) ); + assert( nEq<=p->nColumn ); assert( p->aSample!=0 ); assert( p->nSample>0 ); assert( pBuilder->nRecValidp->nKeyCol ){ + if( nEq>=p->nColumn ){ *pnRow = 1; return SQLITE_OK; } aff = p->pTable->aCol[p->aiColumn[nEq-1]].affinity; @@ -2643,11 +2643,11 @@ } sqlite3StrAccumInit(&txt, 0, 0, SQLITE_MAX_LENGTH); txt.db = db; sqlite3StrAccumAppend(&txt, " (", 2); for(i=0; inKeyCol ) ? "rowid" : aCol[aiColumn[i]].zName; + 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); @@ -2656,15 +2656,15 @@ } } j = i; if( pLoop->wsFlags&WHERE_BTM_LIMIT ){ - char *z = (j==pIndex->nKeyCol ) ? "rowid" : aCol[aiColumn[j]].zName; + char *z = aiColumn[j] < 0 ? "rowid" : aCol[aiColumn[j]].zName; explainAppendTerm(&txt, i++, z, ">"); } if( pLoop->wsFlags&WHERE_TOP_LIMIT ){ - char *z = (j==pIndex->nKeyCol ) ? "rowid" : aCol[aiColumn[j]].zName; + char *z = aiColumn[j] < 0 ? "rowid" : aCol[aiColumn[j]].zName; explainAppendTerm(&txt, i, z, "<"); } sqlite3StrAccumAppend(&txt, ")", 1); return sqlite3StrAccumFinish(&txt); } @@ -4162,16 +4162,13 @@ }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->nKeyCol ); - if( pNew->u.btree.nEq < pProbe->nKeyCol ){ - iCol = pProbe->aiColumn[pNew->u.btree.nEq]; - }else{ - iCol = -1; - } + assert( pNew->u.btree.nEqnColumn ); + 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; @@ -4357,11 +4354,11 @@ }else{ pNew->nOut = nOutUnadjusted; } if( (pNew->wsFlags & WHERE_TOP_LIMIT)==0 - && pNew->u.btree.nEq<(pProbe->nKeyCol + (pProbe->zName!=0)) + && pNew->u.btree.nEqnColumn ){ whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, nInMul+nIn); } pNew->nOut = saved_nOut; #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 @@ -4504,10 +4501,11 @@ ** 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; Index: test/analyze9.test ================================================================== --- test/analyze9.test +++ test/analyze9.test @@ -950,7 +950,75 @@ 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 rowid45 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? +} {/*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" {/*i46 (c=? AND a>?)*/} + 4 1 "c = 2 AND a = 1 AND b<10" {/*i46 (c=? AND a=? AND b5" {/*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