revision-id: bf951046e8df13702ee56e4e269ce5076e15407f (mariadb-10.2.23-99-gbf951046e8d) parent(s): 092602ac9b650f921ec5380866d17d740f0eedb4 author: Varun Gupta committer: Varun Gupta timestamp: 2019-04-29 20:07:47 +0530 message: MDEV-15296: wrong result with window function inside a subquery Window Functions were treated as a constant in a dependent tables less subquery. Made sure that the behaviour of window functions is same as the aggregate function for dependent tables less subquery. --- mysql-test/r/win.result | 17 +++++++++++++++++ mysql-test/t/win.test | 10 ++++++++++ sql/item_subselect.cc | 1 + 3 files changed, 28 insertions(+) diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index 0ddffc551dc..b86f5b1fc16 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -3518,5 +3518,22 @@ rank() OVER (ORDER BY 1) ROW_NUMBER() OVER (ORDER BY (EXPORT_SET(5,'Y','N',',',4 1 3 drop table t1; # +# MDEV-15296: wrong result with window function inside a subquery +# +CREATE TABLE t1(i INT); +INSERT INTO t1 VALUES (1), (2); +EXPLAIN EXTENDED SELECT (SELECT SUM(i) OVER (partition BY i)) FROM t1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1276 Field or reference 'test.t1.i' of SELECT #2 was resolved in SELECT #1 +Note 1003 select <expr_cache><`test`.`t1`.`i`>((select sum(`test`.`t1`.`i`) over ( partition by `test`.`t1`.`i`))) AS `(SELECT SUM(i) OVER (partition BY i))` from `test`.`t1` +SELECT (SELECT SUM(i) OVER (partition BY i)) FROM t1; +(SELECT SUM(i) OVER (partition BY i)) +1 +2 +drop table t1; +# # End of 10.2 tests # diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index fd31e9d4bd9..6cc681cace4 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -2265,6 +2265,16 @@ insert into t1 values (1),(2),(3); SELECT rank() OVER (ORDER BY 1), ROW_NUMBER() OVER (ORDER BY (EXPORT_SET(5,'Y','N',',',4))) FROM t1; drop table t1; +--echo # +--echo # MDEV-15296: wrong result with window function inside a subquery +--echo # + +CREATE TABLE t1(i INT); +INSERT INTO t1 VALUES (1), (2); +EXPLAIN EXTENDED SELECT (SELECT SUM(i) OVER (partition BY i)) FROM t1; +SELECT (SELECT SUM(i) OVER (partition BY i)) FROM t1; +drop table t1; + --echo # --echo # End of 10.2 tests --echo # diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 8cff8f3a5c4..95482364225 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -1138,6 +1138,7 @@ Item_singlerow_subselect::select_transformer(JOIN *join) !select_lex->table_list.elements && select_lex->item_list.elements == 1 && !select_lex->item_list.head()->with_sum_func && + !select_lex->item_list.head()->with_window_func && /* We cant change name of Item_field or Item_ref, because it will prevent it's correct resolving, but we should save name of