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
| Field | Original Value | New Value |
|---|---|---|
| Assignee | Varun Gupta [ varun ] |
| Description |
{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} |
| Summary | Range-typy window frame supports only numeric datatype | Range-type window frame supports only numeric datatype |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Assignee | Varun Gupta [ varun ] | Sergei Petrunia [ psergey ] |
| Affects Version/s | 10.5 [ 23123 ] |
| Component/s | Optimizer - Window functions [ 13502 ] |
| Fix Version/s | 10.5 [ 23123 ] |
| Workflow | MariaDB v3 [ 93531 ] | MariaDB v4 [ 144126 ] |
| Fix Version/s | 10.2 [ 14601 ] |
| Fix Version/s | 10.3 [ 22126 ] |
| Assignee | Sergei Petrunia [ psergey ] | Daniel Black [ danblack ] |
| Status | Confirmed [ 10101 ] | In Progress [ 3 ] |
| Assignee | Daniel Black [ danblack ] | Sergei Petrunia [ psergey ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Fix Version/s | 10.6 [ 24028 ] | |
| Fix Version/s | 10.11 [ 27614 ] | |
| Fix Version/s | 10.4 [ 22408 ] |
| Assignee | Sergei Petrunia [ psergey ] | Daniel Black [ danblack ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Fix Version/s | 10.5.26 [ 29832 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Fix Version/s | 10.6 [ 24028 ] | |
| Fix Version/s | 10.11 [ 27614 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Fix Version/s | 10.6.19 [ 29833 ] | |
| Fix Version/s | 10.11.9 [ 29834 ] | |
| Fix Version/s | 11.1.6 [ 29835 ] | |
| Fix Version/s | 11.2.5 [ 29836 ] | |
| Fix Version/s | 11.4.3 [ 29837 ] |