From 5da6ffe22772897a55b1293f0f054b299e4bf539 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Vicen=C8=9Biu=20Ciorbaru?= <vicentiu@mariadb.org>
Date: Tue, 2 Mar 2021 15:37:12 +0200
Subject: MDEV-25032: Window functions without column references get removed
 from ORDER BY

row_number() over () window function can be used without any column in the OVER
clause. Additionally, the item doesn't reference any tables, as it's not
effectively referencing any table. Rather it is specifically built based
on the end temporary table used for window function computation.

This caused remove_const function to wrongly drop it from the ORDER
list. Effectively, we shouldn't be dropping any window function from the
ORDER clause, so adjust remove_const to account for that.

Reviewed by: Sergei Petrunia sergey@mariadb.com
---
 mysql-test/r/win.result                            | 26 ++++++++++++++++++++++
 .../suite/encryption/r/tempfiles_encrypted.result  | 26 ++++++++++++++++++++++
 mysql-test/t/win.test                              | 20 +++++++++++++++++
 sql/sql_select.cc                                  |  1 +
 4 files changed, 73 insertions(+)

diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result
index 3023a86eaad..dd74c5c77fd 100644
--- a/mysql-test/r/win.result
+++ b/mysql-test/r/win.result
@@ -3866,5 +3866,31 @@ NULL
 DROP VIEW v1;
 DROP TABLE t1,t2;
 #
+# MDEV-25032 Window functions without column references get removed from ORDER BY
+#
+create table t1 (id int, score double);
+insert into t1 values
+(1, 5),
+(1, 6),
+(1, 6),
+(1, 6),
+(1, 7),
+(1, 8.1),
+(1, 9),
+(1, 10);
+select id, row_number() over () rn
+from t1
+order by rn desc;
+id	rn
+1	8
+1	7
+1	6
+1	5
+1	4
+1	3
+1	2
+1	1
+drop table t1;
+#
 # End of 10.2 tests
 #
diff --git a/mysql-test/suite/encryption/r/tempfiles_encrypted.result b/mysql-test/suite/encryption/r/tempfiles_encrypted.result
index 1856c30a36b..27eedc45028 100644
--- a/mysql-test/suite/encryption/r/tempfiles_encrypted.result
+++ b/mysql-test/suite/encryption/r/tempfiles_encrypted.result
@@ -3872,6 +3872,32 @@ NULL
 DROP VIEW v1;
 DROP TABLE t1,t2;
 #
+# MDEV-25032 Window functions without column references get removed from ORDER BY
+#
+create table t1 (id int, score double);
+insert into t1 values
+(1, 5),
+(1, 6),
+(1, 6),
+(1, 6),
+(1, 7),
+(1, 8.1),
+(1, 9),
+(1, 10);
+select id, row_number() over () rn
+from t1
+order by rn desc;
+id	rn
+1	8
+1	7
+1	6
+1	5
+1	4
+1	3
+1	2
+1	1
+drop table t1;
+#
 # End of 10.2 tests
 #
 #
diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test
index c7e3dac598b..57214ab0165 100644
--- a/mysql-test/t/win.test
+++ b/mysql-test/t/win.test
@@ -2522,6 +2522,26 @@ SELECT NTH_VALUE(i1, i1) OVER (PARTITION BY i1) FROM v1;
 DROP VIEW v1;
 DROP TABLE t1,t2;
 
+--echo #
+--echo # MDEV-25032 Window functions without column references get removed from ORDER BY
+--echo #
+
+create table t1 (id int, score double);
+insert into t1 values
+(1, 5),
+(1, 6),
+(1, 6),
+(1, 6),
+(1, 7),
+(1, 8.1),
+(1, 9),
+(1, 10);
+select id, row_number() over () rn
+from t1
+order by rn desc;
+
+drop table t1;
+
 --echo #
 --echo # End of 10.2 tests
 --echo #
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index f30ce088bc4..7bfbf719017 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -12671,6 +12671,7 @@ remove_const(JOIN *join,ORDER *first_order, COND *cond,
   {
     table_map order_tables=order->item[0]->used_tables();
     if (order->item[0]->with_sum_func ||
+        order->item[0]->with_window_func ||
         /*
           If the outer table of an outer join is const (either by itself or
           after applying WHERE condition), grouping on a field from such a
-- 
cgit v1.2.1