SQLite

Check-in [0f3f8fcde1]
Login

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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 0f3f8fcde1a535bcf93e23a68d2411c21785d8c0cac1f9481a06e7225755cdbc
User & Date: dan 2018-07-06 14:15:49.619
Context
2018-07-06
14:31
Also disallow non-constant expressions in "<expr> PRECEDING" or "<expr> FOLLOWING" clauses. (check-in: a6dffecc6b user: dan tags: trunk)
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)
Changes
Unified Diff 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
238
239
240
241
242
243
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 {
  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









|





|




















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




217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
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 {
  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}}

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