The standard, section 6.10, <window function>.
syntax rules says:
5) OF shall not contain an outer reference or a <query expression>.
select rank() over (partition by a order by (select max(a) from t2 ) from ten;
|
- PostgreSQL, Oracle, MS SQL: allow this.
7) If <ntile function>, <lead or lag function>, RANK or DENSE_RANK is specified, then the window ordering clause WOC of WDX shall be present.
select rank() over (partition by a) from ten;
|
- MariaDB: query works
- PostgreSQL: query works
- Oracle: ORA-30485: missing ORDER BY expression in the window specification
- MS SQL: The function 'rank' must have an OVER clause with ORDER BY.
7) b) The window framing clause of WDX shall not be present.
select rank() over (order by a rows between 1 preceding and 1 following) from ten;
|
MariaDB: ok (framing ignored)
PG: Ok (framing ignored)
Oracle: ORA-00907: missing right parenthesis
MS SQL: The function 'rank' may not have a window frame.
ORA-30486: invalid window aggregation group in the window specification
11 a) iii) WDEFOC shall contain a single <sort key> SK.
create table t1 (a int, b int, c int);
|
insert into t1 values (1,1,1);
|
insert into t1 values (2,2,2);
|
select count(*) over (order by a,b range between unbounded preceding and
|
current row) from t1;
|
PG, MS-SQL: ok
Oracle: ORA-30486: invalid window aggregation group in the window specification
MariaDB: crash.
The declared type of SK shall be numeric, datetime, or interval
create table t1 (a varchar(32), b int, c int);
|
insert into t1 values ('aaa',1,1);
|
insert into t1 values ('bbbb',2,2);
|
select count(*) over (order by a range between unbounded preceding and
|
current row) from t1;
|
PG, MS-SQL: OK
Oracle: OK
Oracle, with "1 PRECEDING": "ORA-00902: invalid datatype"
MariaDB: crash.
If RANGE is specified, then ... i) 2) A) I) If V1P is negative or the null value, then an
exception condition is raised: data exception — invalid preceding or following size in window function.
select count(*) over (rows between -1 preceding and current row ) from t1;
|
MariaDB: handled by the parser. One gets "Integer is required for ROWS-type frame" error.
PG: ERROR: frame starting offset must not be negative
MS-SQL: Incorrect syntax near 'rows'.
Oracle: No error
Task
Major
MDEV-19052 Range-type window frame supports only numeric datatype
The standard, section 6.10, <window function>.
syntax rules says:
select rank() over (partition by a order by (select max(a) from t2 ) from ten;select rank() over (partition by a) from ten;select rank() over (order by a rows between 1 preceding and 1 following) from ten;MariaDB: ok (framing ignored)
PG: Ok (framing ignored)
Oracle: ORA-00907: missing right parenthesis
MS SQL: The function 'rank' may not have a window frame.
ORA-30486: invalid window aggregation group in the window specification
create table t1 (a int, b int, c int);insert into t1 values (1,1,1);insert into t1 values (2,2,2);select count(*) over (order by a,b range between unbounded preceding andcurrent row) from t1;PG, MS-SQL: ok
Oracle: ORA-30486: invalid window aggregation group in the window specification
MariaDB: crash.
create table t1 (a varchar(32), b int, c int);insert into t1 values ('aaa',1,1);insert into t1 values ('bbbb',2,2);select count(*) over (order by a range between unbounded preceding andcurrent row) from t1;PG, MS-SQL: OK
Oracle: OK
Oracle, with "1 PRECEDING": "ORA-00902: invalid datatype"
MariaDB: crash.
select count(*) over (rows between -1 preceding and current row ) from t1;MariaDB: handled by the parser. One gets "Integer is required for ROWS-type frame" error.
PG: ERROR: frame starting offset must not be negative
MS-SQL: Incorrect syntax near 'rows'.
Oracle: No error