From: Date: April 17 2007 4:44pm Subject: bk commit into 5.0 tree (gkodinov:1.2456) BUG#27363 List-Archive: http://lists.mysql.com/commits/24671 X-Bug: 27363 Message-Id: <200704171444.l3HEiwKs031881@magare.gmz> Below is the list of changes that have just been committed into a local 5.0 repository of kgeorge. When kgeorge does a push these changes will be propagated to the main repository and, within 24 hours after the push, to the public repository. For information on how to access the public repository see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html ChangeSet@stripped, 2007-04-17 17:44:55+03:00, gkodinov@stripped +4 -0 Bug #27363: To correctly decide where to aggregate or can it aggregate at all an aggregate function the optimizer must know the maximum subquery nesting level of the field references and the aggregation level of the aggregate functions quoted in the arguments of the enclosing aggregate function. The optimizer collects that information when resolving outer field references and aggregate functions. However it was not collection the information from a subquery inside aggregation function arguments, e.g: SELECT SUM(1 + (SELECT COUNT(t1.a) FROM DUAL)) FROM t1. Fixed by adjusting the condition for collecting the maximum aggregation levels of column references and nested aggregate functions to work correctly across subquery boundaries. mysql-test/r/subselect3.result@stripped, 2007-04-17 17:44:53+03:00, gkodinov@stripped +31 -0 Bug #27363: test case mysql-test/t/subselect3.test@stripped, 2007-04-17 17:44:53+03:00, gkodinov@stripped +37 -0 Bug #27363: test case sql/item.cc@stripped, 2007-04-17 17:44:53+03:00, gkodinov@stripped +11 -2 Bug #27363: use the outer field reference to mark the embedding aggregate function even if it's not on the same subquery level. sql/item_sum.cc@stripped, 2007-04-17 17:44:53+03:00, gkodinov@stripped +4 -2 Bug #27363: use the aggregate function aggregation level to mark the embedding aggregate function even if it's not on the same subquery level. # This is a BitKeeper patch. What follows are the unified diffs for the # set of deltas contained in the patch. The rest of the patch, the part # that BitKeeper cares about, is below these diffs. # User: gkodinov # Host: magare.gmz # Root: /home/kgeorge/mysql/work/B27363-evgen-5.0-opt --- 1.262/sql/item.cc 2007-04-15 08:53:24 +03:00 +++ 1.263/sql/item.cc 2007-04-17 17:44:53 +03:00 @@ -3522,8 +3522,7 @@ Item_field::fix_outer_field(THD *thd, Fi rf->in_sum_func= thd->lex->in_sum_func; } if (thd->lex->in_sum_func && - thd->lex->in_sum_func->nest_level == - thd->lex->current_select->nest_level) + thd->lex->in_sum_func->nest_level >= select->nest_level) { Item::Type ref_type= (*reference)->type(); set_if_bigger(thd->lex->in_sum_func->max_arg_level, @@ -5174,6 +5173,11 @@ bool Item_ref::fix_fields(THD *thd, Item thd->change_item_tree(reference, fld); mark_as_dependent(thd, last_checked_context->select_lex, thd->lex->current_select, this, fld); + if (thd->lex->in_sum_func && + thd->lex->in_sum_func->nest_level >= + last_checked_context->select_lex->nest_level) + set_if_bigger(thd->lex->in_sum_func->max_arg_level, + last_checked_context->select_lex->nest_level); return FALSE; } if (ref == 0) @@ -5187,6 +5191,11 @@ bool Item_ref::fix_fields(THD *thd, Item DBUG_ASSERT(*ref && (*ref)->fixed); mark_as_dependent(thd, last_checked_context->select_lex, context->select_lex, this, this); + if (thd->lex->in_sum_func && + thd->lex->in_sum_func->nest_level >= + last_checked_context->select_lex->nest_level) + set_if_bigger(thd->lex->in_sum_func->max_arg_level, + last_checked_context->select_lex->nest_level); } } --- 1.205/sql/item_sum.cc 2007-04-02 11:50:17 +03:00 +++ 1.206/sql/item_sum.cc 2007-04-17 17:44:53 +03:00 @@ -175,13 +175,15 @@ bool Item_sum::check_sum_func(THD *thd, MYF(0)); return TRUE; } - if (in_sum_func && in_sum_func->nest_level == nest_level) + for (Item_sum *sum_func= in_sum_func; + sum_func && sum_func->nest_level >= aggr_level; + sum_func= sum_func->in_sum_func) { /* If the set function is nested adjust the value of max_sum_func_level for the nesting set function. */ - set_if_bigger(in_sum_func->max_sum_func_level, aggr_level); + set_if_bigger(sum_func->max_sum_func_level, aggr_level); } update_used_tables(); thd->lex->in_sum_func= in_sum_func; --- 1.8/mysql-test/r/subselect3.result 2007-04-15 07:34:13 +03:00 +++ 1.9/mysql-test/r/subselect3.result 2007-04-17 17:44:53 +03:00 @@ -692,3 +692,34 @@ a MAX(b) test 2 3 h 3 4 i DROP TABLE t1, t2; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1); +CREATE TABLE t2 (x INT); +INSERT INTO t2 VALUES (1000); +SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1; +ERROR HY000: Invalid use of group function +INSERT INTO t1 VALUES (1); +SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1; +ERROR HY000: Invalid use of group function +INSERT INTO t1 VALUES (1); +SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1; +ERROR HY000: Invalid use of group function +INSERT INTO t1 VALUES (1); +SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1; +ERROR HY000: Invalid use of group function +SELECT SUM( (SELECT SUM(COUNT(a)) FROM t2) ) FROM t1; +ERROR HY000: Invalid use of group function +SELECT COUNT(1) FROM t2; +COUNT(1) +1 +SELECT SUM( (SELECT AVG( (SELECT t1.a FROM t2) ) FROM t2) ) FROM t1; +ERROR HY000: Invalid use of group function +SELECT +SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING t1.a < 12) ) FROM t2) ) +FROM t1; +ERROR HY000: Invalid use of group function +SELECT t1.a as XXA, +SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING XXA < 12) ) FROM t2) ) +FROM t1; +ERROR HY000: Invalid use of group function +DROP TABLE t1,t2; --- 1.8/mysql-test/t/subselect3.test 2007-04-15 07:41:34 +03:00 +++ 1.9/mysql-test/t/subselect3.test 2007-04-17 17:44:53 +03:00 @@ -528,3 +528,40 @@ SELECT a, MAX(b), DROP TABLE t1, t2; + +# +# Bug #27363: nested aggregates in outer, subquery / sum(select +# count(outer)) +# +CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1); +CREATE TABLE t2 (x INT); INSERT INTO t2 VALUES (1000); + +--error ER_INVALID_GROUP_FUNC_USE +SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1; +INSERT INTO t1 VALUES (1); +--error ER_INVALID_GROUP_FUNC_USE +SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1; +INSERT INTO t1 VALUES (1); +--error ER_INVALID_GROUP_FUNC_USE +SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1; +INSERT INTO t1 VALUES (1); +--error ER_INVALID_GROUP_FUNC_USE +SELECT SUM( (SELECT COUNT(a) FROM t2) ) FROM t1; +--error ER_INVALID_GROUP_FUNC_USE +SELECT SUM( (SELECT SUM(COUNT(a)) FROM t2) ) FROM t1; +SELECT COUNT(1) FROM t2; + +--error ER_INVALID_GROUP_FUNC_USE +SELECT SUM( (SELECT AVG( (SELECT t1.a FROM t2) ) FROM t2) ) FROM t1; + +--error ER_INVALID_GROUP_FUNC_USE +SELECT + SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING t1.a < 12) ) FROM t2) ) +FROM t1; + +--error ER_INVALID_GROUP_FUNC_USE +SELECT t1.a as XXA, + SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING XXA < 12) ) FROM t2) ) +FROM t1; + +DROP TABLE t1,t2;