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@mariadb.org 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