Hi, Varun!
This bug shows us whole class of the same problem in the code which slip
of our scope during window function development. so please check and fix
them (one or separate change-set as you wish). See comment below.
Am 29.04.19 um 16:40 schrieb Varun:
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 &&
This case is definitely OK, but I also asked you to check other cases
with with_sum_func, I do not see traces of it here so I did and it is my
findings:
Item_cache_wrapper::Item_cache_wrapper do not transfer with_window_func,
is it impossible to have it here? (then better to put ASSERT)
(same Item_cache_wrapper::get_tmp_table_item)
Item_in_optimizer::fix_left, Item_in_optimizer::fix_fields,
Item_func_interval::fix_length_and_dec do not transfer the flag also.
ASSERT in Item_equal::fix_fields
Item_func::get_tmp_table_item, Item_subselect::get_tmp_table_item
impossible? than better put an ASSERT.
Item_func constructors do not transfer the flag.
Item_allany_subselect::cleanup reset.
Item_singlerow_subselect::select_transformer,
Item_in_subselect::single_value_transformer,
Item_allany_subselect::transform_into_max_min,
Item_exists_subselect::exists2in_processor,
check_and_do_in_subquery_rewrites I doubts that window function allows
the transformation is aggregate prohibit it.
Item_in_subselect::create_single_in_to_exists_cond probably the same as
aggregate.
Item_in_subselect::create_row_in_to_exists_cond not sure if it is
possible to have window functions hare but should be checked.
st_select_lex::check_unrestricted_recursive, pushdown_cond_for_derived
It looks like here also should be checked.
/*
We cant change name of Item_field or Item_ref, because it will
prevent it's correct resolving, but we should save name of
_______________________________________________
commits mailing list
comm...@mariadb.org
https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits
_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
