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: |
0f3f8fcde1a535bcf93e23a68d2411c2 |
| 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
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
|