maria-developers team mailing list archive

[Oleksandr Byelkin <sanja@xxxxxxxxxxxxxxxx>]

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