2 files changed, 348 insertions, 0 deletions
diff --git a/mysql-test/r/win_ordering.result b/mysql-test/r/win_ordering.result
new file mode 100644
index 00000000000..43377e5501e
--- /dev/null
+++ b/mysql-test/r/win_ordering.result
@@ -0,0 +1,264 @@
+#
+# MDEV-10879 Window functions final ordering result set
+#
+# This testcase covers window function ordering without an explicit
+# order by clause. Our behaviour is to reuse the final ordering used
+# during window function computation. This will produce at least one
+# window function with values in the order that they are computed.
+#
+# This feature was implemented as a request from the community, as this
+# is what other DBMS engines are doing and they expect simillar behaviour.
+#
+create table t1 (a int, b varchar(10));
+insert into t1 values (1, 'x'),
+(2, 'xx'),
+(3, 'yy'),
+(4, 'zz'),
+(5, 'xxx'),
+(6, 'yyy'),
+(7, 'zzz'),
+(8, 'aaa'),
+(9, 'bbb'),
+(11, 'aa'),
+(12, 'bb'),
+(13, 'cc'),
+(13, 'dd'),
+(10, 'ccc');
+select row_number() over (), a, b
+from t1;
+row_number() over ()	a	b
+1	3	yy
+2	12	bb
+3	4	zz
+4	13	cc
+5	5	xxx
+6	13	dd
+7	6	yyy
+8	10	ccc
+9	7	zzz
+10	8	aaa
+11	1	x
+12	9	bbb
+13	2	xx
+14	11	aa
+select row_number() over (order by a), a, b
+from t1;
+row_number() over (order by a)	a	b
+1	1	x
+2	2	xx
+3	3	yy
+4	4	zz
+5	5	xxx
+6	6	yyy
+7	7	zzz
+8	8	aaa
+9	9	bbb
+10	10	ccc
+11	11	aa
+12	12	bb
+13	13	cc
+14	13	dd
+select row_number() over (order by b), a, b
+from t1;
+row_number() over (order by b)	a	b
+1	11	aa
+2	8	aaa
+3	12	bb
+4	9	bbb
+5	13	cc
+6	10	ccc
+7	13	dd
+8	1	x
+9	2	xx
+10	5	xxx
+11	3	yy
+12	6	yyy
+13	4	zz
+14	7	zzz
+select row_number() over (order by a,b), a, b
+from t1;
+row_number() over (order by a,b)	a	b
+1	1	x
+2	2	xx
+3	3	yy
+4	4	zz
+5	5	xxx
+6	6	yyy
+7	7	zzz
+8	8	aaa
+9	9	bbb
+10	10	ccc
+11	11	aa
+12	12	bb
+13	13	cc
+14	13	dd
+select row_number() over (partition by substring(b, -1) order by a), a, substring(b, -1)
+from t1;
+row_number() over (partition by substring(b, -1) order by a)	a	substring(b, -1)
+1	8	a
+2	11	a
+1	9	b
+2	12	b
+1	10	c
+2	13	c
+1	13	d
+1	1	x
+2	2	x
+3	5	x
+1	3	y
+2	6	y
+1	4	z
+2	7	z
+select row_number() over (partition by substring(b, -1) order by a),  a, substring(b, -1)
+from t1;
+row_number() over (partition by substring(b, -1) order by a)	a	substring(b, -1)
+1	8	a
+2	11	a
+1	9	b
+2	12	b
+1	10	c
+2	13	c
+1	13	d
+1	1	x
+2	2	x
+3	5	x
+1	3	y
+2	6	y
+1	4	z
+2	7	z
+select row_number() over (order by a),
+row_number() over (partition by substring(b, -1) order by a), a, b
+from t1;
+row_number() over (order by a)	row_number() over (partition by substring(b, -1) order by a)	a	b
+8	1	8	aaa
+11	2	11	aa
+9	1	9	bbb
+12	2	12	bb
+10	1	10	ccc
+13	2	13	cc
+14	1	13	dd
+1	1	1	x
+2	2	2	xx
+5	3	5	xxx
+3	1	3	yy
+6	2	6	yyy
+4	1	4	zz
+7	2	7	zzz
+#
+# Test descending ordering too.
+#
+select row_number() over (order by a desc), a, b
+from t1;
+row_number() over (order by a desc)	a	b
+1	13	cc
+2	13	dd
+3	12	bb
+4	11	aa
+5	10	ccc
+6	9	bbb
+7	8	aaa
+8	7	zzz
+9	6	yyy
+10	5	xxx
+11	4	zz
+12	3	yy
+13	2	xx
+14	1	x
+select row_number() over (order by a),
+row_number() over (partition by substring(b, -1) desc order by a), a, b
+from t1;
+row_number() over (order by a)	row_number() over (partition by substring(b, -1) desc order by a)	a	b
+4	1	4	zz
+7	2	7	zzz
+3	1	3	yy
+6	2	6	yyy
+1	1	1	x
+2	2	2	xx
+5	3	5	xxx
+14	1	13	dd
+10	1	10	ccc
+13	2	13	cc
+9	1	9	bbb
+12	2	12	bb
+8	1	8	aaa
+11	2	11	aa
+#
+# Test that we can still use the order by explicitly
+#
+select row_number() over (order by a),
+row_number() over (partition by substring(b, -1) desc order by a), a, b
+from t1
+order by a;
+row_number() over (order by a)	row_number() over (partition by substring(b, -1) desc order by a)	a	b
+1	1	1	x
+2	2	2	xx
+3	1	3	yy
+4	1	4	zz
+5	3	5	xxx
+6	2	6	yyy
+7	2	7	zzz
+8	1	8	aaa
+9	1	9	bbb
+10	1	10	ccc
+11	2	11	aa
+12	2	12	bb
+13	2	13	cc
+14	1	13	dd
+select row_number() over (order by a),
+row_number() over (partition by substring(b, -1) desc order by a), a, b
+from t1
+order by a desc;
+row_number() over (order by a)	row_number() over (partition by substring(b, -1) desc order by a)	a	b
+13	2	13	cc
+14	1	13	dd
+12	2	12	bb
+11	2	11	aa
+10	1	10	ccc
+9	1	9	bbb
+8	1	8	aaa
+7	2	7	zzz
+6	2	6	yyy
+5	3	5	xxx
+4	1	4	zz
+3	1	3	yy
+2	2	2	xx
+1	1	1	x
+select row_number() over (order by a desc),
+row_number() over (partition by substring(b, -1) desc order by a), a, b
+from t1
+order by a;
+row_number() over (order by a desc)	row_number() over (partition by substring(b, -1) desc order by a)	a	b
+14	1	1	x
+13	2	2	xx
+12	1	3	yy
+11	1	4	zz
+10	3	5	xxx
+9	2	6	yyy
+8	2	7	zzz
+7	1	8	aaa
+6	1	9	bbb
+5	1	10	ccc
+4	2	11	aa
+3	2	12	bb
+1	2	13	cc
+2	1	13	dd
+select row_number() over (order by a) fst_row,
+row_number() over (partition by substring(b, -1) desc order by a), a, b
+from t1
+order by fst_row;
+fst_row	row_number() over (partition by substring(b, -1) desc order by a)	a	b
+1	1	1	x
+2	2	2	xx
+3	1	3	yy
+4	1	4	zz
+5	3	5	xxx
+6	2	6	yyy
+7	2	7	zzz
+8	1	8	aaa
+9	1	9	bbb
+10	1	10	ccc
+11	2	11	aa
+12	2	12	bb
+13	2	13	cc
+14	1	13	dd
+drop table t1;
diff --git a/mysql-test/t/win_ordering.test b/mysql-test/t/win_ordering.test
new file mode 100644
index 00000000000..41f9a34bd16
--- /dev/null
+++ b/mysql-test/t/win_ordering.test
@@ -0,0 +1,84 @@
+--echo #
+--echo # MDEV-10879 Window functions final ordering result set
+--echo #
+--echo # This testcase covers window function ordering without an explicit
+--echo # order by clause. Our behaviour is to reuse the final ordering used
+--echo # during window function computation. This will produce at least one
+--echo # window function with values in the order that they are computed.
+--echo #
+--echo # This feature was implemented as a request from the community, as this
+--echo # is what other DBMS engines are doing and they expect simillar behaviour.
+--echo #
+create table t1 (a int, b varchar(10));
+
+insert into t1 values (1, 'x'),
+(2, 'xx'),
+(3, 'yy'),
+(4, 'zz'),
+(5, 'xxx'),
+(6, 'yyy'),
+(7, 'zzz'),
+(8, 'aaa'),
+(9, 'bbb'),
+(11, 'aa'),
+(12, 'bb'),
+(13, 'cc'),
+(13, 'dd'),
+(10, 'ccc');
+
+select row_number() over (), a, b
+from t1;
+
+select row_number() over (order by a), a, b
+from t1;
+
+select row_number() over (order by b), a, b
+from t1;
+
+select row_number() over (order by a,b), a, b
+from t1;
+
+select row_number() over (partition by substring(b, -1) order by a), a, substring(b, -1)
+from t1;
+
+select row_number() over (partition by substring(b, -1) order by a),  a, substring(b, -1)
+from t1;
+
+select row_number() over (order by a),
+       row_number() over (partition by substring(b, -1) order by a), a, b
+from t1;
+
+--echo #
+--echo # Test descending ordering too.
+--echo #
+select row_number() over (order by a desc), a, b
+from t1;
+
+select row_number() over (order by a),
+       row_number() over (partition by substring(b, -1) desc order by a), a, b
+from t1;
+
+--echo #
+--echo # Test that we can still use the order by explicitly
+--echo #
+select row_number() over (order by a),
+       row_number() over (partition by substring(b, -1) desc order by a), a, b
+from t1
+order by a;
+
+select row_number() over (order by a),
+       row_number() over (partition by substring(b, -1) desc order by a), a, b
+from t1
+order by a desc;
+
+select row_number() over (order by a desc),
+       row_number() over (partition by substring(b, -1) desc order by a), a, b
+from t1
+order by a;
+
+select row_number() over (order by a) fst_row,
+       row_number() over (partition by substring(b, -1) desc order by a), a, b
+from t1
+order by fst_row;
+
+drop table t1;