Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Ensure that all fields are loaded from the stat4 table for records that correspond to indexes on WITHOUT ROWID tables with composite primary keys. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
21981e35062cc6b30e9576786cbf5526 |
| User & Date: | dan 2014-07-01 11:54:02 |
Context
|
2014-07-01
| ||
| 15:22 | Add another test to verify that SQLite is using stat4 data for composite primary keys on WITHOUT ROWID tables. (check-in: 0df1fe72 user: dan tags: trunk) | |
| 11:54 | Ensure that all fields are loaded from the stat4 table for records that correspond to indexes on WITHOUT ROWID tables with composite primary keys. (check-in: 21981e35 user: dan tags: trunk) | |
|
2014-06-30
| ||
| 19:28 | Bump the version number to 3.8.6. (check-in: f925e9ba user: drh tags: trunk) | |
Changes
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