Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Do not apply the WHERE-clause pushdown optimization to terms that originate in the ON or USING clause of a LEFT JOIN. Fix for ticket [c2a19d81652f40568c]. |
|---|---|
| Downloads: | Tarball | ZIP archive |
| Timelines: | family | ancestors | descendants | both | push-down-backport |
| Files: | files | file ages | folders |
| SHA3-256: |
52674f948c3e74e5cc32874d4885f230 |
| User & Date: | drh 2017-07-17 19:14:11.253 |
Context
|
2017-07-17
| ||
| 19:25 | When checking for the WHERE-clause push-down optimization, verify that all terms of the compound inner SELECT are non-aggregate, not just the last term. Fix for ticket [f7f8c97e97597]. (Closed-Leaf check-in: adc082c146 user: drh tags: push-down-backport) | |
| 19:14 | Do not apply the WHERE-clause pushdown optimization to terms that originate in the ON or USING clause of a LEFT JOIN. Fix for ticket [c2a19d81652f40568c]. (check-in: 52674f948c user: drh tags: push-down-backport) | |
| 19:07 | For FROM-clause subqueries that cannot be flattened, try to push relevant WHERE clause terms of the outer query down into the subquery in order to help the subquery run faster and/or use less memory. Cherry-pick from [6df18e949d36]. Still need to backport bug fixes associated with that check-in. (check-in: 043d6ce8ad user: drh tags: push-down-backport) | |
|
2015-08-22
| ||
| 01:32 | Do not apply the WHERE-clause pushdown optimization to terms that originate in the ON or USING clause of a LEFT JOIN. Fix for ticket [c2a19d81652f40568c]. (check-in: 351bc22fa9 user: drh tags: trunk) | |
Changes
Changes to src/select.c.
| ︙ | ︙ | |||
3776 3777 3778 3779 3780 3781 3782 3783 3784 3785 3786 3787 3788 3789 | ** ** (3) The inner query has a LIMIT clause (since the changes to the WHERE ** close would change the meaning of the LIMIT). ** ** (4) The inner query is the right operand of a LEFT JOIN. (The caller ** enforces this restriction since this routine does not have enough ** information to know.) ** ** Return 0 if no changes are made and non-zero if one or more WHERE clause ** terms are duplicated into the subquery. */ static int pushDownWhereTerms( sqlite3 *db, /* The database connection (for malloc()) */ Select *pSubq, /* The subquery whose WHERE clause is to be augmented */ | > > > | 3776 3777 3778 3779 3780 3781 3782 3783 3784 3785 3786 3787 3788 3789 3790 3791 3792 | ** ** (3) The inner query has a LIMIT clause (since the changes to the WHERE ** close would change the meaning of the LIMIT). ** ** (4) The inner query is the right operand of a LEFT JOIN. (The caller ** enforces this restriction since this routine does not have enough ** information to know.) ** ** (5) The WHERE clause expression originates in the ON or USING clause ** of a LEFT JOIN. ** ** Return 0 if no changes are made and non-zero if one or more WHERE clause ** terms are duplicated into the subquery. */ static int pushDownWhereTerms( sqlite3 *db, /* The database connection (for malloc()) */ Select *pSubq, /* The subquery whose WHERE clause is to be augmented */ |
| ︙ | ︙ | |||
3799 3800 3801 3802 3803 3804 3805 3806 3807 3808 3809 3810 3811 3812 |
if( pSubq->pLimit!=0 ){
return 0; /* restriction (3) */
}
while( pWhere->op==TK_AND ){
nChng += pushDownWhereTerms(db, pSubq, pWhere->pRight, iCursor);
pWhere = pWhere->pLeft;
}
if( sqlite3ExprIsTableConstant(pWhere, iCursor) ){
nChng++;
while( pSubq ){
pNew = sqlite3ExprDup(db, pWhere, 0);
pNew = substExpr(db, pNew, iCursor, pSubq->pEList);
pSubq->pWhere = sqlite3ExprAnd(db, pSubq->pWhere, pNew);
pSubq = pSubq->pPrior;
| > | 3802 3803 3804 3805 3806 3807 3808 3809 3810 3811 3812 3813 3814 3815 3816 |
if( pSubq->pLimit!=0 ){
return 0; /* restriction (3) */
}
while( pWhere->op==TK_AND ){
nChng += pushDownWhereTerms(db, pSubq, pWhere->pRight, iCursor);
pWhere = pWhere->pLeft;
}
if( ExprHasProperty(pWhere,EP_FromJoin) ) return 0; /* restriction 5 */
if( sqlite3ExprIsTableConstant(pWhere, iCursor) ){
nChng++;
while( pSubq ){
pNew = sqlite3ExprDup(db, pWhere, 0);
pNew = substExpr(db, pNew, iCursor, pSubq->pEList);
pSubq->pWhere = sqlite3ExprAnd(db, pSubq->pWhere, pNew);
pSubq = pSubq->pPrior;
|
| ︙ | ︙ |
Changes to test/join5.test.
| ︙ | ︙ | |||
156 157 158 159 160 161 162 163 164 |
CREATE TABLE x2(b NOT NULL);
CREATE TABLE x3(c, d);
INSERT INTO x3 VALUES('a', NULL);
INSERT INTO x3 VALUES('b', NULL);
INSERT INTO x3 VALUES('c', NULL);
SELECT * FROM x1 LEFT JOIN x2 JOIN x3 WHERE x3.d = x2.b;
} {}
finish_test
| > > > > > > > > > > > > > > > > > > > > > > > | 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 |
CREATE TABLE x2(b NOT NULL);
CREATE TABLE x3(c, d);
INSERT INTO x3 VALUES('a', NULL);
INSERT INTO x3 VALUES('b', NULL);
INSERT INTO x3 VALUES('c', NULL);
SELECT * FROM x1 LEFT JOIN x2 JOIN x3 WHERE x3.d = x2.b;
} {}
# Ticket https://www.sqlite.org/src/tktview/c2a19d81652f40568c770c43 on
# 2015-08-20. LEFT JOIN and the push-down optimization.
#
do_execsql_test join6-4.1 {
SELECT *
FROM (
SELECT 'apple' fruit
UNION ALL SELECT 'banana'
) a
JOIN (
SELECT 'apple' fruit
UNION ALL SELECT 'banana'
) b ON a.fruit=b.fruit
LEFT JOIN (
SELECT 1 isyellow
) c ON b.fruit='banana';
} {apple apple {} banana banana 1}
do_execsql_test join6-4.2 {
SELECT *
FROM (SELECT 'apple' fruit UNION ALL SELECT 'banana')
LEFT JOIN (SELECT 1) ON fruit='banana';
} {apple {} banana 1}
finish_test
|