Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fix another problem on this branch. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | defer-where-subqueries |
Files: | files | file ages | folders |
SHA3-256: |
a4fc98113aeb10860834f68a2fdcef69 |
User & Date: | dan 2017-07-10 15:17:30.050 |
Context
2017-07-10
| ||
15:26 | Small performance optimization in sqlite3WhereExprUsage(). (check-in: 38edc6770e user: drh tags: defer-where-subqueries) | |
15:17 | Fix another problem on this branch. (check-in: a4fc98113a user: dan tags: defer-where-subqueries) | |
14:39 | Fix a problem causing non-covered WHERE terms to be evaluated before covered WHERE terms. (check-in: 7d3cb39f60 user: dan tags: defer-where-subqueries) | |
Changes
Changes to src/sqliteInt.h.
︙ | ︙ | |||
2394 2395 2396 2397 2398 2399 2400 | #define EP_Subquery 0x200000 /* Tree contains a TK_SELECT operator */ #define EP_Alias 0x400000 /* Is an alias for a result set column */ #define EP_Leaf 0x800000 /* Expr.pLeft, .pRight, .u.pSelect all NULL */ /* ** Combinations of two or more EP_* flags */ | | | 2394 2395 2396 2397 2398 2399 2400 2401 2402 2403 2404 2405 2406 2407 2408 | #define EP_Subquery 0x200000 /* Tree contains a TK_SELECT operator */ #define EP_Alias 0x400000 /* Is an alias for a result set column */ #define EP_Leaf 0x800000 /* Expr.pLeft, .pRight, .u.pSelect all NULL */ /* ** Combinations of two or more EP_* flags */ #define EP_Propagate (EP_Collate|EP_Subquery) /* Propagate these bits up tree */ /* ** 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)) |
︙ | ︙ |
Changes to src/whereInt.h.
︙ | ︙ | |||
280 281 282 283 284 285 286 287 288 289 290 291 292 293 | #else # define TERM_VNULL 0x00 /* Disabled if not using stat3 */ #endif #define TERM_LIKEOPT 0x100 /* Virtual terms from the LIKE optimization */ #define TERM_LIKECOND 0x200 /* Conditionally this LIKE operator term */ #define TERM_LIKE 0x400 /* The original LIKE operator */ #define TERM_IS 0x800 /* Term.pExpr is an IS operator */ /* ** An instance of the WhereScan object is used as an iterator for locating ** terms in the WHERE clause that are useful to the query planner. */ struct WhereScan { WhereClause *pOrigWC; /* Original, innermost WhereClause */ | > | 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 | #else # define TERM_VNULL 0x00 /* Disabled if not using stat3 */ #endif #define TERM_LIKEOPT 0x100 /* Virtual terms from the LIKE optimization */ #define TERM_LIKECOND 0x200 /* Conditionally this LIKE operator term */ #define TERM_LIKE 0x400 /* The original LIKE operator */ #define TERM_IS 0x800 /* Term.pExpr is an IS operator */ #define TERM_VARSELECT 0x1000 /* Term.pExpr contains a correlated sub-query */ /* ** An instance of the WhereScan object is used as an iterator for locating ** terms in the WHERE clause that are useful to the query planner. */ struct WhereScan { WhereClause *pOrigWC; /* Original, innermost WhereClause */ |
︙ | ︙ | |||
369 370 371 372 373 374 375 376 377 378 379 380 381 382 | ** above, the mapping might go like this: 4->3, 5->1, 8->2, 29->0, ** 57->5, 73->4. Or one of 719 other combinations might be used. It ** does not really matter. What is important is that sparse cursor ** numbers all get mapped into bit numbers that begin with 0 and contain ** no gaps. */ struct WhereMaskSet { int n; /* Number of assigned cursor values */ int ix[BMS]; /* Cursor assigned to each bit */ }; /* ** Initialize a WhereMaskSet object */ | > | 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 | ** above, the mapping might go like this: 4->3, 5->1, 8->2, 29->0, ** 57->5, 73->4. Or one of 719 other combinations might be used. It ** does not really matter. What is important is that sparse cursor ** numbers all get mapped into bit numbers that begin with 0 and contain ** no gaps. */ struct WhereMaskSet { int bVarSelect; /* Used by sqlite3WhereExprUsage() */ int n; /* Number of assigned cursor values */ int ix[BMS]; /* Cursor assigned to each bit */ }; /* ** Initialize a WhereMaskSet object */ |
︙ | ︙ |
Changes to src/wherecode.c.
︙ | ︙ | |||
2057 2058 2059 2060 2061 2062 2063 | continue; } if( iLoop==1 && !sqlite3ExprCoveredByIndex(pE, pLevel->iTabCur, pIdx) ){ iNext = 2; continue; } | | | 2057 2058 2059 2060 2061 2062 2063 2064 2065 2066 2067 2068 2069 2070 2071 | continue; } if( iLoop==1 && !sqlite3ExprCoveredByIndex(pE, pLevel->iTabCur, pIdx) ){ iNext = 2; continue; } if( iLoop<3 && (pTerm->wtFlags & TERM_VARSELECT) ){ if( iNext==0 ) iNext = 3; continue; } if( pTerm->wtFlags & TERM_LIKECOND ){ /* If the TERM_LIKECOND flag is set, that means that the range search ** is sufficient to guarantee that the LIKE operator is true, so we |
︙ | ︙ |
Changes to src/whereexpr.c.
︙ | ︙ | |||
947 948 949 950 951 952 953 954 955 956 957 958 959 960 961 | pTerm->prereqRight = sqlite3WhereExprListUsage(pMaskSet, pExpr->x.pList); } }else if( op==TK_ISNULL ){ pTerm->prereqRight = 0; }else{ pTerm->prereqRight = sqlite3WhereExprUsage(pMaskSet, pExpr->pRight); } prereqAll = sqlite3WhereExprUsage(pMaskSet, pExpr); if( ExprHasProperty(pExpr, EP_FromJoin) ){ Bitmask x = sqlite3WhereGetMask(pMaskSet, pExpr->iRightJoinTable); prereqAll |= x; extraRight = x-1; /* ON clause terms may not be used with an index ** on left table of a LEFT JOIN. Ticket #3015 */ if( (prereqAll>>1)>=x ){ sqlite3ErrorMsg(pParse, "ON clause references tables to its right"); | > > | 947 948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 | pTerm->prereqRight = sqlite3WhereExprListUsage(pMaskSet, pExpr->x.pList); } }else if( op==TK_ISNULL ){ pTerm->prereqRight = 0; }else{ pTerm->prereqRight = sqlite3WhereExprUsage(pMaskSet, pExpr->pRight); } pMaskSet->bVarSelect = 0; prereqAll = sqlite3WhereExprUsage(pMaskSet, pExpr); if( pMaskSet->bVarSelect ) pTerm->wtFlags |= TERM_VARSELECT; if( ExprHasProperty(pExpr, EP_FromJoin) ){ Bitmask x = sqlite3WhereGetMask(pMaskSet, pExpr->iRightJoinTable); prereqAll |= x; extraRight = x-1; /* ON clause terms may not be used with an index ** on left table of a LEFT JOIN. Ticket #3015 */ if( (prereqAll>>1)>=x ){ sqlite3ErrorMsg(pParse, "ON clause references tables to its right"); |
︙ | ︙ | |||
1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 | return sqlite3WhereGetMask(pMaskSet, p->iTable); } mask = (p->op==TK_IF_NULL_ROW) ? sqlite3WhereGetMask(pMaskSet, p->iTable) : 0; assert( !ExprHasProperty(p, EP_TokenOnly) ); if( p->pRight ) mask |= sqlite3WhereExprUsage(pMaskSet, p->pRight); if( p->pLeft ) mask |= sqlite3WhereExprUsage(pMaskSet, p->pLeft); if( ExprHasProperty(p, EP_xIsSelect) ){ mask |= exprSelectUsage(pMaskSet, p->x.pSelect); }else if( p->x.pList ){ mask |= sqlite3WhereExprListUsage(pMaskSet, p->x.pList); } return mask; } Bitmask sqlite3WhereExprListUsage(WhereMaskSet *pMaskSet, ExprList *pList){ | > | 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 | return sqlite3WhereGetMask(pMaskSet, p->iTable); } mask = (p->op==TK_IF_NULL_ROW) ? sqlite3WhereGetMask(pMaskSet, p->iTable) : 0; assert( !ExprHasProperty(p, EP_TokenOnly) ); if( p->pRight ) mask |= sqlite3WhereExprUsage(pMaskSet, p->pRight); if( p->pLeft ) mask |= sqlite3WhereExprUsage(pMaskSet, p->pLeft); if( ExprHasProperty(p, EP_xIsSelect) ){ if( ExprHasProperty(p, EP_VarSelect) ) pMaskSet->bVarSelect = 1; mask |= exprSelectUsage(pMaskSet, p->x.pSelect); }else if( p->x.pList ){ mask |= sqlite3WhereExprListUsage(pMaskSet, p->x.pList); } return mask; } Bitmask sqlite3WhereExprListUsage(WhereMaskSet *pMaskSet, ExprList *pList){ |
︙ | ︙ |
Changes to test/pushdown.test.
︙ | ︙ | |||
51 52 53 54 55 56 57 58 59 | } {b2} do_test 1.5 { set L [list] execsql { SELECT * FROM t1 WHERE a=3 AND f(c) AND f(b) } set L } {b3} finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | } {b2} do_test 1.5 { set L [list] execsql { SELECT * FROM t1 WHERE a=3 AND f(c) AND f(b) } set L } {b3} #----------------------------------------------- do_execsql_test 2.0 { CREATE TABLE u1(a, b, c); CREATE TABLE u2(x, y, z); INSERT INTO u1 VALUES('a1', 'b1', 'c1'); INSERT INTO u2 VALUES('a1', 'b1', 'c1'); } do_test 2.1 { set L [list] execsql { SELECT * FROM u1 WHERE f('one')=123 AND 123=( SELECT x FROM u2 WHERE x=a AND f('two') ) } set L } {one} do_test 2.2 { set L [list] breakpoint execsql { SELECT * FROM u1 WHERE 123=( SELECT x FROM u2 WHERE x=a AND f('two') ) AND f('three')=123 } set L } {three} finish_test |