/ Check-in [adc082c1]
Login

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

Changes to src/select.c.

  3791   3791     sqlite3 *db,          /* The database connection (for malloc()) */
  3792   3792     Select *pSubq,        /* The subquery whose WHERE clause is to be augmented */
  3793   3793     Expr *pWhere,         /* The WHERE clause of the outer query */
  3794   3794     int iCursor           /* Cursor number of the subquery */
  3795   3795   ){
  3796   3796     Expr *pNew;
  3797   3797     int nChng = 0;
         3798  +  Select *pX;           /* For looping over compound SELECTs in pSubq */
  3798   3799     if( pWhere==0 ) return 0;
  3799         -  if( (pSubq->selFlags & (SF_Aggregate|SF_Recursive))!=0 ){
  3800         -     return 0; /* restrictions (1) and (2) */
         3800  +  for(pX=pSubq; pX; pX=pX->pPrior){
         3801  +    if( (pX->selFlags & (SF_Aggregate|SF_Recursive))!=0 ){
         3802  +      testcase( pX->selFlags & SF_Aggregate );
         3803  +      testcase( pX->selFlags & SF_Recursive );
         3804  +      testcase( pX!=pSubq );
         3805  +      return 0; /* restrictions (1) and (2) */
         3806  +    }
  3801   3807     }
  3802   3808     if( pSubq->pLimit!=0 ){
  3803         -     return 0; /* restriction (3) */
         3809  +    return 0; /* restriction (3) */
  3804   3810     }
  3805   3811     while( pWhere->op==TK_AND ){
  3806   3812       nChng += pushDownWhereTerms(db, pSubq, pWhere->pRight, iCursor);
  3807   3813       pWhere = pWhere->pLeft;
  3808   3814     }
  3809   3815     if( ExprHasProperty(pWhere,EP_FromJoin) ) return 0; /* restriction 5 */
  3810   3816     if( sqlite3ExprIsTableConstant(pWhere, iCursor) ){

Changes to test/select4.test.

   859    859   } {}
   860    860   do_execsql_test select4-14.8 {
   861    861     SELECT * FROM t14 EXCEPT VALUES('a','b','c') EXCEPT VALUES(4,5,6)
   862    862   } {1 2 3}
   863    863   do_execsql_test select4-14.9 {
   864    864     SELECT * FROM t14 UNION ALL VALUES(3,2,1),(2,3,1),(1,2,3),(2,1,3);
   865    865   } {1 2 3 4 5 6 3 2 1 2 3 1 1 2 3 2 1 3}
          866  +
          867  +# Ticket https://www.sqlite.org/src/tktview/f7f8c97e975978d45  on 2016-04-25
          868  +#
          869  +# The where push-down optimization from 2015-06-02 is suppose to disable
          870  +# on aggregate subqueries.  But if the subquery is a compound where the
          871  +# last SELECT is non-aggregate but some other SELECT is an aggregate, the
          872  +# test is incomplete and the optimization is not properly disabled.
          873  +# 
          874  +# The following test cases verify that the fix works.
          875  +#
          876  +do_execsql_test select4-17.1 {
          877  +  DROP TABLE IF EXISTS t1;
          878  +  CREATE TABLE t1(a int, b int);
          879  +  INSERT INTO t1 VALUES(1,2),(1,18),(2,19);
          880  +  SELECT x, y FROM (
          881  +    SELECT 98 AS x, 99 AS y
          882  +    UNION
          883  +    SELECT a AS x, sum(b) AS y FROM t1 GROUP BY a
          884  +  ) AS w WHERE y>=20
          885  +  ORDER BY +x;
          886  +} {1 20 98 99}
          887  +do_execsql_test select4-17.2 {
          888  +  SELECT x, y FROM (
          889  +    SELECT a AS x, sum(b) AS y FROM t1 GROUP BY a
          890  +    UNION
          891  +    SELECT 98 AS x, 99 AS y
          892  +  ) AS w WHERE y>=20
          893  +  ORDER BY +x;
          894  +} {1 20 98 99}
          895  +do_catchsql_test select4-17.3 {
          896  +  SELECT x, y FROM (
          897  +    SELECT a AS x, sum(b) AS y FROM t1 GROUP BY a LIMIT 3
          898  +    UNION
          899  +    SELECT 98 AS x, 99 AS y
          900  +  ) AS w WHERE y>=20
          901  +  ORDER BY +x;
          902  +} {1 {LIMIT clause should come after UNION not before}}
   866    903   
   867    904   finish_test