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

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 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 <`test`.`t10`.`a`,`test`.`t10`.`b`>((`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`) = (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 <`test`.`t1`.`a`,`test`.`t1`.`b`,max(`test`.`t1`.`b`),max(`test`.`t1`.`b`)>((`test`.`t1`.`a`,<exists>(select `test`.`t2`.`c` from `test`.`t2` where (<nop>(<`test`.`t2`.`d`,`test`.`t1`.`b`,max(`test`.`t1`.`b`),max(`test`.`t1`.`b`)>((`test`.`t2`.`d`,<exists>(select `test`.`t3`.`e` from `test`.`t3` where (max(`test`.`t1`.`b`) = `test`.`t3`.`e`) having (<cache>(`test`.`t2`.`d`) >= (`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 <`test`.`t1`.`a`,`test`.`t1`.`b`,max(`test`.`t1`.`b`),max(`test`.`t1`.`b`)>((`test`.`t1`.`a`,<exists>(select `test`.`t2`.`c` from `test`.`t2` where (<nop>(<`test`.`t2`.`d`,`test`.`t1`.`b`,max(`test`.`t1`.`b`),max(`test`.`t1`.`b`)>((`test`.`t2`.`d`,<exists>(select `test`.`t3`.`e` from `test`.`t3` where (max(`test`.`t1`.`b`) = `test`.`t3`.`e`) having (<cache>(`test`.`t2`.`d`) >= (`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