diff options
| author | Vicențiu Ciorbaru <vicentiu@mariadb.org> | 2021-03-02 15:37:12 +0200 |
|---|---|---|
| committer | Vicențiu Ciorbaru <vicentiu@mariadb.org> | 2021-03-04 15:37:47 +0200 |
| commit | 5da6ffe22772897a55b1293f0f054b299e4bf539 (patch) | |
| tree | 65ca8ccf8ceb1caadd7ebcd7a966e432f83f066d | |
| parent | b044898b97729c36dd22e1133e47c92ed7c42e04 (diff) | |
| download | mariadb-git-5da6ffe22772897a55b1293f0f054b299e4bf539.tar.gz | |
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
| -rw-r--r-- | mysql-test/r/win.result | 26 | ||||
| -rw-r--r-- | mysql-test/suite/encryption/r/tempfiles_encrypted.result | 26 | ||||
| -rw-r--r-- | mysql-test/t/win.test | 20 | ||||
| -rw-r--r-- | sql/sql_select.cc | 1 |
4 files changed, 73 insertions, 0 deletions
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 @@ -2523,5 +2523,25 @@ 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 |