MDEV-9676: RANGE-type frames for window functions

- Handle ORDER BY DESC in window definitions.
- Fix an issue in Frame_range_current_row_top

1 files changed, 110 insertions, 0 deletions

diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result
index 67957efe704..caa0ddbdcdb 100644
--- a/mysql-test/r/win.result
+++ b/mysql-test/r/win.result
@@ -91,6 +91,18 @@ pk	a	rank() over (order by a)
 7	2	5
 9	4	9
 10	4	9
+select pk, a, rank() over (order by a desc) from t2;
+pk	a	rank() over (order by a desc)
+1	0	9
+2	0	9
+3	1	7
+4	1	7
+8	2	3
+5	2	3
+6	2	3
+7	2	3
+9	4	1
+10	4	1
 drop table t2;
 #
 # Try DENSE_RANK() function
@@ -252,6 +264,23 @@ pk	c	CNT
 8	2	2
 9	2	2
 10	2	1
+# Check ORDER BY DESC
+select 
+pk, c, 
+count(*) over (partition by c order by pk desc
+rows between 2 preceding and 2 following) as CNT
+from t1;
+pk	c	CNT
+1	1	3
+2	1	4
+3	1	4
+4	1	3
+5	2	3
+6	2	4
+7	2	5
+8	2	5
+9	2	4
+10	2	3
 drop table t0,t1;
 #
 # Resolution of window names
@@ -817,6 +846,22 @@ pk	a	cnt
 9	72	9
 select
 pk, a,
+count(a) over (ORDER BY a DESC
+RANGE BETWEEN UNBOUNDED PRECEDING 
+AND 10 FOLLOWING) as cnt
+from t1;
+pk	a	cnt
+1	1	9
+2	2	9
+3	4	9
+4	8	9
+5	26	5
+6	27	5
+7	40	3
+8	71	2
+9	72	2
+select
+pk, a,
 count(a) over (ORDER BY a
 RANGE BETWEEN UNBOUNDED PRECEDING 
 AND 1 FOLLOWING) as cnt
@@ -849,6 +894,22 @@ pk	a	cnt
 9	72	7
 select
 pk, a,
+count(a) over (ORDER BY a DESC
+RANGE BETWEEN UNBOUNDED PRECEDING 
+AND 10 PRECEDING) as cnt
+from t1;
+pk	a	cnt
+1	1	5
+2	2	5
+3	4	5
+4	8	5
+5	26	3
+6	27	3
+7	40	2
+8	71	0
+9	72	0
+select
+pk, a,
 count(a) over (ORDER BY a
 RANGE BETWEEN UNBOUNDED PRECEDING 
 AND 1 PRECEDING) as cnt
@@ -881,6 +942,22 @@ pk	a	cnt
 9	72	2
 select
 pk, a,
+count(a) over (ORDER BY a DESC
+RANGE BETWEEN 1 PRECEDING 
+AND CURRENT ROW) as cnt
+from t1;
+pk	a	cnt
+1	1	2
+2	2	1
+3	4	1
+4	8	1
+5	26	2
+6	27	1
+7	40	1
+8	71	2
+9	72	1
+select
+pk, a,
 count(a) over (ORDER BY a
 RANGE BETWEEN 1 FOLLOWING 
 AND 3 FOLLOWING) as cnt
@@ -895,6 +972,39 @@ pk	a	cnt
 7	40	0
 8	71	1
 9	72	0
+# Try CURRENT ROW with[out] DESC
+select
+pk, a,
+count(a) over (ORDER BY a
+RANGE BETWEEN CURRENT ROW
+AND 1 FOLLOWING) as cnt
+from t1;
+pk	a	cnt
+1	1	2
+2	2	1
+3	4	1
+4	8	1
+5	26	2
+6	27	1
+7	40	1
+8	71	2
+9	72	1
+select
+pk, a,
+count(a) over (order by a desc
+range between current row
+and 1 following) as cnt
+from t1;
+pk	a	cnt
+1	1	1
+2	2	2
+3	4	1
+4	8	1
+5	26	1
+6	27	2
+7	40	1
+8	71	1
+9	72	2
 insert into t1 select 22, pk, a from t1;
 select
 part_id, pk, a,