maria-developers team mailing list archive

Thread
Date
Re: [Commits] 2a4ed5f: MDEV-10017: Get unexpected `Empty Set` for correlated subquery with aggregate functions (part 1)

To: maria-developers@xxxxxxxxxxxxxxxxxxx, Oleksandr Byelkin <sanja@xxxxxxxxxxx>
From: Sergey Petrunia <sergey@xxxxxxxxxxx>
Date: Mon, 29 Aug 2016 16:35:12 +0300
Cc: commits@xxxxxxxxxxx
In-reply-to: <57c42172.17a61c0a.34ddb.695f@mx.google.com>
User-agent: Mutt/1.5.21 (2010-09-15)
Hi Sanja,

Table t2 is the testcase is redundant.

Ok to push after this is addressed.

On Mon, Aug 29, 2016 at 01:50:09PM +0200, Oleksandr Byelkin wrote:
> revision-id: 2a4ed5f7a74860608cb36b761eb139a57cbd4463 (mariadb-10.1.16-15-g2a4ed5f)
> parent(s): 76bd9f655a42dc1e21acfa26650a0cb76c70a22e
> committer: Oleksandr Byelkin
> timestamp: 2016-08-29 13:50:09 +0200
> message:
> 
> MDEV-10017: Get unexpected `Empty Set` for correlated subquery with aggregate functions (part 1)
> 
> Make aggregate function dependency visible.
> 
> ---
>  mysql-test/r/func_group.result       | 19 +++++++++++++++++++
>  mysql-test/r/subselect3.result       |  1 +
>  mysql-test/r/subselect3_jcl6.result  |  1 +
>  mysql-test/r/subselect_mat.result    |  1 +
>  mysql-test/r/subselect_sj_mat.result |  1 +
>  mysql-test/t/func_group.test         | 11 +++++++++++
>  sql/item_sum.cc                      | 10 ++++++++++
>  sql/share/errmsg-utf8.txt            |  3 +++
>  8 files changed, 47 insertions(+)
> 
> diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
> index 0d9d4ff..4bc5ad1 100644
> --- a/mysql-test/r/func_group.result
> +++ b/mysql-test/r/func_group.result
> @@ -2340,5 +2340,24 @@ companynr	AVG(fld1)	avg1	avg2
>  37	9223372036854775805.0000	9223372036854775805	9223372036854775805
>  DROP TABLE t1;
>  #
> +# case where aggregate resolved in the local SELECT
> +# but outer ones are checked
> +#
> +create table t10 (a int , b int, c int);
> +insert into t10 values (0,0,0),(1,1,1);
> +create table t11 as select * from t10;
> +create table t12 as select * from t10;
> +create table t2 as select * from t10;
> +explain extended select a from t10 where c<3 or a in (select c from t12 union select max(t10.b) from t11 group by t11.c);
> +id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
> +1	PRIMARY	t10	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
> +2	DEPENDENT SUBQUERY	t12	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
> +3	DEPENDENT UNION	t11	ALL	NULL	NULL	NULL	NULL	2	100.00	Using temporary
> +NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
> +Warnings:
> +Note	1276	Field or reference 'test.t10.b' of SELECT #3 was resolved in SELECT #1
> +Note	1003	select `test`.`t10`.`a` AS `a` from `test`.`t10` where ((`test`.`t10`.`c` < 3) or <expr_cache><`test`.`t10`.`a`,`test`.`t10`.`b`>(<in_optimizer>(`test`.`t10`.`a`,<exists>(select `test`.`t12`.`c` from `test`.`t12` where (<cache>(`test`.`t10`.`a`) = `test`.`t12`.`c`) union select max(`test`.`t10`.`b`) from `test`.`t11` group by `test`.`t11`.`c` having (<cache>(`test`.`t10`.`a`) = <ref_null_helper>(max(`test`.`t10`.`b`)))))))
> +drop table t10,t11,t12,t2;
> +#
>  # End of 10.1 tests
>  #
> diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
> index 24d9f0d..052b46d 100644
> --- a/mysql-test/r/subselect3.result
> +++ b/mysql-test/r/subselect3.result
> @@ -889,6 +889,7 @@ ERROR 42S22: Unknown column 'c' in 'field list'
>  SHOW WARNINGS;
>  Level	Code	Message
>  Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2
> +Note	1981	Aggregate function 'count()' of SELECT #3 belongs to SELECT #2
>  Note	1276	Field or reference 'test.t1.c' of SELECT #3 was resolved in SELECT #2
>  Error	1054	Unknown column 'c' in 'field list'
>  DROP TABLE t1;
> diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result
> index 19d3d25..28f4e40 100644
> --- a/mysql-test/r/subselect3_jcl6.result
> +++ b/mysql-test/r/subselect3_jcl6.result
> @@ -899,6 +899,7 @@ ERROR 42S22: Unknown column 'c' in 'field list'
>  SHOW WARNINGS;
>  Level	Code	Message
>  Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2
> +Note	1981	Aggregate function 'count()' of SELECT #3 belongs to SELECT #2
>  Note	1276	Field or reference 'test.t1.c' of SELECT #3 was resolved in SELECT #2
>  Error	1054	Unknown column 'c' in 'field list'
>  DROP TABLE t1;
> diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
> index d0c5c2e..bfb40b9 100644
> --- a/mysql-test/r/subselect_mat.result
> +++ b/mysql-test/r/subselect_mat.result
> @@ -1139,6 +1139,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
>  3	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
>  Warnings:
>  Note	1276	Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1
> +Note	1981	Aggregate function 'max()' of SELECT #3 belongs to SELECT #1
>  Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having <expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`,max(`test`.`t1`.`b`),max(`test`.`t1`.`b`)>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select `test`.`t2`.`c` from `test`.`t2` where (<nop>(<expr_cache><`test`.`t2`.`d`,`test`.`t1`.`b`,max(`test`.`t1`.`b`),max(`test`.`t1`.`b`)>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select `test`.`t3`.`e` from `test`.`t3` where (max(`test`.`t1`.`b`) = `test`.`t3`.`e`) having (<cache>(`test`.`t2`.`d`) >= <ref_null_helper>(`test`.`t3`.`e`)))))) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`)))))
>  select a from t1 group by a
>  having a in (select c from t2 where d >= some(select e from t3 where max(b)=e));
> diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
> index e846549..366a03b 100644
> --- a/mysql-test/r/subselect_sj_mat.result
> +++ b/mysql-test/r/subselect_sj_mat.result
> @@ -1173,6 +1173,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
>  3	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
>  Warnings:
>  Note	1276	Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1
> +Note	1981	Aggregate function 'max()' of SELECT #3 belongs to SELECT #1
>  Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having <expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`,max(`test`.`t1`.`b`),max(`test`.`t1`.`b`)>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select `test`.`t2`.`c` from `test`.`t2` where (<nop>(<expr_cache><`test`.`t2`.`d`,`test`.`t1`.`b`,max(`test`.`t1`.`b`),max(`test`.`t1`.`b`)>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select `test`.`t3`.`e` from `test`.`t3` where (max(`test`.`t1`.`b`) = `test`.`t3`.`e`) having (<cache>(`test`.`t2`.`d`) >= <ref_null_helper>(`test`.`t3`.`e`)))))) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`)))))
>  select a from t1 group by a
>  having a in (select c from t2 where d >= some(select e from t3 where max(b)=e));
> diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test
> index 47477c7..4a8b76b 100644
> --- a/mysql-test/t/func_group.test
> +++ b/mysql-test/t/func_group.test
> @@ -1617,5 +1617,16 @@ SELECT companynr, AVG(fld1), AVG(fld1)<<0 AS avg1, CAST(AVG(fld1) AS UNSIGNED)<<
>  DROP TABLE t1;
>  
>  --echo #
> +--echo # case where aggregate resolved in the local SELECT
> +--echo # but outer ones are checked
> +--echo #
> +create table t10 (a int , b int, c int);
> +insert into t10 values (0,0,0),(1,1,1);
> +create table t11 as select * from t10;
> +create table t12 as select * from t10;
> +create table t2 as select * from t10;
> +explain extended select a from t10 where c<3 or a in (select c from t12 union select max(t10.b) from t11 group by t11.c);
> +drop table t10,t11,t12,t2;
> +--echo #
>  --echo # End of 10.1 tests
>  --echo #
> diff --git a/sql/item_sum.cc b/sql/item_sum.cc
> index 1cfee1a..2a2b7f9 100644
> --- a/sql/item_sum.cc
> +++ b/sql/item_sum.cc
> @@ -380,6 +380,16 @@ bool Item_sum::register_sum_func(THD *thd, Item **ref)
>        sl->master_unit()->item->with_sum_func= 1;
>    }
>    thd->lex->current_select->mark_as_dependent(thd, aggr_sel, NULL);
> +
> +  if ((thd->lex->describe & DESCRIBE_EXTENDED) && aggr_sel)
> +  {
> +    push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE,
> +                        ER_WARN_AGGFUNC_DEPENDENCE,
> +                        ER_THD(thd, ER_WARN_AGGFUNC_DEPENDENCE),
> +                        func_name(),
> +                        thd->lex->current_select->select_number,
> +                        aggr_sel->select_number);
> +  }
>    return FALSE;
>  }
>  
> diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt
> index 709ac55..a6eaf2b 100644
> --- a/sql/share/errmsg-utf8.txt
> +++ b/sql/share/errmsg-utf8.txt
> @@ -7139,3 +7139,6 @@ ER_KILL_QUERY_DENIED_ERROR
>  ER_NO_EIS_FOR_FIELD
>          eng "Engine-independent statistics are not collected for column '%s'"
>          ukr "Незалежна від типу таблиці статистика не збирається для стовбця '%s'"
> +ER_WARN_AGGFUNC_DEPENDENCE
> +        eng "Aggregate function '%-.192s)' of SELECT #%d belongs to SELECT #%d"
> +        ukr "Агрегатна функція '%-.192s)' з SELECTу #%d належить до SELECTу #%d"
> _______________________________________________
> commits mailing list
> commits@xxxxxxxxxxx
> https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

-- 
BR
 Sergei
-- 
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog