[MDEV-11747] Suspicious limitation for RANGE frames Created: 2017-01-08  Updated: 2017-01-08

Status: Open
Project: MariaDB Server
Component/s: Optimizer - Window functions
Affects Version/s: 10.2
Fix Version/s: 10.2

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Vicențiu Ciorbaru
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-9896 Testing for window functions Open

 Description   

Note: results below are from 10.2 348ccb6f038a6c1.

MariaDB [test]> create table t (i int, j int);
 
Query OK, 0 rows affected (0.36 sec)
 
 
 
MariaDB [test]> select i, nth_value(i,2) over (partition by i order by i, j range unbounded preceding) from t;
 
ERROR 4019 (HY000): RANGE-type frame requires ORDER BY clause with single sort key

I'm not sure whether it's supposed to be so, or it's a leftover from some previous limitations. The KB page is still nothing more than a stub, it's not helpful at all.

For ROWS, it works

MariaDB [test]> select i, nth_value(i,2) over (partition by i order by i, j rows unbounded preceding) from t;
 
Empty set (0.00 sec)

In PostgreSQL 9.4, it works (of course, PostgreSQL has its own limitations for RANGE):

postgres=# create table t (i int, j int);
 
CREATE TABLE
 
postgres=# select i, nth_value(i,2) over (partition by i order by i, j range unbounded preceding) from t;
 
 i | nth_value 
---+-----------
 
(0 rows)


Generated at Thu Feb 08 07:52:21 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.