/ Check-in [6413e38a]
Login

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

Overview
Comment:Fix another issue to do with window-functions in aggregate queries.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | exp-window-functions
Files: files | file ages | folders
SHA3-256:6413e38a174044c28fa9b8b937e6c972d144547a246e6f2882e782538300d042
User & Date: dan 2018-06-12 20:53:38
Context
2018-06-13
20:29
Fix problems with "RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING" window frames. check-in: c34f31db user: dan tags: exp-window-functions
2018-06-12
20:53
Fix another issue to do with window-functions in aggregate queries. check-in: 6413e38a user: dan tags: exp-window-functions
18:40
Fix some problems with using window-functions in aggregate queries. check-in: fe7081e0 user: dan tags: exp-window-functions
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/btree.c.

  5181   5181       assert( pCur->pgnoRoot==0 || pCur->pPage->nCell==0 );
  5182   5182       *pRes = 1;
  5183   5183       rc = SQLITE_OK;
  5184   5184     }
  5185   5185     return rc;
  5186   5186   }
  5187   5187   
         5188  +/*
         5189  +** This function is a no-op if cursor pCur does not point to a valid row.
         5190  +** Otherwise, if pCur is valid, configure it so that the next call to
         5191  +** sqlite3BtreeNext() is a no-op.
         5192  +*/
  5188   5193   void sqlite3BtreeSkipNext(BtCursor *pCur){
  5189   5194     if( pCur->eState==CURSOR_VALID ){
  5190   5195       pCur->eState = CURSOR_SKIPNEXT;
  5191   5196       pCur->skipNext = 1;
  5192   5197     }
  5193   5198   }
  5194   5199   

Changes to src/window.c.

   125    125   **   window frame is sometimes modified before the SELECT statement is
   126    126   **   rewritten. For example, regardless of the specified window frame, the
   127    127   **   row_number() function always uses:
   128    128   **
   129    129   **     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
   130    130   **
   131    131   **   See sqlite3WindowUpdate() for details.
          132  +**
          133  +**   As well as some of the built-in window functions, aggregate window
          134  +**   functions min() and max() are implemented using VDBE instructions if
          135  +**   the start of the window frame is declared as anything other than 
          136  +**   UNBOUNDED PRECEDING.
   132    137   */
   133    138   
   134    139   /*
   135    140   ** Implementation of built-in window function row_number(). Assumes that the
   136    141   ** window frame has been coerced to:
   137    142   **
   138    143   **   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
................................................................................
   467    472       WINDOWFUNC(first_value, 1, 0),
   468    473       WINDOWFUNC(lead, 1, 0), WINDOWFUNC(lead, 2, 0), WINDOWFUNC(lead, 3, 0),
   469    474       WINDOWFUNC(lag, 1, 0),  WINDOWFUNC(lag, 2, 0),  WINDOWFUNC(lag, 3, 0),
   470    475     };
   471    476     sqlite3InsertBuiltinFuncs(aWindowFuncs, ArraySize(aWindowFuncs));
   472    477   }
   473    478   
          479  +/*
          480  +** This function is called immediately after resolving the function name
          481  +** for a window function within a SELECT statement. Argument pList is a
          482  +** linked list of WINDOW definitions for the current SELECT statement.
          483  +** Argument pFunc is the function definition just resolved and pWin
          484  +** is the Window object representing the associated OVER clause. This
          485  +** function updates the contents of pWin as follows:
          486  +**
          487  +**   * If the OVER clause refered to a named window (as in "max(x) OVER win"),
          488  +**     search list pList for a matching WINDOW definition, and update pWin
          489  +**     accordingly. If no such WINDOW clause can be found, leave an error
          490  +**     in pParse.
          491  +**
          492  +**   * If the function is a built-in window function that requires the
          493  +**     window to be coerced (see "BUILT-IN WINDOW FUNCTIONS" at the top
          494  +**     of this file), pWin is updated here.
          495  +*/
   474    496   void sqlite3WindowUpdate(
   475    497     Parse *pParse, 
   476         -  Window *pList, 
   477         -  Window *pWin, 
   478         -  FuncDef *pFunc
          498  +  Window *pList,                  /* List of named windows for this SELECT */
          499  +  Window *pWin,                   /* Window frame to update */
          500  +  FuncDef *pFunc                  /* Window function definition */
   479    501   ){
   480    502     if( pWin->zName ){
   481    503       Window *p;
   482    504       for(p=pList; p; p=p->pNextWin){
   483    505         if( sqlite3StrICmp(p->zName, pWin->zName)==0 ) break;
   484    506       }
   485    507       if( p==0 ){
................................................................................
   519    541         pWin->eStart = TK_UNBOUNDED;
   520    542         pWin->eEnd = TK_CURRENT;
   521    543       }
   522    544     }
   523    545     pWin->pFunc = pFunc;
   524    546   }
   525    547   
          548  +/*
          549  +** Context object passed through sqlite3WalkExprList() to
          550  +** selectWindowRewriteExprCb() by selectWindowRewriteEList().
          551  +*/
   526    552   typedef struct WindowRewrite WindowRewrite;
   527    553   struct WindowRewrite {
   528    554     Window *pWin;
   529    555     ExprList *pSub;
   530    556   };
   531    557   
   532         -static int selectWindowRewriteSelectCb(Walker *pWalker, Select *pSelect){
   533         -  return WRC_Prune;
   534         -}
   535         -
          558  +/*
          559  +** Callback function used by selectWindowRewriteEList(). If necessary,
          560  +** this function appends to the output expression-list and updates 
          561  +** expression (*ppExpr) in place.
          562  +*/
   536    563   static int selectWindowRewriteExprCb(Walker *pWalker, Expr *pExpr){
   537    564     struct WindowRewrite *p = pWalker->u.pRewrite;
   538    565     Parse *pParse = pWalker->pParse;
   539    566   
   540    567     switch( pExpr->op ){
   541    568   
   542    569       case TK_FUNCTION:
................................................................................
   574    601   
   575    602       default: /* no-op */
   576    603         break;
   577    604     }
   578    605   
   579    606     return WRC_Continue;
   580    607   }
          608  +static int selectWindowRewriteSelectCb(Walker *pWalker, Select *pSelect){
          609  +  return WRC_Prune;
          610  +}
   581    611   
          612  +
          613  +/*
          614  +** Iterate through each expression in expression-list pEList. For each:
          615  +**
          616  +**   * TK_COLUMN,
          617  +**   * aggregate function, or
          618  +**   * window function with a Window object that is not a member of the 
          619  +**     linked list passed as the second argument (pWin)
          620  +**
          621  +** Append the node to output expression-list (*ppSub). And replace it
          622  +** with a TK_COLUMN that reads the (N-1)th element of table 
          623  +** pWin->iEphCsr, where N is the number of elements in (*ppSub) after
          624  +** appending the new one.
          625  +*/
   582    626   static int selectWindowRewriteEList(
   583    627     Parse *pParse, 
   584    628     Window *pWin,
   585    629     ExprList *pEList,               /* Rewrite expressions in this list */
   586    630     ExprList **ppSub                /* IN/OUT: Sub-select expression-list */
   587    631   ){
   588    632     Walker sWalker;
................................................................................
   602    646   
   603    647     rc = sqlite3WalkExprList(&sWalker, pEList);
   604    648   
   605    649     *ppSub = sRewrite.pSub;
   606    650     return rc;
   607    651   }
   608    652   
          653  +/*
          654  +** Append a copy of each expression in expression-list pAppend to
          655  +** expression list pList. Return a pointer to the result list.
          656  +*/
   609    657   static ExprList *exprListAppendList(
   610    658     Parse *pParse,          /* Parsing context */
   611    659     ExprList *pList,        /* List to which to append. Might be NULL */
   612    660     ExprList *pAppend       /* List of values to append. Might be NULL */
   613    661   ){
   614    662     if( pAppend ){
   615    663       int i;
................................................................................
   623    671     return pList;
   624    672   }
   625    673   
   626    674   /*
   627    675   ** If the SELECT statement passed as the second argument does not invoke
   628    676   ** any SQL window functions, this function is a no-op. Otherwise, it 
   629    677   ** rewrites the SELECT statement so that window function xStep functions
   630         -** are invoked in the correct order. The simplest version of the 
   631         -** transformation is:
   632         -**
   633         -**   SELECT win(args...) OVER (<list1>) FROM <src> ORDER BY <list2>
   634         -**
   635         -** to
   636         -**
   637         -**   SELECT win(args...) FROM (
   638         -**     SELECT args... FROM <src> ORDER BY <list1>
   639         -**   ) ORDER BY <list2>
   640         -**
   641         -** where <src> may contain WHERE, GROUP BY and HAVING clauses, and <list1>
   642         -** is the concatenation of the PARTITION BY and ORDER BY clauses in the
   643         -** OVER clause.
   644         -**
          678  +** are invoked in the correct order as described under "SELECT REWRITING"
          679  +** at the top of this file.
   645    680   */
   646    681   int sqlite3WindowRewrite(Parse *pParse, Select *p){
   647    682     int rc = SQLITE_OK;
   648    683     if( p->pWin ){
   649    684       Vdbe *v = sqlite3GetVdbe(pParse);
   650    685       int i;
   651    686       sqlite3 *db = pParse->db;
................................................................................
   722    757   
   723    758       sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pMWin->iEphCsr, pSublist->nExpr);
   724    759     }
   725    760   
   726    761     return rc;
   727    762   }
   728    763   
          764  +/*
          765  +** Free the Window object passed as the second argument.
          766  +*/
   729    767   void sqlite3WindowDelete(sqlite3 *db, Window *p){
   730    768     if( p ){
   731    769       sqlite3ExprDelete(db, p->pFilter);
   732    770       sqlite3ExprListDelete(db, p->pPartition);
   733    771       sqlite3ExprListDelete(db, p->pOrderBy);
   734    772       sqlite3ExprDelete(db, p->pEnd);
   735    773       sqlite3ExprDelete(db, p->pStart);
   736    774       sqlite3DbFree(db, p->zName);
   737    775       sqlite3DbFree(db, p);
   738    776     }
   739    777   }
   740    778   
          779  +/*
          780  +** Free the linked list of Window objects starting at the second argument.
          781  +*/
   741    782   void sqlite3WindowListDelete(sqlite3 *db, Window *p){
   742    783     while( p ){
   743    784       Window *pNext = p->pNextWin;
   744    785       sqlite3WindowDelete(db, p);
   745    786       p = pNext;
   746    787     }
   747    788   }
   748    789   
          790  +/*
          791  +** Allocate and return a new Window object.
          792  +*/
   749    793   Window *sqlite3WindowAlloc(
   750    794     Parse *pParse, 
   751    795     int eType,
   752    796     int eStart, Expr *pStart,
   753    797     int eEnd, Expr *pEnd
   754    798   ){
   755    799     Window *pWin = (Window*)sqlite3DbMallocZero(pParse->db, sizeof(Window));
................................................................................
   764    808       sqlite3ExprDelete(pParse->db, pEnd);
   765    809       sqlite3ExprDelete(pParse->db, pStart);
   766    810     }
   767    811   
   768    812     return pWin;
   769    813   }
   770    814   
          815  +/*
          816  +** Attach window object pWin to expression p.
          817  +*/
   771    818   void sqlite3WindowAttach(Parse *pParse, Expr *p, Window *pWin){
   772    819     if( p ){
   773    820       p->pWin = pWin;
   774    821       if( pWin ) pWin->pOwner = p;
   775    822     }else{
   776    823       sqlite3WindowDelete(pParse->db, pWin);
   777    824     }

Changes to test/window4.tcl.

   144    144     INSERT INTO ttt VALUES(8, 2, 4);
   145    145     INSERT INTO ttt VALUES(9, 3, 5);
   146    146   }
   147    147   
   148    148   execsql_test 4.1 {
   149    149     SELECT max(c), max(b) OVER (ORDER BY b) FROM ttt GROUP BY b;
   150    150   }
          151  +
          152  +execsql_test 4.2 {
          153  +  SELECT max(b) OVER (ORDER BY max(c)) FROM ttt GROUP BY b;
          154  +}
          155  +
          156  +execsql_test 4.3 {
          157  +  SELECT abs(max(b) OVER (ORDER BY b)) FROM ttt GROUP BY b;
          158  +}
   151    159   
   152    160   
   153    161   finish_test
   154    162   

Changes to test/window4.test.

   226    226     INSERT INTO ttt VALUES(8, 2, 4);
   227    227     INSERT INTO ttt VALUES(9, 3, 5);
   228    228   } {}
   229    229   
   230    230   do_execsql_test 4.1 {
   231    231     SELECT max(c), max(b) OVER (ORDER BY b) FROM ttt GROUP BY b;
   232    232   } {3 1   4 2   5 3}
          233  +
          234  +do_execsql_test 4.2 {
          235  +  SELECT max(b) OVER (ORDER BY max(c)) FROM ttt GROUP BY b;
          236  +} {1   2   3}
          237  +
          238  +do_execsql_test 4.3 {
          239  +  SELECT abs(max(b) OVER (ORDER BY b)) FROM ttt GROUP BY b;
          240  +} {1   2   3}
   233    241   
   234    242   finish_test