/ Check-in [a52ef2ad]
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 | SQL 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
Context
2017-04-07
19:52
Remove an unused token type. check-in: 13a42223 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: a52ef2ad 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: 48826b22 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

  3188   3188   void sqlite3ExprCodeGetColumnOfTable(
  3189   3189     Vdbe *v,        /* The VDBE under construction */
  3190   3190     Table *pTab,    /* The table containing the value */
  3191   3191     int iTabCur,    /* The table cursor.  Or the PK cursor for WITHOUT ROWID */
  3192   3192     int iCol,       /* Index of the column to extract */
  3193   3193     int regOut      /* Extract the value into this register */
  3194   3194   ){
         3195  +  if( pTab==0 ){
         3196  +    sqlite3VdbeAddOp3(v, OP_Column, iTabCur, iCol, regOut);
         3197  +    return;
         3198  +  }
  3195   3199     if( iCol<0 || iCol==pTab->iPKey ){
  3196   3200       sqlite3VdbeAddOp2(v, OP_Rowid, iTabCur, regOut);
  3197   3201     }else{
  3198   3202       int op = IsVirtual(pTab) ? OP_VColumn : OP_Column;
  3199   3203       int x = iCol;
  3200   3204       if( !HasRowid(pTab) && !IsVirtual(pTab) ){
  3201   3205         x = sqlite3ColumnOfIndex(sqlite3PrimaryKeyIndex(pTab), iCol);

Changes to src/select.c.

  3143   3143   ** a column in table number iTable with a copy of the iColumn-th
  3144   3144   ** entry in pEList.  (But leave references to the ROWID column 
  3145   3145   ** unchanged.)
  3146   3146   **
  3147   3147   ** This routine is part of the flattening procedure.  A subquery
  3148   3148   ** whose result set is defined by pEList appears as entry in the
  3149   3149   ** FROM clause of a SELECT such that the VDBE cursor assigned to that
  3150         -** FORM clause entry is iTable.  This routine make the necessary 
         3150  +** FORM clause entry is iTable.  This routine makes the necessary 
  3151   3151   ** changes to pExpr so that it refers directly to the source table
  3152   3152   ** of the subquery rather the result set of the subquery.
  3153   3153   */
  3154   3154   static Expr *substExpr(
  3155   3155     Parse *pParse,      /* Report errors here */
  3156   3156     Expr *pExpr,        /* Expr in which substitution occurs */
  3157   3157     int iTable,         /* Table to be substituted */

Changes to src/sqliteInt.h.

  3320   3320       int n;                                     /* A counter */
  3321   3321       int iCur;                                  /* A cursor number */
  3322   3322       SrcList *pSrcList;                         /* FROM clause */
  3323   3323       struct SrcCount *pSrcCount;                /* Counting column references */
  3324   3324       struct CCurHint *pCCurHint;                /* Used by codeCursorHint() */
  3325   3325       int *aiCol;                                /* array of column indexes */
  3326   3326       struct IdxCover *pIdxCover;                /* Check for index coverage */
         3327  +    struct IdxExprTrans *pIdxTrans;            /* Convert indexed expr to column */
  3327   3328     } u;
  3328   3329   };
  3329   3330   
  3330   3331   /* Forward declarations */
  3331   3332   int sqlite3WalkExpr(Walker*, Expr*);
  3332   3333   int sqlite3WalkExprList(Walker*, ExprList*);
  3333   3334   int sqlite3WalkSelect(Walker*, Select*);

Changes to src/where.c.

  4443   4443       sqlite3DbFree(db, pWInfo);
  4444   4444       pWInfo = 0;
  4445   4445       goto whereBeginError;
  4446   4446     }
  4447   4447     pWInfo->pParse = pParse;
  4448   4448     pWInfo->pTabList = pTabList;
  4449   4449     pWInfo->pOrderBy = pOrderBy;
         4450  +  pWInfo->pWhere = pWhere;
  4450   4451     pWInfo->pResultSet = pResultSet;
  4451   4452     pWInfo->aiCurOnePass[0] = pWInfo->aiCurOnePass[1] = -1;
  4452   4453     pWInfo->nLevel = nTabList;
  4453   4454     pWInfo->iBreak = pWInfo->iContinue = sqlite3VdbeMakeLabel(v);
  4454   4455     pWInfo->wctrlFlags = wctrlFlags;
  4455   4456     pWInfo->iLimit = iAuxArg;
  4456   4457     pWInfo->savedNQueryLoop = pParse->nQueryLoop;

Changes to src/whereInt.h.

   413    413   ** planner.
   414    414   */
   415    415   struct WhereInfo {
   416    416     Parse *pParse;            /* Parsing and code generating context */
   417    417     SrcList *pTabList;        /* List of tables in the join */
   418    418     ExprList *pOrderBy;       /* The ORDER BY clause or NULL */
   419    419     ExprList *pResultSet;     /* Result set of the query */
          420  +  Expr *pWhere;             /* The complete WHERE clause */
   420    421     LogEst iLimit;            /* LIMIT if wctrlFlags has WHERE_USE_LIMIT */
   421    422     int aiCurOnePass[2];      /* OP_OpenWrite cursors for the ONEPASS opt */
   422    423     int iContinue;            /* Jump here to continue with next record */
   423    424     int iBreak;               /* Jump here to break out of the loop */
   424    425     int savedNQueryLoop;      /* pParse->nQueryLoop outside the WHERE loop */
   425    426     u16 wctrlFlags;           /* Flags originally passed to sqlite3WhereBegin() */
   426    427     u8 nLevel;                /* Number of nested loop */

Changes to src/wherecode.c.

  1035   1035       }
  1036   1036     }else{
  1037   1037       assert( nReg==1 );
  1038   1038       sqlite3ExprCode(pParse, p, iReg);
  1039   1039     }
  1040   1040   }
  1041   1041   
         1042  +#if 1 /* INDEXEXPRTRANS */
         1043  +typedef struct IdxExprTrans {
         1044  +  Expr *pIdxExpr;    /* The index expression */
         1045  +  int iTabCur;       /* The cursor of the corresponding table */
         1046  +  int iIdxCur;       /* The cursor for the index */
         1047  +  int iIdxCol;       /* The column for the index */
         1048  +} IdxExprTrans;
         1049  +
         1050  +static int whereIndexExprTransNode(Walker *p, Expr *pExpr){
         1051  +  IdxExprTrans *pX = p->u.pIdxTrans;
         1052  +  if( sqlite3ExprCompare(pExpr, pX->pIdxExpr, pX->iTabCur)==0 ){
         1053  +    pExpr->op = TK_COLUMN;
         1054  +    pExpr->iTable = pX->iIdxCur;
         1055  +    pExpr->iColumn = pX->iIdxCol;
         1056  +    pExpr->pTab = 0;
         1057  +    return WRC_Prune;
         1058  +  }else{
         1059  +    return WRC_Continue;
         1060  +  }
         1061  +}
         1062  +
         1063  +/*
         1064  +** For an indexes on expression X, locate every instance of expression X in pExpr
         1065  +** and change that subexpression into a reference to the appropriate column of
         1066  +** the index.
         1067  +*/
         1068  +static void whereIndexExprTrans(
         1069  +  Index *pIdx,      /* The Index */
         1070  +  int iTabCur,      /* Cursor of the table that is being indexed */
         1071  +  int iIdxCur,      /* Cursor of the index itself */
         1072  +  WhereInfo *pWInfo /* Transform expressions in this WHERE clause */
         1073  +){
         1074  +  int iIdxCol;               /* Column number of the index */
         1075  +  ExprList *aColExpr;        /* Expressions that are indexed */
         1076  +  Walker w;
         1077  +  IdxExprTrans x;
         1078  +  aColExpr = pIdx->aColExpr;
         1079  +  if( aColExpr==0 ) return;  /* Not an index on expressions */
         1080  +  memset(&w, 0, sizeof(w));
         1081  +  w.xExprCallback = whereIndexExprTransNode;
         1082  +  w.u.pIdxTrans = &x;
         1083  +  x.iTabCur = iTabCur;
         1084  +  x.iIdxCur = iIdxCur;
         1085  +  for(iIdxCol=0; iIdxCol<aColExpr->nExpr; iIdxCol++){
         1086  +    if( pIdx->aiColumn[iIdxCol]!=XN_EXPR ) continue;
         1087  +    assert( aColExpr->a[iIdxCol].pExpr!=0 );
         1088  +    x.iIdxCol = iIdxCol;
         1089  +    x.pIdxExpr = aColExpr->a[iIdxCol].pExpr;
         1090  +    sqlite3WalkExpr(&w, pWInfo->pWhere);
         1091  +    sqlite3WalkExprList(&w, pWInfo->pOrderBy);
         1092  +    sqlite3WalkExprList(&w, pWInfo->pResultSet);
         1093  +  }
         1094  +}
         1095  +#endif
         1096  +
  1042   1097   /*
  1043   1098   ** Generate code for the start of the iLevel-th loop in the WHERE clause
  1044   1099   ** implementation described by pWInfo.
  1045   1100   */
  1046   1101   Bitmask sqlite3WhereCodeOneLoopStart(
  1047   1102     WhereInfo *pWInfo,   /* Complete information about the WHERE clause */
  1048   1103     int iLevel,          /* Which level of pWInfo->a[] should be coded */
................................................................................
  1615   1670         for(j=0; j<pPk->nKeyCol; j++){
  1616   1671           k = sqlite3ColumnOfIndex(pIdx, pPk->aiColumn[j]);
  1617   1672           sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, k, iRowidReg+j);
  1618   1673         }
  1619   1674         sqlite3VdbeAddOp4Int(v, OP_NotFound, iCur, addrCont,
  1620   1675                              iRowidReg, pPk->nKeyCol); VdbeCoverage(v);
  1621   1676       }
         1677  +
         1678  +#if 1  /* INDEXEXPRTANS */
         1679  +    whereIndexExprTrans(pIdx, iCur, iIdxCur, pWInfo);
         1680  +#endif
  1622   1681   
  1623   1682       /* Record the instruction used to terminate the loop. */
  1624   1683       if( pLoop->wsFlags & WHERE_ONEROW ){
  1625   1684         pLevel->op = OP_Noop;
  1626   1685       }else if( bRev ){
  1627   1686         pLevel->op = OP_Prev;
  1628   1687       }else{

Changes to tool/addopcodes.tcl.

    28     28     TO_INT
    29     29     TO_REAL
    30     30     ISNOT
    31     31     END_OF_FILE
    32     32     UNCLOSED_STRING
    33     33     FUNCTION
    34     34     COLUMN
           35  +  IDXCOLUMN
    35     36     AGG_FUNCTION
    36     37     AGG_COLUMN
    37     38     UMINUS
    38     39     UPLUS
    39     40     REGISTER
    40     41     VECTOR
    41     42     SELECT_COLUMN