Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Improved analysis and usage of indexed expressions in the query planner. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | index-expr |
Files: | files | file ages | folders |
SHA1: |
f8893696387cba9d293a05a68dc38228 |
User & Date: | drh 2015-08-31 15:58:06.350 |
Context
2015-08-31
| ||
17:34 | Make the distinction between truly deterministic functions and date/time functions which only return the same answer for a single query. Only truly deterministic functions are allowed in indexes. Add new expression index test cases. (check-in: c77554b5c4 user: drh tags: index-expr) | |
15:58 | Improved analysis and usage of indexed expressions in the query planner. (check-in: f889369638 user: drh tags: index-expr) | |
14:27 | Merge the latest enhancements from trunk. (check-in: 7bde6d4d8c user: drh tags: index-expr) | |
Changes
Changes to src/where.c.
︙ | ︙ | |||
4046 4047 4048 4049 4050 4051 4052 | if( wctrlFlags & WHERE_WANT_DISTINCT ){ pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE; } } /* Assign a bit from the bitmask to every term in the FROM clause. ** | | > | | < < < | | | < < | | | < < | 4046 4047 4048 4049 4050 4051 4052 4053 4054 4055 4056 4057 4058 4059 4060 4061 4062 4063 4064 4065 4066 4067 4068 4069 4070 4071 4072 4073 4074 4075 4076 4077 4078 4079 | if( wctrlFlags & WHERE_WANT_DISTINCT ){ pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE; } } /* Assign a bit from the bitmask to every term in the FROM clause. ** ** The N-th term of the FROM clause is assigned a bitmask of 1<<N. ** ** The rule of the previous sentence ensures thta if X is the bitmask for ** a table T, then X-1 is the bitmask for all other tables to the left of T. ** Knowing the bitmask for all tables to the left of a left join is ** important. Ticket #3015. ** ** Note that bitmasks are created for all pTabList->nSrc tables in ** pTabList, not just the first nTabList tables. nTabList is normally ** equal to pTabList->nSrc but might be shortened to 1 if the ** WHERE_ONETABLE_ONLY flag is set. */ for(ii=0; ii<pTabList->nSrc; ii++){ createMask(pMaskSet, pTabList->a[ii].iCursor); sqlite3WhereTabFuncArgs(pParse, &pTabList->a[ii], &pWInfo->sWC); } #ifdef SQLITE_DEBUG for(ii=0; ii<pTabList->nSrc; ii++){ Bitmask m = sqlite3WhereGetMask(pMaskSet, pTabList->a[ii].iCursor); assert( m==MASKBIT(ii) ); } #endif /* Analyze all of the subexpressions. */ sqlite3WhereExprAnalyze(pTabList, &pWInfo->sWC); if( db->mallocFailed ) goto whereBeginError; |
︙ | ︙ |
Changes to src/whereexpr.c.
︙ | ︙ | |||
790 791 792 793 794 795 796 797 798 799 800 801 802 803 | mask |= sqlite3WhereExprUsage(pMaskSet, pSrc->a[i].pOn); } } pS = pS->pPrior; } return mask; } /* ** The input to this routine is an WhereTerm structure with only the ** "pExpr" field filled in. The job of this routine is to analyze the ** subexpression and populate all the other fields of the WhereTerm ** structure. ** | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 | mask |= sqlite3WhereExprUsage(pMaskSet, pSrc->a[i].pOn); } } pS = pS->pPrior; } return mask; } /* ** Expression pExpr is one operand of a comparison operator that might ** be useful for indexing. This routine checks to see if pExpr appears ** in any index. Return TRUE (1) if pExpr is an indexed term and return ** FALSE (0) if not. If TRUE is returned, also set *piCur to the cursor ** number of the table that is indexed and *piColumn to the column number ** of the column that is indexed, or -2 if an expression is being indexed. ** ** If pExpr is a TK_COLUMN column reference, then this routine always returns ** true even if that particular column is not indexed, because the column ** might be added to an automatic index later. */ static int exprMightBeIndexed( SrcList *pFrom, /* The FROM clause */ Bitmask mPrereq, /* Bitmask of FROM clause terms referenced by pExpr */ Expr *pExpr, /* An operand of a comparison operator */ int *piCur, /* Write the referenced table cursor number here */ int *piColumn /* Write the referenced table column number here */ ){ Index *pIdx; int i; int iCur; if( pExpr->op==TK_COLUMN ){ *piCur = pExpr->iTable; *piColumn = pExpr->iColumn; return 1; } if( mPrereq==0 ) return 0; /* No table references */ if( (mPrereq&(mPrereq-1))!=0 ) return 0; /* Refs more than one table */ for(i=0; mPrereq>1; i++, mPrereq>>=1){} iCur = pFrom->a[i].iCursor; for(pIdx=pFrom->a[i].pTab->pIndex; pIdx; pIdx=pIdx->pNext){ if( pIdx->aColExpr==0 ) continue; for(i=0; i<pIdx->nKeyCol; i++){ if( pIdx->aiColumn[i]!=(-2) ) continue; if( sqlite3ExprCompare(pExpr, pIdx->aColExpr->a[i].pExpr, iCur)==0 ){ *piCur = iCur; *piColumn = -2; return 1; } } } return 0; } /* ** The input to this routine is an WhereTerm structure with only the ** "pExpr" field filled in. The job of this routine is to analyze the ** subexpression and populate all the other fields of the WhereTerm ** structure. ** |
︙ | ︙ | |||
861 862 863 864 865 866 867 868 869 870 | ** on left table of a LEFT JOIN. Ticket #3015 */ } pTerm->prereqAll = prereqAll; pTerm->leftCursor = -1; pTerm->iParent = -1; pTerm->eOperator = 0; if( allowedOp(op) ){ Expr *pLeft = sqlite3ExprSkipCollate(pExpr->pLeft); Expr *pRight = sqlite3ExprSkipCollate(pExpr->pRight); u16 opMask = (pTerm->prereqRight & prereqLeft)==0 ? WO_ALL : WO_EQUIV; | > | | | < < < < < < | > > | 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 | ** on left table of a LEFT JOIN. Ticket #3015 */ } pTerm->prereqAll = prereqAll; pTerm->leftCursor = -1; pTerm->iParent = -1; pTerm->eOperator = 0; if( allowedOp(op) ){ int iCur, iColumn; Expr *pLeft = sqlite3ExprSkipCollate(pExpr->pLeft); Expr *pRight = sqlite3ExprSkipCollate(pExpr->pRight); u16 opMask = (pTerm->prereqRight & prereqLeft)==0 ? WO_ALL : WO_EQUIV; if( exprMightBeIndexed(pSrc, prereqLeft, pLeft, &iCur, &iColumn) ){ pTerm->leftCursor = iCur; pTerm->u.leftColumn = iColumn; pTerm->eOperator = operatorMask(op) & opMask; } if( op==TK_IS ) pTerm->wtFlags |= TERM_IS; if( pRight && exprMightBeIndexed(pSrc, pTerm->prereqRight, pRight, &iCur, &iColumn) ){ WhereTerm *pNew; Expr *pDup; u16 eExtraOp = 0; /* Extra bits for pNew->eOperator */ if( pTerm->leftCursor>=0 ){ int idxNew; pDup = sqlite3ExprDup(db, pExpr, 0); if( db->mallocFailed ){ |
︙ | ︙ | |||
905 906 907 908 909 910 911 | } }else{ pDup = pExpr; pNew = pTerm; } exprCommute(pParse, pDup); pLeft = sqlite3ExprSkipCollate(pDup->pLeft); | | | | 947 948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 | } }else{ pDup = pExpr; pNew = pTerm; } exprCommute(pParse, pDup); pLeft = sqlite3ExprSkipCollate(pDup->pLeft); pNew->leftCursor = iCur; pNew->u.leftColumn = iColumn; testcase( (prereqLeft | extraRight) != prereqLeft ); pNew->prereqRight = prereqLeft | extraRight; pNew->prereqAll = prereqAll; pNew->eOperator = (operatorMask(pDup->op) + eExtraOp) & opMask; } } |
︙ | ︙ |
Added test/indexexpr1.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | # 2015-08-31 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing indexes on expressions. # set testdir [file dirname $argv0] source $testdir/tester.tcl do_execsql_test indexexpr1-100 { CREATE TABLE t1(a,b,c); INSERT INTO t1(a,b,c) VALUES('In the beginning was the Word',1,1), ('and the Word was with God',1,2), ('and the Word was God',1,3), ('The same was in the beginning with God',2,1), ('All things were made by him',3,1), ('and without him was not any thing made that was made',3,2); CREATE INDEX t1a1 ON t1(substr(a,1,12)); } {} do_execsql_test indexexpr1-110 { SELECT b, c, '|' FROM t1 WHERE substr(a,1,12)=='and the Word' ORDER BY b, c; } {1 2 | 1 3 |} do_execsql_test indexexpr1-110eqp { EXPLAIN QUERY PLAN SELECT b, c, '|' FROM t1 WHERE substr(a,1,12)=='and the Word' ORDER BY b, c; } {/USING INDEX t1a1/} do_execsql_test indexexpr1-120 { SELECT b, c, '|' FROM t1 WHERE 'and the Word'==substr(a,1,12) ORDER BY b, c; } {1 2 | 1 3 |} do_execsql_test indexexpr1-120eqp { EXPLAIN QUERY PLAN SELECT b, c, '|' FROM t1 WHERE 'and the Word'==substr(a,1,12) ORDER BY b, c; } {/USING INDEX t1a1/} finish_test |