/ Check-in [f08c1731]
Login

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:f08c1731b0b1dddcba190b094a35306a159713d3db939330f73075ff1d72c81e
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
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

  3853   3853   **           to suppress it. **)
  3854   3854   **
  3855   3855   **   (2) The inner query is the recursive part of a common table expression.
  3856   3856   **
  3857   3857   **   (3) The inner query has a LIMIT clause (since the changes to the WHERE
  3858   3858   **       close would change the meaning of the LIMIT).
  3859   3859   **
  3860         -**   (4) (** This restriction was removed on 2018-03-21.  It used to read:
  3861         -**       The inner query is the right operand of a LEFT JOIN. **)
         3860  +**   (4) The inner query is the right operand of a LEFT JOIN and the
         3861  +**       expression to be pushed down does not come from the ON clause
         3862  +**       on that LEFT JOIN.
  3862   3863   **
  3863   3864   **   (5) The WHERE clause expression originates in the ON or USING clause
  3864   3865   **       of a LEFT JOIN where iCursor is not the right-hand table of that
  3865   3866   **       left join.  An example:
  3866   3867   **
  3867   3868   **           SELECT *
  3868   3869   **           FROM (SELECT 1 AS a1 UNION ALL SELECT 2) AS aa
................................................................................
  3876   3877   ** Return 0 if no changes are made and non-zero if one or more WHERE clause
  3877   3878   ** terms are duplicated into the subquery.
  3878   3879   */
  3879   3880   static int pushDownWhereTerms(
  3880   3881     Parse *pParse,        /* Parse context (for malloc() and error reporting) */
  3881   3882     Select *pSubq,        /* The subquery whose WHERE clause is to be augmented */
  3882   3883     Expr *pWhere,         /* The WHERE clause of the outer query */
  3883         -  int iCursor           /* Cursor number of the subquery */
         3884  +  int iCursor,          /* Cursor number of the subquery */
         3885  +  int isLeftJoin        /* True if pSubq is the right term of a LEFT JOIN */
  3884   3886   ){
  3885   3887     Expr *pNew;
  3886   3888     int nChng = 0;
  3887   3889     if( pWhere==0 ) return 0;
  3888   3890     if( pSubq->selFlags & SF_Recursive ) return 0;  /* restriction (2) */
  3889   3891   
  3890   3892   #ifdef SQLITE_DEBUG
................................................................................
  3900   3902     }
  3901   3903   #endif
  3902   3904   
  3903   3905     if( pSubq->pLimit!=0 ){
  3904   3906       return 0; /* restriction (3) */
  3905   3907     }
  3906   3908     while( pWhere->op==TK_AND ){
  3907         -    nChng += pushDownWhereTerms(pParse, pSubq, pWhere->pRight, iCursor);
         3909  +    nChng += pushDownWhereTerms(pParse, pSubq, pWhere->pRight,
         3910  +                                iCursor, isLeftJoin);
  3908   3911       pWhere = pWhere->pLeft;
  3909   3912     }
         3913  +  if( isLeftJoin
         3914  +   && (ExprHasProperty(pWhere,EP_FromJoin)==0
         3915  +         || pWhere->iRightJoinTable!=iCursor)
         3916  +  ){
         3917  +    return 0; /* restriction (4) */
         3918  +  }
  3910   3919     if( ExprHasProperty(pWhere,EP_FromJoin) && pWhere->iRightJoinTable!=iCursor ){
  3911   3920       return 0; /* restriction (5) */
  3912   3921     }
  3913   3922     if( sqlite3ExprIsTableConstant(pWhere, iCursor) ){
  3914   3923       nChng++;
  3915   3924       while( pSubq ){
  3916   3925         SubstContext x;
................................................................................
  5370   5379       */
  5371   5380       pParse->nHeight += sqlite3SelectExprHeight(p);
  5372   5381   
  5373   5382       /* Make copies of constant WHERE-clause terms in the outer query down
  5374   5383       ** inside the subquery.  This can help the subquery to run more efficiently.
  5375   5384       */
  5376   5385       if( OptimizationEnabled(db, SQLITE_PushDown)
  5377         -     && pushDownWhereTerms(pParse, pSub, p->pWhere, pItem->iCursor)
         5386  +     && pushDownWhereTerms(pParse, pSub, p->pWhere, pItem->iCursor,
         5387  +                           (pItem->fg.jointype & JT_OUTER)!=0)
  5378   5388       ){
  5379   5389   #if SELECTTRACE_ENABLED
  5380   5390         if( sqlite3SelectTrace & 0x100 ){
  5381   5391           SELECTTRACE(0x100,pParse,p,("After WHERE-clause push-down:\n"));
  5382   5392           sqlite3TreeViewSelect(0, p, 0);
  5383   5393         }
  5384   5394   #endif

Changes to test/join2.test.

   260    260     CREATE INDEX u1ab ON u1(b, c);
   261    261   }
   262    262   do_eqp_test 6.1 {
   263    263     SELECT u2.* FROM u2 LEFT JOIN u1 ON( u1.a=u2.a AND u1.b=u2.b AND u1.c=u2.c );
   264    264   } {
   265    265     0 0 0 {SCAN TABLE u2}
   266    266   }
          267  +
          268  +db close
          269  +sqlite3 db :memory:
          270  +do_execsql_test 7.0 {
          271  +  CREATE TABLE t1(a,b);  INSERT INTO t1 VALUES(1,2),(3,4),(5,6);
          272  +  CREATE TABLE t2(c,d);  INSERT INTO t2 VALUES(2,4),(3,6);
          273  +  CREATE TABLE t3(x);    INSERT INTO t3 VALUES(9);
          274  +  CREATE VIEW test AS
          275  +    SELECT *, 'x'
          276  +      FROM t1 LEFT JOIN (SELECT * FROM t2, t3) ON (c=b AND x=9)
          277  +      WHERE c IS NULL;
          278  +  SELECT * FROM test;
          279  +} {3 4 {} {} {} x 5 6 {} {} {} x}
          280  +
   267    281   
   268    282   finish_test