/ Check-in [943bccd2]
Login

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

Overview
Comment:Fix problems with using window functions in CREATE VIEW statements.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | exp-window-functions
Files: files | file ages | folders
SHA3-256: 943bccd2a6bd4cf3e0534c1fa46885bfa2ba7b780ddcdff9f1ea4cbb3f04e786
User & Date: dan 2018-06-18 20:34:43
Context
2018-06-19
17:13
Fix a problem with using min() or max() as a window function. check-in: 801074ce user: dan tags: exp-window-functions
2018-06-18
20:34
Fix problems with using window functions in CREATE VIEW statements. check-in: 943bccd2 user: dan tags: exp-window-functions
17:36
Ensure that all four callbacks are provided when registering a window function (otherwise SQLITE_MISUSE is returned). check-in: 5720dcd8 user: dan tags: exp-window-functions
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

  1112   1112   ** The size of the structure can be found by masking the return value
  1113   1113   ** of this routine with 0xfff.  The flags can be found by masking the
  1114   1114   ** return value with EP_Reduced|EP_TokenOnly.
  1115   1115   **
  1116   1116   ** Note that with flags==EXPRDUP_REDUCE, this routines works on full-size
  1117   1117   ** (unreduced) Expr objects as they or originally constructed by the parser.
  1118   1118   ** During expression analysis, extra information is computed and moved into
  1119         -** later parts of teh Expr object and that extra information might get chopped
         1119  +** later parts of the Expr object and that extra information might get chopped
  1120   1120   ** off if the expression is reduced.  Note also that it does not work to
  1121   1121   ** make an EXPRDUP_REDUCE copy of a reduced expression.  It is only legal
  1122   1122   ** to reduce a pristine expression tree from the parser.  The implementation
  1123   1123   ** of dupedExprStructSize() contain multiple assert() statements that attempt
  1124   1124   ** to enforce this constraint.
  1125   1125   */
  1126   1126   static int dupedExprStructSize(Expr *p, int flags){
  1127   1127     int nSize;
  1128   1128     assert( flags==EXPRDUP_REDUCE || flags==0 ); /* Only one flag value allowed */
  1129   1129     assert( EXPR_FULLSIZE<=0xfff );
  1130   1130     assert( (0xfff & (EP_Reduced|EP_TokenOnly))==0 );
  1131         -  if( 0==flags || p->op==TK_SELECT_COLUMN ){
         1131  +  if( 0==flags || p->op==TK_SELECT_COLUMN || p->pWin ){
  1132   1132       nSize = EXPR_FULLSIZE;
  1133   1133     }else{
  1134   1134       assert( !ExprHasProperty(p, EP_TokenOnly|EP_Reduced) );
  1135   1135       assert( !ExprHasProperty(p, EP_FromJoin) ); 
  1136   1136       assert( !ExprHasProperty(p, EP_MemToken) );
  1137   1137       assert( !ExprHasProperty(p, EP_NoReduce) );
  1138   1138       if( p->pLeft || p->x.pList ){
................................................................................
  1476   1476       pNew->iOffset = 0;
  1477   1477       pNew->selFlags = p->selFlags & ~SF_UsesEphemeral;
  1478   1478       pNew->addrOpenEphm[0] = -1;
  1479   1479       pNew->addrOpenEphm[1] = -1;
  1480   1480       pNew->nSelectRow = p->nSelectRow;
  1481   1481       pNew->pWith = withDup(db, p->pWith);
  1482   1482       pNew->pWin = 0;
  1483         -    pNew->pWinDefn = 0;           /* TODO!! */
         1483  +    pNew->pWinDefn = sqlite3WindowListDup(db, p->pWinDefn);
  1484   1484       sqlite3SelectSetName(pNew, p->zSelName);
  1485   1485       *pp = pNew;
  1486   1486       pp = &pNew->pPrior;
  1487   1487       pNext = pNew;
  1488   1488     }
  1489   1489   
  1490   1490     return pRet;

Changes to src/sqliteInt.h.

  3524   3524   int sqlite3WindowCompare(Parse*, Window*, Window*);
  3525   3525   void sqlite3WindowCodeInit(Parse*, Window*);
  3526   3526   void sqlite3WindowCodeStep(Parse*, Select*, WhereInfo*, int, int);
  3527   3527   int sqlite3WindowRewrite(Parse*, Select*);
  3528   3528   int sqlite3ExpandSubquery(Parse*, struct SrcList_item*);
  3529   3529   void sqlite3WindowUpdate(Parse*, Window*, Window*, FuncDef*);
  3530   3530   Window *sqlite3WindowDup(sqlite3 *db, Expr *pOwner, Window *p);
         3531  +Window *sqlite3WindowListDup(sqlite3 *db, Window *p);
  3531   3532   void sqlite3WindowFunctions(void);
  3532   3533   
  3533   3534   /*
  3534   3535   ** Assuming zIn points to the first byte of a UTF-8 character,
  3535   3536   ** advance zIn to point to the first byte of the next UTF-8 character.
  3536   3537   */
  3537   3538   #define SQLITE_SKIP_UTF8(zIn) {                        \

Changes to src/window.c.

   499    499   */
   500    500   void sqlite3WindowUpdate(
   501    501     Parse *pParse, 
   502    502     Window *pList,                  /* List of named windows for this SELECT */
   503    503     Window *pWin,                   /* Window frame to update */
   504    504     FuncDef *pFunc                  /* Window function definition */
   505    505   ){
   506         -  if( pWin->zName ){
          506  +  if( pWin->zName && pWin->eType==0 ){
   507    507       Window *p;
   508    508       for(p=pList; p; p=p->pNextWin){
   509    509         if( sqlite3StrICmp(p->zName, pWin->zName)==0 ) break;
   510    510       }
   511    511       if( p==0 ){
   512    512         sqlite3ErrorMsg(pParse, "no such window: %s", pWin->zName);
   513    513         return;
................................................................................
   514    514       }
   515    515       pWin->pPartition = sqlite3ExprListDup(pParse->db, p->pPartition, 0);
   516    516       pWin->pOrderBy = sqlite3ExprListDup(pParse->db, p->pOrderBy, 0);
   517    517       pWin->pStart = sqlite3ExprDup(pParse->db, p->pStart, 0);
   518    518       pWin->pEnd = sqlite3ExprDup(pParse->db, p->pEnd, 0);
   519    519       pWin->eStart = p->eStart;
   520    520       pWin->eEnd = p->eEnd;
          521  +    pWin->eType = p->eType;
   521    522     }
   522    523     if( pFunc->funcFlags & SQLITE_FUNC_WINDOW ){
   523    524       sqlite3 *db = pParse->db;
   524    525       if( pWin->pFilter ){
   525    526         sqlite3ErrorMsg(pParse, 
   526    527             "FILTER clause may only be used with aggregate window functions"
   527    528         );
................................................................................
   796    797     int eType,
   797    798     int eStart, Expr *pStart,
   798    799     int eEnd, Expr *pEnd
   799    800   ){
   800    801     Window *pWin = (Window*)sqlite3DbMallocZero(pParse->db, sizeof(Window));
   801    802   
   802    803     if( pWin ){
          804  +    assert( eType );
   803    805       pWin->eType = eType;
   804    806       pWin->eStart = eStart;
   805    807       pWin->eEnd = eEnd;
   806    808       pWin->pEnd = pEnd;
   807    809       pWin->pStart = pStart;
   808    810     }else{
   809    811       sqlite3ExprDelete(pParse->db, pEnd);
................................................................................
  1914   1916   ** pOwner.
  1915   1917   */
  1916   1918   Window *sqlite3WindowDup(sqlite3 *db, Expr *pOwner, Window *p){
  1917   1919     Window *pNew = 0;
  1918   1920     if( p ){
  1919   1921       pNew = sqlite3DbMallocZero(db, sizeof(Window));
  1920   1922       if( pNew ){
         1923  +      pNew->zName = sqlite3DbStrDup(db, p->zName);
  1921   1924         pNew->pFilter = sqlite3ExprDup(db, p->pFilter, 0);
  1922   1925         pNew->pPartition = sqlite3ExprListDup(db, p->pPartition, 0);
  1923   1926         pNew->pOrderBy = sqlite3ExprListDup(db, p->pOrderBy, 0);
  1924   1927         pNew->eType = p->eType;
  1925   1928         pNew->eEnd = p->eEnd;
  1926   1929         pNew->eStart = p->eStart;
  1927   1930         pNew->pStart = sqlite3ExprDup(db, p->pStart, 0);
  1928   1931         pNew->pEnd = sqlite3ExprDup(db, p->pEnd, 0);
  1929   1932         pNew->pOwner = pOwner;
  1930   1933       }
  1931   1934     }
  1932   1935     return pNew;
  1933   1936   }
         1937  +
         1938  +/*
         1939  +** Return a copy of the linked list of Window objects passed as the
         1940  +** second argument.
         1941  +*/
         1942  +Window *sqlite3WindowListDup(sqlite3 *db, Window *p){
         1943  +  Window *pWin;
         1944  +  Window *pRet = 0;
         1945  +  Window **pp = &pRet;
         1946  +
         1947  +  for(pWin=p; pWin; pWin=pWin->pNextWin){
         1948  +    *pp = sqlite3WindowDup(db, 0, pWin);
         1949  +    if( *pp==0 ) break;
         1950  +    pp = &((*pp)->pNextWin);
         1951  +  }
         1952  +
         1953  +  return pRet;
         1954  +}
  1934   1955   
  1935   1956   /*
  1936   1957   ** sqlite3WhereBegin() has already been called for the SELECT statement 
  1937   1958   ** passed as the second argument when this function is invoked. It generates
  1938   1959   ** code to populate the Window.regResult register for each window function and
  1939   1960   ** invoke the sub-routine at instruction addrGosub once for each row.
  1940   1961   ** This function calls sqlite3WhereEnd() before returning. 

Changes to test/window1.test.

   265    265     4 6 8   6 8 10   8 10 default   10 {} default   {} {} default
   266    266   }
   267    267   
   268    268   do_execsql_test 7.3 {
   269    269     SELECT row_number() OVER (ORDER BY x) FROM t1
   270    270   } {1 2 3 4 5}
   271    271   
          272  +breakpoint
   272    273   do_execsql_test 7.4 {
   273    274     SELECT 
   274    275       row_number() OVER win,
   275    276       lead(x) OVER win
   276    277     FROM t1
   277    278     WINDOW win AS (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
   278    279   } {1 3  2 5  3 7  4 9   5 {}}
          280  +
          281  +#-------------------------------------------------------------------------
          282  +# Attempt to use a window function in a view.
          283  +#
          284  +do_execsql_test 8.0 {
          285  +  CREATE TABLE t3(a, b, c);
          286  +
          287  +  WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<6 )
          288  +  INSERT INTO t3 SELECT i, i, i FROM s;
          289  +
          290  +  CREATE VIEW v1 AS SELECT
          291  +    sum(b) OVER (ORDER BY c),
          292  +    min(b) OVER (ORDER BY c),
          293  +    max(b) OVER (ORDER BY c)
          294  +  FROM t3;
          295  +
          296  +  CREATE VIEW v2 AS SELECT
          297  +    sum(b) OVER win,
          298  +    min(b) OVER win,
          299  +    max(b) OVER win
          300  +  FROM t3
          301  +  WINDOW win AS (ORDER BY c);
          302  +}
          303  +
          304  +do_execsql_test 8.1.1 {
          305  +  SELECT * FROM v1
          306  +} {1 1 1  3 1 2  6 1 3  10 1 4  15 1 5  21 1 6}
          307  +do_execsql_test 8.1.2 {
          308  +  SELECT * FROM v2
          309  +} {1 1 1  3 1 2  6 1 3  10 1 4  15 1 5  21 1 6}
          310  +
          311  +db close
          312  +sqlite3 db test.db
          313  +do_execsql_test 8.2.1 {
          314  +  SELECT * FROM v1
          315  +} {1 1 1  3 1 2  6 1 3  10 1 4  15 1 5  21 1 6}
          316  +do_execsql_test 8.2.2 {
          317  +  SELECT * FROM v2
          318  +} {1 1 1  3 1 2  6 1 3  10 1 4  15 1 5  21 1 6}
          319  +
          320  +
   279    321   
   280    322   finish_test
   281    323