Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | 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]. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | push-down-backport |
| Files: | files | file ages | folders |
| SHA3-256: |
adc082c1461e0237cd42653b529fbc13 |
| User & Date: | drh 2017-07-17 19:25:10 |
Context
|
2017-07-17
| ||
| 19:37 | 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. This is a cherry-pick of [6df18e949d36] with bug fixes. check-in: cd6ac078 user: drh tags: branch-3.8.9 | |
| 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: adc082c1 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: 52674f94 user: drh tags: push-down-backport | |
|
2016-04-25
| ||
| 02:20 | 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]. check-in: ec215f94 user: drh tags: trunk | |
Changes
Changes to src/select.c.
3791 3792 3793 3794 3795 3796 3797 3798 3799 3800 3801 3802 3803 3804 3805 3806 3807 3808 |
sqlite3 *db, /* The database connection (for malloc()) */
Select *pSubq, /* The subquery whose WHERE clause is to be augmented */
Expr *pWhere, /* The WHERE clause of the outer query */
int iCursor /* Cursor number of the subquery */
){
Expr *pNew;
int nChng = 0;
if( pWhere==0 ) return 0;
if( (pSubq->selFlags & (SF_Aggregate|SF_Recursive))!=0 ){
return 0; /* restrictions (1) and (2) */
}
if( pSubq->pLimit!=0 ){
return 0; /* restriction (3) */
}
while( pWhere->op==TK_AND ){
nChng += pushDownWhereTerms(db, pSubq, pWhere->pRight, iCursor);
pWhere = pWhere->pLeft;
}
|
> > | > > > | | > |
3791 3792 3793 3794 3795 3796 3797 3798 3799 3800 3801 3802 3803 3804 3805 3806 3807 3808 3809 3810 3811 3812 3813 3814 |
sqlite3 *db, /* The database connection (for malloc()) */
Select *pSubq, /* The subquery whose WHERE clause is to be augmented */
Expr *pWhere, /* The WHERE clause of the outer query */
int iCursor /* Cursor number of the subquery */
){
Expr *pNew;
int nChng = 0;
Select *pX; /* For looping over compound SELECTs in pSubq */
if( pWhere==0 ) return 0;
for(pX=pSubq; pX; pX=pX->pPrior){
if( (pX->selFlags & (SF_Aggregate|SF_Recursive))!=0 ){
testcase( pX->selFlags & SF_Aggregate );
testcase( pX->selFlags & SF_Recursive );
testcase( pX!=pSubq );
return 0; /* restrictions (1) and (2) */
}
}
if( pSubq->pLimit!=0 ){
return 0; /* restriction (3) */
}
while( pWhere->op==TK_AND ){
nChng += pushDownWhereTerms(db, pSubq, pWhere->pRight, iCursor);
pWhere = pWhere->pLeft;
}
|
Changes to test/select4.test.
859 860 861 862 863 864 865 866 867 |
} {}
do_execsql_test select4-14.8 {
SELECT * FROM t14 EXCEPT VALUES('a','b','c') EXCEPT VALUES(4,5,6)
} {1 2 3}
do_execsql_test select4-14.9 {
SELECT * FROM t14 UNION ALL VALUES(3,2,1),(2,3,1),(1,2,3),(2,1,3);
} {1 2 3 4 5 6 3 2 1 2 3 1 1 2 3 2 1 3}
finish_test
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 |
} {}
do_execsql_test select4-14.8 {
SELECT * FROM t14 EXCEPT VALUES('a','b','c') EXCEPT VALUES(4,5,6)
} {1 2 3}
do_execsql_test select4-14.9 {
SELECT * FROM t14 UNION ALL VALUES(3,2,1),(2,3,1),(1,2,3),(2,1,3);
} {1 2 3 4 5 6 3 2 1 2 3 1 1 2 3 2 1 3}
# Ticket https://www.sqlite.org/src/tktview/f7f8c97e975978d45 on 2016-04-25
#
# The where push-down optimization from 2015-06-02 is suppose to disable
# on aggregate subqueries. But if the subquery is a compound where the
# last SELECT is non-aggregate but some other SELECT is an aggregate, the
# test is incomplete and the optimization is not properly disabled.
#
# The following test cases verify that the fix works.
#
do_execsql_test select4-17.1 {
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a int, b int);
INSERT INTO t1 VALUES(1,2),(1,18),(2,19);
SELECT x, y FROM (
SELECT 98 AS x, 99 AS y
UNION
SELECT a AS x, sum(b) AS y FROM t1 GROUP BY a
) AS w WHERE y>=20
ORDER BY +x;
} {1 20 98 99}
do_execsql_test select4-17.2 {
SELECT x, y FROM (
SELECT a AS x, sum(b) AS y FROM t1 GROUP BY a
UNION
SELECT 98 AS x, 99 AS y
) AS w WHERE y>=20
ORDER BY +x;
} {1 20 98 99}
do_catchsql_test select4-17.3 {
SELECT x, y FROM (
SELECT a AS x, sum(b) AS y FROM t1 GROUP BY a LIMIT 3
UNION
SELECT 98 AS x, 99 AS y
) AS w WHERE y>=20
ORDER BY +x;
} {1 {LIMIT clause should come after UNION not before}}
finish_test
|