/ Check-in [e51fdf66]
Login

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

Overview
Comment:Prevent "UNBOUNDED FOLLOWING" from being used as the starting boundary of a window-frame. And "UNBOUNDED PRECEDING" from being used as the ending boundary.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: e51fdf66a24c27ba6491391fffcc88fc5ca2ae79cfc4ab769a149b19c394e308
User & Date: dan 2018-07-06 13:48:09
Context
2018-07-06
14:15
Ensure an error is returned if the user specifies an unsupported frame type. check-in: 0f3f8fcd user: dan tags: trunk
13:48
Prevent "UNBOUNDED FOLLOWING" from being used as the starting boundary of a window-frame. And "UNBOUNDED PRECEDING" from being used as the ending boundary. check-in: e51fdf66 user: dan tags: trunk
13:25
Remove some bad assert() statements from the implementations of window functions percent_rank() and cume_dist(). check-in: 443f0c28 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/parse.y.

  1637   1637   %type filter_opt {Expr*}
  1638   1638   %destructor filter_opt {sqlite3ExprDelete(pParse->db, $$);}
  1639   1639   
  1640   1640   %type range_or_rows {int}
  1641   1641   
  1642   1642   %type frame_bound {struct FrameBound}
  1643   1643   %destructor frame_bound {sqlite3ExprDelete(pParse->db, $$.pExpr);}
         1644  +%type frame_bound_s {struct FrameBound}
         1645  +%destructor frame_bound_s {sqlite3ExprDelete(pParse->db, $$.pExpr);}
         1646  +%type frame_bound_e {struct FrameBound}
         1647  +%destructor frame_bound_e {sqlite3ExprDelete(pParse->db, $$.pExpr);}
  1644   1648   
  1645   1649   window_or_nm(A) ::= window(Z). {A = Z;}
  1646   1650   window_or_nm(A) ::= nm(Z). {
  1647   1651     A = (Window*)sqlite3DbMallocZero(pParse->db, sizeof(Window));
  1648   1652     if( A ){
  1649   1653       A->zName = sqlite3DbStrNDup(pParse->db, Z.z, Z.n);
  1650   1654     }
................................................................................
  1660   1664   
  1661   1665   part_opt(A) ::= PARTITION BY exprlist(X). { A = X; }
  1662   1666   part_opt(A) ::= .                         { A = 0; }
  1663   1667   
  1664   1668   frame_opt(A) ::= .                             { 
  1665   1669     A = sqlite3WindowAlloc(pParse, TK_RANGE, TK_UNBOUNDED, 0, TK_CURRENT, 0);
  1666   1670   }
  1667         -frame_opt(A) ::= range_or_rows(X) frame_bound(Y). { 
         1671  +frame_opt(A) ::= range_or_rows(X) frame_bound_s(Y). { 
  1668   1672     A = sqlite3WindowAlloc(pParse, X, Y.eType, Y.pExpr, TK_CURRENT, 0);
  1669   1673   }
  1670         -frame_opt(A) ::= range_or_rows(X) BETWEEN frame_bound(Y) AND frame_bound(Z). { 
         1674  +frame_opt(A) ::= range_or_rows(X) BETWEEN frame_bound_s(Y) AND frame_bound_e(Z). { 
  1671   1675     A = sqlite3WindowAlloc(pParse, X, Y.eType, Y.pExpr, Z.eType, Z.pExpr);
  1672   1676   }
  1673   1677   
  1674   1678   range_or_rows(A) ::= RANGE.   { A = TK_RANGE; }
  1675   1679   range_or_rows(A) ::= ROWS.    { A = TK_ROWS;  }
  1676   1680   
  1677         -frame_bound(A) ::= UNBOUNDED PRECEDING. { A.eType = TK_UNBOUNDED; A.pExpr = 0; }
         1681  +
         1682  +frame_bound_s(A) ::= frame_bound(X). { A = X; }
         1683  +frame_bound_s(A) ::= UNBOUNDED PRECEDING. {A.eType = TK_UNBOUNDED; A.pExpr = 0;}
         1684  +frame_bound_e(A) ::= frame_bound(X). { A = X; }
         1685  +frame_bound_e(A) ::= UNBOUNDED FOLLOWING. {A.eType = TK_UNBOUNDED; A.pExpr = 0;}
         1686  +
  1678   1687   frame_bound(A) ::= expr(X) PRECEDING.   { A.eType = TK_PRECEDING; A.pExpr = X; }
  1679   1688   frame_bound(A) ::= CURRENT ROW.         { A.eType = TK_CURRENT  ; A.pExpr = 0; }
  1680   1689   frame_bound(A) ::= expr(X) FOLLOWING.   { A.eType = TK_FOLLOWING; A.pExpr = X; }
  1681         -frame_bound(A) ::= UNBOUNDED FOLLOWING. { A.eType = TK_UNBOUNDED; A.pExpr = 0; }
  1682   1690   
  1683   1691   %type windowdefn_opt {Window*}
  1684   1692   %destructor windowdefn_opt {sqlite3WindowDelete(pParse->db, $$);}
  1685   1693   windowdefn_opt(A) ::= . { A = 0; }
  1686   1694   windowdefn_opt(A) ::= WINDOW windowdefn_list(B). { A = B; }
  1687   1695   
  1688   1696   %type over_opt {Window*}

Changes to test/window6.test.

   230    230   } {1 {RANGE FOLLOWING is only supported with UNBOUNDED}}
   231    231   
   232    232   do_catchsql_test 9.3 {
   233    233     WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
   234    234     SELECT count(DISTINCT x) OVER (ORDER BY x) FROM c;
   235    235   } {1 {DISTINCT is not supported for window functions}}
   236    236   
          237  +do_catchsql_test 9.4 {
          238  +  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
          239  +  SELECT count() OVER (ORDER BY x RANGE UNBOUNDED FOLLOWING) FROM c;
          240  +} {1 {near "FOLLOWING": syntax error}}
          241  +
          242  +do_catchsql_test 9.5 {
          243  +  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
          244  +  SELECT count() OVER (ORDER BY x RANGE BETWEEN UNBOUNDED FOLLOWING AND UNBOUNDED FOLLOWING) FROM c;
          245  +} {1 {near "FOLLOWING": syntax error}}
          246  +
          247  +do_catchsql_test 9.6 {
          248  +  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
          249  +  SELECT count() OVER (ORDER BY x RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED PRECEDING) FROM c;
          250  +} {1 {near "PRECEDING": syntax error}}
          251  +
   237    252   finish_test
   238    253