/ Check-in [1e16d3e8]
Login

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

Overview
Comment:Prevent aliases of window functions expressions from being used as arguments to aggregate or other window functions.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 1e16d3e8fc60d39ca3899759ff15d355fdd7d3e23b325d8d2b0f954e11ce8dce
User & Date: dan 2019-03-28 16:15:05
Context
2019-03-28
20:50
Add new test cases to test/fuzzdata8.db check-in: f908cd40 user: drh tags: trunk
16:15
Prevent aliases of window functions expressions from being used as arguments to aggregate or other window functions. check-in: 1e16d3e8 user: dan tags: trunk
13:53
Show the pointer address for Window objects in the TreeView display. check-in: 1ae70ad2 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/resolve.c.

   430    430             assert( pExpr->pLeft==0 && pExpr->pRight==0 );
   431    431             assert( pExpr->x.pList==0 );
   432    432             assert( pExpr->x.pSelect==0 );
   433    433             pOrig = pEList->a[j].pExpr;
   434    434             if( (pNC->ncFlags&NC_AllowAgg)==0 && ExprHasProperty(pOrig, EP_Agg) ){
   435    435               sqlite3ErrorMsg(pParse, "misuse of aliased aggregate %s", zAs);
   436    436               return WRC_Abort;
          437  +          }
          438  +          if( (pNC->ncFlags&NC_AllowWin)==0 && ExprHasProperty(pOrig, EP_Win) ){
          439  +            sqlite3ErrorMsg(pParse, "misuse of aliased window function %s",zAs);
          440  +            return WRC_Abort;
   437    441             }
   438    442             if( sqlite3ExprVectorSize(pOrig)!=1 ){
   439    443               sqlite3ErrorMsg(pParse, "row value misused");
   440    444               return WRC_Abort;
   441    445             }
   442    446             resolveAlias(pParse, pEList, j, pExpr, "", nSubquery);
   443    447             cnt = 1;
................................................................................
   721    725         int no_such_func = 0;       /* True if no such function exists */
   722    726         int wrong_num_args = 0;     /* True if wrong number of arguments */
   723    727         int is_agg = 0;             /* True if is an aggregate function */
   724    728         int nId;                    /* Number of characters in function name */
   725    729         const char *zId;            /* The function name. */
   726    730         FuncDef *pDef;              /* Information about the function */
   727    731         u8 enc = ENC(pParse->db);   /* The database encoding */
          732  +      int savedAllowFlags = (pNC->ncFlags & (NC_AllowAgg | NC_AllowWin));
   728    733   
   729    734         assert( !ExprHasProperty(pExpr, EP_xIsSelect) );
   730    735         zId = pExpr->u.zToken;
   731    736         nId = sqlite3Strlen30(zId);
   732    737         pDef = sqlite3FindFunction(pParse->db, zId, n, enc, 0);
   733    738         if( pDef==0 ){
   734    739           pDef = sqlite3FindFunction(pParse->db, zId, -2, enc, 0);
................................................................................
   842    847             pNC->nErr++;
   843    848           }else if( wrong_num_args ){
   844    849             sqlite3ErrorMsg(pParse,"wrong number of arguments to function %.*s()",
   845    850                  nId, zId);
   846    851             pNC->nErr++;
   847    852           }
   848    853           if( is_agg ){
          854  +          /* Window functions may not be arguments of aggregate functions.
          855  +          ** Or arguments of other window functions. But aggregate functions
          856  +          ** may be arguments for window functions.  */
   849    857   #ifndef SQLITE_OMIT_WINDOWFUNC
   850         -          pNC->ncFlags &= ~(pExpr->y.pWin ? NC_AllowWin : NC_AllowAgg);
          858  +          pNC->ncFlags &= ~(NC_AllowWin | (!pExpr->y.pWin ? NC_AllowAgg : 0));
   851    859   #else
   852    860             pNC->ncFlags &= ~NC_AllowAgg;
   853    861   #endif
   854    862           }
   855    863         }
   856    864         sqlite3WalkExprList(pWalker, pList);
   857    865         if( is_agg ){
................................................................................
   864    872             sqlite3WalkExpr(pWalker, pExpr->y.pWin->pFilter);
   865    873             if( 0==pSel->pWin 
   866    874              || 0==sqlite3WindowCompare(pParse, pSel->pWin, pExpr->y.pWin) 
   867    875             ){
   868    876               pExpr->y.pWin->pNextWin = pSel->pWin;
   869    877               pSel->pWin = pExpr->y.pWin;
   870    878             }
   871         -          pNC->ncFlags |= NC_AllowWin;
          879  +          pNC->ncFlags |= NC_HasWin;
   872    880           }else
   873    881   #endif /* SQLITE_OMIT_WINDOWFUNC */
   874    882           {
   875    883             NameContext *pNC2 = pNC;
   876    884             pExpr->op = TK_AGG_FUNCTION;
   877    885             pExpr->op2 = 0;
   878    886             while( pNC2 && !sqlite3FunctionUsesThisSrc(pExpr, pNC2->pSrcList) ){
................................................................................
   882    890             assert( pDef!=0 );
   883    891             if( pNC2 ){
   884    892               assert( SQLITE_FUNC_MINMAX==NC_MinMaxAgg );
   885    893               testcase( (pDef->funcFlags & SQLITE_FUNC_MINMAX)!=0 );
   886    894               pNC2->ncFlags |= NC_HasAgg | (pDef->funcFlags & SQLITE_FUNC_MINMAX);
   887    895   
   888    896             }
   889         -          pNC->ncFlags |= NC_AllowAgg;
   890    897           }
          898  +        pNC->ncFlags |= savedAllowFlags;
   891    899         }
   892    900         /* FIX ME:  Compute pExpr->affinity based on the expected return
   893    901         ** type of the function 
   894    902         */
   895    903         return WRC_Prune;
   896    904       }
   897    905   #ifndef SQLITE_OMIT_SUBQUERY
................................................................................
  1644   1652     NameContext *pNC,       /* Namespace to resolve expressions in. */
  1645   1653     Expr *pExpr             /* The expression to be analyzed. */
  1646   1654   ){
  1647   1655     u16 savedHasAgg;
  1648   1656     Walker w;
  1649   1657   
  1650   1658     if( pExpr==0 ) return SQLITE_OK;
  1651         -  savedHasAgg = pNC->ncFlags & (NC_HasAgg|NC_MinMaxAgg);
  1652         -  pNC->ncFlags &= ~(NC_HasAgg|NC_MinMaxAgg);
         1659  +  savedHasAgg = pNC->ncFlags & (NC_HasAgg|NC_MinMaxAgg|NC_HasWin);
         1660  +  pNC->ncFlags &= ~(NC_HasAgg|NC_MinMaxAgg|NC_HasWin);
  1653   1661     w.pParse = pNC->pParse;
  1654   1662     w.xExprCallback = resolveExprStep;
  1655   1663     w.xSelectCallback = resolveSelectStep;
  1656   1664     w.xSelectCallback2 = 0;
  1657   1665     w.u.pNC = pNC;
  1658   1666   #if SQLITE_MAX_EXPR_DEPTH>0
  1659   1667     w.pParse->nHeight += pExpr->nHeight;
................................................................................
  1664   1672     sqlite3WalkExpr(&w, pExpr);
  1665   1673   #if SQLITE_MAX_EXPR_DEPTH>0
  1666   1674     w.pParse->nHeight -= pExpr->nHeight;
  1667   1675   #endif
  1668   1676     if( pNC->ncFlags & NC_HasAgg ){
  1669   1677       ExprSetProperty(pExpr, EP_Agg);
  1670   1678     }
         1679  +  if( pNC->ncFlags & NC_HasWin ){
         1680  +    ExprSetProperty(pExpr, EP_Win);
         1681  +  }
  1671   1682     pNC->ncFlags |= savedHasAgg;
  1672   1683     return pNC->nErr>0 || w.pParse->nErr>0;
  1673   1684   }
  1674   1685   
  1675   1686   /*
  1676   1687   ** Resolve all names for all expression in an expression list.  This is
  1677   1688   ** just like sqlite3ResolveExprNames() except that it works for an expression

Changes to src/sqliteInt.h.

  2520   2520   #define EP_CanBeNull 0x100000 /* Can be null despite NOT NULL constraint */
  2521   2521   #define EP_Subquery  0x200000 /* Tree contains a TK_SELECT operator */
  2522   2522   #define EP_Alias     0x400000 /* Is an alias for a result set column */
  2523   2523   #define EP_Leaf      0x800000 /* Expr.pLeft, .pRight, .u.pSelect all NULL */
  2524   2524   #define EP_WinFunc  0x1000000 /* TK_FUNCTION with Expr.y.pWin set */
  2525   2525   #define EP_Subrtn   0x2000000 /* Uses Expr.y.sub. TK_IN, _SELECT, or _EXISTS */
  2526   2526   #define EP_Quoted   0x4000000 /* TK_ID was originally quoted */
         2527  +#define EP_Win      0x8000000 /* Contains window functions */
  2527   2528   
  2528   2529   /*
  2529   2530   ** The EP_Propagate mask is a set of properties that automatically propagate
  2530   2531   ** upwards into parent nodes.
  2531   2532   */
  2532   2533   #define EP_Propagate (EP_Collate|EP_Subquery|EP_HasFunc)
  2533   2534   
................................................................................
  2776   2777   #define NC_VarSelect 0x0040  /* A correlated subquery has been seen */
  2777   2778   #define NC_UEList    0x0080  /* True if uNC.pEList is used */
  2778   2779   #define NC_UAggInfo  0x0100  /* True if uNC.pAggInfo is used */
  2779   2780   #define NC_UUpsert   0x0200  /* True if uNC.pUpsert is used */
  2780   2781   #define NC_MinMaxAgg 0x1000  /* min/max aggregates seen.  See note above */
  2781   2782   #define NC_Complex   0x2000  /* True if a function or subquery seen */
  2782   2783   #define NC_AllowWin  0x4000  /* Window functions are allowed here */
         2784  +#define NC_HasWin    0x8000  /* One or more window functions seen */
  2783   2785   
  2784   2786   /*
  2785   2787   ** An instance of the following object describes a single ON CONFLICT
  2786   2788   ** clause in an upsert.
  2787   2789   **
  2788   2790   ** The pUpsertTarget field is only set if the ON CONFLICT clause includes
  2789   2791   ** conflict-target clause.  (In "ON CONFLICT(a,b)" the "(a,b)" is the

Changes to test/windowerr.tcl.

    43     43   } {
    44     44     errorsql_test 1.$tn "
    45     45     SELECT a, sum(b) OVER (
    46     46       $frame
    47     47     ) FROM t1 ORDER BY 1
    48     48     "
    49     49   }
           50  +errorsql_test 2.1 {
           51  +  SELECT sum( sum(a) OVER () ) FROM t1;
           52  +}
           53  +
           54  +errorsql_test 2.2 {
           55  +  SELECT sum(a) OVER () AS xyz FROM t1 ORDER BY sum(xyz);
           56  +}
           57  +
    50     58   
    51     59   finish_test
    52     60   

Changes to test/windowerr.test.

    81     81   
    82     82   # PG says ERROR:  RANGE with offset PRECEDING/FOLLOWING requires exactly one ORDER BY column
    83     83   do_test 1.8 { catch { execsql {
    84     84     SELECT a, sum(b) OVER (
    85     85       PARTITION BY a RANGE BETWEEN  1 PRECEDING AND 1 FOLLOWING
    86     86     ) FROM t1 ORDER BY 1
    87     87   } } } 1
           88  +
           89  +# PG says ERROR:  aggregate function calls cannot contain window function calls
           90  +do_test 2.1 { catch { execsql {
           91  +  SELECT sum( sum(a) OVER () ) FROM t1;
           92  +} } } 1
           93  +
           94  +# PG says ERROR:  column "xyz" does not exist
           95  +do_test 2.2 { catch { execsql {
           96  +  SELECT sum(a) OVER () AS xyz FROM t1 ORDER BY sum(xyz);
           97  +} } } 1
    88     98   
    89     99   finish_test