From 6a9b9527fafb22eeda04326b53bae20ec8ad2339 Mon Sep 17 00:00:00 2001 From: drh Date: Tue, 27 Mar 2018 15:13:43 +0000 Subject: [PATCH] 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]. FossilOrigin-Name: f08c1731b0b1dddcba190b094a35306a159713d3db939330f73075ff1d72c81e --- manifest | 15 +++++++-------- manifest.uuid | 2 +- src/select.c | 20 +++++++++++++++----- test/join2.test | 14 ++++++++++++++ 4 files changed, 37 insertions(+), 14 deletions(-) diff --git a/manifest b/manifest index a76ccd2163..524dd74857 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Provide\sthe\sability\sfor\sthe\sVFS\sto\sdo\sa\sblocking\swait\son\slocks\sif\scompiled\nwith\sSQLITE_ENABLE_SETLK_TIMEOUT. -D 2018-03-27T13:57:31.556 +C The\spush-down\soptimization\swas\sbeing\stoo\saggressive\ssuch\sthat\sit\ssometimes\ngenerated\sincorrect\sresults.\s\sReinstate\sthe\srestriction\s(4)\s(with\nqualifications)\sthat\swas\sremoved\sby\scheck-ins\n[b5d3dd8cb0b1e4]\sand\s[dd568c27b1d765]. +D 2018-03-27T15:13:43.312 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea F Makefile.in 7016fc56c6b9bfe5daac4f34be8be38d8c0b5fab79ccbfb764d3b23bf1c6fff3 @@ -491,7 +491,7 @@ F src/printf.c d3b7844ddeb11fbbdd38dd84d09c9c1ac171d21fb038473c3aa97981201cc660 F src/random.c 80f5d666f23feb3e6665a6ce04c7197212a88384 F src/resolve.c 66c73fcb7719b8ff0e841b58338f13604ff3e2b50a723f9b8f383595735262f6 F src/rowset.c 7b7e7e479212e65b723bf40128c7b36dc5afdfac -F src/select.c 2c408ab9b4bfc807e6a9d3d45337d20ebe72a8b7ac793854710bf9d572e7ab6d +F src/select.c e51efe5479d1cb4f48defe0b97cdba7391df42a755ba9592b9159510d03cf738 F src/shell.c.in d6a07811aa9f3b10200c15ab8dd4b6b998849a3b0c8b125bfa980329a33c26a6 F src/sqlite.h.in 56e6c8fb785b09dac31331d19680f7133d84cb96fc073a47847f1066e03fc2ff F src/sqlite3.rc 5121c9e10c3964d5755191c80dd1180c122fc3a8 @@ -1006,7 +1006,7 @@ F test/ioerr5.test 2edfa4fb0f896f733071303b42224df8bedd9da4 F test/ioerr6.test a395a6ab144b26a9e3e21059a1ab6a7149cca65b F test/istrue.test d6e659764da5ccc03adcdba18fe77d7917ba5e4abd04ef14bd4e4cf43e024b5b F test/join.test 730e3e8d511289531efca01f8684f98da1e6de51eacf95c5960d0c46e77719e3 -F test/join2.test 2339e3a8089178607a718c7fa1f6c3826579132396f5304a0f8e9dbefa60c84f +F test/join2.test f5ea0fd3b0a441c8e439706339dcd17cec63a896a755c04a30bfd442ecce1190 F test/join3.test 6f0c774ff1ba0489e6c88a3e77b9d3528fb4fda0 F test/join4.test 1a352e4e267114444c29266ce79e941af5885916 F test/join5.test c6bd62effc37a152bea735f9ef241b19bb967bd4593dc99b20e2fc55ae707e38 @@ -1717,8 +1717,7 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0 -P c7473bdb1055df06dc6a93a39ba7f56116f6866292784c4938d843531500c368 5474e560eeb0269964b70bcd876bb86b4197c4fb164bc13454fb8204f083062c -R 6c455877087c47966ac2965f617b41ed -T +closed 5474e560eeb0269964b70bcd876bb86b4197c4fb164bc13454fb8204f083062c +P e7dff982be753bc93123db6a0e8fefd56f2a14fd1c9a8699af0b6b75bc88fc15 +R 8a3586de3bb11cc1f485806813b62194 U drh -Z 6e1769ba8ee258d77420d4f502f17cc1 +Z b155dbaac537f3a53a88186e0aba231b diff --git a/manifest.uuid b/manifest.uuid index fdf0a47968..9137e77596 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -e7dff982be753bc93123db6a0e8fefd56f2a14fd1c9a8699af0b6b75bc88fc15 \ No newline at end of file +f08c1731b0b1dddcba190b094a35306a159713d3db939330f73075ff1d72c81e \ No newline at end of file diff --git a/src/select.c b/src/select.c index ca3e8b7f05..4a8c3d22f3 100644 --- a/src/select.c +++ b/src/select.c @@ -3857,8 +3857,9 @@ static int flattenSubquery( ** (3) The inner query has a LIMIT clause (since the changes to the WHERE ** close would change the meaning of the LIMIT). ** -** (4) (** This restriction was removed on 2018-03-21. It used to read: -** The inner query is the right operand of a LEFT JOIN. **) +** (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 @@ -3880,7 +3881,8 @@ 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 iCursor, /* Cursor number of the subquery */ + int isLeftJoin /* True if pSubq is the right term of a LEFT JOIN */ ){ Expr *pNew; int nChng = 0; @@ -3904,9 +3906,16 @@ static int pushDownWhereTerms( return 0; /* restriction (3) */ } while( pWhere->op==TK_AND ){ - nChng += pushDownWhereTerms(pParse, pSubq, pWhere->pRight, iCursor); + 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) */ } @@ -5374,7 +5383,8 @@ int sqlite3Select( ** inside the subquery. This can help the subquery to run more efficiently. */ if( OptimizationEnabled(db, SQLITE_PushDown) - && pushDownWhereTerms(pParse, pSub, p->pWhere, pItem->iCursor) + && pushDownWhereTerms(pParse, pSub, p->pWhere, pItem->iCursor, + (pItem->fg.jointype & JT_OUTER)!=0) ){ #if SELECTTRACE_ENABLED if( sqlite3SelectTrace & 0x100 ){ diff --git a/test/join2.test b/test/join2.test index 7dfab4d693..b5b9e6be10 100644 --- a/test/join2.test +++ b/test/join2.test @@ -265,4 +265,18 @@ do_eqp_test 6.1 { 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 -- 2.47.2