Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
-
None
Description
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; |
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
|
Mysql/Postgres return:
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)
|
Also range-type frame always expects ORDER BY clause:
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
|
Mysql 8.0.15:
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)
|
Attachments
Issue Links
- relates to
-
MDEV-9740 Window functions: catch invalid window frame specs
-
- Closed
-
Activity
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).
psergei, can I please get a review on https://github.com/MariaDB/server/pull/3334
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)
|
Thanks psergei