{code:sql}
create table t1 (a int, b varchar(5));
insert into t1 values (1,'a'), (2, 'b'), (3, 'c');
select sum(a) over (order by b range between unbounded preceding and current row) from t1;
{code}
{noformat}
MariaDB [test]> select sum(a) over (order by b range between unbounded preceding and current row) from t1;
ERROR 4021 (HY000): Numeric datatype is required for RANGE-type frame
{noformat}
Mysql/Postgres return:
{noformat}
mysql> select sum(a) over (order by b range between unbounded preceding and current row) from t1;
+----------------------------------------------------------------------------+
| sum(a) over (order by b range between unbounded preceding and current row) |
+----------------------------------------------------------------------------+
| 1 |
| 3 |
| 6 |
+----------------------------------------------------------------------------+
3 rows in set (0.00 sec)
{noformat}
{code:sql}
create table t1 (a int, b varchar(5));
insert into t1 values (1,'a'), (2, 'b'), (3, 'c');
select sum(a) over (order by b range between unbounded preceding and current row) from t1;
{code}
{noformat}
MariaDB [test]> select sum(a) over (order by b range between unbounded preceding and current row) from t1;
ERROR 4021 (HY000): Numeric datatype is required for RANGE-type frame
{noformat}
Mysql/Postgres return:
{noformat}
mysql> select sum(a) over (order by b range between unbounded preceding and current row) from t1;
+----------------------------------------------------------------------------+
| sum(a) over (order by b range between unbounded preceding and current row) |
+----------------------------------------------------------------------------+
| 1 |
| 3 |
| 6 |
+----------------------------------------------------------------------------+
3 rows in set (0.00 sec)
{noformat}
Also range-type frame always expects ORDER BY clause:
{noformat}
MariaDB [test]> select sum(a) over (range between unbounded preceding and current row) from t1;
ERROR 4019 (HY000): RANGE-type frame requires ORDER BY clause with single sort key
{noformat}
Mysql 8.0.15: {noformat}
mysql> select sum(a) over (range between unbounded preceding and current row) from t1;
+-----------------------------------------------------------------+
| sum(a) over (range between unbounded preceding and current row) |
+-----------------------------------------------------------------+
| 6 |
| 6 |
| 6 |
+-----------------------------------------------------------------+
3 rows in set (0.00 sec)
{noformat}
Alice Sherepa
added a comment -
create table t1 (a int , b int );
insert into t1 values (1,1), (1, 2), (1, 3), (2,1), (2,2);
select sum (a) over ( order by a,b range between unbounded preceding and current row) from t1;
RANGE-type frame requires ORDER BY clause with single sort key, while in Mysql/Postgres this works:
MariaDB [test]> select sum(a) over (order by a,b range between unbounded preceding and current row) from t1;
ERROR 4019 (HY000): RANGE-type frame requires ORDER BY clause with single sort key
------------------------------------------------------------------------------------
mysql> select sum(a) over (order by a,b range between unbounded preceding and current row) from t1;
+------------------------------------------------------------------------------+
| sum(a) over (order by a,b range between unbounded preceding and current row) |
+------------------------------------------------------------------------------+
| 1 |
| 2 |
| 3 |
| 5 |
| 7 |
+------------------------------------------------------------------------------+
5 rows in set (0.00 sec)
Not sure if it's compliant with standard or not but this change adds the following like alice put on the first comment:
"select a, b, sum(b) over (order by a desc, b range between unbounded preceding and current row) from t1" as a valid syntax.
It seems to work properly, and MySQL 8.0 and Postgres support that syntax as well (results of the query are equal as well).
Daniel Black
added a comment - Not sure if it's compliant with standard or not but this change adds the following like alice put on the first comment:
"select a, b, sum(b) over (order by a desc, b range between unbounded preceding and current row) from t1" as a valid syntax.
It seems to work properly, and MySQL 8.0 and Postgres support that syntax as well (results of the query are equal as well).
Bug
Major
MDEV-9740 Window functions: catch invalid window frame specs
RANGE-type frame requires ORDER BY clause with single sort key, while in Mysql/Postgres this works:
MariaDB [test]> select sum(a) over (order by a,b range between unbounded preceding and current row) from t1;ERROR 4019 (HY000): RANGE-type frame requires ORDER BY clause with single sort key------------------------------------------------------------------------------------mysql> select sum(a) over (order by a,b range between unbounded preceding and current row) from t1;+------------------------------------------------------------------------------+| sum(a) over (order by a,b range between unbounded preceding and current row) |+------------------------------------------------------------------------------+| 1 || 2 || 3 || 5 || 7 |+------------------------------------------------------------------------------+5 rows in set (0.00 sec)