[Commits] bf951046e8d: MDEV-15296: wrong result with window function inside a subquery

Varun varunraiko1803 at gmail.com
Mon Apr 29 17:40:47 EEST 2019


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


More information about the commits mailing list