SQLite

Check-in [e51fdf66a2]
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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: e51fdf66a24c27ba6491391fffcc88fc5ca2ae79cfc4ab769a149b19c394e308
User & Date: dan 2018-07-06 13:48:09.421
Context
2018-07-06
14:15
Ensure an error is returned if the user specifies an unsupported frame type. (check-in: 0f3f8fcde1 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: e51fdf66a2 user: dan tags: trunk)
13:25
Remove some bad assert() statements from the implementations of window functions percent_rank() and cume_dist(). (check-in: 443f0c286f user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/parse.y.
1637
1638
1639
1640
1641
1642
1643




1644
1645
1646
1647
1648
1649
1650
%type filter_opt {Expr*}
%destructor filter_opt {sqlite3ExprDelete(pParse->db, $$);}

%type range_or_rows {int}

%type frame_bound {struct FrameBound}
%destructor frame_bound {sqlite3ExprDelete(pParse->db, $$.pExpr);}





window_or_nm(A) ::= window(Z). {A = Z;}
window_or_nm(A) ::= nm(Z). {
  A = (Window*)sqlite3DbMallocZero(pParse->db, sizeof(Window));
  if( A ){
    A->zName = sqlite3DbStrNDup(pParse->db, Z.z, Z.n);
  }







>
>
>
>







1637
1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
%type filter_opt {Expr*}
%destructor filter_opt {sqlite3ExprDelete(pParse->db, $$);}

%type range_or_rows {int}

%type frame_bound {struct FrameBound}
%destructor frame_bound {sqlite3ExprDelete(pParse->db, $$.pExpr);}
%type frame_bound_s {struct FrameBound}
%destructor frame_bound_s {sqlite3ExprDelete(pParse->db, $$.pExpr);}
%type frame_bound_e {struct FrameBound}
%destructor frame_bound_e {sqlite3ExprDelete(pParse->db, $$.pExpr);}

window_or_nm(A) ::= window(Z). {A = Z;}
window_or_nm(A) ::= nm(Z). {
  A = (Window*)sqlite3DbMallocZero(pParse->db, sizeof(Window));
  if( A ){
    A->zName = sqlite3DbStrNDup(pParse->db, Z.z, Z.n);
  }
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676


1677



1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688

part_opt(A) ::= PARTITION BY exprlist(X). { A = X; }
part_opt(A) ::= .                         { A = 0; }

frame_opt(A) ::= .                             { 
  A = sqlite3WindowAlloc(pParse, TK_RANGE, TK_UNBOUNDED, 0, TK_CURRENT, 0);
}
frame_opt(A) ::= range_or_rows(X) frame_bound(Y). { 
  A = sqlite3WindowAlloc(pParse, X, Y.eType, Y.pExpr, TK_CURRENT, 0);
}
frame_opt(A) ::= range_or_rows(X) BETWEEN frame_bound(Y) AND frame_bound(Z). { 
  A = sqlite3WindowAlloc(pParse, X, Y.eType, Y.pExpr, Z.eType, Z.pExpr);
}

range_or_rows(A) ::= RANGE.   { A = TK_RANGE; }
range_or_rows(A) ::= ROWS.    { A = TK_ROWS;  }



frame_bound(A) ::= UNBOUNDED PRECEDING. { A.eType = TK_UNBOUNDED; A.pExpr = 0; }



frame_bound(A) ::= expr(X) PRECEDING.   { A.eType = TK_PRECEDING; A.pExpr = X; }
frame_bound(A) ::= CURRENT ROW.         { A.eType = TK_CURRENT  ; A.pExpr = 0; }
frame_bound(A) ::= expr(X) FOLLOWING.   { A.eType = TK_FOLLOWING; A.pExpr = X; }
frame_bound(A) ::= UNBOUNDED FOLLOWING. { A.eType = TK_UNBOUNDED; A.pExpr = 0; }

%type windowdefn_opt {Window*}
%destructor windowdefn_opt {sqlite3WindowDelete(pParse->db, $$);}
windowdefn_opt(A) ::= . { A = 0; }
windowdefn_opt(A) ::= WINDOW windowdefn_list(B). { A = B; }

%type over_opt {Window*}







|


|






>
>
|
>
>
>



<







1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689

1690
1691
1692
1693
1694
1695
1696

part_opt(A) ::= PARTITION BY exprlist(X). { A = X; }
part_opt(A) ::= .                         { A = 0; }

frame_opt(A) ::= .                             { 
  A = sqlite3WindowAlloc(pParse, TK_RANGE, TK_UNBOUNDED, 0, TK_CURRENT, 0);
}
frame_opt(A) ::= range_or_rows(X) frame_bound_s(Y). { 
  A = sqlite3WindowAlloc(pParse, X, Y.eType, Y.pExpr, TK_CURRENT, 0);
}
frame_opt(A) ::= range_or_rows(X) BETWEEN frame_bound_s(Y) AND frame_bound_e(Z). { 
  A = sqlite3WindowAlloc(pParse, X, Y.eType, Y.pExpr, Z.eType, Z.pExpr);
}

range_or_rows(A) ::= RANGE.   { A = TK_RANGE; }
range_or_rows(A) ::= ROWS.    { A = TK_ROWS;  }


frame_bound_s(A) ::= frame_bound(X). { A = X; }
frame_bound_s(A) ::= UNBOUNDED PRECEDING. {A.eType = TK_UNBOUNDED; A.pExpr = 0;}
frame_bound_e(A) ::= frame_bound(X). { A = X; }
frame_bound_e(A) ::= UNBOUNDED FOLLOWING. {A.eType = TK_UNBOUNDED; A.pExpr = 0;}

frame_bound(A) ::= expr(X) PRECEDING.   { A.eType = TK_PRECEDING; A.pExpr = X; }
frame_bound(A) ::= CURRENT ROW.         { A.eType = TK_CURRENT  ; A.pExpr = 0; }
frame_bound(A) ::= expr(X) FOLLOWING.   { A.eType = TK_FOLLOWING; A.pExpr = X; }


%type windowdefn_opt {Window*}
%destructor windowdefn_opt {sqlite3WindowDelete(pParse->db, $$);}
windowdefn_opt(A) ::= . { A = 0; }
windowdefn_opt(A) ::= WINDOW windowdefn_list(B). { A = B; }

%type over_opt {Window*}
Changes to test/window6.test.
230
231
232
233
234
235
236















237
238
} {1 {RANGE FOLLOWING is only supported with UNBOUNDED}}

do_catchsql_test 9.3 {
  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
  SELECT count(DISTINCT x) OVER (ORDER BY x) FROM c;
} {1 {DISTINCT is not supported for window functions}}
















finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
} {1 {RANGE FOLLOWING is only supported with UNBOUNDED}}

do_catchsql_test 9.3 {
  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
  SELECT count(DISTINCT x) OVER (ORDER BY x) FROM c;
} {1 {DISTINCT is not supported for window functions}}

do_catchsql_test 9.4 {
  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
  SELECT count() OVER (ORDER BY x RANGE UNBOUNDED FOLLOWING) FROM c;
} {1 {near "FOLLOWING": syntax error}}

do_catchsql_test 9.5 {
  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
  SELECT count() OVER (ORDER BY x RANGE BETWEEN UNBOUNDED FOLLOWING AND UNBOUNDED FOLLOWING) FROM c;
} {1 {near "FOLLOWING": syntax error}}

do_catchsql_test 9.6 {
  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
  SELECT count() OVER (ORDER BY x RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED PRECEDING) FROM c;
} {1 {near "PRECEDING": syntax error}}

finish_test