Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | The push-down optimization was being too aggressive such that it sometimes generated incorrect results. Reinstate the restriction (4) (with qualifications) that was removed by check-ins [b5d3dd8cb0b1e4] and [dd568c27b1d765]. |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
f08c1731b0b1dddcba190b094a35306a |
User & Date: | drh 2018-03-27 15:13:43 |
Context
2018-03-27
| ||
22:58 | Fix a typo in the README file for ICU. No code changes. (check-in: 79c4383b user: drh tags: trunk) | |
15:13 | The push-down optimization was being too aggressive such that it sometimes generated incorrect results. Reinstate the restriction (4) (with qualifications) that was removed by check-ins [b5d3dd8cb0b1e4] and [dd568c27b1d765]. (check-in: f08c1731 user: drh tags: trunk) | |
13:57 | Provide the ability for the VFS to do a blocking wait on locks if compiled with SQLITE_ENABLE_SETLK_TIMEOUT. (check-in: e7dff982 user: drh tags: trunk) | |
Changes
Changes to src/select.c.
︙ | ︙ | |||
3853 3854 3855 3856 3857 3858 3859 | ** to suppress it. **) ** ** (2) The inner query is the recursive part of a common table expression. ** ** (3) The inner query has a LIMIT clause (since the changes to the WHERE ** close would change the meaning of the LIMIT). ** | < | > > | 3853 3854 3855 3856 3857 3858 3859 3860 3861 3862 3863 3864 3865 3866 3867 3868 3869 | ** to suppress it. **) ** ** (2) The inner query is the recursive part of a common table expression. ** ** (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 and the ** expression to be pushed down does not come from the ON clause ** on that LEFT JOIN. ** ** (5) The WHERE clause expression originates in the ON or USING clause ** of a LEFT JOIN where iCursor is not the right-hand table of that ** left join. An example: ** ** SELECT * ** FROM (SELECT 1 AS a1 UNION ALL SELECT 2) AS aa |
︙ | ︙ | |||
3876 3877 3878 3879 3880 3881 3882 | ** 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( Parse *pParse, /* Parse context (for malloc() and error reporting) */ Select *pSubq, /* The subquery whose WHERE clause is to be augmented */ Expr *pWhere, /* The WHERE clause of the outer query */ | | > | 3877 3878 3879 3880 3881 3882 3883 3884 3885 3886 3887 3888 3889 3890 3891 3892 | ** 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( Parse *pParse, /* Parse context (for malloc() and error reporting) */ 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 */ int isLeftJoin /* True if pSubq is the right term of a LEFT JOIN */ ){ Expr *pNew; int nChng = 0; if( pWhere==0 ) return 0; if( pSubq->selFlags & SF_Recursive ) return 0; /* restriction (2) */ #ifdef SQLITE_DEBUG |
︙ | ︙ | |||
3900 3901 3902 3903 3904 3905 3906 | } #endif if( pSubq->pLimit!=0 ){ return 0; /* restriction (3) */ } while( pWhere->op==TK_AND ){ | | > > > > > > > | 3902 3903 3904 3905 3906 3907 3908 3909 3910 3911 3912 3913 3914 3915 3916 3917 3918 3919 3920 3921 3922 3923 3924 3925 | } #endif if( pSubq->pLimit!=0 ){ return 0; /* restriction (3) */ } while( pWhere->op==TK_AND ){ nChng += pushDownWhereTerms(pParse, pSubq, pWhere->pRight, iCursor, isLeftJoin); pWhere = pWhere->pLeft; } if( isLeftJoin && (ExprHasProperty(pWhere,EP_FromJoin)==0 || pWhere->iRightJoinTable!=iCursor) ){ return 0; /* restriction (4) */ } if( ExprHasProperty(pWhere,EP_FromJoin) && pWhere->iRightJoinTable!=iCursor ){ return 0; /* restriction (5) */ } if( sqlite3ExprIsTableConstant(pWhere, iCursor) ){ nChng++; while( pSubq ){ SubstContext x; |
︙ | ︙ | |||
5370 5371 5372 5373 5374 5375 5376 | */ pParse->nHeight += sqlite3SelectExprHeight(p); /* Make copies of constant WHERE-clause terms in the outer query down ** inside the subquery. This can help the subquery to run more efficiently. */ if( OptimizationEnabled(db, SQLITE_PushDown) | | > | 5379 5380 5381 5382 5383 5384 5385 5386 5387 5388 5389 5390 5391 5392 5393 5394 | */ pParse->nHeight += sqlite3SelectExprHeight(p); /* Make copies of constant WHERE-clause terms in the outer query down ** inside the subquery. This can help the subquery to run more efficiently. */ if( OptimizationEnabled(db, SQLITE_PushDown) && pushDownWhereTerms(pParse, pSub, p->pWhere, pItem->iCursor, (pItem->fg.jointype & JT_OUTER)!=0) ){ #if SELECTTRACE_ENABLED if( sqlite3SelectTrace & 0x100 ){ SELECTTRACE(0x100,pParse,p,("After WHERE-clause push-down:\n")); sqlite3TreeViewSelect(0, p, 0); } #endif |
︙ | ︙ |
Changes to test/join2.test.
︙ | ︙ | |||
260 261 262 263 264 265 266 267 268 | CREATE INDEX u1ab ON u1(b, c); } do_eqp_test 6.1 { SELECT u2.* FROM u2 LEFT JOIN u1 ON( u1.a=u2.a AND u1.b=u2.b AND u1.c=u2.c ); } { 0 0 0 {SCAN TABLE u2} } finish_test | > > > > > > > > > > > > > > | 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 | CREATE INDEX u1ab ON u1(b, c); } do_eqp_test 6.1 { SELECT u2.* FROM u2 LEFT JOIN u1 ON( u1.a=u2.a AND u1.b=u2.b AND u1.c=u2.c ); } { 0 0 0 {SCAN TABLE u2} } db close sqlite3 db :memory: do_execsql_test 7.0 { CREATE TABLE t1(a,b); INSERT INTO t1 VALUES(1,2),(3,4),(5,6); CREATE TABLE t2(c,d); INSERT INTO t2 VALUES(2,4),(3,6); CREATE TABLE t3(x); INSERT INTO t3 VALUES(9); CREATE VIEW test AS SELECT *, 'x' FROM t1 LEFT JOIN (SELECT * FROM t2, t3) ON (c=b AND x=9) WHERE c IS NULL; SELECT * FROM test; } {3 4 {} {} {} x 5 6 {} {} {} x} finish_test |