Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix another problem on this branch. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL 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 |
Context
|
2017-07-10
| ||
| 15:26 | Small performance optimization in sqlite3WhereExprUsage(). (check-in: 38edc677 user: drh tags: defer-where-subqueries) | |
| 15:17 | Fix another problem on this branch. (check-in: a4fc9811 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: 7d3cb39f user: dan tags: defer-where-subqueries) | |
Changes
Changes to src/sqliteInt.h.
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|EP_VarSelect) /* Prop. 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)) |
| |
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
...
369
370
371
372
373
374
375
376
377
378
379
380
381
382
|
#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 */
................................................................................
** 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
*/
|
>
>
|
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
...
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
|
#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 */ ................................................................................ ** 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 2064 2065 2066 2067 2068 2069 2070 2071 |
continue;
}
if( iLoop==1 && !sqlite3ExprCoveredByIndex(pE, pLevel->iTabCur, pIdx) ){
iNext = 2;
continue;
}
if( iLoop<3 && (pE->flags & EP_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
|
| |
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
....
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
|
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");
................................................................................
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){
|
>
>
>
|
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
....
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
|
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");
................................................................................
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
|