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 commits@mariadb.org https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits