SQLite

Check-in [a52ef2ad7c]
Login

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

Overview
Comment:Proof of concept for the ability to use the expression columns in an index on expressions in place of equivalent expressions in the result set or in the WHERE clause. This check-in compiles but is mostly untested.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | covering-index-on-expr
Files: files | file ages | folders
SHA3-256: a52ef2ad7c0e14b78b801f16a1f6ea8d8fa9ae5d7d810e18dd24c600b662a312
User & Date: drh 2017-04-07 19:41:31.032
Context
2017-04-07
19:52
Remove an unused token type. (check-in: 13a422230d user: drh tags: covering-index-on-expr)
19:41
Proof of concept for the ability to use the expression columns in an index on expressions in place of equivalent expressions in the result set or in the WHERE clause. This check-in compiles but is mostly untested. (check-in: a52ef2ad7c user: drh tags: covering-index-on-expr)
2017-04-06
14:56
Fix the ".lint fkey" shell command for cases where the child key is also an INTEGER PRIMARY KEY. (check-in: 48826b222c user: dan tags: trunk)
Changes
Side-by-Side Diff Ignore Whitespace Patch
Changes to src/expr.c.
3188
3189
3190
3191
3192
3193
3194




3195
3196
3197
3198
3199
3200
3201
3188
3189
3190
3191
3192
3193
3194
3195
3196
3197
3198
3199
3200
3201
3202
3203
3204
3205







+
+
+
+







void sqlite3ExprCodeGetColumnOfTable(
  Vdbe *v,        /* The VDBE under construction */
  Table *pTab,    /* The table containing the value */
  int iTabCur,    /* The table cursor.  Or the PK cursor for WITHOUT ROWID */
  int iCol,       /* Index of the column to extract */
  int regOut      /* Extract the value into this register */
){
  if( pTab==0 ){
    sqlite3VdbeAddOp3(v, OP_Column, iTabCur, iCol, regOut);
    return;
  }
  if( iCol<0 || iCol==pTab->iPKey ){
    sqlite3VdbeAddOp2(v, OP_Rowid, iTabCur, regOut);
  }else{
    int op = IsVirtual(pTab) ? OP_VColumn : OP_Column;
    int x = iCol;
    if( !HasRowid(pTab) && !IsVirtual(pTab) ){
      x = sqlite3ColumnOfIndex(sqlite3PrimaryKeyIndex(pTab), iCol);
Changes to src/select.c.
3143
3144
3145
3146
3147
3148
3149
3150

3151
3152
3153
3154
3155
3156
3157
3143
3144
3145
3146
3147
3148
3149

3150
3151
3152
3153
3154
3155
3156
3157







-
+







** a column in table number iTable with a copy of the iColumn-th
** entry in pEList.  (But leave references to the ROWID column 
** unchanged.)
**
** This routine is part of the flattening procedure.  A subquery
** whose result set is defined by pEList appears as entry in the
** FROM clause of a SELECT such that the VDBE cursor assigned to that
** FORM clause entry is iTable.  This routine make the necessary 
** FORM clause entry is iTable.  This routine makes the necessary 
** changes to pExpr so that it refers directly to the source table
** of the subquery rather the result set of the subquery.
*/
static Expr *substExpr(
  Parse *pParse,      /* Report errors here */
  Expr *pExpr,        /* Expr in which substitution occurs */
  int iTable,         /* Table to be substituted */
Changes to src/sqliteInt.h.
3320
3321
3322
3323
3324
3325
3326

3327
3328
3329
3330
3331
3332
3333
3320
3321
3322
3323
3324
3325
3326
3327
3328
3329
3330
3331
3332
3333
3334







+







    int n;                                     /* A counter */
    int iCur;                                  /* A cursor number */
    SrcList *pSrcList;                         /* FROM clause */
    struct SrcCount *pSrcCount;                /* Counting column references */
    struct CCurHint *pCCurHint;                /* Used by codeCursorHint() */
    int *aiCol;                                /* array of column indexes */
    struct IdxCover *pIdxCover;                /* Check for index coverage */
    struct IdxExprTrans *pIdxTrans;            /* Convert indexed expr to column */
  } u;
};

/* Forward declarations */
int sqlite3WalkExpr(Walker*, Expr*);
int sqlite3WalkExprList(Walker*, ExprList*);
int sqlite3WalkSelect(Walker*, Select*);
Changes to src/where.c.
4443
4444
4445
4446
4447
4448
4449

4450
4451
4452
4453
4454
4455
4456
4443
4444
4445
4446
4447
4448
4449
4450
4451
4452
4453
4454
4455
4456
4457







+







    sqlite3DbFree(db, pWInfo);
    pWInfo = 0;
    goto whereBeginError;
  }
  pWInfo->pParse = pParse;
  pWInfo->pTabList = pTabList;
  pWInfo->pOrderBy = pOrderBy;
  pWInfo->pWhere = pWhere;
  pWInfo->pResultSet = pResultSet;
  pWInfo->aiCurOnePass[0] = pWInfo->aiCurOnePass[1] = -1;
  pWInfo->nLevel = nTabList;
  pWInfo->iBreak = pWInfo->iContinue = sqlite3VdbeMakeLabel(v);
  pWInfo->wctrlFlags = wctrlFlags;
  pWInfo->iLimit = iAuxArg;
  pWInfo->savedNQueryLoop = pParse->nQueryLoop;
Changes to src/whereInt.h.
413
414
415
416
417
418
419

420
421
422
423
424
425
426
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427







+







** planner.
*/
struct WhereInfo {
  Parse *pParse;            /* Parsing and code generating context */
  SrcList *pTabList;        /* List of tables in the join */
  ExprList *pOrderBy;       /* The ORDER BY clause or NULL */
  ExprList *pResultSet;     /* Result set of the query */
  Expr *pWhere;             /* The complete WHERE clause */
  LogEst iLimit;            /* LIMIT if wctrlFlags has WHERE_USE_LIMIT */
  int aiCurOnePass[2];      /* OP_OpenWrite cursors for the ONEPASS opt */
  int iContinue;            /* Jump here to continue with next record */
  int iBreak;               /* Jump here to break out of the loop */
  int savedNQueryLoop;      /* pParse->nQueryLoop outside the WHERE loop */
  u16 wctrlFlags;           /* Flags originally passed to sqlite3WhereBegin() */
  u8 nLevel;                /* Number of nested loop */
Changes to src/wherecode.c.
1035
1036
1037
1038
1039
1040
1041























































1042
1043
1044
1045
1046
1047
1048
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103







+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+







    }
  }else{
    assert( nReg==1 );
    sqlite3ExprCode(pParse, p, iReg);
  }
}

#if 1 /* INDEXEXPRTRANS */
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;

static int whereIndexExprTransNode(Walker *p, Expr *pExpr){
  IdxExprTrans *pX = p->u.pIdxTrans;
  if( sqlite3ExprCompare(pExpr, pX->pIdxExpr, pX->iTabCur)==0 ){
    pExpr->op = TK_COLUMN;
    pExpr->iTable = pX->iIdxCur;
    pExpr->iColumn = pX->iIdxCol;
    pExpr->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);
  }
}
#endif

/*
** Generate code for the start of the iLevel-th loop in the WHERE clause
** implementation described by pWInfo.
*/
Bitmask sqlite3WhereCodeOneLoopStart(
  WhereInfo *pWInfo,   /* Complete information about the WHERE clause */
  int iLevel,          /* Which level of pWInfo->a[] should be coded */
1615
1616
1617
1618
1619
1620
1621




1622
1623
1624
1625
1626
1627
1628
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687







+
+
+
+







      for(j=0; j<pPk->nKeyCol; j++){
        k = sqlite3ColumnOfIndex(pIdx, pPk->aiColumn[j]);
        sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, k, iRowidReg+j);
      }
      sqlite3VdbeAddOp4Int(v, OP_NotFound, iCur, addrCont,
                           iRowidReg, pPk->nKeyCol); VdbeCoverage(v);
    }

#if 1  /* INDEXEXPRTANS */
    whereIndexExprTrans(pIdx, iCur, iIdxCur, pWInfo);
#endif

    /* Record the instruction used to terminate the loop. */
    if( pLoop->wsFlags & WHERE_ONEROW ){
      pLevel->op = OP_Noop;
    }else if( bRev ){
      pLevel->op = OP_Prev;
    }else{
Changes to tool/addopcodes.tcl.
28
29
30
31
32
33
34

35
36
37
38
39
40
41
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42







+







  TO_INT
  TO_REAL
  ISNOT
  END_OF_FILE
  UNCLOSED_STRING
  FUNCTION
  COLUMN
  IDXCOLUMN
  AGG_FUNCTION
  AGG_COLUMN
  UMINUS
  UPLUS
  REGISTER
  VECTOR
  SELECT_COLUMN