/ Check-in [0f3f8fcd]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Ensure an error is returned if the user specifies an unsupported frame type.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 0f3f8fcde1a535bcf93e23a68d2411c21785d8c0cac1f9481a06e7225755cdbc
User & Date: dan 2018-07-06 14:15:49
Context
2018-07-06
14:31
Also disallow non-constant expressions in "<expr> PRECEDING" or "<expr> FOLLOWING" clauses. check-in: a6dffecc user: dan tags: trunk
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
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/window.c.

833
834
835
836
837
838
839















840
841
842


843

844
845
846
847
848
849
850
  Parse *pParse, 
  int eType,
  int eStart, Expr *pStart,
  int eEnd, Expr *pEnd
){
  Window *pWin = 0;
















  if( eType==TK_RANGE && (pStart || pEnd) ){
    sqlite3ErrorMsg(pParse, "RANGE %s is only supported with UNBOUNDED",
        (pStart ? "PRECEDING" : "FOLLOWING")


    );

  }else{
    pWin = (Window*)sqlite3DbMallocZero(pParse->db, sizeof(Window));
  }

  if( pWin ){
    assert( eType );
    pWin->eType = eType;







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
<
<
>
>
|
>







833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855


856
857
858
859
860
861
862
863
864
865
866
  Parse *pParse, 
  int eType,
  int eStart, Expr *pStart,
  int eEnd, Expr *pEnd
){
  Window *pWin = 0;

  /* If a frame is declared "RANGE" (not "ROWS"), then it may not use
  ** either "<expr> PRECEDING" or "<expr> FOLLOWING". Additionally, the
  ** starting boundary type may not occur earlier in the following list than
  ** the ending boundary type:
  **
  **   UNBOUNDED PRECEDING
  **   <expr> PRECEDING
  **   CURRENT ROW
  **   <expr> FOLLOWING
  **   UNBOUNDED FOLLOWING
  **
  ** The parser ensures that "UNBOUNDED PRECEDING" cannot be used as an ending
  ** boundary, and than "UNBOUNDED FOLLOWING" cannot be used as a starting
  ** frame boundary.
  */
  if( eType==TK_RANGE && (pStart || pEnd) 


   || (eStart==TK_CURRENT && eEnd==TK_PRECEDING)
   || (eStart==TK_FOLLOWING && (eEnd==TK_PRECEDING || eEnd==TK_CURRENT))
  ){
    sqlite3ErrorMsg(pParse, "unsupported window-frame type");
  }else{
    pWin = (Window*)sqlite3DbMallocZero(pParse->db, sizeof(Window));
  }

  if( pWin ){
    assert( eType );
    pWin->eType = eType;

Changes to test/window6.test.

217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
...
244
245
246
247
248
249
250














251
252
253
} {
  1 1  2 1,2  3 1,2,3  4 2,3,4  5 3,4,5
}
do_catchsql_test 9.1 {
  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
  SELECT x, group_concat(x) OVER (ORDER BY x RANGE 2 PRECEDING)
  FROM c;
} {1 {RANGE PRECEDING is only supported with UNBOUNDED}}

do_catchsql_test 9.2 {
  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
  SELECT x, group_concat(x) OVER (ORDER BY x RANGE BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING)
  FROM c;
} {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 {
................................................................................
  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








|





|







 







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



217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
...
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
} {
  1 1  2 1,2  3 1,2,3  4 2,3,4  5 3,4,5
}
do_catchsql_test 9.1 {
  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
  SELECT x, group_concat(x) OVER (ORDER BY x RANGE 2 PRECEDING)
  FROM c;
} {1 {unsupported window-frame type}}

do_catchsql_test 9.2 {
  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
  SELECT x, group_concat(x) OVER (ORDER BY x RANGE BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING)
  FROM c;
} {1 {unsupported window-frame type}}

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 {
................................................................................
  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}}

foreach {tn frame} {
  1 "BETWEEN CURRENT ROW AND 4 PRECEDING"
  2 "4 FOLLOWING"
  3 "BETWEEN 4 FOLLOWING AND CURRENT ROW"
  4 "BETWEEN 4 FOLLOWING AND 2 PRECEDING"
} {
  do_catchsql_test 9.7.$tn "
    WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
    SELECT count() OVER (
        ORDER BY x ROWS $frame 
    ) FROM c;
  " {1 {unsupported window-frame type}}
}

finish_test