Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch unlikely-func Excluding Merge-Ins
This is equivalent to a diff from 75a8a8c1b3 to 695aee46e9
2013-09-13
| ||
17:47 | Adjust the query planner to take into account WHERE clause terms that do not drive indices. Add the unlikely() and likelihood() functions used to give hints to the query planner about the selectivity of WHERE clause terms. (check-in: bc446449a1 user: drh tags: trunk) | |
2013-09-12
| ||
23:42 | Refactor the ExprSetIrreducible() macro into ExprSetVVAProperty(*,EP_NoReduce). This is a naming change only. The logic is the same. (Closed-Leaf check-in: 695aee46e9 user: drh tags: unlikely-func) | |
23:12 | Fix typo in a macro name: "GlogUpperToLower" should be "GlobUpperToLower" (check-in: 73634ca463 user: drh tags: trunk) | |
17:29 | Merge in the Expr.flags expansion to 32-bits. Use an extra bit to help optimize the sqlite3ExprSkipCollate() routine. (check-in: 4c84d1b4c2 user: drh tags: unlikely-func) | |
16:50 | Increase the number of bits available in Expr.flags. Other tweaks aimed at making expression processing more robust. (Closed-Leaf check-in: 579a512538 user: drh tags: expr-tuning) | |
02:09 | For error log messages generated by the Win32 native allocator, make sure the correct format specifier is used for the value returned by GetLastError(). (check-in: 75a8a8c1b3 user: mistachkin tags: trunk) | |
01:47 | Fix a couple more harmless compiler warnings. (check-in: 59708674f6 user: mistachkin tags: trunk) | |
Changes to src/attach.c.
︙ | ︙ | |||
505 506 507 508 509 510 511 | return 0; } int sqlite3FixExpr( DbFixer *pFix, /* Context of the fixation */ Expr *pExpr /* The expression to be fixed to one database */ ){ while( pExpr ){ | | | 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 | return 0; } int sqlite3FixExpr( DbFixer *pFix, /* Context of the fixation */ Expr *pExpr /* The expression to be fixed to one database */ ){ while( pExpr ){ if( ExprHasProperty(pExpr, EP_TokenOnly) ) break; if( ExprHasProperty(pExpr, EP_xIsSelect) ){ if( sqlite3FixSelect(pFix, pExpr->x.pSelect) ) return 1; }else{ if( sqlite3FixExprList(pFix, pExpr->x.pList) ) return 1; } if( sqlite3FixExpr(pFix, pExpr->pRight) ){ return 1; |
︙ | ︙ |
Changes to src/expr.c.
︙ | ︙ | |||
66 67 68 69 70 71 72 | ** and the pExpr parameter is returned unchanged. */ Expr *sqlite3ExprAddCollateToken(Parse *pParse, Expr *pExpr, Token *pCollName){ if( pCollName->n>0 ){ Expr *pNew = sqlite3ExprAlloc(pParse->db, TK_COLLATE, pCollName, 1); if( pNew ){ pNew->pLeft = pExpr; | | | | > > > > > > > | | | > | 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 | ** and the pExpr parameter is returned unchanged. */ Expr *sqlite3ExprAddCollateToken(Parse *pParse, Expr *pExpr, Token *pCollName){ if( pCollName->n>0 ){ Expr *pNew = sqlite3ExprAlloc(pParse->db, TK_COLLATE, pCollName, 1); if( pNew ){ pNew->pLeft = pExpr; pNew->flags |= EP_Collate|EP_Skip; pExpr = pNew; } } return pExpr; } Expr *sqlite3ExprAddCollateString(Parse *pParse, Expr *pExpr, const char *zC){ Token s; assert( zC!=0 ); s.z = zC; s.n = sqlite3Strlen30(s.z); return sqlite3ExprAddCollateToken(pParse, pExpr, &s); } /* ** Skip over any TK_COLLATE or TK_AS operators and any unlikely() ** or likelihood() function at the root of an expression. */ Expr *sqlite3ExprSkipCollate(Expr *pExpr){ while( pExpr && ExprHasProperty(pExpr, EP_Skip) ){ if( ExprHasProperty(pExpr, EP_Unlikely) ){ assert( !ExprHasProperty(pExpr, EP_xIsSelect) ); assert( pExpr->x.pList->nExpr>0 ); assert( pExpr->op==TK_FUNCTION ); pExpr = pExpr->x.pList->a[0].pExpr; }else{ assert( pExpr->op==TK_COLLATE || pExpr->op==TK_AS ); pExpr = pExpr->pLeft; } } return pExpr; } /* ** Return the collation sequence for the expression pExpr. If ** there is no defined collating sequence, return NULL. ** |
︙ | ︙ | |||
592 593 594 595 596 597 598 | ** assigned. */ void sqlite3ExprAssignVarNumber(Parse *pParse, Expr *pExpr){ sqlite3 *db = pParse->db; const char *z; if( pExpr==0 ) return; | | | 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 | ** assigned. */ void sqlite3ExprAssignVarNumber(Parse *pParse, Expr *pExpr){ sqlite3 *db = pParse->db; const char *z; if( pExpr==0 ) return; assert( !ExprHasProperty(pExpr, EP_IntValue|EP_Reduced|EP_TokenOnly) ); z = pExpr->u.zToken; assert( z!=0 ); assert( z[0]!=0 ); if( z[1]==0 ){ /* Wildcard of the form "?". Assign the next variable number */ assert( z[0]=='?' ); pExpr->iColumn = (ynVar)(++pParse->nVar); |
︙ | ︙ | |||
662 663 664 665 666 667 668 | /* ** Recursively delete an expression tree. */ void sqlite3ExprDelete(sqlite3 *db, Expr *p){ if( p==0 ) return; /* Sanity check: Assert that the IntValue is non-negative if it exists */ assert( !ExprHasProperty(p, EP_IntValue) || p->u.iValue>=0 ); | | > > < | < | 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 | /* ** Recursively delete an expression tree. */ void sqlite3ExprDelete(sqlite3 *db, Expr *p){ if( p==0 ) return; /* Sanity check: Assert that the IntValue is non-negative if it exists */ assert( !ExprHasProperty(p, EP_IntValue) || p->u.iValue>=0 ); if( !ExprHasProperty(p, EP_TokenOnly) ){ /* The Expr.x union is never used at the same time as Expr.pRight */ assert( p->x.pList==0 || p->pRight==0 ); sqlite3ExprDelete(db, p->pLeft); sqlite3ExprDelete(db, p->pRight); if( ExprHasProperty(p, EP_MemToken) ) sqlite3DbFree(db, p->u.zToken); if( ExprHasProperty(p, EP_xIsSelect) ){ sqlite3SelectDelete(db, p->x.pSelect); }else{ sqlite3ExprListDelete(db, p->x.pList); } } if( !ExprHasProperty(p, EP_Static) ){ |
︙ | ︙ | |||
730 731 732 733 734 735 736 | */ static int dupedExprStructSize(Expr *p, int flags){ int nSize; assert( flags==EXPRDUP_REDUCE || flags==0 ); /* Only one flag value allowed */ if( 0==(flags&EXPRDUP_REDUCE) ){ nSize = EXPR_FULLSIZE; }else{ | | | | | 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 | */ static int dupedExprStructSize(Expr *p, int flags){ int nSize; assert( flags==EXPRDUP_REDUCE || flags==0 ); /* Only one flag value allowed */ if( 0==(flags&EXPRDUP_REDUCE) ){ nSize = EXPR_FULLSIZE; }else{ assert( !ExprHasProperty(p, EP_TokenOnly|EP_Reduced) ); assert( !ExprHasProperty(p, EP_FromJoin) ); assert( !ExprHasProperty(p, EP_MemToken) ); assert( !ExprHasProperty(p, EP_NoReduce) ); if( p->pLeft || p->pRight || p->x.pList ){ nSize = EXPR_REDUCEDSIZE | EP_Reduced; }else{ nSize = EXPR_TOKENONLYSIZE | EP_TokenOnly; } } return nSize; |
︙ | ︙ | |||
830 831 832 833 834 835 836 | }else{ int nSize = exprStructSize(p); memcpy(zAlloc, p, nSize); memset(&zAlloc[nSize], 0, EXPR_FULLSIZE-nSize); } /* Set the EP_Reduced, EP_TokenOnly, and EP_Static flags appropriately. */ | | | < | | 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 | }else{ int nSize = exprStructSize(p); memcpy(zAlloc, p, nSize); memset(&zAlloc[nSize], 0, EXPR_FULLSIZE-nSize); } /* Set the EP_Reduced, EP_TokenOnly, and EP_Static flags appropriately. */ pNew->flags &= ~(EP_Reduced|EP_TokenOnly|EP_Static|EP_MemToken); pNew->flags |= nStructSize & (EP_Reduced|EP_TokenOnly); pNew->flags |= staticFlag; /* Copy the p->u.zToken string, if any. */ if( nToken ){ char *zToken = pNew->u.zToken = (char*)&zAlloc[nNewSize]; memcpy(zToken, p->u.zToken, nToken); } if( 0==((p->flags|pNew->flags) & EP_TokenOnly) ){ /* Fill in the pNew->x.pSelect or pNew->x.pList member. */ if( ExprHasProperty(p, EP_xIsSelect) ){ pNew->x.pSelect = sqlite3SelectDup(db, p->x.pSelect, isReduced); }else{ pNew->x.pList = sqlite3ExprListDup(db, p->x.pList, isReduced); } } /* Fill in pNew->pLeft and pNew->pRight. */ if( ExprHasProperty(pNew, EP_Reduced|EP_TokenOnly) ){ zAlloc += dupedExprNodeSize(p, flags); if( ExprHasProperty(pNew, EP_Reduced) ){ pNew->pLeft = exprDup(db, p->pLeft, EXPRDUP_REDUCE, &zAlloc); pNew->pRight = exprDup(db, p->pRight, EXPRDUP_REDUCE, &zAlloc); } if( pzBuffer ){ *pzBuffer = zAlloc; } }else{ if( !ExprHasProperty(p, EP_TokenOnly) ){ pNew->pLeft = sqlite3ExprDup(db, p->pLeft, 0); pNew->pRight = sqlite3ExprDup(db, p->pRight, 0); } } } } |
︙ | ︙ | |||
1171 1172 1173 1174 1175 1176 1177 | ** */ static int exprNodeIsConstant(Walker *pWalker, Expr *pExpr){ /* If pWalker->u.i is 3 then any term of the expression that comes from ** the ON or USING clauses of a join disqualifies the expression ** from being considered constant. */ | | | 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 | ** */ static int exprNodeIsConstant(Walker *pWalker, Expr *pExpr){ /* If pWalker->u.i is 3 then any term of the expression that comes from ** the ON or USING clauses of a join disqualifies the expression ** from being considered constant. */ if( pWalker->u.i==3 && ExprHasProperty(pExpr, EP_FromJoin) ){ pWalker->u.i = 0; return WRC_Abort; } switch( pExpr->op ){ /* Consider functions to be constant if all their arguments are constant ** and pWalker->u.i==2 */ |
︙ | ︙ | |||
1602 1603 1604 1605 1606 1607 1608 | eType = IN_INDEX_EPH; if( prNotFound ){ *prNotFound = rMayHaveNull = ++pParse->nMem; sqlite3VdbeAddOp2(v, OP_Null, 0, *prNotFound); }else{ testcase( pParse->nQueryLoop>0 ); pParse->nQueryLoop = 0; | | | 1609 1610 1611 1612 1613 1614 1615 1616 1617 1618 1619 1620 1621 1622 1623 | eType = IN_INDEX_EPH; if( prNotFound ){ *prNotFound = rMayHaveNull = ++pParse->nMem; sqlite3VdbeAddOp2(v, OP_Null, 0, *prNotFound); }else{ testcase( pParse->nQueryLoop>0 ); pParse->nQueryLoop = 0; if( pX->pLeft->iColumn<0 && !ExprHasProperty(pX, EP_xIsSelect) ){ eType = IN_INDEX_ROWID; } } sqlite3CodeSubselect(pParse, pX, rMayHaveNull, eType==IN_INDEX_ROWID); pParse->nQueryLoop = savedNQueryLoop; }else{ pX->iTable = iTab; |
︙ | ︙ | |||
1671 1672 1673 1674 1675 1676 1677 | ** * The right-hand side is a correlated subquery ** * The right-hand side is an expression list containing variables ** * We are inside a trigger ** ** If all of the above are false, then we can run this code just once ** save the results, and reuse the same result on subsequent invocations. */ | | | 1678 1679 1680 1681 1682 1683 1684 1685 1686 1687 1688 1689 1690 1691 1692 | ** * The right-hand side is a correlated subquery ** * The right-hand side is an expression list containing variables ** * We are inside a trigger ** ** If all of the above are false, then we can run this code just once ** save the results, and reuse the same result on subsequent invocations. */ if( !ExprHasProperty(pExpr, EP_VarSelect) ){ testAddr = sqlite3CodeOnce(pParse); } #ifndef SQLITE_OMIT_EXPLAIN if( pParse->explain==2 ){ char *zMsg = sqlite3MPrintf( pParse->db, "EXECUTE %s%s SUBQUERY %d", testAddr>=0?"":"CORRELATED ", |
︙ | ︙ | |||
1840 1841 1842 1843 1844 1845 1846 | pSel->pLimit = sqlite3PExpr(pParse, TK_INTEGER, 0, 0, &sqlite3IntTokens[1]); pSel->iLimit = 0; if( sqlite3Select(pParse, pSel, &dest) ){ return 0; } rReg = dest.iSDParm; | | | 1847 1848 1849 1850 1851 1852 1853 1854 1855 1856 1857 1858 1859 1860 1861 | pSel->pLimit = sqlite3PExpr(pParse, TK_INTEGER, 0, 0, &sqlite3IntTokens[1]); pSel->iLimit = 0; if( sqlite3Select(pParse, pSel, &dest) ){ return 0; } rReg = dest.iSDParm; ExprSetVVAProperty(pExpr, EP_NoReduce); break; } } if( testAddr>=0 ){ sqlite3VdbeJumpHere(v, testAddr); } |
︙ | ︙ | |||
2311 2312 2313 2314 2315 2316 2317 2318 2319 2320 2321 2322 2323 2324 | for(i=0, p=pParse->aColCache; i<SQLITE_N_COLCACHE; i++, p++){ int r = p->iReg; if( r>=iFrom && r<=iTo ) return 1; /*NO_TEST*/ } return 0; } #endif /* SQLITE_DEBUG || SQLITE_COVERAGE_TEST */ /* ** Generate code into the current Vdbe to evaluate the given ** expression. Attempt to store the results in register "target". ** Return the register where results are stored. ** ** With this routine, there is no guarantee that results will | > > > > > > > > > > | 2318 2319 2320 2321 2322 2323 2324 2325 2326 2327 2328 2329 2330 2331 2332 2333 2334 2335 2336 2337 2338 2339 2340 2341 | for(i=0, p=pParse->aColCache; i<SQLITE_N_COLCACHE; i++, p++){ int r = p->iReg; if( r>=iFrom && r<=iTo ) return 1; /*NO_TEST*/ } return 0; } #endif /* SQLITE_DEBUG || SQLITE_COVERAGE_TEST */ /* ** Convert an expression node to a TK_REGISTER */ static void exprToRegister(Expr *p, int iReg){ p->op2 = p->op; p->op = TK_REGISTER; p->iTable = iReg; ExprClearProperty(p, EP_Skip); } /* ** Generate code into the current Vdbe to evaluate the given ** expression. Attempt to store the results in register "target". ** Return the register where results are stored. ** ** With this routine, there is no guarantee that results will |
︙ | ︙ | |||
2611 2612 2613 2614 2615 2616 2617 | int i; /* Loop counter */ u8 enc = ENC(db); /* The text encoding used by this database */ CollSeq *pColl = 0; /* A collating sequence */ assert( !ExprHasProperty(pExpr, EP_xIsSelect) ); testcase( op==TK_CONST_FUNC ); testcase( op==TK_FUNCTION ); | | | 2628 2629 2630 2631 2632 2633 2634 2635 2636 2637 2638 2639 2640 2641 2642 | int i; /* Loop counter */ u8 enc = ENC(db); /* The text encoding used by this database */ CollSeq *pColl = 0; /* A collating sequence */ assert( !ExprHasProperty(pExpr, EP_xIsSelect) ); testcase( op==TK_CONST_FUNC ); testcase( op==TK_FUNCTION ); if( ExprHasProperty(pExpr, EP_TokenOnly) ){ pFarg = 0; }else{ pFarg = pExpr->x.pList; } nFarg = pFarg ? pFarg->nExpr : 0; assert( !ExprHasProperty(pExpr, EP_IntValue) ); zId = pExpr->u.zToken; |
︙ | ︙ | |||
2645 2646 2647 2648 2649 2650 2651 2652 2653 2654 2655 2656 2657 2658 | sqlite3ExprCode(pParse, pFarg->a[i].pExpr, target); sqlite3ExprCachePop(pParse, 1); } sqlite3VdbeResolveLabel(v, endCoalesce); break; } if( pFarg ){ r1 = sqlite3GetTempRange(pParse, nFarg); /* For length() and typeof() functions with a column argument, ** set the P5 parameter to the OP_Column opcode to OPFLAG_LENGTHARG ** or OPFLAG_TYPEOFARG respectively, to avoid unnecessary data | > > > > > > > > | 2662 2663 2664 2665 2666 2667 2668 2669 2670 2671 2672 2673 2674 2675 2676 2677 2678 2679 2680 2681 2682 2683 | sqlite3ExprCode(pParse, pFarg->a[i].pExpr, target); sqlite3ExprCachePop(pParse, 1); } sqlite3VdbeResolveLabel(v, endCoalesce); break; } /* The UNLIKELY() function is a no-op. The result is the value ** of the first argument. */ if( pDef->funcFlags & SQLITE_FUNC_UNLIKELY ){ assert( nFarg>=1 ); sqlite3ExprCode(pParse, pFarg->a[0].pExpr, target); break; } if( pFarg ){ r1 = sqlite3GetTempRange(pParse, nFarg); /* For length() and typeof() functions with a column argument, ** set the P5 parameter to the OP_Column opcode to OPFLAG_LENGTHARG ** or OPFLAG_TYPEOFARG respectively, to avoid unnecessary data |
︙ | ︙ | |||
2842 2843 2844 2845 2846 2847 2848 | ** CASE WHEN e1 THEN r1 WHEN e2 THEN r2 ... WHEN eN THEN rN ELSE y END ** ** Form A is can be transformed into the equivalent form B as follows: ** CASE WHEN x=e1 THEN r1 WHEN x=e2 THEN r2 ... ** WHEN x=eN THEN rN ELSE y END ** ** X (if it exists) is in pExpr->pLeft. | > | | < < | < | | | | 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 2903 2904 2905 2906 2907 2908 2909 2910 2911 2912 2913 2914 2915 2916 2917 2918 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 | ** CASE WHEN e1 THEN r1 WHEN e2 THEN r2 ... WHEN eN THEN rN ELSE y END ** ** Form A is can be transformed into the equivalent form B as follows: ** CASE WHEN x=e1 THEN r1 WHEN x=e2 THEN r2 ... ** WHEN x=eN THEN rN ELSE y END ** ** X (if it exists) is in pExpr->pLeft. ** Y is in the last element of pExpr->x.pList if pExpr->x.pList->nExpr is ** odd. The Y is also optional. If the number of elements in x.pList ** is even, then Y is omitted and the "otherwise" result is NULL. ** Ei is in pExpr->pList->a[i*2] and Ri is pExpr->pList->a[i*2+1]. ** ** The result of the expression is the Ri for the first matching Ei, ** or if there is no matching Ei, the ELSE term Y, or if there is ** no ELSE term, NULL. */ default: assert( op==TK_CASE ); { int endLabel; /* GOTO label for end of CASE stmt */ int nextCase; /* GOTO label for next WHEN clause */ int nExpr; /* 2x number of WHEN terms */ int i; /* Loop counter */ ExprList *pEList; /* List of WHEN terms */ struct ExprList_item *aListelem; /* Array of WHEN terms */ Expr opCompare; /* The X==Ei expression */ Expr cacheX; /* Cached expression X */ Expr *pX; /* The X expression */ Expr *pTest = 0; /* X==Ei (form A) or just Ei (form B) */ VVA_ONLY( int iCacheLevel = pParse->iCacheLevel; ) assert( !ExprHasProperty(pExpr, EP_xIsSelect) && pExpr->x.pList ); assert(pExpr->x.pList->nExpr > 0); pEList = pExpr->x.pList; aListelem = pEList->a; nExpr = pEList->nExpr; endLabel = sqlite3VdbeMakeLabel(v); if( (pX = pExpr->pLeft)!=0 ){ cacheX = *pX; testcase( pX->op==TK_COLUMN ); testcase( pX->op==TK_REGISTER ); exprToRegister(&cacheX, sqlite3ExprCodeTemp(pParse, pX, ®Free1)); testcase( regFree1==0 ); opCompare.op = TK_EQ; opCompare.pLeft = &cacheX; pTest = &opCompare; /* Ticket b351d95f9cd5ef17e9d9dbae18f5ca8611190001: ** The value in regFree1 might get SCopy-ed into the file result. ** So make sure that the regFree1 register is not reused for other ** purposes and possibly overwritten. */ regFree1 = 0; } for(i=0; i<nExpr-1; i=i+2){ sqlite3ExprCachePush(pParse); if( pX ){ assert( pTest!=0 ); opCompare.pRight = aListelem[i].pExpr; }else{ pTest = aListelem[i].pExpr; } nextCase = sqlite3VdbeMakeLabel(v); testcase( pTest->op==TK_COLUMN ); sqlite3ExprIfFalse(pParse, pTest, nextCase, SQLITE_JUMPIFNULL); testcase( aListelem[i+1].pExpr->op==TK_COLUMN ); testcase( aListelem[i+1].pExpr->op==TK_REGISTER ); sqlite3ExprCode(pParse, aListelem[i+1].pExpr, target); sqlite3VdbeAddOp2(v, OP_Goto, 0, endLabel); sqlite3ExprCachePop(pParse, 1); sqlite3VdbeResolveLabel(v, nextCase); } if( (nExpr&1)!=0 ){ sqlite3ExprCachePush(pParse); sqlite3ExprCode(pParse, pEList->a[nExpr-1].pExpr, target); sqlite3ExprCachePop(pParse, 1); }else{ sqlite3VdbeAddOp2(v, OP_Null, 0, target); } assert( db->mallocFailed || pParse->nErr>0 || pParse->iCacheLevel==iCacheLevel ); sqlite3VdbeResolveLabel(v, endLabel); |
︙ | ︙ | |||
3019 3020 3021 3022 3023 3024 3025 | ** no way for a TK_REGISTER to exist here. But it seems prudent to ** keep the ALWAYS() in case the conditions above change with future ** modifications or enhancements. */ if( ALWAYS(pExpr->op!=TK_REGISTER) ){ int iMem; iMem = ++pParse->nMem; sqlite3VdbeAddOp2(v, OP_Copy, inReg, iMem); | | < < | 3042 3043 3044 3045 3046 3047 3048 3049 3050 3051 3052 3053 3054 3055 3056 | ** no way for a TK_REGISTER to exist here. But it seems prudent to ** keep the ALWAYS() in case the conditions above change with future ** modifications or enhancements. */ if( ALWAYS(pExpr->op!=TK_REGISTER) ){ int iMem; iMem = ++pParse->nMem; sqlite3VdbeAddOp2(v, OP_Copy, inReg, iMem); exprToRegister(pExpr, iMem); } return inReg; } #if defined(SQLITE_ENABLE_TREE_EXPLAIN) /* ** Generate a human-readable explanation of an expression tree. |
︙ | ︙ | |||
3151 3152 3153 3154 3155 3156 3157 | break; } case TK_AGG_FUNCTION: case TK_CONST_FUNC: case TK_FUNCTION: { ExprList *pFarg; /* List of function arguments */ | | | 3172 3173 3174 3175 3176 3177 3178 3179 3180 3181 3182 3183 3184 3185 3186 | break; } case TK_AGG_FUNCTION: case TK_CONST_FUNC: case TK_FUNCTION: { ExprList *pFarg; /* List of function arguments */ if( ExprHasProperty(pExpr, EP_TokenOnly) ){ pFarg = 0; }else{ pFarg = pExpr->x.pList; } if( op==TK_AGG_FUNCTION ){ sqlite3ExplainPrintf(pOut, "AGG_FUNCTION%d:%s(", pExpr->op2, pExpr->u.zToken); |
︙ | ︙ | |||
3400 3401 3402 3403 3404 3405 3406 | if( isAppropriateForFactoring(pExpr) ){ int r1 = ++pParse->nMem; int r2 = sqlite3ExprCodeTarget(pParse, pExpr, r1); /* If r2!=r1, it means that register r1 is never used. That is harmless ** but suboptimal, so we want to know about the situation to fix it. ** Hence the following assert: */ assert( r2==r1 ); | < < | | 3421 3422 3423 3424 3425 3426 3427 3428 3429 3430 3431 3432 3433 3434 3435 | if( isAppropriateForFactoring(pExpr) ){ int r1 = ++pParse->nMem; int r2 = sqlite3ExprCodeTarget(pParse, pExpr, r1); /* If r2!=r1, it means that register r1 is never used. That is harmless ** but suboptimal, so we want to know about the situation to fix it. ** Hence the following assert: */ assert( r2==r1 ); exprToRegister(pExpr, r2); return WRC_Prune; } return WRC_Continue; } /* ** Preevaluate constant subexpressions within pExpr and store the |
︙ | ︙ | |||
3500 3501 3502 3503 3504 3505 3506 | exprAnd.pRight = &compRight; compLeft.op = TK_GE; compLeft.pLeft = &exprX; compLeft.pRight = pExpr->x.pList->a[0].pExpr; compRight.op = TK_LE; compRight.pLeft = &exprX; compRight.pRight = pExpr->x.pList->a[1].pExpr; | | < < | 3519 3520 3521 3522 3523 3524 3525 3526 3527 3528 3529 3530 3531 3532 3533 | exprAnd.pRight = &compRight; compLeft.op = TK_GE; compLeft.pLeft = &exprX; compLeft.pRight = pExpr->x.pList->a[0].pExpr; compRight.op = TK_LE; compRight.pLeft = &exprX; compRight.pRight = pExpr->x.pList->a[1].pExpr; exprToRegister(&exprX, sqlite3ExprCodeTemp(pParse, &exprX, ®Free1)); if( jumpIfTrue ){ sqlite3ExprIfTrue(pParse, &exprAnd, dest, jumpIfNull); }else{ sqlite3ExprIfFalse(pParse, &exprAnd, dest, jumpIfNull); } sqlite3ReleaseTempReg(pParse, regFree1); |
︙ | ︙ | |||
3817 3818 3819 3820 3821 3822 3823 | ** just might result in some slightly slower code. But returning ** an incorrect 0 or 1 could lead to a malfunction. */ int sqlite3ExprCompare(Expr *pA, Expr *pB, int iTab){ if( pA==0||pB==0 ){ return pB==pA ? 0 : 2; } | | | | 3834 3835 3836 3837 3838 3839 3840 3841 3842 3843 3844 3845 3846 3847 3848 3849 | ** just might result in some slightly slower code. But returning ** an incorrect 0 or 1 could lead to a malfunction. */ int sqlite3ExprCompare(Expr *pA, Expr *pB, int iTab){ if( pA==0||pB==0 ){ return pB==pA ? 0 : 2; } assert( !ExprHasProperty(pA, EP_TokenOnly|EP_Reduced) ); assert( !ExprHasProperty(pB, EP_TokenOnly|EP_Reduced) ); if( ExprHasProperty(pA, EP_xIsSelect) || ExprHasProperty(pB, EP_xIsSelect) ){ return 2; } if( (pA->flags & EP_Distinct)!=(pB->flags & EP_Distinct) ) return 2; if( pA->op!=pB->op && (pA->op!=TK_REGISTER || pA->op2!=pB->op) ){ if( pA->op==TK_COLLATE && sqlite3ExprCompare(pA->pLeft, pB, iTab)<2 ){ return 1; |
︙ | ︙ | |||
4032 4033 4034 4035 4036 4037 4038 | testcase( pExpr->op==TK_COLUMN ); /* Check to see if the column is in one of the tables in the FROM ** clause of the aggregate query */ if( ALWAYS(pSrcList!=0) ){ struct SrcList_item *pItem = pSrcList->a; for(i=0; i<pSrcList->nSrc; i++, pItem++){ struct AggInfo_col *pCol; | | | 4049 4050 4051 4052 4053 4054 4055 4056 4057 4058 4059 4060 4061 4062 4063 | testcase( pExpr->op==TK_COLUMN ); /* Check to see if the column is in one of the tables in the FROM ** clause of the aggregate query */ if( ALWAYS(pSrcList!=0) ){ struct SrcList_item *pItem = pSrcList->a; for(i=0; i<pSrcList->nSrc; i++, pItem++){ struct AggInfo_col *pCol; assert( !ExprHasProperty(pExpr, EP_TokenOnly|EP_Reduced) ); if( pExpr->iTable==pItem->iCursor ){ /* If we reach this point, it means that pExpr refers to a table ** that is in the FROM clause of the aggregate query. ** ** Make an entry for the column in pAggInfo->aCol[] if there ** is not an entry there already. */ |
︙ | ︙ | |||
4081 4082 4083 4084 4085 4086 4087 | } } /* There is now an entry for pExpr in pAggInfo->aCol[] (either ** because it was there before or because we just created it). ** Convert the pExpr to be a TK_AGG_COLUMN referring to that ** pAggInfo->aCol[] entry. */ | | | 4098 4099 4100 4101 4102 4103 4104 4105 4106 4107 4108 4109 4110 4111 4112 | } } /* There is now an entry for pExpr in pAggInfo->aCol[] (either ** because it was there before or because we just created it). ** Convert the pExpr to be a TK_AGG_COLUMN referring to that ** pAggInfo->aCol[] entry. */ ExprSetVVAProperty(pExpr, EP_NoReduce); pExpr->pAggInfo = pAggInfo; pExpr->op = TK_AGG_COLUMN; pExpr->iAgg = (i16)k; break; } /* endif pExpr->iTable==pItem->iCursor */ } /* end loop over pSrcList */ } |
︙ | ︙ | |||
4127 4128 4129 4130 4131 4132 4133 | }else{ pItem->iDistinct = -1; } } } /* Make pExpr point to the appropriate pAggInfo->aFunc[] entry */ | | | | 4144 4145 4146 4147 4148 4149 4150 4151 4152 4153 4154 4155 4156 4157 4158 4159 | }else{ pItem->iDistinct = -1; } } } /* Make pExpr point to the appropriate pAggInfo->aFunc[] entry */ assert( !ExprHasProperty(pExpr, EP_TokenOnly|EP_Reduced) ); ExprSetVVAProperty(pExpr, EP_NoReduce); pExpr->iAgg = (i16)i; pExpr->pAggInfo = pAggInfo; return WRC_Prune; }else{ return WRC_Continue; } } |
︙ | ︙ |
Changes to src/func.c.
︙ | ︙ | |||
414 415 416 417 418 419 420 | } sqlite3_result_text(context, z1, n, sqlite3_free); } } } /* | | | | | | | | | 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 | } sqlite3_result_text(context, z1, n, sqlite3_free); } } } /* ** Some functions like COALESCE() and IFNULL() and UNLIKELY() are implemented ** as VDBE code so that unused argument values do not have to be computed. ** However, we still need some kind of function implementation for this ** routines in the function table. The noopFunc macro provides this. ** noopFunc will never be called so it doesn't matter what the implementation ** is. We might as well use the "version()" function as a substitute. */ #define noopFunc versionFunc /* Substitute function - never called */ /* ** Implementation of random(). Return a random integer. */ static void randomFunc( sqlite3_context *context, int NotUsed, |
︙ | ︙ | |||
1655 1656 1657 1658 1659 1660 1661 | FUNCTION(round, 1, 0, 0, roundFunc ), FUNCTION(round, 2, 0, 0, roundFunc ), #endif FUNCTION(upper, 1, 0, 0, upperFunc ), FUNCTION(lower, 1, 0, 0, lowerFunc ), FUNCTION(coalesce, 1, 0, 0, 0 ), FUNCTION(coalesce, 0, 0, 0, 0 ), | | | > > | 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 1672 1673 | FUNCTION(round, 1, 0, 0, roundFunc ), FUNCTION(round, 2, 0, 0, roundFunc ), #endif FUNCTION(upper, 1, 0, 0, upperFunc ), FUNCTION(lower, 1, 0, 0, lowerFunc ), FUNCTION(coalesce, 1, 0, 0, 0 ), FUNCTION(coalesce, 0, 0, 0, 0 ), FUNCTION2(coalesce, -1, 0, 0, noopFunc, SQLITE_FUNC_COALESCE), FUNCTION(hex, 1, 0, 0, hexFunc ), FUNCTION2(ifnull, 2, 0, 0, noopFunc, SQLITE_FUNC_COALESCE), FUNCTION2(unlikely, 1, 0, 0, noopFunc, SQLITE_FUNC_UNLIKELY), FUNCTION2(likelihood, 2, 0, 0, noopFunc, SQLITE_FUNC_UNLIKELY), FUNCTION(random, 0, 0, 0, randomFunc ), FUNCTION(randomblob, 1, 0, 0, randomBlob ), FUNCTION(nullif, 2, 0, 1, nullifFunc ), FUNCTION(sqlite_version, 0, 0, 0, versionFunc ), FUNCTION(sqlite_source_id, 0, 0, 0, sourceidFunc ), FUNCTION(sqlite_log, 2, 0, 0, errlogFunc ), #ifndef SQLITE_OMIT_COMPILEOPTION_DIAGS |
︙ | ︙ |
Changes to src/parse.y.
︙ | ︙ | |||
1077 1078 1079 1080 1081 1082 1083 | A.zStart = B.z; A.zEnd = &E.z[E.n]; } %endif SQLITE_OMIT_SUBQUERY /* CASE expressions */ expr(A) ::= CASE(C) case_operand(X) case_exprlist(Y) case_else(Z) END(E). { | | | > | 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 | A.zStart = B.z; A.zEnd = &E.z[E.n]; } %endif SQLITE_OMIT_SUBQUERY /* CASE expressions */ expr(A) ::= CASE(C) case_operand(X) case_exprlist(Y) case_else(Z) END(E). { A.pExpr = sqlite3PExpr(pParse, TK_CASE, X, 0, 0); if( A.pExpr ){ A.pExpr->x.pList = Z ? sqlite3ExprListAppend(pParse,Y,Z) : Y; sqlite3ExprSetHeight(pParse, A.pExpr); }else{ sqlite3ExprListDelete(pParse->db, Y); sqlite3ExprDelete(pParse->db, Z); } A.zStart = C.z; A.zEnd = &E.z[E.n]; } %type case_exprlist {ExprList*} %destructor case_exprlist {sqlite3ExprListDelete(pParse->db, $$);} case_exprlist(A) ::= case_exprlist(X) WHEN expr(Y) THEN expr(Z). { |
︙ | ︙ |
Changes to src/resolve.c.
︙ | ︙ | |||
103 104 105 106 107 108 109 110 111 112 113 114 115 116 | db = pParse->db; pDup = sqlite3ExprDup(db, pOrig, 0); if( pDup==0 ) return; if( pOrig->op!=TK_COLUMN && zType[0]!='G' ){ incrAggFunctionDepth(pDup, nSubquery); pDup = sqlite3PExpr(pParse, TK_AS, pDup, 0, 0); if( pDup==0 ) return; if( pEList->a[iCol].iAlias==0 ){ pEList->a[iCol].iAlias = (u16)(++pParse->nAlias); } pDup->iTable = pEList->a[iCol].iAlias; } if( pExpr->op==TK_COLLATE ){ pDup = sqlite3ExprAddCollateString(pParse, pDup, pExpr->u.zToken); | > | 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 | db = pParse->db; pDup = sqlite3ExprDup(db, pOrig, 0); if( pDup==0 ) return; if( pOrig->op!=TK_COLUMN && zType[0]!='G' ){ incrAggFunctionDepth(pDup, nSubquery); pDup = sqlite3PExpr(pParse, TK_AS, pDup, 0, 0); if( pDup==0 ) return; ExprSetProperty(pDup, EP_Skip); if( pEList->a[iCol].iAlias==0 ){ pEList->a[iCol].iAlias = (u16)(++pParse->nAlias); } pDup->iTable = pEList->a[iCol].iAlias; } if( pExpr->op==TK_COLLATE ){ pDup = sqlite3ExprAddCollateString(pParse, pDup, pExpr->u.zToken); |
︙ | ︙ | |||
125 126 127 128 129 130 131 | */ ExprSetProperty(pExpr, EP_Static); sqlite3ExprDelete(db, pExpr); memcpy(pExpr, pDup, sizeof(*pExpr)); if( !ExprHasProperty(pExpr, EP_IntValue) && pExpr->u.zToken!=0 ){ assert( (pExpr->flags & (EP_Reduced|EP_TokenOnly))==0 ); pExpr->u.zToken = sqlite3DbStrDup(db, pExpr->u.zToken); | | | 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 | */ ExprSetProperty(pExpr, EP_Static); sqlite3ExprDelete(db, pExpr); memcpy(pExpr, pDup, sizeof(*pExpr)); if( !ExprHasProperty(pExpr, EP_IntValue) && pExpr->u.zToken!=0 ){ assert( (pExpr->flags & (EP_Reduced|EP_TokenOnly))==0 ); pExpr->u.zToken = sqlite3DbStrDup(db, pExpr->u.zToken); pExpr->flags |= EP_MemToken; } sqlite3DbFree(db, pDup); } /* ** Return TRUE if the name zCol occurs anywhere in the USING clause. |
︙ | ︙ | |||
225 226 227 228 229 230 231 | struct SrcList_item *pMatch = 0; /* The matching pSrcList item */ NameContext *pTopNC = pNC; /* First namecontext in the list */ Schema *pSchema = 0; /* Schema of the expression */ int isTrigger = 0; assert( pNC ); /* the name context cannot be NULL. */ assert( zCol ); /* The Z in X.Y.Z cannot be NULL */ | | | | 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 | struct SrcList_item *pMatch = 0; /* The matching pSrcList item */ NameContext *pTopNC = pNC; /* First namecontext in the list */ Schema *pSchema = 0; /* Schema of the expression */ int isTrigger = 0; assert( pNC ); /* the name context cannot be NULL. */ assert( zCol ); /* The Z in X.Y.Z cannot be NULL */ assert( !ExprHasProperty(pExpr, EP_TokenOnly|EP_Reduced) ); /* Initialize the node to no-match */ pExpr->iTable = -1; pExpr->pTab = 0; ExprSetVVAProperty(pExpr, EP_NoReduce); /* Translate the schema name in zDb into a pointer to the corresponding ** schema. If not found, pSchema will remain NULL and nothing will match ** resulting in an appropriate error message toward the end of this routine */ if( zDb ){ testcase( pNC->ncFlags & NC_PartIdx ); |
︙ | ︙ | |||
566 567 568 569 570 571 572 573 574 575 576 577 578 579 | sqlite3ErrorMsg(pParse,"%s prohibited in CHECK constraints", zMsg); } } #else # define notValidCheckConstraint(P,N,M) #endif /* ** This routine is callback for sqlite3WalkExpr(). ** ** Resolve symbolic names into TK_COLUMN operators for the current ** node in the expression tree. Return 0 to continue the search down ** the tree or 2 to abort the tree walk. | > > > > > > > > > > > > > | 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 | sqlite3ErrorMsg(pParse,"%s prohibited in CHECK constraints", zMsg); } } #else # define notValidCheckConstraint(P,N,M) #endif /* ** Expression p should encode a floating point value between 1.0 and 0.0. ** Return 1024 times this value. Or return -1 if p is not a floating point ** value between 1.0 and 0.0. */ static int exprProbability(Expr *p){ double r = -1.0; if( p->op!=TK_FLOAT ) return -1; sqlite3AtoF(p->u.zToken, &r, sqlite3Strlen30(p->u.zToken), SQLITE_UTF8); assert( r>=0.0 ); if( r>1.0 ) return -1; return (int)(r*1000.0); } /* ** This routine is callback for sqlite3WalkExpr(). ** ** Resolve symbolic names into TK_COLUMN operators for the current ** node in the expression tree. Return 0 to continue the search down ** the tree or 2 to abort the tree walk. |
︙ | ︙ | |||
587 588 589 590 591 592 593 | Parse *pParse; pNC = pWalker->u.pNC; assert( pNC!=0 ); pParse = pNC->pParse; assert( pParse==pWalker->pParse ); | | | 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 | Parse *pParse; pNC = pWalker->u.pNC; assert( pNC!=0 ); pParse = pNC->pParse; assert( pParse==pWalker->pParse ); if( ExprHasProperty(pExpr, EP_Resolved) ) return WRC_Prune; ExprSetProperty(pExpr, EP_Resolved); #ifndef NDEBUG if( pNC->pSrcList && pNC->pSrcList->nAlloc>0 ){ SrcList *pSrcList = pNC->pSrcList; int i; for(i=0; i<pNC->pSrcList->nSrc; i++){ assert( pSrcList->a[i].iCursor>=0 && pSrcList->a[i].iCursor<pParse->nTab); |
︙ | ︙ | |||
679 680 681 682 683 684 685 686 687 688 689 690 691 692 | if( pDef==0 ){ no_such_func = 1; }else{ wrong_num_args = 1; } }else{ is_agg = pDef->xFunc==0; } #ifndef SQLITE_OMIT_AUTHORIZATION if( pDef ){ auth = sqlite3AuthCheck(pParse, SQLITE_FUNCTION, 0, pDef->zName, 0); if( auth!=SQLITE_OK ){ if( auth==SQLITE_DENY ){ sqlite3ErrorMsg(pParse, "not authorized to use function: %s", | > > > > > > > > > > > > > | 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 | if( pDef==0 ){ no_such_func = 1; }else{ wrong_num_args = 1; } }else{ is_agg = pDef->xFunc==0; if( pDef->funcFlags & SQLITE_FUNC_UNLIKELY ){ ExprSetProperty(pExpr, EP_Unlikely|EP_Skip); if( n==2 ){ pExpr->iTable = exprProbability(pList->a[1].pExpr); if( pExpr->iTable<0 ){ sqlite3ErrorMsg(pParse, "second argument to likelihood() must be a " "constant between 0.0 and 1.0"); pNC->nErr++; } }else{ pExpr->iTable = 62; /* TUNING: Default 2nd arg to unlikely() is 0.0625 */ } } } #ifndef SQLITE_OMIT_AUTHORIZATION if( pDef ){ auth = sqlite3AuthCheck(pParse, SQLITE_FUNCTION, 0, pDef->zName, 0); if( auth!=SQLITE_OK ){ if( auth==SQLITE_DENY ){ sqlite3ErrorMsg(pParse, "not authorized to use function: %s", |
︙ | ︙ |
Changes to src/select.c.
︙ | ︙ | |||
260 261 262 263 264 265 266 | pE1 = sqlite3CreateColumnExpr(db, pSrc, iLeft, iColLeft); pE2 = sqlite3CreateColumnExpr(db, pSrc, iRight, iColRight); pEq = sqlite3PExpr(pParse, TK_EQ, pE1, pE2, 0); if( pEq && isOuterJoin ){ ExprSetProperty(pEq, EP_FromJoin); | | | | 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 | pE1 = sqlite3CreateColumnExpr(db, pSrc, iLeft, iColLeft); pE2 = sqlite3CreateColumnExpr(db, pSrc, iRight, iColRight); pEq = sqlite3PExpr(pParse, TK_EQ, pE1, pE2, 0); if( pEq && isOuterJoin ){ ExprSetProperty(pEq, EP_FromJoin); assert( !ExprHasProperty(pEq, EP_TokenOnly|EP_Reduced) ); ExprSetVVAProperty(pEq, EP_NoReduce); pEq->iRightJoinTable = (i16)pE2->iTable; } *ppWhere = sqlite3ExprAnd(db, *ppWhere, pEq); } /* ** Set the EP_FromJoin property on all terms of the given expression. |
︙ | ︙ | |||
296 297 298 299 300 301 302 | ** defer the handling of t1.x=5, it will be processed immediately ** after the t1 loop and rows with t1.x!=5 will never appear in ** the output, which is incorrect. */ static void setJoinExpr(Expr *p, int iTable){ while( p ){ ExprSetProperty(p, EP_FromJoin); | | | | 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 | ** defer the handling of t1.x=5, it will be processed immediately ** after the t1 loop and rows with t1.x!=5 will never appear in ** the output, which is incorrect. */ static void setJoinExpr(Expr *p, int iTable){ while( p ){ ExprSetProperty(p, EP_FromJoin); assert( !ExprHasProperty(p, EP_TokenOnly|EP_Reduced) ); ExprSetVVAProperty(p, EP_NoReduce); p->iRightJoinTable = (i16)iTable; setJoinExpr(p->pLeft, iTable); p = p->pRight; } } /* |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 | #define SQLITE_DistinctOpt 0x0020 /* DISTINCT using indexes */ #define SQLITE_CoverIdxScan 0x0040 /* Covering index scans */ #define SQLITE_OrderByIdxJoin 0x0080 /* ORDER BY of joins via index */ #define SQLITE_SubqCoroutine 0x0100 /* Evaluate subqueries as coroutines */ #define SQLITE_Transitive 0x0200 /* Transitive constraints */ #define SQLITE_OmitNoopJoin 0x0400 /* Omit unused tables in joins */ #define SQLITE_Stat3 0x0800 /* Use the SQLITE_STAT3 table */ #define SQLITE_AllOpts 0xffff /* All optimizations */ /* ** Macros for testing whether or not optimizations are enabled or disabled. */ #ifndef SQLITE_OMIT_BUILTIN_TEST #define OptimizationDisabled(db, mask) (((db)->dbOptFlags&(mask))!=0) | > | 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 | #define SQLITE_DistinctOpt 0x0020 /* DISTINCT using indexes */ #define SQLITE_CoverIdxScan 0x0040 /* Covering index scans */ #define SQLITE_OrderByIdxJoin 0x0080 /* ORDER BY of joins via index */ #define SQLITE_SubqCoroutine 0x0100 /* Evaluate subqueries as coroutines */ #define SQLITE_Transitive 0x0200 /* Transitive constraints */ #define SQLITE_OmitNoopJoin 0x0400 /* Omit unused tables in joins */ #define SQLITE_Stat3 0x0800 /* Use the SQLITE_STAT3 table */ #define SQLITE_AdjustOutEst 0x1000 /* Adjust output estimates using WHERE */ #define SQLITE_AllOpts 0xffff /* All optimizations */ /* ** Macros for testing whether or not optimizations are enabled or disabled. */ #ifndef SQLITE_OMIT_BUILTIN_TEST #define OptimizationDisabled(db, mask) (((db)->dbOptFlags&(mask))!=0) |
︙ | ︙ | |||
1718 1719 1720 1721 1722 1723 1724 | ** are contained within the same memory allocation. Note, however, that ** the subtrees in Expr.x.pList or Expr.x.pSelect are always separately ** allocated, regardless of whether or not EP_Reduced is set. */ struct Expr { u8 op; /* Operation performed by this node */ char affinity; /* The affinity of the column or 0 if not a column */ | | | | | > < | | | | | | | | | | | | | | | | < < < < | | | < < < < < < < < < < < | | > > > > > > > > > > | 1719 1720 1721 1722 1723 1724 1725 1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736 1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750 1751 1752 1753 1754 1755 1756 1757 1758 1759 1760 1761 1762 1763 1764 1765 1766 1767 1768 1769 1770 1771 1772 1773 1774 1775 1776 1777 1778 1779 1780 1781 1782 1783 1784 1785 1786 1787 1788 1789 1790 1791 1792 1793 1794 1795 1796 1797 1798 1799 1800 1801 1802 1803 1804 1805 1806 1807 1808 1809 1810 1811 1812 1813 1814 | ** are contained within the same memory allocation. Note, however, that ** the subtrees in Expr.x.pList or Expr.x.pSelect are always separately ** allocated, regardless of whether or not EP_Reduced is set. */ struct Expr { u8 op; /* Operation performed by this node */ char affinity; /* The affinity of the column or 0 if not a column */ u32 flags; /* Various flags. EP_* See below */ union { char *zToken; /* Token value. Zero terminated and dequoted */ int iValue; /* Non-negative integer value if EP_IntValue */ } u; /* If the EP_TokenOnly flag is set in the Expr.flags mask, then no ** space is allocated for the fields below this point. An attempt to ** access them will result in a segfault or malfunction. *********************************************************************/ Expr *pLeft; /* Left subnode */ Expr *pRight; /* Right subnode */ union { ExprList *pList; /* op = IN, EXISTS, SELECT, CASE, FUNCTION, BETWEEN */ Select *pSelect; /* EP_xIsSelect and op = IN, EXISTS, SELECT */ } x; /* If the EP_Reduced flag is set in the Expr.flags mask, then no ** space is allocated for the fields below this point. An attempt to ** access them will result in a segfault or malfunction. *********************************************************************/ #if SQLITE_MAX_EXPR_DEPTH>0 int nHeight; /* Height of the tree headed by this node */ #endif int iTable; /* TK_COLUMN: cursor number of table holding column ** TK_REGISTER: register number ** TK_TRIGGER: 1 -> new, 0 -> old ** EP_Unlikely: 1000 times likelihood */ ynVar iColumn; /* TK_COLUMN: column index. -1 for rowid. ** TK_VARIABLE: variable number (always >= 1). */ i16 iAgg; /* Which entry in pAggInfo->aCol[] or ->aFunc[] */ i16 iRightJoinTable; /* If EP_FromJoin, the right table of the join */ u8 op2; /* TK_REGISTER: original value of Expr.op ** TK_COLUMN: the value of p5 for OP_Column ** TK_AGG_FUNCTION: nesting depth */ AggInfo *pAggInfo; /* Used by TK_AGG_COLUMN and TK_AGG_FUNCTION */ Table *pTab; /* Table for TK_COLUMN expressions. */ }; /* ** The following are the meanings of bits in the Expr.flags field. */ #define EP_FromJoin 0x000001 /* Originated in ON or USING clause of a join */ #define EP_Agg 0x000002 /* Contains one or more aggregate functions */ #define EP_Resolved 0x000004 /* IDs have been resolved to COLUMNs */ #define EP_Error 0x000008 /* Expression contains one or more errors */ #define EP_Distinct 0x000010 /* Aggregate function with DISTINCT keyword */ #define EP_VarSelect 0x000020 /* pSelect is correlated, not constant */ #define EP_DblQuoted 0x000040 /* token.z was originally in "..." */ #define EP_InfixFunc 0x000080 /* True for an infix function: LIKE, GLOB, etc */ #define EP_Collate 0x000100 /* Tree contains a TK_COLLATE opeartor */ #define EP_FixedDest 0x000200 /* Result needed in a specific register */ #define EP_IntValue 0x000400 /* Integer value contained in u.iValue */ #define EP_xIsSelect 0x000800 /* x.pSelect is valid (otherwise x.pList is) */ #define EP_Skip 0x001000 /* COLLATE, AS, or UNLIKELY */ #define EP_Reduced 0x002000 /* Expr struct EXPR_REDUCEDSIZE bytes only */ #define EP_TokenOnly 0x004000 /* Expr struct EXPR_TOKENONLYSIZE bytes only */ #define EP_Static 0x008000 /* Held in memory not obtained from malloc() */ #define EP_MemToken 0x010000 /* Need to sqlite3DbFree() Expr.zToken */ #define EP_NoReduce 0x020000 /* Cannot EXPRDUP_REDUCE this Expr */ #define EP_Unlikely 0x040000 /* unlikely() or likelihood() function */ /* ** These macros can be used to test, set, or clear bits in the ** Expr.flags field. */ #define ExprHasProperty(E,P) (((E)->flags&(P))!=0) #define ExprHasAllProperty(E,P) (((E)->flags&(P))==(P)) #define ExprSetProperty(E,P) (E)->flags|=(P) #define ExprClearProperty(E,P) (E)->flags&=~(P) /* The ExprSetVVAProperty() macro is used for Verification, Validation, ** and Accreditation only. It works like ExprSetProperty() during VVA ** processes but is a no-op for delivery. */ #ifdef SQLITE_DEBUG # define ExprSetVVAProperty(E,P) (E)->flags|=(P) #else # define ExprSetVVAProperty(E,P) #endif /* ** Macros to determine the number of bytes required by a normal Expr ** struct, an Expr struct with the EP_Reduced flag set in Expr.flags ** and an Expr struct with the EP_TokenOnly flag set. */ #define EXPR_FULLSIZE sizeof(Expr) /* Full size */ |
︙ | ︙ |
Changes to src/walker.c.
︙ | ︙ | |||
39 40 41 42 43 44 45 | int sqlite3WalkExpr(Walker *pWalker, Expr *pExpr){ int rc; if( pExpr==0 ) return WRC_Continue; testcase( ExprHasProperty(pExpr, EP_TokenOnly) ); testcase( ExprHasProperty(pExpr, EP_Reduced) ); rc = pWalker->xExprCallback(pWalker, pExpr); if( rc==WRC_Continue | | | 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | int sqlite3WalkExpr(Walker *pWalker, Expr *pExpr){ int rc; if( pExpr==0 ) return WRC_Continue; testcase( ExprHasProperty(pExpr, EP_TokenOnly) ); testcase( ExprHasProperty(pExpr, EP_Reduced) ); rc = pWalker->xExprCallback(pWalker, pExpr); if( rc==WRC_Continue && !ExprHasProperty(pExpr,EP_TokenOnly) ){ if( sqlite3WalkExpr(pWalker, pExpr->pLeft) ) return WRC_Abort; if( sqlite3WalkExpr(pWalker, pExpr->pRight) ) return WRC_Abort; if( ExprHasProperty(pExpr, EP_xIsSelect) ){ if( sqlite3WalkSelect(pWalker, pExpr->x.pSelect) ) return WRC_Abort; }else{ if( sqlite3WalkExprList(pWalker, pExpr->x.pList) ) return WRC_Abort; } |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
48 49 50 51 52 53 54 | typedef struct WhereOrCost WhereOrCost; typedef struct WhereOrSet WhereOrSet; /* ** Cost X is tracked as 10*log2(X) stored in a 16-bit integer. The ** maximum cost for ordinary tables is 64*(2**63) which becomes 6900. ** (Virtual tables can return a larger cost, but let's assume they do not.) | | | | > | | 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 | typedef struct WhereOrCost WhereOrCost; typedef struct WhereOrSet WhereOrSet; /* ** Cost X is tracked as 10*log2(X) stored in a 16-bit integer. The ** maximum cost for ordinary tables is 64*(2**63) which becomes 6900. ** (Virtual tables can return a larger cost, but let's assume they do not.) ** So all costs can be stored in a 16-bit integer without risk ** of overflow. ** ** Costs are estimates, so no effort is made to compute 10*log2(X) exactly. ** Instead, a close estimate is used. Any value of X=1 is stored as 0. ** X=2 is 10. X=3 is 16. X=1000 is 99. etc. Negative values are allowed. ** A WhereCost of -10 means 0.5. WhereCost of -20 means 0.25. And so forth. ** ** The tool/wherecosttest.c source file implements a command-line program ** that will convert WhereCosts to integers, convert integers to WhereCosts ** and do addition and multiplication on WhereCost values. The wherecosttest ** command-line program is a useful utility to have around when working with ** this module. */ typedef short int WhereCost; /* ** This object contains information needed to implement a single nested ** loop in WHERE clause. ** ** Contrast this object with WhereLoop. This object describes the ** implementation of the loop. WhereLoop describes the algorithm. |
︙ | ︙ | |||
265 266 267 268 269 270 271 272 273 274 275 276 277 278 | int iParent; /* Disable pWC->a[iParent] when this term disabled */ int leftCursor; /* Cursor number of X in "X <op> <expr>" */ union { int leftColumn; /* Column number of X in "X <op> <expr>" */ WhereOrInfo *pOrInfo; /* Extra information if (eOperator & WO_OR)!=0 */ WhereAndInfo *pAndInfo; /* Extra information if (eOperator& WO_AND)!=0 */ } u; u16 eOperator; /* A WO_xx value describing <op> */ u8 wtFlags; /* TERM_xxx bit flags. See below */ u8 nChild; /* Number of children that must disable us */ WhereClause *pWC; /* The clause this term is part of */ Bitmask prereqRight; /* Bitmask of tables used by pExpr->pRight */ Bitmask prereqAll; /* Bitmask of tables referenced by pExpr */ }; | > | 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 | int iParent; /* Disable pWC->a[iParent] when this term disabled */ int leftCursor; /* Cursor number of X in "X <op> <expr>" */ union { int leftColumn; /* Column number of X in "X <op> <expr>" */ WhereOrInfo *pOrInfo; /* Extra information if (eOperator & WO_OR)!=0 */ WhereAndInfo *pAndInfo; /* Extra information if (eOperator& WO_AND)!=0 */ } u; WhereCost truthProb; /* Probability of truth for this expression */ u16 eOperator; /* A WO_xx value describing <op> */ u8 wtFlags; /* TERM_xxx bit flags. See below */ u8 nChild; /* Number of children that must disable us */ WhereClause *pWC; /* The clause this term is part of */ Bitmask prereqRight; /* Bitmask of tables used by pExpr->pRight */ Bitmask prereqAll; /* Bitmask of tables referenced by pExpr */ }; |
︙ | ︙ | |||
639 640 641 642 643 644 645 646 647 648 649 650 651 652 | } } if( pWC->a!=pWC->aStatic ){ sqlite3DbFree(db, pWC->a); } } /* ** Add a single new WhereTerm entry to the WhereClause object pWC. ** The new WhereTerm object is constructed from Expr p and with wtFlags. ** The index in pWC->a[] of the new WhereTerm is returned on success. ** 0 is returned if the new WhereTerm could not be added due to a memory ** allocation error. The memory allocation failure will be recorded in ** the db->mallocFailed flag so that higher-level functions can detect it. | > > > | 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 | } } if( pWC->a!=pWC->aStatic ){ sqlite3DbFree(db, pWC->a); } } /* Forward declaration */ static WhereCost whereCost(tRowcnt x); /* ** Add a single new WhereTerm entry to the WhereClause object pWC. ** The new WhereTerm object is constructed from Expr p and with wtFlags. ** The index in pWC->a[] of the new WhereTerm is returned on success. ** 0 is returned if the new WhereTerm could not be added due to a memory ** allocation error. The memory allocation failure will be recorded in ** the db->mallocFailed flag so that higher-level functions can detect it. |
︙ | ︙ | |||
680 681 682 683 684 685 686 687 688 689 690 691 692 693 | memcpy(pWC->a, pOld, sizeof(pWC->a[0])*pWC->nTerm); if( pOld!=pWC->aStatic ){ sqlite3DbFree(db, pOld); } pWC->nSlot = sqlite3DbMallocSize(db, pWC->a)/sizeof(pWC->a[0]); } pTerm = &pWC->a[idx = pWC->nTerm++]; pTerm->pExpr = sqlite3ExprSkipCollate(p); pTerm->wtFlags = wtFlags; pTerm->pWC = pWC; pTerm->iParent = -1; return idx; } | > > > > > | 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 | memcpy(pWC->a, pOld, sizeof(pWC->a[0])*pWC->nTerm); if( pOld!=pWC->aStatic ){ sqlite3DbFree(db, pOld); } pWC->nSlot = sqlite3DbMallocSize(db, pWC->a)/sizeof(pWC->a[0]); } pTerm = &pWC->a[idx = pWC->nTerm++]; if( p && ExprHasProperty(p, EP_Unlikely) ){ pTerm->truthProb = whereCost(p->iTable) - 99; }else{ pTerm->truthProb = -1; } pTerm->pExpr = sqlite3ExprSkipCollate(p); pTerm->wtFlags = wtFlags; pTerm->pWC = pWC; pTerm->iParent = -1; return idx; } |
︙ | ︙ | |||
2541 2542 2543 2544 2545 2546 2547 2548 2549 2550 2551 2552 2553 2554 | WhereLoopBuilder *pBuilder, WhereTerm *pLower, /* Lower bound on the range. ex: "x>123" Might be NULL */ WhereTerm *pUpper, /* Upper bound on the range. ex: "x<455" Might be NULL */ WhereCost *pnOut /* IN/OUT: Number of rows visited */ ){ int rc = SQLITE_OK; int nOut = (int)*pnOut; #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 Index *p = pBuilder->pNew->u.btree.pIndex; int nEq = pBuilder->pNew->u.btree.nEq; if( p->nSample>0 && nEq==pBuilder->nRecValid | > | 2551 2552 2553 2554 2555 2556 2557 2558 2559 2560 2561 2562 2563 2564 2565 | WhereLoopBuilder *pBuilder, WhereTerm *pLower, /* Lower bound on the range. ex: "x>123" Might be NULL */ WhereTerm *pUpper, /* Upper bound on the range. ex: "x<455" Might be NULL */ WhereCost *pnOut /* IN/OUT: Number of rows visited */ ){ int rc = SQLITE_OK; int nOut = (int)*pnOut; WhereCost nNew; #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 Index *p = pBuilder->pNew->u.btree.pIndex; int nEq = pBuilder->pNew->u.btree.nEq; if( p->nSample>0 && nEq==pBuilder->nRecValid |
︙ | ︙ | |||
2603 2604 2605 2606 2607 2608 2609 2610 2611 2612 2613 2614 2615 2616 2617 2618 2619 2620 2621 2622 2623 2624 2625 2626 2627 2628 | assert( (pLower->eOperator & (WO_GT|WO_GE))!=0 ); rc = sqlite3Stat4ProbeSetValue(pParse, p, &pRec, pExpr, aff, nEq, &bOk); if( rc==SQLITE_OK && bOk ){ tRowcnt iNew; whereKeyStats(pParse, p, pRec, 0, a); iNew = a[0] + ((pLower->eOperator & WO_GT) ? a[1] : 0); if( iNew>iLower ) iLower = iNew; } } /* If possible, improve on the iUpper estimate using ($P:$U). */ if( pUpper ){ int bOk; /* True if value is extracted from pExpr */ Expr *pExpr = pUpper->pExpr->pRight; assert( (pUpper->eOperator & (WO_LT|WO_LE))!=0 ); rc = sqlite3Stat4ProbeSetValue(pParse, p, &pRec, pExpr, aff, nEq, &bOk); if( rc==SQLITE_OK && bOk ){ tRowcnt iNew; whereKeyStats(pParse, p, pRec, 1, a); iNew = a[0] + ((pUpper->eOperator & WO_LE) ? a[1] : 0); if( iNew<iUpper ) iUpper = iNew; } } pBuilder->pRec = pRec; if( rc==SQLITE_OK ){ | > > < | 2614 2615 2616 2617 2618 2619 2620 2621 2622 2623 2624 2625 2626 2627 2628 2629 2630 2631 2632 2633 2634 2635 2636 2637 2638 2639 2640 2641 2642 2643 2644 2645 2646 2647 2648 | assert( (pLower->eOperator & (WO_GT|WO_GE))!=0 ); rc = sqlite3Stat4ProbeSetValue(pParse, p, &pRec, pExpr, aff, nEq, &bOk); if( rc==SQLITE_OK && bOk ){ tRowcnt iNew; whereKeyStats(pParse, p, pRec, 0, a); iNew = a[0] + ((pLower->eOperator & WO_GT) ? a[1] : 0); if( iNew>iLower ) iLower = iNew; nOut--; } } /* If possible, improve on the iUpper estimate using ($P:$U). */ if( pUpper ){ int bOk; /* True if value is extracted from pExpr */ Expr *pExpr = pUpper->pExpr->pRight; assert( (pUpper->eOperator & (WO_LT|WO_LE))!=0 ); rc = sqlite3Stat4ProbeSetValue(pParse, p, &pRec, pExpr, aff, nEq, &bOk); if( rc==SQLITE_OK && bOk ){ tRowcnt iNew; whereKeyStats(pParse, p, pRec, 1, a); iNew = a[0] + ((pUpper->eOperator & WO_LE) ? a[1] : 0); if( iNew<iUpper ) iUpper = iNew; nOut--; } } pBuilder->pRec = pRec; if( rc==SQLITE_OK ){ if( iUpper>iLower ){ nNew = whereCost(iUpper - iLower); }else{ nNew = 10; assert( 10==whereCost(2) ); } if( nNew<nOut ){ nOut = nNew; |
︙ | ︙ | |||
2644 2645 2646 2647 2648 2649 2650 2651 | #else UNUSED_PARAMETER(pParse); UNUSED_PARAMETER(pBuilder); #endif assert( pLower || pUpper ); /* TUNING: Each inequality constraint reduces the search space 4-fold. ** A BETWEEN operator, therefore, reduces the search space 16-fold */ if( pLower && (pLower->wtFlags & TERM_VNULL)==0 ){ | > | > | > > | | 2656 2657 2658 2659 2660 2661 2662 2663 2664 2665 2666 2667 2668 2669 2670 2671 2672 2673 2674 2675 2676 2677 2678 2679 2680 | #else UNUSED_PARAMETER(pParse); UNUSED_PARAMETER(pBuilder); #endif assert( pLower || pUpper ); /* TUNING: Each inequality constraint reduces the search space 4-fold. ** A BETWEEN operator, therefore, reduces the search space 16-fold */ nNew = nOut; if( pLower && (pLower->wtFlags & TERM_VNULL)==0 ){ nNew -= 20; assert( 20==whereCost(4) ); nOut--; } if( pUpper ){ nNew -= 20; assert( 20==whereCost(4) ); nOut--; } if( nNew<10 ) nNew = 10; if( nNew<nOut ) nOut = nNew; *pnOut = (WhereCost)nOut; return rc; } #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 /* ** Estimate the number of rows that will be returned based on |
︙ | ︙ | |||
4185 4186 4187 4188 4189 4190 4191 | ){ /* This branch taken when p is equal or better than pTemplate in ** all of (1) dependencies (2) setup-cost, (3) run-cost, and ** (4) number of output rows. */ assert( p->rSetup==pTemplate->rSetup ); if( p->prereq==pTemplate->prereq && p->nLTerm<pTemplate->nLTerm | < | | > < > | 4201 4202 4203 4204 4205 4206 4207 4208 4209 4210 4211 4212 4213 4214 4215 4216 4217 4218 4219 4220 4221 4222 4223 4224 4225 4226 4227 4228 4229 4230 4231 4232 4233 4234 4235 4236 4237 | ){ /* This branch taken when p is equal or better than pTemplate in ** all of (1) dependencies (2) setup-cost, (3) run-cost, and ** (4) number of output rows. */ assert( p->rSetup==pTemplate->rSetup ); if( p->prereq==pTemplate->prereq && p->nLTerm<pTemplate->nLTerm && (p->wsFlags & pTemplate->wsFlags & WHERE_INDEXED)!=0 && (p->u.btree.pIndex==pTemplate->u.btree.pIndex || pTemplate->rRun+p->nLTerm<=p->rRun+pTemplate->nLTerm) ){ /* Overwrite an existing WhereLoop with an similar one that uses ** more terms of the index */ pNext = p->pNextLoop; break; }else{ /* pTemplate is not helpful. ** Return without changing or adding anything */ goto whereLoopInsert_noop; } } if( (p->prereq & pTemplate->prereq)==pTemplate->prereq && p->rRun>=pTemplate->rRun && p->nOut>=pTemplate->nOut ){ /* Overwrite an existing WhereLoop with a better one: one that is ** better at one of (1) dependencies, (2) setup-cost, (3) run-cost ** or (4) number of output rows, and is no worse in any of those ** categories. */ assert( p->rSetup>=pTemplate->rSetup ); /* SETUP-INVARIANT above */ pNext = p->pNextLoop; break; } } /* If we reach this point it means that either p[] should be overwritten ** with pTemplate[] if p[] exists, or if p==NULL then allocate a new |
︙ | ︙ | |||
4253 4254 4255 4256 4257 4258 4259 4260 4261 4262 4263 4264 4265 4266 | if( sqlite3WhereTrace & 0x8 ){ sqlite3DebugPrintf("ins-noop: "); whereLoopPrint(pTemplate, pWInfo->pTabList); } #endif return SQLITE_OK; } /* ** We have so far matched pBuilder->pNew->u.btree.nEq terms of the index pIndex. ** Try to match one more. ** ** If pProbe->tnum==0, that means pIndex is a fake index used for the ** INTEGER PRIMARY KEY. | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 4269 4270 4271 4272 4273 4274 4275 4276 4277 4278 4279 4280 4281 4282 4283 4284 4285 4286 4287 4288 4289 4290 4291 4292 4293 4294 4295 4296 4297 4298 4299 4300 4301 4302 4303 4304 4305 4306 4307 4308 4309 4310 4311 4312 | if( sqlite3WhereTrace & 0x8 ){ sqlite3DebugPrintf("ins-noop: "); whereLoopPrint(pTemplate, pWInfo->pTabList); } #endif return SQLITE_OK; } /* ** Adjust the WhereLoop.nOut value downward to account for terms of the ** WHERE clause that reference the loop but which are not used by an ** index. ** ** In the current implementation, the first extra WHERE clause term reduces ** the number of output rows by a factor of 10 and each additional term ** reduces the number of output rows by sqrt(2). */ static void whereLoopOutputAdjust(WhereClause *pWC, WhereLoop *pLoop, int iCur){ WhereTerm *pTerm, *pX; Bitmask notAllowed = ~(pLoop->prereq|pLoop->maskSelf); int i, j; if( !OptimizationEnabled(pWC->pWInfo->pParse->db, SQLITE_AdjustOutEst) ){ return; } for(i=pWC->nTerm, pTerm=pWC->a; i>0; i--, pTerm++){ if( (pTerm->wtFlags & TERM_VIRTUAL)!=0 ) break; if( (pTerm->prereqAll & pLoop->maskSelf)==0 ) continue; if( (pTerm->prereqAll & notAllowed)!=0 ) continue; for(j=pLoop->nLTerm-1; j>=0; j--){ pX = pLoop->aLTerm[j]; if( pX==pTerm ) break; if( pX->iParent>=0 && (&pWC->a[pX->iParent])==pTerm ) break; } if( j<0 ) pLoop->nOut += pTerm->truthProb; } } /* ** We have so far matched pBuilder->pNew->u.btree.nEq terms of the index pIndex. ** Try to match one more. ** ** If pProbe->tnum==0, that means pIndex is a fake index used for the ** INTEGER PRIMARY KEY. |
︙ | ︙ | |||
4420 4421 4422 4423 4424 4425 4426 | if( (pNew->wsFlags & (WHERE_IDX_ONLY|WHERE_IPK))==0 ){ /* Each row involves a step of the index, then a binary search of ** the main table */ pNew->rRun = whereCostAdd(pNew->rRun, rLogSize>27 ? rLogSize-17 : 10); } /* Step cost for each output row */ pNew->rRun = whereCostAdd(pNew->rRun, pNew->nOut); | | | 4466 4467 4468 4469 4470 4471 4472 4473 4474 4475 4476 4477 4478 4479 4480 | if( (pNew->wsFlags & (WHERE_IDX_ONLY|WHERE_IPK))==0 ){ /* Each row involves a step of the index, then a binary search of ** the main table */ pNew->rRun = whereCostAdd(pNew->rRun, rLogSize>27 ? rLogSize-17 : 10); } /* Step cost for each output row */ pNew->rRun = whereCostAdd(pNew->rRun, pNew->nOut); whereLoopOutputAdjust(pBuilder->pWC, pNew, pSrc->iCursor); rc = whereLoopInsert(pBuilder, pNew); if( (pNew->wsFlags & WHERE_TOP_LIMIT)==0 && pNew->u.btree.nEq<(pProbe->nColumn + (pProbe->zName!=0)) ){ whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, nInMul+nIn); } pNew->nOut = saved_nOut; |
︙ | ︙ | |||
4624 4625 4626 4627 4628 4629 4630 4631 4632 4633 4634 4635 4636 4637 4638 | pNew->iSortIdx = b ? iSortIdx : 0; /* TUNING: Cost of full table scan is 3*(N + log2(N)). ** + The extra 3 factor is to encourage the use of indexed lookups ** over full scans. A smaller constant 2 is used for covering ** index scans so that a covering index scan will be favored over ** a table scan. */ pNew->rRun = whereCostAdd(rSize,rLogSize) + 16; rc = whereLoopInsert(pBuilder, pNew); if( rc ) break; }else{ Bitmask m = pSrc->colUsed & ~columnsInIndex(pProbe); pNew->wsFlags = (m==0) ? (WHERE_IDX_ONLY|WHERE_INDEXED) : WHERE_INDEXED; /* Full scan via index */ if( b | > > | 4670 4671 4672 4673 4674 4675 4676 4677 4678 4679 4680 4681 4682 4683 4684 4685 4686 | pNew->iSortIdx = b ? iSortIdx : 0; /* TUNING: Cost of full table scan is 3*(N + log2(N)). ** + The extra 3 factor is to encourage the use of indexed lookups ** over full scans. A smaller constant 2 is used for covering ** index scans so that a covering index scan will be favored over ** a table scan. */ pNew->rRun = whereCostAdd(rSize,rLogSize) + 16; whereLoopOutputAdjust(pWC, pNew, pSrc->iCursor); rc = whereLoopInsert(pBuilder, pNew); pNew->nOut = rSize; if( rc ) break; }else{ Bitmask m = pSrc->colUsed & ~columnsInIndex(pProbe); pNew->wsFlags = (m==0) ? (WHERE_IDX_ONLY|WHERE_INDEXED) : WHERE_INDEXED; /* Full scan via index */ if( b |
︙ | ︙ | |||
4656 4657 4658 4659 4660 4661 4662 4663 4664 4665 4666 4667 4668 4669 4670 | pNew->rRun = 10 + whereCostAdd(rSize,rLogSize) - b; }else{ assert( b!=0 ); /* TUNING: Cost of scanning a non-covering index is (N+1)*log2(N) ** which we will simplify to just N*log2(N) */ pNew->rRun = rSize + rLogSize; } rc = whereLoopInsert(pBuilder, pNew); if( rc ) break; } } rc = whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, 0); #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 sqlite3Stat4ProbeFree(pBuilder->pRec); | > > | 4704 4705 4706 4707 4708 4709 4710 4711 4712 4713 4714 4715 4716 4717 4718 4719 4720 | pNew->rRun = 10 + whereCostAdd(rSize,rLogSize) - b; }else{ assert( b!=0 ); /* TUNING: Cost of scanning a non-covering index is (N+1)*log2(N) ** which we will simplify to just N*log2(N) */ pNew->rRun = rSize + rLogSize; } whereLoopOutputAdjust(pWC, pNew, pSrc->iCursor); rc = whereLoopInsert(pBuilder, pNew); pNew->nOut = rSize; if( rc ) break; } } rc = whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, 0); #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 sqlite3Stat4ProbeFree(pBuilder->pRec); |
︙ | ︙ | |||
5263 5264 5265 5266 5267 5268 5269 | static int wherePathSolver(WhereInfo *pWInfo, WhereCost nRowEst){ int mxChoice; /* Maximum number of simultaneous paths tracked */ int nLoop; /* Number of terms in the join */ Parse *pParse; /* Parsing context */ sqlite3 *db; /* The database connection */ int iLoop; /* Loop counter over the terms of the join */ int ii, jj; /* Loop counters */ | > | > | > | | 5313 5314 5315 5316 5317 5318 5319 5320 5321 5322 5323 5324 5325 5326 5327 5328 5329 5330 5331 5332 | static int wherePathSolver(WhereInfo *pWInfo, WhereCost nRowEst){ int mxChoice; /* Maximum number of simultaneous paths tracked */ int nLoop; /* Number of terms in the join */ Parse *pParse; /* Parsing context */ sqlite3 *db; /* The database connection */ int iLoop; /* Loop counter over the terms of the join */ int ii, jj; /* Loop counters */ int mxI = 0; /* Index of next entry to replace */ WhereCost rCost; /* Cost of a path */ WhereCost nOut; /* Number of outputs */ WhereCost mxCost = 0; /* Maximum cost of a set of paths */ WhereCost mxOut = 0; /* Maximum nOut value on the set of paths */ WhereCost rSortCost; /* Cost to do a sort */ int nTo, nFrom; /* Number of valid entries in aTo[] and aFrom[] */ WherePath *aFrom; /* All nFrom paths at the previous level */ WherePath *aTo; /* The nTo best paths at the current level */ WherePath *pFrom; /* An element of aFrom[] that we are working on */ WherePath *pTo; /* An element of aTo[] that we are working on */ WhereLoop *pWLoop; /* One of the WhereLoop objects */ WhereLoop **pX; /* Used to divy up the pSpace memory */ |
︙ | ︙ | |||
5334 5335 5336 5337 5338 5339 5340 5341 5342 5343 5344 5345 5346 5347 | u8 isOrdered = pFrom->isOrdered; if( (pWLoop->prereq & ~pFrom->maskLoop)!=0 ) continue; if( (pWLoop->maskSelf & pFrom->maskLoop)!=0 ) continue; /* At this point, pWLoop is a candidate to be the next loop. ** Compute its cost */ rCost = whereCostAdd(pWLoop->rSetup,pWLoop->rRun + pFrom->nRow); rCost = whereCostAdd(rCost, pFrom->rCost); maskNew = pFrom->maskLoop | pWLoop->maskSelf; if( !isOrderedValid ){ switch( wherePathSatisfiesOrderBy(pWInfo, pWInfo->pOrderBy, pFrom, pWInfo->wctrlFlags, iLoop, pWLoop, &revMask) ){ case 1: /* Yes. pFrom+pWLoop does satisfy the ORDER BY clause */ isOrdered = 1; | > | 5387 5388 5389 5390 5391 5392 5393 5394 5395 5396 5397 5398 5399 5400 5401 | u8 isOrdered = pFrom->isOrdered; if( (pWLoop->prereq & ~pFrom->maskLoop)!=0 ) continue; if( (pWLoop->maskSelf & pFrom->maskLoop)!=0 ) continue; /* At this point, pWLoop is a candidate to be the next loop. ** Compute its cost */ rCost = whereCostAdd(pWLoop->rSetup,pWLoop->rRun + pFrom->nRow); rCost = whereCostAdd(rCost, pFrom->rCost); nOut = pFrom->nRow + pWLoop->nOut; maskNew = pFrom->maskLoop | pWLoop->maskSelf; if( !isOrderedValid ){ switch( wherePathSatisfiesOrderBy(pWInfo, pWInfo->pOrderBy, pFrom, pWInfo->wctrlFlags, iLoop, pWLoop, &revMask) ){ case 1: /* Yes. pFrom+pWLoop does satisfy the ORDER BY clause */ isOrdered = 1; |
︙ | ︙ | |||
5356 5357 5358 5359 5360 5361 5362 | break; } }else{ revMask = pFrom->revLoop; } /* Check to see if pWLoop should be added to the mxChoice best so far */ for(jj=0, pTo=aTo; jj<nTo; jj++, pTo++){ | | > > > > | | | | | | | | | | | | | | | > > > | > > > | 5410 5411 5412 5413 5414 5415 5416 5417 5418 5419 5420 5421 5422 5423 5424 5425 5426 5427 5428 5429 5430 5431 5432 5433 5434 5435 5436 5437 5438 5439 5440 5441 5442 5443 5444 5445 5446 5447 5448 5449 5450 5451 5452 5453 5454 5455 5456 5457 5458 5459 5460 5461 5462 5463 5464 5465 5466 5467 5468 5469 5470 5471 5472 5473 5474 5475 5476 5477 5478 5479 5480 5481 5482 5483 5484 5485 5486 5487 5488 5489 5490 5491 5492 5493 5494 5495 5496 5497 5498 5499 5500 5501 5502 5503 5504 5505 5506 5507 5508 | break; } }else{ revMask = pFrom->revLoop; } /* Check to see if pWLoop should be added to the mxChoice best so far */ for(jj=0, pTo=aTo; jj<nTo; jj++, pTo++){ if( pTo->maskLoop==maskNew && pTo->isOrderedValid==isOrderedValid && ((pTo->rCost<=rCost && pTo->nRow<=nOut) || (pTo->rCost>=rCost && pTo->nRow>=nOut)) ){ testcase( jj==nTo-1 ); break; } } if( jj>=nTo ){ if( nTo>=mxChoice && rCost>=mxCost ){ #ifdef WHERETRACE_ENABLED if( sqlite3WhereTrace&0x4 ){ sqlite3DebugPrintf("Skip %s cost=%-3d,%3d order=%c\n", wherePathName(pFrom, iLoop, pWLoop), rCost, nOut, isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?'); } #endif continue; } /* Add a new Path to the aTo[] set */ if( nTo<mxChoice ){ /* Increase the size of the aTo set by one */ jj = nTo++; }else{ /* New path replaces the prior worst to keep count below mxChoice */ jj = mxI; } pTo = &aTo[jj]; #ifdef WHERETRACE_ENABLED if( sqlite3WhereTrace&0x4 ){ sqlite3DebugPrintf("New %s cost=%-3d,%3d order=%c\n", wherePathName(pFrom, iLoop, pWLoop), rCost, nOut, isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?'); } #endif }else{ if( pTo->rCost<=rCost && pTo->nRow<=nOut ){ #ifdef WHERETRACE_ENABLED if( sqlite3WhereTrace&0x4 ){ sqlite3DebugPrintf( "Skip %s cost=%-3d,%3d order=%c", wherePathName(pFrom, iLoop, pWLoop), rCost, nOut, isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?'); sqlite3DebugPrintf(" vs %s cost=%-3d,%d order=%c\n", wherePathName(pTo, iLoop+1, 0), pTo->rCost, pTo->nRow, pTo->isOrderedValid ? (pTo->isOrdered ? 'Y' : 'N') : '?'); } #endif testcase( pTo->rCost==rCost ); continue; } testcase( pTo->rCost==rCost+1 ); /* A new and better score for a previously created equivalent path */ #ifdef WHERETRACE_ENABLED if( sqlite3WhereTrace&0x4 ){ sqlite3DebugPrintf( "Update %s cost=%-3d,%3d order=%c", wherePathName(pFrom, iLoop, pWLoop), rCost, nOut, isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?'); sqlite3DebugPrintf(" was %s cost=%-3d,%3d order=%c\n", wherePathName(pTo, iLoop+1, 0), pTo->rCost, pTo->nRow, pTo->isOrderedValid ? (pTo->isOrdered ? 'Y' : 'N') : '?'); } #endif } /* pWLoop is a winner. Add it to the set of best so far */ pTo->maskLoop = pFrom->maskLoop | pWLoop->maskSelf; pTo->revLoop = revMask; pTo->nRow = nOut; pTo->rCost = rCost; pTo->isOrderedValid = isOrderedValid; pTo->isOrdered = isOrdered; memcpy(pTo->aLoop, pFrom->aLoop, sizeof(WhereLoop*)*iLoop); pTo->aLoop[iLoop] = pWLoop; if( nTo>=mxChoice ){ mxI = 0; mxCost = aTo[0].rCost; mxOut = aTo[0].nRow; for(jj=1, pTo=&aTo[1]; jj<mxChoice; jj++, pTo++){ if( pTo->rCost>mxCost || (pTo->rCost==mxCost && pTo->nRow>mxOut) ){ mxCost = pTo->rCost; mxOut = pTo->nRow; mxI = jj; } } } } } #ifdef WHERETRACE_ENABLED if( sqlite3WhereTrace>=2 ){ |
︙ | ︙ | |||
5467 5468 5469 5470 5471 5472 5473 | sqlite3ErrorMsg(pParse, "no query solution"); sqlite3DbFree(db, pSpace); return SQLITE_ERROR; } /* Find the lowest cost path. pFrom will be left pointing to that path */ pFrom = aFrom; | < < < | 5531 5532 5533 5534 5535 5536 5537 5538 5539 5540 5541 5542 5543 5544 5545 5546 5547 | sqlite3ErrorMsg(pParse, "no query solution"); sqlite3DbFree(db, pSpace); return SQLITE_ERROR; } /* Find the lowest cost path. pFrom will be left pointing to that path */ pFrom = aFrom; for(ii=1; ii<nFrom; ii++){ if( pFrom->rCost>aFrom[ii].rCost ) pFrom = &aFrom[ii]; } assert( pWInfo->nLevel==nLoop ); /* Load the lowest cost path into pWInfo */ for(iLoop=0; iLoop<nLoop; iLoop++){ WhereLevel *pLevel = pWInfo->a + iLoop; pLevel->pWLoop = pWLoop = pFrom->aLoop[iLoop]; pLevel->iFrom = pWLoop->iTab; pLevel->iTabCur = pWInfo->pTabList->a[pLevel->iFrom].iCursor; |
︙ | ︙ |
Changes to test/analyze9.test.
︙ | ︙ | |||
841 842 843 844 845 846 847 | SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=10 AND c=10; } {/USING INDEX i1/} do_eqp_test 17.3 { SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=0 AND c=10; } {/USING INDEX i1/} do_execsql_test 17.4 { | | | 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 | SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=10 AND c=10; } {/USING INDEX i1/} do_eqp_test 17.3 { SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=0 AND c=10; } {/USING INDEX i1/} do_execsql_test 17.4 { CREATE INDEX i2 ON t1(c, d); ANALYZE main.i2; } do_eqp_test 17.5 { SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=10 AND c=10; } {/USING INDEX i1/} do_eqp_test 17.6 { SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=0 AND c=10; |
︙ | ︙ |
Added test/whereG.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 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 | # 2013-09-05 # # 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. # #*********************************************************************** # # Test cases for query planning decisions and the unlikely() and # likelihood() functions. set testdir [file dirname $argv0] source $testdir/tester.tcl do_execsql_test whereG-1.0 { CREATE TABLE composer( cid INTEGER PRIMARY KEY, cname TEXT ); CREATE TABLE album( aid INTEGER PRIMARY KEY, aname TEXT ); CREATE TABLE track( tid INTEGER PRIMARY KEY, cid INTEGER REFERENCES composer, aid INTEGER REFERENCES album, title TEXT ); CREATE INDEX track_i1 ON track(cid); CREATE INDEX track_i2 ON track(aid); INSERT INTO composer VALUES(1, 'W. A. Mozart'); INSERT INTO composer VALUES(2, 'Beethoven'); INSERT INTO composer VALUES(3, 'Thomas Tallis'); INSERT INTO composer VALUES(4, 'Joseph Hayden'); INSERT INTO composer VALUES(5, 'Thomas Weelkes'); INSERT INTO composer VALUES(6, 'J. S. Bach'); INSERT INTO composer VALUES(7, 'Orlando Gibbons'); INSERT INTO composer VALUES(8, 'Josquin des Prés'); INSERT INTO composer VALUES(9, 'Byrd'); INSERT INTO composer VALUES(10, 'Francis Poulenc'); INSERT INTO composer VALUES(11, 'Mendelsshon'); INSERT INTO composer VALUES(12, 'Zoltán Kodály'); INSERT INTO composer VALUES(13, 'Handel'); INSERT INTO album VALUES(100, 'Kodály: Missa Brevis'); INSERT INTO album VALUES(101, 'Messiah'); INSERT INTO album VALUES(102, 'Missa Brevis in D-, K.65'); INSERT INTO album VALUES(103, 'The complete English anthems'); INSERT INTO album VALUES(104, 'Mass in B Minor, BWV 232'); INSERT INTO track VALUES(10005, 12, 100, 'Sanctus'); INSERT INTO track VALUES(10007, 12, 100, 'Agnus Dei'); INSERT INTO track VALUES(10115, 13, 101, 'Surely He Hath Borne Our Griefs'); INSERT INTO track VALUES(10129, 13, 101, 'Since By Man Came Death'); INSERT INTO track VALUES(10206, 1, 102, 'Agnus Dei'); INSERT INTO track VALUES(10301, 3, 103, 'If Ye Love Me'); INSERT INTO track VALUES(10402, 6, 104, 'Domine Deus'); INSERT INTO track VALUES(10403, 6, 104, 'Qui tollis'); } {} do_eqp_test whereG-1.1 { SELECT DISTINCT aname FROM album, composer, track WHERE unlikely(cname LIKE '%bach%') AND composer.cid=track.cid AND album.aid=track.aid; } {/.*composer.*track.*album.*/} do_execsql_test whereG-1.2 { SELECT DISTINCT aname FROM album, composer, track WHERE unlikely(cname LIKE '%bach%') AND composer.cid=track.cid AND album.aid=track.aid; } {{Mass in B Minor, BWV 232}} do_eqp_test whereG-1.3 { SELECT DISTINCT aname FROM album, composer, track WHERE likelihood(cname LIKE '%bach%', 0.5) AND composer.cid=track.cid AND album.aid=track.aid; } {/.*track.*composer.*album.*/} do_execsql_test whereG-1.4 { SELECT DISTINCT aname FROM album, composer, track WHERE likelihood(cname LIKE '%bach%', 0.5) AND composer.cid=track.cid AND album.aid=track.aid; } {{Mass in B Minor, BWV 232}} do_eqp_test whereG-1.5 { SELECT DISTINCT aname FROM album, composer, track WHERE cname LIKE '%bach%' AND composer.cid=track.cid AND album.aid=track.aid; } {/.*track.*composer.*album.*/} do_execsql_test whereG-1.6 { SELECT DISTINCT aname FROM album, composer, track WHERE cname LIKE '%bach%' AND composer.cid=track.cid AND album.aid=track.aid; } {{Mass in B Minor, BWV 232}} do_eqp_test whereG-1.7 { SELECT DISTINCT aname FROM album, composer, track WHERE cname LIKE '%bach%' AND unlikely(composer.cid=track.cid) AND unlikely(album.aid=track.aid); } {/.*track.*composer.*album.*/} do_execsql_test whereG-1.8 { SELECT DISTINCT aname FROM album, composer, track WHERE cname LIKE '%bach%' AND unlikely(composer.cid=track.cid) AND unlikely(album.aid=track.aid); } {{Mass in B Minor, BWV 232}} do_test whereG-2.1 { catchsql { SELECT DISTINCT aname FROM album, composer, track WHERE likelihood(cname LIKE '%bach%', -0.01) AND composer.cid=track.cid AND album.aid=track.aid; } } {1 {second argument to likelihood() must be a constant between 0.0 and 1.0}} do_test whereG-2.2 { catchsql { SELECT DISTINCT aname FROM album, composer, track WHERE likelihood(cname LIKE '%bach%', 1.01) AND composer.cid=track.cid AND album.aid=track.aid; } } {1 {second argument to likelihood() must be a constant between 0.0 and 1.0}} do_test whereG-2.3 { catchsql { SELECT DISTINCT aname FROM album, composer, track WHERE likelihood(cname LIKE '%bach%', track.cid) AND composer.cid=track.cid AND album.aid=track.aid; } } {1 {second argument to likelihood() must be a constant between 0.0 and 1.0}} # Commuting a term of the WHERE clause should not change the query plan # do_execsql_test whereG-3.0 { CREATE TABLE a(a1 PRIMARY KEY, a2); CREATE TABLE b(b1 PRIMARY KEY, b2); } {} do_eqp_test whereG-3.1 { SELECT * FROM a, b WHERE b1=a1 AND a2=5; } {/.*SCAN TABLE a.*SEARCH TABLE b USING INDEX .*b_1 .b1=..*/} do_eqp_test whereG-3.2 { SELECT * FROM a, b WHERE a1=b1 AND a2=5; } {/.*SCAN TABLE a.*SEARCH TABLE b USING INDEX .*b_1 .b1=..*/} do_eqp_test whereG-3.3 { SELECT * FROM a, b WHERE a2=5 AND b1=a1; } {/.*SCAN TABLE a.*SEARCH TABLE b USING INDEX .*b_1 .b1=..*/} do_eqp_test whereG-3.4 { SELECT * FROM a, b WHERE a2=5 AND a1=b1; } {/.*SCAN TABLE a.*SEARCH TABLE b USING INDEX .*b_1 .b1=..*/} finish_test |