/ Check-in [430bb59d]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Improved handling of aggregate subqueries within an aggregate query.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 430bb59d798286a86c351de92c429345f016b3f0
User & Date: drh 2012-04-17 16:38:53
References
2012-11-02
12:58 Ticket [bfbf38e5] Segfault on a nested join status still Open with 3 other changes artifact: 23d9feb9 user: drh
2012-05-21
04:31 Ticket [c2ad16f9] Segfault on query involving deeply nested aggregate views status still Open with 3 other changes artifact: 956d8d76 user: mistachkin
03:21 New ticket [c2ad16f9]. artifact: 24e75e76 user: drh
Context
2012-04-18
09:59
Fix a typecast problem in lemon that could cause problems on 64-bit machines. check-in: 4a5641cc user: drh tags: trunk
05:57
Import all the latest trunk changes into the WinRT branch. Refactor and/or remove WinCE-specific macros and functions used for file locking to improve clarity of presentation. check-in: ad5cd15f user: mistachkin tags: winrt
01:41
Import all the latest trunk changes into the sessions branch. check-in: 87a0eab5 user: drh tags: sessions
2012-04-17
16:38
Improved handling of aggregate subqueries within an aggregate query. check-in: 430bb59d user: drh tags: trunk
09:09
Add an undocumented and possibly ephemeral ".breakpoint" command to the command-line shell, to call a no-op routine on which it is convenient to set a symbolic debugger breakpoint. check-in: 8e2363ad user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

  3774   3774     if( sqlite3ExprCompare(pA->pRight, pB->pRight) ) return 2;
  3775   3775     if( sqlite3ExprListCompare(pA->x.pList, pB->x.pList) ) return 2;
  3776   3776     if( pA->iTable!=pB->iTable || pA->iColumn!=pB->iColumn ) return 2;
  3777   3777     if( ExprHasProperty(pA, EP_IntValue) ){
  3778   3778       if( !ExprHasProperty(pB, EP_IntValue) || pA->u.iValue!=pB->u.iValue ){
  3779   3779         return 2;
  3780   3780       }
  3781         -  }else if( pA->op!=TK_COLUMN && pA->u.zToken ){
         3781  +  }else if( pA->op!=TK_COLUMN && pA->op!=TK_AGG_COLUMN && pA->u.zToken ){
  3782   3782       if( ExprHasProperty(pB, EP_IntValue) || NEVER(pB->u.zToken==0) ) return 2;
  3783   3783       if( strcmp(pA->u.zToken,pB->u.zToken)!=0 ){
  3784   3784         return 2;
  3785   3785       }
  3786   3786     }
  3787   3787     if( (pA->flags & EP_ExpCollate)!=(pB->flags & EP_ExpCollate) ) return 1;
  3788   3788     if( (pA->flags & EP_ExpCollate)!=0 && pA->pColl!=pB->pColl ) return 2;
................................................................................
  3810   3810       Expr *pExprA = pA->a[i].pExpr;
  3811   3811       Expr *pExprB = pB->a[i].pExpr;
  3812   3812       if( pA->a[i].sortOrder!=pB->a[i].sortOrder ) return 1;
  3813   3813       if( sqlite3ExprCompare(pExprA, pExprB) ) return 1;
  3814   3814     }
  3815   3815     return 0;
  3816   3816   }
         3817  +
         3818  +/*
         3819  +** This is the expression callback for sqlite3FunctionUsesOtherSrc().
         3820  +**
         3821  +** Determine if an expression references any table other than one of the
         3822  +** tables in pWalker->u.pSrcList and abort if it does.
         3823  +*/
         3824  +static int exprUsesOtherSrc(Walker *pWalker, Expr *pExpr){
         3825  +  if( pExpr->op==TK_COLUMN || pExpr->op==TK_AGG_COLUMN ){
         3826  +    int i;
         3827  +    SrcList *pSrc = pWalker->u.pSrcList;
         3828  +    for(i=0; i<pSrc->nSrc; i++){
         3829  +      if( pExpr->iTable==pSrc->a[i].iCursor ) return WRC_Continue;
         3830  +    }
         3831  +    return WRC_Abort;
         3832  +  }else{
         3833  +    return WRC_Continue;
         3834  +  }
         3835  +}
         3836  +
         3837  +/*
         3838  +** Determine if any of the arguments to the pExpr Function references
         3839  +** any SrcList other than pSrcList.  Return true if they do.  Return
         3840  +** false if pExpr has no argument or has only constant arguments or
         3841  +** only references tables named in pSrcList.
         3842  +*/
         3843  +static int sqlite3FunctionUsesOtherSrc(Expr *pExpr, SrcList *pSrcList){
         3844  +  Walker w;
         3845  +  assert( pExpr->op==TK_AGG_FUNCTION );
         3846  +  memset(&w, 0, sizeof(w));
         3847  +  w.xExprCallback = exprUsesOtherSrc;
         3848  +  w.u.pSrcList = pSrcList;
         3849  +  if( sqlite3WalkExprList(&w, pExpr->x.pList)!=WRC_Continue ) return 1;
         3850  +  return 0;
         3851  +}
  3817   3852   
  3818   3853   /*
  3819   3854   ** Add a new element to the pAggInfo->aCol[] array.  Return the index of
  3820   3855   ** the new element.  Return a negative number if malloc fails.
  3821   3856   */
  3822   3857   static int addAggInfoColumn(sqlite3 *db, AggInfo *pInfo){
  3823   3858     int i;
................................................................................
  3926   3961               break;
  3927   3962             } /* endif pExpr->iTable==pItem->iCursor */
  3928   3963           } /* end loop over pSrcList */
  3929   3964         }
  3930   3965         return WRC_Prune;
  3931   3966       }
  3932   3967       case TK_AGG_FUNCTION: {
  3933         -      /* The pNC->nDepth==0 test causes aggregate functions in subqueries
  3934         -      ** to be ignored */
  3935         -      if( pNC->nDepth==0 ){
         3968  +      if( !sqlite3FunctionUsesOtherSrc(pExpr, pSrcList) ){
  3936   3969           /* Check to see if pExpr is a duplicate of another aggregate 
  3937   3970           ** function that is already in the pAggInfo structure
  3938   3971           */
  3939   3972           struct AggInfo_func *pItem = pAggInfo->aFunc;
  3940   3973           for(i=0; i<pAggInfo->nFunc; i++, pItem++){
  3941   3974             if( sqlite3ExprCompare(pItem->pExpr, pExpr)==0 ){
  3942   3975               break;
................................................................................
  3972   4005           return WRC_Prune;
  3973   4006         }
  3974   4007       }
  3975   4008     }
  3976   4009     return WRC_Continue;
  3977   4010   }
  3978   4011   static int analyzeAggregatesInSelect(Walker *pWalker, Select *pSelect){
  3979         -  NameContext *pNC = pWalker->u.pNC;
  3980         -  if( pNC->nDepth==0 ){
  3981         -    pNC->nDepth++;
  3982         -    sqlite3WalkSelect(pWalker, pSelect);
  3983         -    pNC->nDepth--;
  3984         -    return WRC_Prune;
  3985         -  }else{
  3986         -    return WRC_Continue;
  3987         -  }
         4012  +  return WRC_Continue;
  3988   4013   }
  3989   4014   
  3990   4015   /*
  3991   4016   ** Analyze the given expression looking for aggregate functions and
  3992   4017   ** for variables that need to be added to the pParse->aAgg[] array.
  3993   4018   ** Make additional entries to the pParse->aAgg[] array as necessary.
  3994   4019   **
  3995   4020   ** This routine should only be called after the expression has been
  3996   4021   ** analyzed by sqlite3ResolveExprNames().
  3997   4022   */
  3998   4023   void sqlite3ExprAnalyzeAggregates(NameContext *pNC, Expr *pExpr){
  3999   4024     Walker w;
         4025  +  memset(&w, 0, sizeof(w));
  4000   4026     w.xExprCallback = analyzeAggregate;
  4001   4027     w.xSelectCallback = analyzeAggregatesInSelect;
  4002   4028     w.u.pNC = pNC;
  4003   4029     assert( pNC->pSrcList!=0 );
  4004   4030     sqlite3WalkExpr(&w, pExpr);
  4005   4031   }
  4006   4032   

Changes to src/sqliteInt.h.

  2007   2007     SrcList *pSrcList;   /* One or more tables used to resolve names */
  2008   2008     ExprList *pEList;    /* Optional list of named expressions */
  2009   2009     int nRef;            /* Number of names resolved by this context */
  2010   2010     int nErr;            /* Number of errors encountered while resolving names */
  2011   2011     u8 allowAgg;         /* Aggregate functions allowed here */
  2012   2012     u8 hasAgg;           /* True if aggregates are seen */
  2013   2013     u8 isCheck;          /* True if resolving names in a CHECK constraint */
  2014         -  int nDepth;          /* Depth of subquery recursion. 1 for no recursion */
  2015   2014     AggInfo *pAggInfo;   /* Information about aggregates at this level */
  2016   2015     NameContext *pNext;  /* Next outer name context.  NULL for outermost */
  2017   2016   };
  2018   2017   
  2019   2018   /*
  2020   2019   ** An instance of the following structure contains all information
  2021   2020   ** needed to generate code for a single SELECT statement.
................................................................................
  2473   2472   struct Walker {
  2474   2473     int (*xExprCallback)(Walker*, Expr*);     /* Callback for expressions */
  2475   2474     int (*xSelectCallback)(Walker*,Select*);  /* Callback for SELECTs */
  2476   2475     Parse *pParse;                            /* Parser context.  */
  2477   2476     union {                                   /* Extra data for callback */
  2478   2477       NameContext *pNC;                          /* Naming context */
  2479   2478       int i;                                     /* Integer value */
         2479  +    SrcList *pSrcList;                         /* FROM clause */
  2480   2480     } u;
  2481   2481   };
  2482   2482   
  2483   2483   /* Forward declarations */
  2484   2484   int sqlite3WalkExpr(Walker*, Expr*);
  2485   2485   int sqlite3WalkExprList(Walker*, ExprList*);
  2486   2486   int sqlite3WalkSelect(Walker*, Select*);

Changes to test/subquery.test.

   326    326     }
   327    327   } {1 one 2 two}
   328    328   do_test subquery-3.3.5 {
   329    329     execsql {
   330    330       SELECT a, (SELECT count(*) FROM t2 WHERE a=c) FROM t1;
   331    331     }
   332    332   } {1 1 2 1}
          333  +
          334  +# The following tests check for aggregate subqueries in an aggregate
          335  +# query.
          336  +#
          337  +do_test subquery-3.4.1 {
          338  +  execsql {
          339  +    CREATE TABLE t34(x,y);
          340  +    INSERT INTO t34 VALUES(106,4), (107,3), (106,5), (107,5);
          341  +    SELECT a.x, avg(a.y)
          342  +      FROM t34 AS a
          343  +     GROUP BY a.x
          344  +     HAVING NOT EXISTS( SELECT b.x, avg(b.y)
          345  +                          FROM t34 AS b
          346  +                         GROUP BY b.x
          347  +                         HAVING avg(a.y) > avg(b.y));
          348  +  }
          349  +} {107 4.0}
          350  +do_test subquery-3.4.2 {
          351  +  execsql {
          352  +    SELECT a.x, avg(a.y) AS avg1
          353  +      FROM t34 AS a
          354  +     GROUP BY a.x
          355  +     HAVING NOT EXISTS( SELECT b.x, avg(b.y) AS avg2
          356  +                          FROM t34 AS b
          357  +                         GROUP BY b.x
          358  +                         HAVING avg1 > avg2);
          359  +  }
          360  +} {107 4.0}
          361  +do_test subquery-3.4.3 {
          362  +  execsql {
          363  +    SELECT
          364  +       a.x,
          365  +       avg(a.y),
          366  +       NOT EXISTS ( SELECT b.x, avg(b.y)
          367  +                      FROM t34 AS b
          368  +                      GROUP BY b.x
          369  +                     HAVING avg(a.y) > avg(b.y)),
          370  +       EXISTS ( SELECT c.x, avg(c.y)
          371  +                  FROM t34 AS c
          372  +                  GROUP BY c.x
          373  +                 HAVING avg(a.y) > avg(c.y))
          374  +      FROM t34 AS a
          375  +     GROUP BY a.x
          376  +     ORDER BY a.x;
          377  +  }
          378  +} {106 4.5 0 1 107 4.0 1 0}
          379  +
   333    380   
   334    381   #------------------------------------------------------------------
   335    382   # These tests - subquery-4.* - use the TCL statement cache to try 
   336    383   # and expose bugs to do with re-using statements that have been 
   337    384   # passed to sqlite3_reset().
   338    385   #
   339    386   # One problem was that VDBE memory cells were not being initialised