Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Changes to src/analyze.c.

  1515   1515   ** stored in pIdx->aSample[]. 
  1516   1516   */
  1517   1517   static void initAvgEq(Index *pIdx){
  1518   1518     if( pIdx ){
  1519   1519       IndexSample *aSample = pIdx->aSample;
  1520   1520       IndexSample *pFinal = &aSample[pIdx->nSample-1];
  1521   1521       int iCol;
  1522         -    for(iCol=0; iCol<pIdx->nKeyCol; iCol++){
         1522  +    int nCol = 1;
         1523  +    if( pIdx->nSampleCol>1 ){
         1524  +      /* If this is stat4 data, then calculate aAvgEq[] values for all
         1525  +      ** sample columns except the last. The last is always set to 1, as
         1526  +      ** once the trailing PK fields are considered all index keys are
         1527  +      ** unique.  */
         1528  +      nCol = pIdx->nSampleCol-1;
         1529  +      pIdx->aAvgEq[nCol] = 1;
         1530  +    }
         1531  +    for(iCol=0; iCol<nCol; iCol++){
  1523   1532         int i;                    /* Used to iterate through samples */
  1524   1533         tRowcnt sumEq = 0;        /* Sum of the nEq values */
  1525   1534         tRowcnt nSum = 0;         /* Number of terms contributing to sumEq */
  1526   1535         tRowcnt avgEq = 0;
  1527   1536         tRowcnt nDLt = pFinal->anDLt[iCol];
  1528   1537   
  1529   1538         /* Set nSum to the number of distinct (iCol+1) field prefixes that
................................................................................
  1538   1547           }
  1539   1548         }
  1540   1549         if( nDLt>nSum ){
  1541   1550           avgEq = (pFinal->anLt[iCol] - sumEq)/(nDLt - nSum);
  1542   1551         }
  1543   1552         if( avgEq==0 ) avgEq = 1;
  1544   1553         pIdx->aAvgEq[iCol] = avgEq;
  1545         -      if( pIdx->nSampleCol==1 ) break;
  1546   1554       }
  1547   1555     }
  1548   1556   }
  1549   1557   
  1550   1558   /*
  1551   1559   ** Look up an index by name.  Or, if the name of a WITHOUT ROWID table
  1552   1560   ** is supplied instead, find the PRIMARY KEY index for that table.
................................................................................
  1597   1605     }
  1598   1606     rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0);
  1599   1607     sqlite3DbFree(db, zSql);
  1600   1608     if( rc ) return rc;
  1601   1609   
  1602   1610     while( sqlite3_step(pStmt)==SQLITE_ROW ){
  1603   1611       int nIdxCol = 1;              /* Number of columns in stat4 records */
  1604         -    int nAvgCol = 1;              /* Number of entries in Index.aAvgEq */
  1605   1612   
  1606   1613       char *zIndex;   /* Index name */
  1607   1614       Index *pIdx;    /* Pointer to the index object */
  1608   1615       int nSample;    /* Number of samples */
  1609   1616       int nByte;      /* Bytes of space required */
  1610   1617       int i;          /* Bytes of space required */
  1611   1618       tRowcnt *pSpace;
................................................................................
  1615   1622       nSample = sqlite3_column_int(pStmt, 1);
  1616   1623       pIdx = findIndexOrPrimaryKey(db, zIndex, zDb);
  1617   1624       assert( pIdx==0 || bStat3 || pIdx->nSample==0 );
  1618   1625       /* Index.nSample is non-zero at this point if data has already been
  1619   1626       ** loaded from the stat4 table. In this case ignore stat3 data.  */
  1620   1627       if( pIdx==0 || pIdx->nSample ) continue;
  1621   1628       if( bStat3==0 ){
  1622         -      nIdxCol = pIdx->nKeyCol+1;
  1623         -      nAvgCol = pIdx->nKeyCol;
         1629  +      assert( !HasRowid(pIdx->pTable) || pIdx->nColumn==pIdx->nKeyCol+1 );
         1630  +      if( !HasRowid(pIdx->pTable) && IsPrimaryKeyIndex(pIdx) ){
         1631  +        nIdxCol = pIdx->nKeyCol;
         1632  +      }else{
         1633  +        nIdxCol = pIdx->nColumn;
         1634  +      }
  1624   1635       }
  1625   1636       pIdx->nSampleCol = nIdxCol;
  1626   1637       nByte = sizeof(IndexSample) * nSample;
  1627   1638       nByte += sizeof(tRowcnt) * nIdxCol * 3 * nSample;
  1628         -    nByte += nAvgCol * sizeof(tRowcnt);     /* Space for Index.aAvgEq[] */
         1639  +    nByte += nIdxCol * sizeof(tRowcnt);     /* Space for Index.aAvgEq[] */
  1629   1640   
  1630   1641       pIdx->aSample = sqlite3DbMallocZero(db, nByte);
  1631   1642       if( pIdx->aSample==0 ){
  1632   1643         sqlite3_finalize(pStmt);
  1633   1644         return SQLITE_NOMEM;
  1634   1645       }
  1635   1646       pSpace = (tRowcnt*)&pIdx->aSample[nSample];
  1636         -    pIdx->aAvgEq = pSpace; pSpace += nAvgCol;
         1647  +    pIdx->aAvgEq = pSpace; pSpace += nIdxCol;
  1637   1648       for(i=0; i<nSample; i++){
  1638   1649         pIdx->aSample[i].anEq = pSpace; pSpace += nIdxCol;
  1639   1650         pIdx->aSample[i].anLt = pSpace; pSpace += nIdxCol;
  1640   1651         pIdx->aSample[i].anDLt = pSpace; pSpace += nIdxCol;
  1641   1652       }
  1642   1653       assert( ((u8*)pSpace)-nByte==(u8*)(pIdx->aSample) );
  1643   1654     }

Changes to src/where.c.

  1955   1955         iLower = 0;
  1956   1956         iUpper = aSample[0].anLt[iCol];
  1957   1957       }else{
  1958   1958         i64 nRow0 = sqlite3LogEstToInt(pIdx->aiRowLogEst[0]);
  1959   1959         iUpper = i>=pIdx->nSample ? nRow0 : aSample[i].anLt[iCol];
  1960   1960         iLower = aSample[i-1].anEq[iCol] + aSample[i-1].anLt[iCol];
  1961   1961       }
  1962         -    aStat[1] = (pIdx->nKeyCol>iCol ? pIdx->aAvgEq[iCol] : 1);
         1962  +    aStat[1] = pIdx->aAvgEq[iCol];
  1963   1963       if( iLower>=iUpper ){
  1964   1964         iGap = 0;
  1965   1965       }else{
  1966   1966         iGap = iUpper - iLower;
  1967   1967       }
  1968   1968       if( roundUp ){
  1969   1969         iGap = (iGap*2)/3;

Changes to test/analyze9.test.

  1014   1014   # and (d<20) over (c='one' AND a='A').
  1015   1015   foreach {tn where res} {
  1016   1016     1 "c='one' AND a='B' AND d < 20"   {/*INDEX i3 (c=? AND a=?)*/}
  1017   1017     2 "c='one' AND a='A' AND d < 20"   {/*INDEX i4 (d<?)*/}
  1018   1018   } {
  1019   1019     do_eqp_test 22.2.$tn "SELECT * FROM t3 WHERE $where" $res
  1020   1020   }
         1021  +
         1022  +proc int_to_char {i} {
         1023  +  set ret ""
         1024  +  set char [list a b c d e f g h i j]
         1025  +  foreach {div} {1000 100 10 1} {
         1026  +    append ret [lindex $char [expr ($i / $div) % 10]]
         1027  +  }
         1028  +  set ret
         1029  +}
         1030  +db func int_to_char int_to_char
         1031  +
         1032  +do_execsql_test 23.0 {
         1033  +  CREATE TABLE t4(
         1034  +    a COLLATE nocase, b, c, 
         1035  +    d, e, f, 
         1036  +    PRIMARY KEY(c, b, a)
         1037  +  ) WITHOUT ROWID;
         1038  +  CREATE INDEX i41 ON t4(e);
         1039  +  CREATE INDEX i42 ON t4(f);
         1040  +
         1041  +  WITH data(a, b, c, d, e, f) AS (
         1042  +    SELECT int_to_char(0), 'xyz', 'zyx', '*', 0, 0
         1043  +    UNION ALL
         1044  +    SELECT 
         1045  +      int_to_char(f+1), b, c, d, (e+1) % 2, f+1
         1046  +    FROM data WHERE f<1024
         1047  +  )
         1048  +  INSERT INTO t4 SELECT a, b, c, d, e, f FROM data;
         1049  +  ANALYZE;
         1050  +} {}
         1051  +
         1052  +do_eqp_test 23.1 {
         1053  +  SELECT * FROM t4 WHERE 
         1054  +    (e=1 AND b='xyz' AND c='zyx' AND a<'AEA') AND f<300
         1055  +} {
         1056  +  0 0 0 {SEARCH TABLE t4 USING INDEX i41 (e=? AND c=? AND b=? AND a<?)}
         1057  +}
         1058  +
         1059  +do_eqp_test 23.2 {
         1060  +  SELECT * FROM t4 WHERE 
         1061  +    (e=1 AND b='xyz' AND c='zyx' AND a<'JJJ') AND f<300
         1062  +} {
         1063  +  0 0 0 {SEARCH TABLE t4 USING INDEX i42 (f<?)}
         1064  +}
  1021   1065   
  1022   1066   finish_test
  1023   1067   
  1024   1068