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: |
a52ef2ad7c0e14b78b801f16a1f6ea8d |
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
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 | 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 |
︙ |
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 |
︙ |