/ Check-in [23643131]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Make sure that all automatic indices are covering indices. Otherwise, the table and index might be used together in a query but the table could get out of sync with the automatic index through out-of-band changes.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | experimental
Files: files | file ages | folders
SHA1: 2364313142668b3d0ad144447b4862709be929cd
User & Date: drh 2010-04-07 14:59:45
References
2011-06-23
16:17 New ticket [91e2e8ba] Automatic indices cause undesirable type conversions. artifact: 43bb4571 user: drh
Context
2010-04-07
16:54
Wrap all automatic index changes inside SQLITE_OMIT_AUTOMATIC_INDEX. Add the automatic_index PRAGMA to turn it on and off. check-in: a811a47f user: drh tags: experimental
14:59
Make sure that all automatic indices are covering indices. Otherwise, the table and index might be used together in a query but the table could get out of sync with the automatic index through out-of-band changes. check-in: 23643131 user: drh tags: experimental
14:33
Enhance comments on the SrcList object definition to better explain the operation of the SrcList.a[].colUsed field. No changes to code. check-in: c0f67ea1 user: drh tags: experimental
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

1632
1633
1634
1635
1636
1637
1638



















1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
....
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
1694
1695
1696
1697
....
1719
1720
1721
1722
1723
1724
1725


1726


1727
1728
1729
1730
1731
1732
1733
1734
1735
1736

1737
1738
1739
1740
1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751

















1752
1753
1754
1755
1756
1757
1758
1759
....
1763
1764
1765
1766
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
1780
1781
1782
1783
1784

















1785
1786
1787
1788
1789
1790
1791
1792
....
2590
2591
2592
2593
2594
2595
2596
2597
2598
2599
2600
2601
2602
2603
2604
....
4260
4261
4262
4263
4264
4265
4266
4267

4268

4269
4270
4271
4272
4273
4274
4275
        pCost->plan.wsFlags = flags;
        pCost->plan.u.pTerm = pTerm;
      }
    }
  }
#endif /* SQLITE_OMIT_OR_OPTIMIZATION */
}




















/*
** If the query plan for pSrc specified in pCost is a full table scan
** an indexing is allows (if there is no NOT INDEXED clause) and it
** possible to construct a transient index that would perform better
** than a full table scan even when the cost of constructing the index
** is taken into account, then alter the query plan to use the
** transient index.
*/
static void bestTransientIndex(
  Parse *pParse,              /* The parsing context */
................................................................................
    return;
  }

  /* Search for any equality comparison term */
  pTable = pSrc->pTab;
  pWCEnd = &pWC->a[pWC->nTerm];
  for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
    if( pTerm->leftCursor==pSrc->iCursor
       && (pTerm->prereqRight & notReady)==0
       && (pTerm->eOperator & WO_EQ)!=0
       && sqlite3IndexAffinityOk(pTerm->pExpr,
                                 pTable->aCol[pTerm->u.leftColumn].affinity)
    ){
      WHERETRACE(("auto-index reduces cost from %.2f to %.2f\n",
                    pCost->rCost, costTempIdx));
      pCost->rCost = costTempIdx;
      pCost->nRow = logN + 1;
      pCost->plan.wsFlags = WHERE_TEMP_INDEX;
      pCost->used = pTerm->prereqRight;
      break;
................................................................................
  int regIsInit;              /* Register set by initialization */
  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 record */
  int n;                      /* Column counter */


  CollSeq *pColl;             /* Collating sequence to on a column */



  /* Generate code to skip over the creation and initialization of the
  ** transient index on 2nd and subsequent iterations of the loop. */
  v = pParse->pVdbe;
  assert( v!=0 );
  regIsInit = ++pParse->nMem;
  addrInit = sqlite3VdbeAddOp1(v, OP_If, regIsInit);
  sqlite3VdbeAddOp2(v, OP_Integer, 1, regIsInit);

  /* Count the number of columns that will be added to the index */

  nColumn = 0;
  pTable = pSrc->pTab;
  pWCEnd = &pWC->a[pWC->nTerm];
  for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
    if( pTerm->leftCursor==pSrc->iCursor
       && (pTerm->prereqRight & notReady)==0
       && (pTerm->eOperator & WO_EQ)!=0
       && sqlite3IndexAffinityOk(pTerm->pExpr,
                                 pTable->aCol[pTerm->u.leftColumn].affinity)
    ){
      nColumn++;
    }
  }
  assert( nColumn>0 );
  pLevel->plan.nEq = nColumn;

















  pLevel->plan.wsFlags |= WHERE_COLUMN_EQ | WO_EQ;

  /* Construct the Index object to describe this index */
  nByte = sizeof(Index);
  nByte += nColumn*sizeof(int);     /* Index.aiColumn */
  nByte += nColumn*sizeof(char*);   /* Index.azColl */
  nByte += nColumn;                 /* Index.aSortOrder */
  pIdx = sqlite3DbMallocZero(pParse->db, nByte);
................................................................................
  pIdx->aiColumn = (int*)&pIdx->azColl[nColumn];
  pIdx->aSortOrder = (u8*)&pIdx->aiColumn[nColumn];
  pIdx->zName = "auto-index";
  pIdx->nColumn = nColumn;
  pIdx->pTable = pTable;
  n = 0;
  for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
    if( pTerm->leftCursor==pSrc->iCursor
       && (pTerm->prereqRight & notReady)==0
       && (pTerm->eOperator & WO_EQ)!=0
       && sqlite3IndexAffinityOk(pTerm->pExpr,
                                 pTable->aCol[pTerm->u.leftColumn].affinity)
    ){
      int iCol = pTerm->u.leftColumn;
      Expr *pX;
      pIdx->aiColumn[n] = iCol;
      pX = pTerm->pExpr;
      pColl = sqlite3BinaryCompareCollSeq(pParse, pX->pLeft, pX->pRight);
      pIdx->azColl[n] = pColl->zName;
      n++;
    }
  }

















  assert( n==pIdx->nColumn );

  /* Create the transient index */
  pKeyinfo = sqlite3IndexKeyinfo(pParse, pIdx);
  assert( pLevel->iIdxCur>=0 );
  sqlite3VdbeAddOp4(v, OP_OpenAutoindex, pLevel->iIdxCur, nColumn+1, 0,
                    (char*)pKeyinfo, P4_KEYINFO_HANDOFF);
  VdbeComment((v, "for %s", pTable->zName));
................................................................................
      }else{
        bSort = 1;
      }
    }

    /* If currently calculating the cost of using an index (not the IPK
    ** index), determine if all required column data may be obtained without 
    ** seeking to entries in the main table (i.e. if the index is a covering
    ** index for this query). If it is, set the WHERE_IDX_ONLY flag in
    ** wsFlags. Otherwise, set the bLookup variable to true.  */
    if( pIdx && wsFlags ){
      Bitmask m = pSrc->colUsed;
      int j;
      for(j=0; j<pIdx->nColumn; j++){
        int x = pIdx->aiColumn[j];
................................................................................
  /* Close all of the cursors that were opened by sqlite3WhereBegin.
  */
  assert( pWInfo->nLevel==1 || pWInfo->nLevel==pTabList->nSrc );
  for(i=0, pLevel=pWInfo->a; i<pWInfo->nLevel; i++, pLevel++){
    struct SrcList_item *pTabItem = &pTabList->a[pLevel->iFrom];
    Table *pTab = pTabItem->pTab;
    assert( pTab!=0 );
    if( (pTab->tabFlags & TF_Ephemeral)!=0 || pTab->pSelect ) continue;

    if( (pWInfo->wctrlFlags & WHERE_OMIT_CLOSE)==0 ){

      int ws = pLevel->plan.wsFlags;
      if( !pWInfo->okOnePass && (ws & WHERE_IDX_ONLY)==0 ){
        sqlite3VdbeAddOp1(v, OP_Close, pTabItem->iCursor);
      }
      if( (ws & (WHERE_INDEXED|WHERE_TEMP_INDEX)) == WHERE_INDEXED ){
        sqlite3VdbeAddOp1(v, OP_Close, pLevel->iIdxCur);
      }







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



|







 







|
<
<
<
<
<







 







>
>

>
>









|
>



|
|
|
|
|
<
<





>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|







 







|
<
<
<
<
<
<
|
|
<





>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|







 







|







 







|
>
|
>







1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
....
1697
1698
1699
1700
1701
1702
1703
1704





1705
1706
1707
1708
1709
1710
1711
....
1733
1734
1735
1736
1737
1738
1739
1740
1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762
1763


1764
1765
1766
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
1780
1781
1782
1783
1784
1785
1786
1787
1788
1789
1790
1791
1792
1793
....
1797
1798
1799
1800
1801
1802
1803
1804






1805
1806

1807
1808
1809
1810
1811
1812
1813
1814
1815
1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
....
2634
2635
2636
2637
2638
2639
2640
2641
2642
2643
2644
2645
2646
2647
2648
....
4304
4305
4306
4307
4308
4309
4310
4311
4312
4313
4314
4315
4316
4317
4318
4319
4320
4321
        pCost->plan.wsFlags = flags;
        pCost->plan.u.pTerm = pTerm;
      }
    }
  }
#endif /* SQLITE_OMIT_OR_OPTIMIZATION */
}

/*
** Return TRUE if the WHERE clause term pTerm is of a form where it
** could be used with an index to access pSrc, assuming an appropriate
** index existed.
*/
static int termCanDriveIndex(
  WhereTerm *pTerm,              /* WHERE clause term to check */
  struct SrcList_item *pSrc,     /* Table we are trying to access */
  Bitmask notReady               /* Tables in outer loops of the join */
){
  char aff;
  if( pTerm->leftCursor!=pSrc->iCursor ) return 0;
  if( pTerm->eOperator!=WO_EQ ) return 0;
  if( (pTerm->prereqRight & notReady)!=0 ) return 0;
  aff = pSrc->pTab->aCol[pTerm->u.leftColumn].affinity;
  if( !sqlite3IndexAffinityOk(pTerm->pExpr, aff) ) return 0;
  return 1;
}

/*
** If the query plan for pSrc specified in pCost is a full table scan
** and indexing is allows (if there is no NOT INDEXED clause) and it
** possible to construct a transient index that would perform better
** than a full table scan even when the cost of constructing the index
** is taken into account, then alter the query plan to use the
** transient index.
*/
static void bestTransientIndex(
  Parse *pParse,              /* The parsing context */
................................................................................
    return;
  }

  /* Search for any equality comparison term */
  pTable = pSrc->pTab;
  pWCEnd = &pWC->a[pWC->nTerm];
  for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
    if( termCanDriveIndex(pTerm, pSrc, notReady) ){





      WHERETRACE(("auto-index reduces cost from %.2f to %.2f\n",
                    pCost->rCost, costTempIdx));
      pCost->rCost = costTempIdx;
      pCost->nRow = logN + 1;
      pCost->plan.wsFlags = WHERE_TEMP_INDEX;
      pCost->used = pTerm->prereqRight;
      break;
................................................................................
  int regIsInit;              /* Register set by initialization */
  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 record */
  int n;                      /* Column counter */
  int i;                      /* Loop counter */
  int mxBitCol;               /* Maximum column in pSrc->colUsed */
  CollSeq *pColl;             /* Collating sequence to on a column */
  Bitmask idxCols;            /* Bitmap of columns used for indexing */
  Bitmask extraCols;          /* Bitmap of additional columns */

  /* Generate code to skip over the creation and initialization of the
  ** transient index on 2nd and subsequent iterations of the loop. */
  v = pParse->pVdbe;
  assert( v!=0 );
  regIsInit = ++pParse->nMem;
  addrInit = sqlite3VdbeAddOp1(v, OP_If, regIsInit);
  sqlite3VdbeAddOp2(v, OP_Integer, 1, regIsInit);

  /* Count the number of columns that will be added to the index
  ** and used to match WHERE clause constraints */
  nColumn = 0;
  pTable = pSrc->pTab;
  pWCEnd = &pWC->a[pWC->nTerm];
  idxCols = 0;
  for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
    if( termCanDriveIndex(pTerm, pSrc, notReady) ){
      int iCol = pTerm->u.leftColumn;
      if( iCol<BMS && iCol>=0 ) idxCols |= 1<<iCol;


      nColumn++;
    }
  }
  assert( nColumn>0 );
  pLevel->plan.nEq = nColumn;

  /* Count the number of additional columns needed to create a
  ** covering index.  A "covering index" is an index that contains all
  ** columns that are needed by the query.  With a covering index, the
  ** original table never needs to be accessed.  Automatic indices must
  ** be a covering index because the index will not be updated if the
  ** original table changes and the index and table cannot both be used
  ** if they go out of sync.
  */
  extraCols = pSrc->colUsed & ~idxCols;
  mxBitCol = (pTable->nCol >= BMS-1) ? BMS-1 : pTable->nCol;
  for(i=0; i<mxBitCol; i++){
    if( extraCols & (1<<i) ) nColumn++;
  }
  if( pSrc->colUsed & (((Bitmask)1)<<(BMS-1)) ){
    nColumn += pTable->nCol - BMS + 1;
  }
  pLevel->plan.wsFlags |= WHERE_COLUMN_EQ | WHERE_IDX_ONLY | WO_EQ;

  /* Construct the Index object to describe this index */
  nByte = sizeof(Index);
  nByte += nColumn*sizeof(int);     /* Index.aiColumn */
  nByte += nColumn*sizeof(char*);   /* Index.azColl */
  nByte += nColumn;                 /* Index.aSortOrder */
  pIdx = sqlite3DbMallocZero(pParse->db, nByte);
................................................................................
  pIdx->aiColumn = (int*)&pIdx->azColl[nColumn];
  pIdx->aSortOrder = (u8*)&pIdx->aiColumn[nColumn];
  pIdx->zName = "auto-index";
  pIdx->nColumn = nColumn;
  pIdx->pTable = pTable;
  n = 0;
  for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
    if( termCanDriveIndex(pTerm, pSrc, notReady) ){






      Expr *pX = pTerm->pExpr;
      pIdx->aiColumn[n] = pTerm->u.leftColumn;

      pColl = sqlite3BinaryCompareCollSeq(pParse, pX->pLeft, pX->pRight);
      pIdx->azColl[n] = pColl->zName;
      n++;
    }
  }
  assert( n==pLevel->plan.nEq );

  /* Add additional columns needed to make the index into a covering index */
  for(i=0; i<mxBitCol; i++){
    if( extraCols & (1<<i) ){
      pIdx->aiColumn[n] = i;
      pIdx->azColl[n] = "BINARY";
      n++;
    }
  }
  if( pSrc->colUsed & (((Bitmask)1)<<(BMS-1)) ){
    for(i=BMS-1; i<pTable->nCol; i++){
      pIdx->aiColumn[n] = i;
      pIdx->azColl[n] = "BINARY";
      n++;
    }
  }
  assert( n==nColumn );

  /* Create the transient index */
  pKeyinfo = sqlite3IndexKeyinfo(pParse, pIdx);
  assert( pLevel->iIdxCur>=0 );
  sqlite3VdbeAddOp4(v, OP_OpenAutoindex, pLevel->iIdxCur, nColumn+1, 0,
                    (char*)pKeyinfo, P4_KEYINFO_HANDOFF);
  VdbeComment((v, "for %s", pTable->zName));
................................................................................
      }else{
        bSort = 1;
      }
    }

    /* If currently calculating the cost of using an index (not the IPK
    ** index), determine if all required column data may be obtained without 
    ** using the main table (i.e. if the index is a covering
    ** index for this query). If it is, set the WHERE_IDX_ONLY flag in
    ** wsFlags. Otherwise, set the bLookup variable to true.  */
    if( pIdx && wsFlags ){
      Bitmask m = pSrc->colUsed;
      int j;
      for(j=0; j<pIdx->nColumn; j++){
        int x = pIdx->aiColumn[j];
................................................................................
  /* Close all of the cursors that were opened by sqlite3WhereBegin.
  */
  assert( pWInfo->nLevel==1 || pWInfo->nLevel==pTabList->nSrc );
  for(i=0, pLevel=pWInfo->a; i<pWInfo->nLevel; i++, pLevel++){
    struct SrcList_item *pTabItem = &pTabList->a[pLevel->iFrom];
    Table *pTab = pTabItem->pTab;
    assert( pTab!=0 );
    if( (pTab->tabFlags & TF_Ephemeral)==0
     && pTab->pSelect==0
     && (pWInfo->wctrlFlags & WHERE_OMIT_CLOSE)==0
    ){
      int ws = pLevel->plan.wsFlags;
      if( !pWInfo->okOnePass && (ws & WHERE_IDX_ONLY)==0 ){
        sqlite3VdbeAddOp1(v, OP_Close, pTabItem->iCursor);
      }
      if( (ws & (WHERE_INDEXED|WHERE_TEMP_INDEX)) == WHERE_INDEXED ){
        sqlite3VdbeAddOp1(v, OP_Close, pLevel->iIdxCur);
      }