[MDEV-19052] Range-type window frame supports only numeric datatype Created: 2019-03-26  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: Optimizer - Window functions
Affects Version/s: 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Major
Reporter: Alice Sherepa Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 1
Labels: 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)



 Comments   
Comment by Alice Sherepa [ 2019-04-01 ] 

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)

Comment by John Medeiros [ 2020-01-10 ]

Same behavior founded. Version: 10.3.21-MariaDB.

Generated at Thu Feb 08 08:48:42 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.