Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch xbestindex-fix Excluding Merge-Ins
This is equivalent to a diff from 5f2a262d3f to fd7cd06093
2016-03-05
| ||
21:07 | Change the way SQLite invokes the xBestIndex method of virtual tables so that N-way joins involving virtual tables work as expected. (check-in: 195444380b user: dan tags: trunk) | |
20:28 | Adjust the cost estimates for RTREE such that the expected number of rows and expected cost is halved for each additional constraint. (Closed-Leaf check-in: fd7cd06093 user: drh tags: xbestindex-fix) | |
17:29 | Change the way SQLite invokes the xBestIndex method of virtual tables so that N-way joins involving virtual tables work as expected. (check-in: ffc65968ed user: dan tags: xbestindex-fix) | |
15:35 | Remove the unused journal.c source file (its function have been subsumed into memjournal.c). Refactor some of the names in memjournal.c. No functional changes. (check-in: 5f2a262d3f user: drh tags: trunk) | |
15:03 | Work around (incorrect) ASAN warnings in memjournal.c. (check-in: 4de09777da user: drh tags: trunk) | |
Changes to ext/rtree/rtree.c.
︙ | ︙ | |||
1737 1738 1739 1740 1741 1742 1743 | pIdxInfo->idxNum = 2; pIdxInfo->needToFreeIdxStr = 1; if( iIdx>0 && 0==(pIdxInfo->idxStr = sqlite3_mprintf("%s", zIdxStr)) ){ return SQLITE_NOMEM; } | | | 1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750 1751 | pIdxInfo->idxNum = 2; pIdxInfo->needToFreeIdxStr = 1; if( iIdx>0 && 0==(pIdxInfo->idxStr = sqlite3_mprintf("%s", zIdxStr)) ){ return SQLITE_NOMEM; } nRow = pRtree->nRowEst >> (iIdx/2); pIdxInfo->estimatedCost = (double)6.0 * (double)nRow; setEstimatedRows(pIdxInfo, nRow); return rc; } /* |
︙ | ︙ |
Changes to src/test_bestindex.c.
︙ | ︙ | |||
111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 | }; /* A tcl cursor object */ struct tcl_cursor { sqlite3_vtab_cursor base; sqlite3_stmt *pStmt; /* Read data from here */ }; /* ** This function is the implementation of both the xConnect and xCreate ** methods of the fs virtual table. ** ** The argv[] array contains the following: ** ** argv[0] -> module name ("fs") ** argv[1] -> database name ** argv[2] -> table name ** argv[...] -> other module argument fields. */ static int tclConnect( sqlite3 *db, void *pAux, int argc, const char *const*argv, sqlite3_vtab **ppVtab, char **pzErr ){ Tcl_Interp *interp = (Tcl_Interp*)pAux; | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | | | < > | > > | | | | | | | | | | | | | | | | | | | > > | > > | 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 | }; /* A tcl cursor object */ struct tcl_cursor { sqlite3_vtab_cursor base; sqlite3_stmt *pStmt; /* Read data from here */ }; /* ** Dequote string z in place. */ static void tclDequote(char *z){ char q = z[0]; /* Set stack variable q to the close-quote character */ if( q=='[' || q=='\'' || q=='"' || q=='`' ){ int iIn = 1; int iOut = 0; if( q=='[' ) q = ']'; while( ALWAYS(z[iIn]) ){ if( z[iIn]==q ){ if( z[iIn+1]!=q ){ /* Character iIn was the close quote. */ iIn++; break; }else{ /* Character iIn and iIn+1 form an escaped quote character. Skip ** the input cursor past both and copy a single quote character ** to the output buffer. */ iIn += 2; z[iOut++] = q; } }else{ z[iOut++] = z[iIn++]; } } z[iOut] = '\0'; } } /* ** This function is the implementation of both the xConnect and xCreate ** methods of the fs virtual table. ** ** The argv[] array contains the following: ** ** argv[0] -> module name ("fs") ** argv[1] -> database name ** argv[2] -> table name ** argv[...] -> other module argument fields. */ static int tclConnect( sqlite3 *db, void *pAux, int argc, const char *const*argv, sqlite3_vtab **ppVtab, char **pzErr ){ Tcl_Interp *interp = (Tcl_Interp*)pAux; tcl_vtab *pTab = 0; char *zCmd = 0; Tcl_Obj *pScript = 0; int rc = SQLITE_OK; if( argc!=4 ){ *pzErr = sqlite3_mprintf("wrong number of arguments"); return SQLITE_ERROR; } zCmd = sqlite3_malloc(strlen(argv[3])+1); pTab = (tcl_vtab*)sqlite3_malloc(sizeof(tcl_vtab)); if( zCmd && pTab ){ memcpy(zCmd, argv[3], strlen(argv[3])+1); tclDequote(zCmd); memset(pTab, 0, sizeof(tcl_vtab)); pTab->pCmd = Tcl_NewStringObj(zCmd, -1); pTab->interp = interp; pTab->db = db; Tcl_IncrRefCount(pTab->pCmd); pScript = Tcl_DuplicateObj(pTab->pCmd); Tcl_IncrRefCount(pScript); Tcl_ListObjAppendElement(interp, pScript, Tcl_NewStringObj("xConnect", -1)); rc = Tcl_EvalObjEx(interp, pScript, TCL_EVAL_GLOBAL); if( rc!=TCL_OK ){ *pzErr = sqlite3_mprintf("%s", Tcl_GetStringResult(interp)); rc = SQLITE_ERROR; }else{ rc = sqlite3_declare_vtab(db, Tcl_GetStringResult(interp)); } if( rc!=SQLITE_OK ){ sqlite3_free(pTab); pTab = 0; } }else{ rc = SQLITE_NOMEM; } sqlite3_free(zCmd); *ppVtab = &pTab->base; return rc; } /* The xDisconnect and xDestroy methods are also the same */ static int tclDisconnect(sqlite3_vtab *pVtab){ tcl_vtab *pTab = (tcl_vtab*)pVtab; |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
2744 2745 2746 2747 2748 2749 2750 2751 2752 2753 2754 2755 2756 2757 | ** considered. */ if( pSrc->pIBIndex ) break; } return rc; } #ifndef SQLITE_OMIT_VIRTUALTABLE /* ** Add all WhereLoop objects for a table of the join identified by ** pBuilder->pNew->iTab. That table is guaranteed to be a virtual table. ** ** If there are no LEFT or CROSS JOIN joins in the query, both mExtra and ** mUnusable are set to 0. Otherwise, mExtra is a mask of all FROM clause ** entries that occur before the virtual table in the FROM clause and are | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 2744 2745 2746 2747 2748 2749 2750 2751 2752 2753 2754 2755 2756 2757 2758 2759 2760 2761 2762 2763 2764 2765 2766 2767 2768 2769 2770 2771 2772 2773 2774 2775 2776 2777 2778 2779 2780 2781 2782 2783 2784 2785 2786 2787 2788 2789 2790 2791 2792 2793 2794 2795 2796 2797 2798 2799 2800 2801 2802 2803 2804 2805 2806 2807 2808 2809 2810 2811 2812 2813 2814 2815 2816 2817 2818 2819 2820 2821 2822 2823 2824 2825 2826 2827 2828 2829 2830 2831 2832 2833 2834 2835 2836 2837 2838 2839 2840 2841 2842 2843 2844 2845 2846 2847 2848 2849 2850 2851 2852 2853 2854 2855 2856 2857 2858 2859 2860 2861 2862 2863 2864 2865 2866 2867 2868 2869 2870 2871 2872 2873 2874 2875 2876 2877 2878 2879 2880 2881 2882 2883 2884 2885 2886 2887 2888 2889 2890 2891 2892 2893 2894 2895 2896 2897 2898 2899 2900 2901 2902 | ** considered. */ if( pSrc->pIBIndex ) break; } return rc; } #ifndef SQLITE_OMIT_VIRTUALTABLE /* ** Argument pIdxInfo is already populated with all constraints that may ** be used by the virtual table identified by pBuilder->pNew->iTab. This ** function marks a subset of those constraints usable, invokes the ** xBestIndex method and adds the returned plan to pBuilder. ** ** A constraint is marked usable if: ** ** * Argument mUsable indicates that its prerequisites are available, and ** ** * It is not one of the operators specified in the mExclude mask passed ** as the fourth argument (which in practice is either WO_IN or 0). ** ** Argument mExtra is a mask of tables that must be scanned before the ** virtual table in question. These are added to the plans prerequisites ** before it is added to pBuilder. ** ** Output parameter *pbIn is set to true if the plan added to pBuilder ** uses one or more WO_IN terms, or false otherwise. */ static int whereLoopAddVirtualOne( WhereLoopBuilder *pBuilder, Bitmask mExtra, /* Mask of tables that must be used. */ Bitmask mUsable, /* Mask of usable prereqs */ u16 mExclude, /* Exclude terms for this operator */ sqlite3_index_info *pIdxInfo, /* Populated object for xBestIndex */ int *pbIn /* OUT: True if plan uses an IN(...) op */ ){ WhereClause *pWC = pBuilder->pWC; struct sqlite3_index_constraint *pIdxCons; struct sqlite3_index_constraint_usage *pUsage = pIdxInfo->aConstraintUsage; int i; int mxTerm; int rc = SQLITE_OK; WhereLoop *pNew = pBuilder->pNew; Parse *pParse = pBuilder->pWInfo->pParse; struct SrcList_item *pSrc = &pBuilder->pWInfo->pTabList->a[pNew->iTab]; int nConstraint = pIdxInfo->nConstraint; assert( (mUsable & mExtra)==mExtra ); *pbIn = 0; pNew->prereq = mExtra; /* Set the usable flag on the subset of constraints identified by ** arguments mUsable and mExclude. */ pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint; for(i=0; i<nConstraint; i++, pIdxCons++){ WhereTerm *pTerm = &pWC->a[pIdxCons->iTermOffset]; pIdxCons->usable = 0; if( (pTerm->prereqRight & mUsable)==pTerm->prereqRight && (pTerm->eOperator & mExclude)==0 ){ pIdxCons->usable = 1; } } /* Initialize the output fields of the sqlite3_index_info structure */ memset(pUsage, 0, sizeof(pUsage[0])*nConstraint); if( pIdxInfo->needToFreeIdxStr ) sqlite3_free(pIdxInfo->idxStr); pIdxInfo->idxStr = 0; pIdxInfo->idxNum = 0; pIdxInfo->needToFreeIdxStr = 0; pIdxInfo->orderByConsumed = 0; pIdxInfo->estimatedCost = SQLITE_BIG_DBL / (double)2; pIdxInfo->estimatedRows = 25; pIdxInfo->idxFlags = 0; pIdxInfo->colUsed = (sqlite3_int64)pSrc->colUsed; /* Invoke the virtual table xBestIndex() method */ rc = vtabBestIndex(pParse, pSrc->pTab, pIdxInfo); if( rc ) return rc; mxTerm = -1; assert( pNew->nLSlot>=nConstraint ); for(i=0; i<nConstraint; i++) pNew->aLTerm[i] = 0; pNew->u.vtab.omitMask = 0; pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint; for(i=0; i<nConstraint; i++, pIdxCons++){ int iTerm; if( (iTerm = pUsage[i].argvIndex - 1)>=0 ){ WhereTerm *pTerm; int j = pIdxCons->iTermOffset; if( iTerm>=nConstraint || j<0 || j>=pWC->nTerm || pNew->aLTerm[iTerm]!=0 ){ rc = SQLITE_ERROR; sqlite3ErrorMsg(pParse,"%s.xBestIndex() malfunction",pSrc->pTab->zName); return rc; } testcase( iTerm==nConstraint-1 ); testcase( j==0 ); testcase( j==pWC->nTerm-1 ); pTerm = &pWC->a[j]; pNew->prereq |= pTerm->prereqRight; assert( iTerm<pNew->nLSlot ); pNew->aLTerm[iTerm] = pTerm; if( iTerm>mxTerm ) mxTerm = iTerm; testcase( iTerm==15 ); testcase( iTerm==16 ); if( iTerm<16 && pUsage[i].omit ) pNew->u.vtab.omitMask |= 1<<iTerm; if( (pTerm->eOperator & WO_IN)!=0 ){ /* A virtual table that is constrained by an IN clause may not ** consume the ORDER BY clause because (1) the order of IN terms ** is not necessarily related to the order of output terms and ** (2) Multiple outputs from a single IN value will not merge ** together. */ pIdxInfo->orderByConsumed = 0; pIdxInfo->idxFlags &= ~SQLITE_INDEX_SCAN_UNIQUE; *pbIn = 1; } } } pNew->nLTerm = mxTerm+1; assert( pNew->nLTerm<=pNew->nLSlot ); pNew->u.vtab.idxNum = pIdxInfo->idxNum; pNew->u.vtab.needFree = pIdxInfo->needToFreeIdxStr; pIdxInfo->needToFreeIdxStr = 0; pNew->u.vtab.idxStr = pIdxInfo->idxStr; pNew->u.vtab.isOrdered = (i8)(pIdxInfo->orderByConsumed ? pIdxInfo->nOrderBy : 0); pNew->rSetup = 0; pNew->rRun = sqlite3LogEstFromDouble(pIdxInfo->estimatedCost); pNew->nOut = sqlite3LogEst(pIdxInfo->estimatedRows); /* Set the WHERE_ONEROW flag if the xBestIndex() method indicated ** that the scan will visit at most one row. Clear it otherwise. */ if( pIdxInfo->idxFlags & SQLITE_INDEX_SCAN_UNIQUE ){ pNew->wsFlags |= WHERE_ONEROW; }else{ pNew->wsFlags &= ~WHERE_ONEROW; } whereLoopInsert(pBuilder, pNew); if( pNew->u.vtab.needFree ){ sqlite3_free(pNew->u.vtab.idxStr); pNew->u.vtab.needFree = 0; } return SQLITE_OK; } /* ** Add all WhereLoop objects for a table of the join identified by ** pBuilder->pNew->iTab. That table is guaranteed to be a virtual table. ** ** If there are no LEFT or CROSS JOIN joins in the query, both mExtra and ** mUnusable are set to 0. Otherwise, mExtra is a mask of all FROM clause ** entries that occur before the virtual table in the FROM clause and are |
︙ | ︙ | |||
2774 2775 2776 2777 2778 2779 2780 2781 2782 2783 2784 | ** mUnusable should always be configured as "not-usable" for xBestIndex. */ static int whereLoopAddVirtual( WhereLoopBuilder *pBuilder, /* WHERE clause information */ Bitmask mExtra, /* Tables that must be scanned before this one */ Bitmask mUnusable /* Tables that must be scanned after this one */ ){ WhereInfo *pWInfo; /* WHERE analysis context */ Parse *pParse; /* The parsing context */ WhereClause *pWC; /* The WHERE clause */ struct SrcList_item *pSrc; /* The FROM clause term to search */ | > < < | < < < < < | < < | < > < < | | | < < | | | | | | < | > | | | < < > | > | | < | < < < < < < < < < < < < < < | > | | > > > | < > < < < < < < < < < < < < < < < < < < < < < < < < < < < < < | < > > | < > | < | > | | | < < | < < < < < < < | > > > > > > > < < < < < < < < < < < < | | > | < > | < | < < < < | > > > > > | < | | | 2919 2920 2921 2922 2923 2924 2925 2926 2927 2928 2929 2930 2931 2932 2933 2934 2935 2936 2937 2938 2939 2940 2941 2942 2943 2944 2945 2946 2947 2948 2949 2950 2951 2952 2953 2954 2955 2956 2957 2958 2959 2960 2961 2962 2963 2964 2965 2966 2967 2968 2969 2970 2971 2972 2973 2974 2975 2976 2977 2978 2979 2980 2981 2982 2983 2984 2985 2986 2987 2988 2989 2990 2991 2992 2993 2994 2995 2996 2997 2998 2999 3000 3001 3002 3003 3004 3005 3006 3007 3008 3009 3010 3011 3012 3013 3014 3015 3016 3017 3018 3019 3020 3021 3022 3023 3024 3025 3026 3027 3028 | ** mUnusable should always be configured as "not-usable" for xBestIndex. */ static int whereLoopAddVirtual( WhereLoopBuilder *pBuilder, /* WHERE clause information */ Bitmask mExtra, /* Tables that must be scanned before this one */ Bitmask mUnusable /* Tables that must be scanned after this one */ ){ int rc = SQLITE_OK; /* Return code */ WhereInfo *pWInfo; /* WHERE analysis context */ Parse *pParse; /* The parsing context */ WhereClause *pWC; /* The WHERE clause */ struct SrcList_item *pSrc; /* The FROM clause term to search */ sqlite3_index_info *p; /* Object to pass to xBestIndex() */ int nConstraint; /* Number of constraints in p */ int bIn; /* True if plan uses IN(...) operator */ WhereLoop *pNew; Bitmask mBest; /* Tables used by best possible plan */ assert( (mExtra & mUnusable)==0 ); pWInfo = pBuilder->pWInfo; pParse = pWInfo->pParse; pWC = pBuilder->pWC; pNew = pBuilder->pNew; pSrc = &pWInfo->pTabList->a[pNew->iTab]; assert( IsVirtual(pSrc->pTab) ); p = allocateIndexInfo(pParse, pWC, mUnusable, pSrc,pBuilder->pOrderBy); if( p==0 ) return SQLITE_NOMEM_BKPT; pNew->rSetup = 0; pNew->wsFlags = WHERE_VIRTUALTABLE; pNew->nLTerm = 0; pNew->u.vtab.needFree = 0; nConstraint = p->nConstraint; if( whereLoopResize(pParse->db, pNew, nConstraint) ){ sqlite3DbFree(pParse->db, p); return SQLITE_NOMEM_BKPT; } /* First call xBestIndex() with all constraints usable. */ rc = whereLoopAddVirtualOne(pBuilder, mExtra, (Bitmask)(-1), 0, p, &bIn); mBest = pNew->prereq & ~mExtra; /* If the call to xBestIndex() with all terms enabled produced a plan ** that does not require any source tables, there is no point in making ** any further calls - if the xBestIndex() method is sane they will all ** return the same plan anyway. */ if( mBest ){ int seenZero = 0; /* True if a plan with no prereqs seen */ int seenZeroNoIN = 0; /* Plan with no prereqs and no IN(...) seen */ Bitmask mPrev = 0; Bitmask mBestNoIn = 0; /* If the plan produced by the earlier call uses an IN(...) term, call ** xBestIndex again, this time with IN(...) terms disabled. */ if( rc==SQLITE_OK && bIn ){ rc = whereLoopAddVirtualOne(pBuilder, mExtra, (Bitmask)-1, WO_IN, p,&bIn); mBestNoIn = pNew->prereq & ~mExtra; if( mBestNoIn==0 ){ seenZero = 1; if( bIn==0 ) seenZeroNoIN = 1; } } /* Call xBestIndex once for each distinct value of (prereqRight & ~mExtra) ** in the set of terms that apply to the current virtual table. */ while( rc==SQLITE_OK ){ int i; Bitmask mNext = (Bitmask)(-1); assert( mNext>0 ); for(i=0; i<nConstraint; i++){ Bitmask mThis = ( pWC->a[p->aConstraint[i].iTermOffset].prereqRight & ~mExtra ); if( mThis>mPrev && mThis<mNext ) mNext = mThis; } mPrev = mNext; if( mNext==(Bitmask)(-1) ) break; if( mNext==mBest || mNext==mBestNoIn ) continue; rc = whereLoopAddVirtualOne(pBuilder, mExtra, mNext, 0, p, &bIn); if( pNew->prereq==mExtra ){ seenZero = 1; if( bIn==0 ) seenZeroNoIN = 1; } } /* If the calls to xBestIndex() in the above loop did not find a plan ** that requires no source tables at all (i.e. one guaranteed to be ** usable), make a call here with all source tables disabled */ if( rc==SQLITE_OK && seenZero==0 ){ rc = whereLoopAddVirtualOne(pBuilder, mExtra, mExtra, 0, p, &bIn); if( bIn==0 ) seenZeroNoIN = 1; } /* If the calls to xBestIndex() have so far failed to find a plan ** that requires no source tables at all and does not use an IN(...) ** operator, make a final call to obtain one here. */ if( rc==SQLITE_OK && seenZeroNoIN==0 ){ rc = whereLoopAddVirtualOne(pBuilder, mExtra, mExtra, WO_IN, p, &bIn); } } if( p->needToFreeIdxStr ) sqlite3_free(p->idxStr); sqlite3DbFree(pParse->db, p); return rc; } #endif /* SQLITE_OMIT_VIRTUALTABLE */ /* ** Add WhereLoop entries to handle OR terms. This works for either ** btrees or virtual tables. |
︙ | ︙ |
Added test/bestindex2.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 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 | # 2016 March 3 # # 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. # #*********************************************************************** set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix bestindex2 #------------------------------------------------------------------------- # Virtual table callback for table named $tbl, with the columns specified # by list argument $cols. e.g. if the function is invoked as: # # vtab_cmd t1 {a b c} ... # # The table created is: # # "CREATE TABLE t1 (a, b, c)" # # The tables xBestIndex method behaves as if all possible combinations of # "=" constraints (but no others) may be optimized. The cost of a full table # scan is: # # "WHERE 1" "cost 1000000 rows 1000000" # # If one or more "=" constraints are in use, the cost and estimated number # of rows returned are both is (11 - nCons)*1000, where nCons is the number # of constraints used. e.g. # # "WHERE a=? AND b=?" -> "cost 900 rows 900" # "WHERE c=? AND b<?" -> "cost 1000 rows 1000" # proc vtab_cmd {tbl cols method args} { switch -- $method { xConnect { return "CREATE TABLE $tbl ([join $cols ,])" } xBestIndex { foreach {clist orderby mask} $args {} set cons [list] set used [list] for {set i 0} {$i < [llength $clist]} {incr i} { array unset C array set C [lindex $clist $i] if {$C(op)=="eq" && $C(usable) && [lsearch $cons $C(column)]<0} { lappend used use $i lappend cons $C(column) } } set nCons [llength $cons] if {$nCons==0} { return "cost 1000000 rows 1000000" } else { set cost [expr (11-$nCons) * 1000] set ret [concat $used "cost $cost rows $cost"] set txt [list] foreach c $cons { lappend txt "[lindex $cols $c]=?" } lappend ret idxstr "indexed([join $txt { AND }])" return $ret } } } return "" } register_tcl_module db do_execsql_test 1.0 { CREATE VIRTUAL TABLE t1 USING tcl("vtab_cmd t1 {a b}"); CREATE VIRTUAL TABLE t2 USING tcl("vtab_cmd t2 {c d}"); CREATE VIRTUAL TABLE t3 USING tcl("vtab_cmd t3 {e f}"); } do_eqp_test 1.1 { SELECT * FROM t1 WHERE a='abc' } { 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:indexed(a=?)} } do_eqp_test 1.2 { SELECT * FROM t1 WHERE a='abc' AND b='def' } { 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:indexed(a=? AND b=?)} } do_eqp_test 1.3 { SELECT * FROM t1 WHERE a='abc' AND a='def' } { 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:indexed(a=?)} } do_eqp_test 1.4 { SELECT * FROM t1,t2 WHERE c=a } { 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:} 0 1 1 {SCAN TABLE t2 VIRTUAL TABLE INDEX 0:indexed(c=?)} } do_eqp_test 1.5 { SELECT * FROM t1, t2 CROSS JOIN t3 WHERE t2.c = +t1.b AND t3.e=t2.d } { 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:} 0 1 1 {SCAN TABLE t2 VIRTUAL TABLE INDEX 0:indexed(c=?)} 0 2 2 {SCAN TABLE t3 VIRTUAL TABLE INDEX 0:indexed(e=?)} } # This is the one that fails (as of 2016/3/3). # do_eqp_test 1.6 { SELECT * FROM t1, t2, t3 WHERE t2.c = +t1.b AND t3.e = t2.d } { 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:} 0 1 1 {SCAN TABLE t2 VIRTUAL TABLE INDEX 0:indexed(c=?)} 0 2 2 {SCAN TABLE t3 VIRTUAL TABLE INDEX 0:indexed(e=?)} } finish_test |