SQLite

Check-in [e0f7e321ec]
Login

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

Overview
Comment:Fix handling of covering indexes that use virtual columns.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | generated-columns
Files: files | file ages | folders
SHA3-256: e0f7e321eca91c49102649f70728de69347cbd7b16599b854203c24cfdc348e6
User & Date: drh 2019-10-24 20:29:25.035
Context
2019-10-24
21:02
The previous fix was incomplete. It is also necessary to disable the Expr.y.pTab field when making the translation. (check-in: b99d570131 user: drh tags: generated-columns)
20:29
Fix handling of covering indexes that use virtual columns. (check-in: e0f7e321ec user: drh tags: generated-columns)
01:04
Do not allow ALTER TABLE ADD COLUMN for a STORED column. (check-in: 42fc08bc15 user: drh tags: generated-columns)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/build.c.
1918
1919
1920
1921
1922
1923
1924








1925
1926
1927
1928
1929
1930
1931

1932
1933
1934
1935
1936
1937
1938
1939
1940
1941

/* Recompute the colNotIdxed field of the Index.
**
** colNotIdxed is a bitmask that has a 0 bit representing each indexed
** columns that are within the first 63 columns of the table.  The
** high-order bit of colNotIdxed is always 1.  All unindexed columns
** of the table have a 1.








**
** The colNotIdxed mask is AND-ed with the SrcList.a[].colUsed mask
** to determine if the index is covering index.
*/
static void recomputeColumnsNotIndexed(Index *pIdx){
  Bitmask m = 0;
  int j;

  for(j=pIdx->nColumn-1; j>=0; j--){
    int x = pIdx->aiColumn[j];
    if( x>=0 ){
      testcase( x==BMS-1 );
      testcase( x==BMS-2 );
      if( x<BMS-1 ) m |= MASKBIT(x);
    }
  }
  pIdx->colNotIdxed = ~m;
  assert( (pIdx->colNotIdxed>>63)==1 );







>
>
>
>
>
>
>
>







>


|







1918
1919
1920
1921
1922
1923
1924
1925
1926
1927
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
1945
1946
1947
1948
1949
1950

/* Recompute the colNotIdxed field of the Index.
**
** colNotIdxed is a bitmask that has a 0 bit representing each indexed
** columns that are within the first 63 columns of the table.  The
** high-order bit of colNotIdxed is always 1.  All unindexed columns
** of the table have a 1.
**
** 2019-10-24:  For the purpose of this computation, virtual columns are
** not considered to be covered by the index, even if they are in the
** index, because we do not trust the logic in whereIndexExprTrans() to be
** able to find all instances of a reference to the indexed table column
** and convert them into references to the index.  Hence we always want
** the actual table at hand in order to recompute the virtual column, if
** necessary.
**
** The colNotIdxed mask is AND-ed with the SrcList.a[].colUsed mask
** to determine if the index is covering index.
*/
static void recomputeColumnsNotIndexed(Index *pIdx){
  Bitmask m = 0;
  int j;
  Table *pTab = pIdx->pTable;
  for(j=pIdx->nColumn-1; j>=0; j--){
    int x = pIdx->aiColumn[j];
    if( x>=0 && (pTab->aCol[x].colFlags & COLFLAG_VIRTUAL)==0 ){
      testcase( x==BMS-1 );
      testcase( x==BMS-2 );
      if( x<BMS-1 ) m |= MASKBIT(x);
    }
  }
  pIdx->colNotIdxed = ~m;
  assert( (pIdx->colNotIdxed>>63)==1 );
3613
3614
3615
3616
3617
3618
3619

3620
3621




3622
3623
3624
3625
3626
3627
3628
      pIndex->aiColumn[i] = XN_EXPR;
      pIndex->uniqNotNull = 0;
    }else{
      j = pCExpr->iColumn;
      assert( j<=0x7fff );
      if( j<0 ){
        j = pTab->iPKey;

      }else if( pTab->aCol[j].notNull==0 ){
        pIndex->uniqNotNull = 0;




      }
      pIndex->aiColumn[i] = (i16)j;
    }
    zColl = 0;
    if( pListItem->pExpr->op==TK_COLLATE ){
      int nColl;
      zColl = pListItem->pExpr->u.zToken;







>
|
|
>
>
>
>







3622
3623
3624
3625
3626
3627
3628
3629
3630
3631
3632
3633
3634
3635
3636
3637
3638
3639
3640
3641
3642
      pIndex->aiColumn[i] = XN_EXPR;
      pIndex->uniqNotNull = 0;
    }else{
      j = pCExpr->iColumn;
      assert( j<=0x7fff );
      if( j<0 ){
        j = pTab->iPKey;
      }else{
        if( pTab->aCol[j].notNull==0 ){
          pIndex->uniqNotNull = 0;
        }
        if( pTab->aCol[j].colFlags & COLFLAG_VIRTUAL ){
          pIndex->bHasVCol = 1;
        }
      }
      pIndex->aiColumn[i] = (i16)j;
    }
    zColl = 0;
    if( pListItem->pExpr->op==TK_COLLATE ){
      int nColl;
      zColl = pListItem->pExpr->u.zToken;
Changes to src/sqliteInt.h.
2291
2292
2293
2294
2295
2296
2297

2298
2299
2300
2301
2302
2303
2304
  unsigned uniqNotNull:1;  /* True if UNIQUE and NOT NULL for all columns */
  unsigned isResized:1;    /* True if resizeIndexObject() has been called */
  unsigned isCovering:1;   /* True if this is a covering index */
  unsigned noSkipScan:1;   /* Do not try to use skip-scan if true */
  unsigned hasStat1:1;     /* aiRowLogEst values come from sqlite_stat1 */
  unsigned bNoQuery:1;     /* Do not use this index to optimize queries */
  unsigned bAscKeyBug:1;   /* True if the bba7b69f9849b5bf bug applies */

#ifdef SQLITE_ENABLE_STAT4
  int nSample;             /* Number of elements in aSample[] */
  int nSampleCol;          /* Size of IndexSample.anEq[] and so on */
  tRowcnt *aAvgEq;         /* Average nEq values for keys not in aSample */
  IndexSample *aSample;    /* Samples of the left-most key */
  tRowcnt *aiRowEst;       /* Non-logarithmic stat1 data for this index */
  tRowcnt nRowEst0;        /* Non-logarithmic number of rows in the index */







>







2291
2292
2293
2294
2295
2296
2297
2298
2299
2300
2301
2302
2303
2304
2305
  unsigned uniqNotNull:1;  /* True if UNIQUE and NOT NULL for all columns */
  unsigned isResized:1;    /* True if resizeIndexObject() has been called */
  unsigned isCovering:1;   /* True if this is a covering index */
  unsigned noSkipScan:1;   /* Do not try to use skip-scan if true */
  unsigned hasStat1:1;     /* aiRowLogEst values come from sqlite_stat1 */
  unsigned bNoQuery:1;     /* Do not use this index to optimize queries */
  unsigned bAscKeyBug:1;   /* True if the bba7b69f9849b5bf bug applies */
  unsigned bHasVCol:1;     /* Index references one or more VIRTUAL columns */
#ifdef SQLITE_ENABLE_STAT4
  int nSample;             /* Number of elements in aSample[] */
  int nSampleCol;          /* Size of IndexSample.anEq[] and so on */
  tRowcnt *aAvgEq;         /* Average nEq values for keys not in aSample */
  IndexSample *aSample;    /* Samples of the left-most key */
  tRowcnt *aiRowEst;       /* Non-logarithmic stat1 data for this index */
  tRowcnt nRowEst0;        /* Non-logarithmic number of rows in the index */
Changes to src/wherecode.c.
1102
1103
1104
1105
1106
1107
1108

1109
1110
1111
1112
1113
1114
1115
** down through the Walker.
*/
typedef struct IdxExprTrans {
  Expr *pIdxExpr;    /* The index expression */
  int iTabCur;       /* The cursor of the corresponding table */
  int iIdxCur;       /* The cursor for the index */
  int iIdxCol;       /* The column for the index */

} IdxExprTrans;

/* The walker node callback used to transform matching expressions into
** a reference to an index column for an index on an expression.
**
** If pExpr matches, then transform it into a reference to the index column
** that contains the value of pExpr.







>







1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
** down through the Walker.
*/
typedef struct IdxExprTrans {
  Expr *pIdxExpr;    /* The index expression */
  int iTabCur;       /* The cursor of the corresponding table */
  int iIdxCur;       /* The cursor for the index */
  int iIdxCol;       /* The column for the index */
  int iTabCol;       /* The column for the table */
} IdxExprTrans;

/* The walker node callback used to transform matching expressions into
** a reference to an index column for an index on an expression.
**
** If pExpr matches, then transform it into a reference to the index column
** that contains the value of pExpr.
1124
1125
1126
1127
1128
1129
1130
















1131
1132
1133
1134




1135
1136
1137
1138
1139
1140
1141
1142
1143

1144
1145
1146
1147





1148
1149
1150
1151
1152
1153
1154

1155
1156
1157










1158
1159
1160
1161
1162
1163
1164
    pExpr->y.pTab = 0;
    return WRC_Prune;
  }else{
    return WRC_Continue;
  }
}

















/*
** For an indexes on expression X, locate every instance of expression X
** in pExpr and change that subexpression into a reference to the appropriate
** column of the index.




*/
static void whereIndexExprTrans(
  Index *pIdx,      /* The Index */
  int iTabCur,      /* Cursor of the table that is being indexed */
  int iIdxCur,      /* Cursor of the index itself */
  WhereInfo *pWInfo /* Transform expressions in this WHERE clause */
){
  int iIdxCol;               /* Column number of the index */
  ExprList *aColExpr;        /* Expressions that are indexed */

  Walker w;
  IdxExprTrans x;
  aColExpr = pIdx->aColExpr;
  if( aColExpr==0 ) return;  /* Not an index on expressions */





  memset(&w, 0, sizeof(w));
  w.xExprCallback = whereIndexExprTransNode;
  w.u.pIdxTrans = &x;
  x.iTabCur = iTabCur;
  x.iIdxCur = iIdxCur;
  for(iIdxCol=0; iIdxCol<aColExpr->nExpr; iIdxCol++){
    if( pIdx->aiColumn[iIdxCol]!=XN_EXPR ) continue;

    assert( aColExpr->a[iIdxCol].pExpr!=0 );
    x.iIdxCol = iIdxCol;
    x.pIdxExpr = aColExpr->a[iIdxCol].pExpr;










    sqlite3WalkExpr(&w, pWInfo->pWhere);
    sqlite3WalkExprList(&w, pWInfo->pOrderBy);
    sqlite3WalkExprList(&w, pWInfo->pResultSet);
  }
}

/*







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




>
>
>
>









>



|
>
>
>
>
>

<



|
|
>
|
<
|
>
>
>
>
>
>
>
>
>
>







1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175

1176
1177
1178
1179
1180
1181
1182

1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
    pExpr->y.pTab = 0;
    return WRC_Prune;
  }else{
    return WRC_Continue;
  }
}

#ifndef SQLITE_OMIT_GENERATED_COLUMNS
/* A walker node callback that translates a column reference to a table
** into a corresponding column reference of an index.
*/
static int whereIndexExprTransColumn(Walker *p, Expr *pExpr){
  if( pExpr->op==TK_COLUMN ){
    IdxExprTrans *pX = p->u.pIdxTrans;
    if( pExpr->iTable==pX->iTabCur && pExpr->iColumn==pX->iTabCol ){
      pExpr->iTable = pX->iIdxCur;
      pExpr->iColumn = pX->iIdxCol;
    }
  }
  return WRC_Continue;
}
#endif /* SQLITE_OMIT_GENERATED_COLUMNS */

/*
** For an indexes on expression X, locate every instance of expression X
** in pExpr and change that subexpression into a reference to the appropriate
** column of the index.
**
** 2019-10-24: Updated to also translate references to a VIRTUAL column in
** the table into references to the corresponding (stored) column of the
** index.
*/
static void whereIndexExprTrans(
  Index *pIdx,      /* The Index */
  int iTabCur,      /* Cursor of the table that is being indexed */
  int iIdxCur,      /* Cursor of the index itself */
  WhereInfo *pWInfo /* Transform expressions in this WHERE clause */
){
  int iIdxCol;               /* Column number of the index */
  ExprList *aColExpr;        /* Expressions that are indexed */
  Table *pTab;
  Walker w;
  IdxExprTrans x;
  aColExpr = pIdx->aColExpr;
  if( aColExpr==0 && !pIdx->bHasVCol ){
    /* The index does not reference any expressions or virtual columns
    ** so no translations are needed. */
    return;
  }
  pTab = pIdx->pTable;
  memset(&w, 0, sizeof(w));

  w.u.pIdxTrans = &x;
  x.iTabCur = iTabCur;
  x.iIdxCur = iIdxCur;
  for(iIdxCol=0; iIdxCol<pIdx->nColumn; iIdxCol++){
    i16 iRef = pIdx->aiColumn[iIdxCol];
    if( iRef==XN_EXPR ){
      assert( aColExpr->a[iIdxCol].pExpr!=0 );

      x.pIdxExpr = aColExpr->a[iIdxCol].pExpr;
      w.xExprCallback = whereIndexExprTransNode;
#ifndef SQLITE_OMIT_GENERATED_COLUMNS
    }else if( iRef>=0 && (pTab->aCol[iRef].colFlags & COLFLAG_VIRTUAL)!=0 ){
      x.iTabCol = iRef;
      w.xExprCallback = whereIndexExprTransColumn;
#endif /* SQLITE_OMIT_GENERATED_COLUMNS */
    }else{
      continue;
    }
    x.iIdxCol = iIdxCol;
    sqlite3WalkExpr(&w, pWInfo->pWhere);
    sqlite3WalkExprList(&w, pWInfo->pOrderBy);
    sqlite3WalkExprList(&w, pWInfo->pResultSet);
  }
}

/*
1831
1832
1833
1834
1835
1836
1837
1838


1839
1840
1841
1842
1843
1844
1845
      }
      sqlite3VdbeAddOp4Int(v, OP_NotFound, iCur, addrCont,
                           iRowidReg, pPk->nKeyCol); VdbeCoverage(v);
    }

    /* If pIdx is an index on one or more expressions, then look through
    ** all the expressions in pWInfo and try to transform matching expressions
    ** into reference to index columns.


    **
    ** Do not do this for the RHS of a LEFT JOIN. This is because the 
    ** expression may be evaluated after OP_NullRow has been executed on
    ** the cursor. In this case it is important to do the full evaluation,
    ** as the result of the expression may not be NULL, even if all table
    ** column values are.  https://www.sqlite.org/src/info/7fa8049685b50b5a
    **







|
>
>







1867
1868
1869
1870
1871
1872
1873
1874
1875
1876
1877
1878
1879
1880
1881
1882
1883
      }
      sqlite3VdbeAddOp4Int(v, OP_NotFound, iCur, addrCont,
                           iRowidReg, pPk->nKeyCol); VdbeCoverage(v);
    }

    /* If pIdx is an index on one or more expressions, then look through
    ** all the expressions in pWInfo and try to transform matching expressions
    ** into reference to index columns.  Also attempt to translate references
    ** to virtual columns in the table into references to (stored) columns
    ** of the index.
    **
    ** Do not do this for the RHS of a LEFT JOIN. This is because the 
    ** expression may be evaluated after OP_NullRow has been executed on
    ** the cursor. In this case it is important to do the full evaluation,
    ** as the result of the expression may not be NULL, even if all table
    ** column values are.  https://www.sqlite.org/src/info/7fa8049685b50b5a
    **