/ Check-in [00b1dc71]
Login

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

Overview
Comment:Add test cases and fix bugs associated with the previous check-in enhancements to nested aggregate subquery processing.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | nested-agg
Files: files | file ages | folders
SHA1: 00b1dc71be4c3420730b5f7840af824ea86165e7
User & Date: drh 2012-08-23 19:46:11
Context
2012-08-24
01:07
Merge the nested aggregate query enhancements into trunk. check-in: d4cd6017 user: drh tags: trunk
2012-08-23
19:46
Add test cases and fix bugs associated with the previous check-in enhancements to nested aggregate subquery processing. Closed-Leaf check-in: 00b1dc71 user: drh tags: nested-agg
16:18
Further improvements to the processing of nested aggregate queries. check-in: 3c3ffa90 user: drh tags: nested-agg
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

  3119   3119       case TK_FUNCTION: {
  3120   3120         ExprList *pFarg;       /* List of function arguments */
  3121   3121         if( ExprHasAnyProperty(pExpr, EP_TokenOnly) ){
  3122   3122           pFarg = 0;
  3123   3123         }else{
  3124   3124           pFarg = pExpr->x.pList;
  3125   3125         }
  3126         -      sqlite3ExplainPrintf(pOut, "%sFUNCTION:%s(",
  3127         -                           op==TK_AGG_FUNCTION ? "AGG_" : "",
  3128         -                           pExpr->u.zToken);
         3126  +      if( op==TK_AGG_FUNCTION ){
         3127  +        sqlite3ExplainPrintf(pOut, "AGG_FUNCTION%d:%s(",
         3128  +                             pExpr->op2, pExpr->u.zToken);
         3129  +      }else{
         3130  +        sqlite3ExplainPrintf(pOut, "FUNCTION:%s(", pExpr->u.zToken);
         3131  +      }
  3129   3132         if( pFarg ){
  3130   3133           sqlite3ExplainExprList(pOut, pFarg);
  3131   3134         }
  3132   3135         sqlite3ExplainPrintf(pOut, ")");
  3133   3136         break;
  3134   3137       }
  3135   3138   #ifndef SQLITE_OMIT_SUBQUERY
................................................................................
  3814   3817     }
  3815   3818     return 0;
  3816   3819   }
  3817   3820   
  3818   3821   /*
  3819   3822   ** An instance of the following structure is used by the tree walker
  3820   3823   ** to count references to table columns in the arguments of an 
  3821         -** aggregate function, in order to implement the sqlite3FunctionUsesOtherSrc()
  3822         -** and sqlite3FunctionThisSrc() routines.
         3824  +** aggregate function, in order to implement the
         3825  +** sqlite3FunctionThisSrc() routine.
  3823   3826   */
  3824   3827   struct SrcCount {
  3825   3828     SrcList *pSrc;   /* One particular FROM clause in a nested query */
  3826   3829     int nThis;       /* Number of references to columns in pSrcList */
  3827   3830     int nOther;      /* Number of references to columns in other FROM clauses */
  3828   3831   };
  3829   3832   
................................................................................
  3843   3846       }else{
  3844   3847         p->nOther++;
  3845   3848       }
  3846   3849     }
  3847   3850     return WRC_Continue;
  3848   3851   }
  3849   3852   
  3850         -/*
  3851         -** Determine if any of the arguments to the pExpr Function references
  3852         -** any SrcList other than pSrcList.  Return true if they do.  Return
  3853         -** false if pExpr has no argument or has only constant arguments or
  3854         -** only references tables named in pSrcList.
  3855         -*/
  3856         -static int sqlite3FunctionUsesOtherSrc(Expr *pExpr, SrcList *pSrcList){
  3857         -  Walker w;
  3858         -  struct SrcCount cnt;
  3859         -  assert( pExpr->op==TK_AGG_FUNCTION );
  3860         -  memset(&w, 0, sizeof(w));
  3861         -  w.xExprCallback = exprSrcCount;
  3862         -  w.u.pSrcCount = &cnt;
  3863         -  cnt.pSrc = pSrcList;
  3864         -  cnt.nThis = 0;
  3865         -  cnt.nOther = 0;
  3866         -  sqlite3WalkExprList(&w, pExpr->x.pList);
  3867         -  return cnt.nOther>0;
  3868         -}
  3869         -
  3870   3853   /*
  3871   3854   ** Determine if any of the arguments to the pExpr Function reference
  3872   3855   ** pSrcList.  Return true if they do.  Also return true if the function
  3873   3856   ** has no arguments or has only constant arguments.  Return false if pExpr
  3874   3857   ** references columns but not columns of tables found in pSrcList.
  3875   3858   */
  3876   3859   int sqlite3FunctionUsesThisSrc(Expr *pExpr, SrcList *pSrcList){
................................................................................
  3999   3982             } /* endif pExpr->iTable==pItem->iCursor */
  4000   3983           } /* end loop over pSrcList */
  4001   3984         }
  4002   3985         return WRC_Prune;
  4003   3986       }
  4004   3987       case TK_AGG_FUNCTION: {
  4005   3988         if( (pNC->ncFlags & NC_InAggFunc)==0
  4006         -       && !sqlite3FunctionUsesOtherSrc(pExpr, pSrcList)
         3989  +       && pWalker->walkerDepth==pExpr->op2
  4007   3990         ){
  4008   3991           /* Check to see if pExpr is a duplicate of another aggregate 
  4009   3992           ** function that is already in the pAggInfo structure
  4010   3993           */
  4011   3994           struct AggInfo_func *pItem = pAggInfo->aFunc;
  4012   3995           for(i=0; i<pAggInfo->nFunc; i++, pItem++){
  4013   3996             if( sqlite3ExprCompare(pItem->pExpr, pExpr)==0 ){

Changes to src/resolve.c.

    13     13   ** This file contains routines used for walking the parser tree and
    14     14   ** resolve all identifiers by associating them with a particular
    15     15   ** table and column.
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   #include <stdlib.h>
    19     19   #include <string.h>
           20  +
           21  +/*
           22  +** Walk the expression tree pExpr and increase the aggregate function
           23  +** depth (the Expr.op2 field) by N on every TK_AGG_FUNCTION node.
           24  +** This needs to occur when copying a TK_AGG_FUNCTION node from an
           25  +** outer query into an inner subquery.
           26  +**
           27  +** incrAggFunctionDepth(pExpr,n) is the main routine.  incrAggDepth(..)
           28  +** is a helper function - a callback for the tree walker.
           29  +*/
           30  +static int incrAggDepth(Walker *pWalker, Expr *pExpr){
           31  +  if( pExpr->op==TK_AGG_FUNCTION ) pExpr->op2 += pWalker->u.i;
           32  +  return WRC_Continue;
           33  +}
           34  +static void incrAggFunctionDepth(Expr *pExpr, int N){
           35  +  if( N>0 ){
           36  +    Walker w;
           37  +    memset(&w, 0, sizeof(w));
           38  +    w.xExprCallback = incrAggDepth;
           39  +    w.u.i = N;
           40  +    sqlite3WalkExpr(&w, pExpr);
           41  +  }
           42  +}
    20     43   
    21     44   /*
    22     45   ** Turn the pExpr expression into an alias for the iCol-th column of the
    23     46   ** result set in pEList.
    24     47   **
    25     48   ** If the result set column is a simple column reference, then this routine
    26     49   ** makes an exact copy.  But for any other kind of expression, this
................................................................................
    40     63   ** Is equivalent to:
    41     64   **
    42     65   **     SELECT random()%5 AS x, count(*) FROM tab GROUP BY random()%5
    43     66   **
    44     67   ** The result of random()%5 in the GROUP BY clause is probably different
    45     68   ** from the result in the result-set.  We might fix this someday.  Or
    46     69   ** then again, we might not...
           70  +**
           71  +** The nSubquery parameter specifies how many levels of subquery the
           72  +** alias is removed from the original expression.  The usually value is
           73  +** zero but it might be more if the alias is contained within a subquery
           74  +** of the original expression.  The Expr.op2 field of TK_AGG_FUNCTION
           75  +** structures must be increased by the nSubquery amount.
    47     76   */
    48     77   static void resolveAlias(
    49     78     Parse *pParse,         /* Parsing context */
    50     79     ExprList *pEList,      /* A result set */
    51     80     int iCol,              /* A column in the result set.  0..pEList->nExpr-1 */
    52     81     Expr *pExpr,           /* Transform this into an alias to the result set */
    53         -  const char *zType      /* "GROUP" or "ORDER" or "" */
           82  +  const char *zType,     /* "GROUP" or "ORDER" or "" */
           83  +  int nSubquery          /* Number of subqueries that the label is moving */
    54     84   ){
    55     85     Expr *pOrig;           /* The iCol-th column of the result set */
    56     86     Expr *pDup;            /* Copy of pOrig */
    57     87     sqlite3 *db;           /* The database connection */
    58     88   
    59     89     assert( iCol>=0 && iCol<pEList->nExpr );
    60     90     pOrig = pEList->a[iCol].pExpr;
    61     91     assert( pOrig!=0 );
    62     92     assert( pOrig->flags & EP_Resolved );
    63     93     db = pParse->db;
    64     94     if( pOrig->op!=TK_COLUMN && zType[0]!='G' ){
    65     95       pDup = sqlite3ExprDup(db, pOrig, 0);
           96  +    incrAggFunctionDepth(pDup, nSubquery);
    66     97       pDup = sqlite3PExpr(pParse, TK_AS, pDup, 0, 0);
    67     98       if( pDup==0 ) return;
    68     99       if( pEList->a[iCol].iAlias==0 ){
    69    100         pEList->a[iCol].iAlias = (u16)(++pParse->nAlias);
    70    101       }
    71    102       pDup->iTable = pEList->a[iCol].iAlias;
    72    103     }else if( ExprHasProperty(pOrig, EP_IntValue) || pOrig->u.zToken==0 ){
................................................................................
   147    178     Parse *pParse,       /* The parsing context */
   148    179     const char *zDb,     /* Name of the database containing table, or NULL */
   149    180     const char *zTab,    /* Name of table containing column, or NULL */
   150    181     const char *zCol,    /* Name of the column. */
   151    182     NameContext *pNC,    /* The name context used to resolve the name */
   152    183     Expr *pExpr          /* Make this EXPR node point to the selected column */
   153    184   ){
   154         -  int i, j;            /* Loop counters */
          185  +  int i, j;                         /* Loop counters */
   155    186     int cnt = 0;                      /* Number of matching column names */
   156    187     int cntTab = 0;                   /* Number of matching table names */
          188  +  int nSubquery = 0;                /* How many levels of subquery */
   157    189     sqlite3 *db = pParse->db;         /* The database connection */
   158    190     struct SrcList_item *pItem;       /* Use for looping over pSrcList items */
   159    191     struct SrcList_item *pMatch = 0;  /* The matching pSrcList item */
   160    192     NameContext *pTopNC = pNC;        /* First namecontext in the list */
   161    193     Schema *pSchema = 0;              /* Schema of the expression */
   162    194     int isTrigger = 0;
   163    195   
................................................................................
   311    343             assert( pExpr->x.pList==0 );
   312    344             assert( pExpr->x.pSelect==0 );
   313    345             pOrig = pEList->a[j].pExpr;
   314    346             if( (pNC->ncFlags&NC_AllowAgg)==0 && ExprHasProperty(pOrig, EP_Agg) ){
   315    347               sqlite3ErrorMsg(pParse, "misuse of aliased aggregate %s", zAs);
   316    348               return WRC_Abort;
   317    349             }
   318         -          resolveAlias(pParse, pEList, j, pExpr, "");
          350  +          resolveAlias(pParse, pEList, j, pExpr, "", nSubquery);
   319    351             cnt = 1;
   320    352             pMatch = 0;
   321    353             assert( zTab==0 && zDb==0 );
   322    354             goto lookupname_end;
   323    355           }
   324    356         } 
   325    357       }
   326    358   
   327    359       /* Advance to the next name context.  The loop will exit when either
   328    360       ** we have a match (cnt>0) or when we run out of name contexts.
   329    361       */
   330    362       if( cnt==0 ){
   331    363         pNC = pNC->pNext;
          364  +      nSubquery++;
   332    365       }
   333    366     }
   334    367   
   335    368     /*
   336    369     ** If X and Y are NULL (in other words if only the column name Z is
   337    370     ** supplied) and the value of Z is enclosed in double-quotes, then
   338    371     ** Z is a string literal if it doesn't match any column names.  In that
................................................................................
   855    888     assert( pEList!=0 );  /* sqlite3SelectNew() guarantees this */
   856    889     for(i=0, pItem=pOrderBy->a; i<pOrderBy->nExpr; i++, pItem++){
   857    890       if( pItem->iOrderByCol ){
   858    891         if( pItem->iOrderByCol>pEList->nExpr ){
   859    892           resolveOutOfRangeError(pParse, zType, i+1, pEList->nExpr);
   860    893           return 1;
   861    894         }
   862         -      resolveAlias(pParse, pEList, pItem->iOrderByCol-1, pItem->pExpr, zType);
          895  +      resolveAlias(pParse, pEList, pItem->iOrderByCol-1, pItem->pExpr, zType,0);
   863    896       }
   864    897     }
   865    898     return 0;
   866    899   }
   867    900   
   868    901   /*
   869    902   ** pOrderBy is an ORDER BY or GROUP BY clause in SELECT statement pSelect.

Changes to src/walker.c.

   121    121   ** If the Walker does not have an xSelectCallback() then this routine
   122    122   ** is a no-op returning WRC_Continue.
   123    123   */
   124    124   int sqlite3WalkSelect(Walker *pWalker, Select *p){
   125    125     int rc;
   126    126     if( p==0 || pWalker->xSelectCallback==0 ) return WRC_Continue;
   127    127     rc = WRC_Continue;
   128         -  while( p  ){
          128  +  pWalker->walkerDepth++;
          129  +  while( p ){
   129    130       rc = pWalker->xSelectCallback(pWalker, p);
   130    131       if( rc ) break;
   131         -    if( sqlite3WalkSelectExpr(pWalker, p) ) return WRC_Abort;
   132         -    if( sqlite3WalkSelectFrom(pWalker, p) ) return WRC_Abort;
          132  +    if( sqlite3WalkSelectExpr(pWalker, p)
          133  +     || sqlite3WalkSelectFrom(pWalker, p)
          134  +    ){
          135  +      pWalker->walkerDepth--;
          136  +      return WRC_Abort;
          137  +    }
   133    138       p = p->pPrior;
   134    139     }
          140  +  pWalker->walkerDepth--;
   135    141     return rc & WRC_Abort;
   136    142   }

Changes to test/aggnested.test.

    30     30   do_test aggnested-1.2 {
    31     31     db eval {
    32     32       SELECT
    33     33        (SELECT group_concat(a1,'x') || '-' || group_concat(b1,'y') FROM t2)
    34     34       FROM t1;
    35     35     }
    36     36   } {1x2x3-4y5}
           37  +do_test aggnested-1.3 {
           38  +  db eval {
           39  +    SELECT (SELECT group_concat(b1,a1) FROM t2) FROM t1;
           40  +  }
           41  +} {415 425 435}
           42  +do_test aggnested-1.4 {
           43  +  db eval {
           44  +    SELECT (SELECT group_concat(a1,b1) FROM t2) FROM t1;
           45  +  }
           46  +} {151 252 353}
           47  +
           48  +
           49  +# This test case is a copy of the one in
           50  +# http://www.mail-archive.com/sqlite-users@sqlite.org/msg70787.html
           51  +#
           52  +do_test aggnested-2.0 {
           53  +  sqlite3 db2 :memory:
           54  +  db2 eval {
           55  +    CREATE TABLE t1 (A1 INTEGER NOT NULL,A2 INTEGER NOT NULL,A3 INTEGER NOT 
           56  +    NULL,A4 INTEGER NOT NULL,PRIMARY KEY(A1));
           57  +    REPLACE INTO t1 VALUES(1,11,111,1111);
           58  +    REPLACE INTO t1 VALUES(2,22,222,2222);
           59  +    REPLACE INTO t1 VALUES(3,33,333,3333);
           60  +    CREATE TABLE t2 (B1 INTEGER NOT NULL,B2 INTEGER NOT NULL,B3 INTEGER NOT 
           61  +    NULL,B4 INTEGER NOT NULL,PRIMARY KEY(B1));
           62  +    REPLACE INTO t2 VALUES(1,88,888,8888);
           63  +    REPLACE INTO t2 VALUES(2,99,999,9999);
           64  +    SELECT (SELECT GROUP_CONCAT(CASE WHEN a1=1 THEN'A' ELSE 'B' END) FROM t2),
           65  +            t1.* 
           66  +    FROM t1;
           67  +  }
           68  +} {A,B,B 3 33 333 3333}
           69  +db2 close
    37     70   
    38     71   finish_test