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-26 10:46:13+03:00, gkodinov@stripped +4 -0
Bug #27363:
Validity checks for nested set functions
were not taking into account that the enclosed
set function may be on a nest level that is
lower than the nest level of the enclosing set
function.
Fixed by :
- propagating max_sum_func_level
up the enclosing set functions chain.
- updating the max_sum_func_level of the
enclosing set function when the enclosed set
function is aggregated above or on the same
nest level of as the level of the enclosing
set function.
- updating the max_arg_level of the enclosing
set function on a reference that refers to
an item above or on the same nest level
as the level of the enclosing set function.
- Treating both Item_field and Item_ref as possibly
referencing items from outer nest levels.
mysql-test/r/subselect.result@stripped, 2007-04-26 10:46:12+03:00, gkodinov@stripped +23 -0
Bug #27363: test cases
mysql-test/t/subselect.test@stripped, 2007-04-26 10:46:12+03:00, gkodinov@stripped +30 -0
Bug #27363: test cases
sql/item.cc@stripped, 2007-04-26 10:46:12+03:00, gkodinov@stripped +26 -2
Bug #27363:
Treat the reference as an outer reference for the
enclosing set function even if it's referencing
an item that is above the nest level of the
enclosing set function.
Consider both Item_field and Item_ref.
sql/item_sum.cc@stripped, 2007-04-26 10:46:12+03:00, gkodinov@stripped +14 -2
Bug #27363: Use the enclosed set function aggregation
level to mark the enclosing set function even
if it's aggregated on a level that is above the
nest level of the enclosing set function.
Pass max_sum_func_level up the accending branch of the
recursion because it must take into account each
directly or indirectly nested set function.
# 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-5.0-opt
--- 1.260/sql/item.cc 2007-03-28 14:35:22 +03:00
+++ 1.261/sql/item.cc 2007-04-26 10:46:12 +03:00
@@ -3538,9 +3538,13 @@ Item_field::fix_outer_field(THD *thd, Fi
thd->restore_active_arena(arena, &backup);
fixed_as_field= 1;
}
+ /*
+ A reference is resolved to a nest level that's outer or the same as
+ the nest level of the enclosing set funcion : adjust the value of
+ max_arg_level for the function if it's needed.
+ */
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,
@@ -5182,6 +5186,16 @@ 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);
+ /*
+ A reference is resolved to a nest level that's outer or the same as
+ the nest level of the enclosing set funcion : adjust the value of
+ max_arg_level for the function if it's needed.
+ */
+ 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)
@@ -5195,6 +5209,16 @@ 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);
+ /*
+ A reference is resolved to a nest level that's outer or the same as
+ the nest level of the enclosing set funcion : adjust the value of
+ max_arg_level for the function if it's needed.
+ */
+ 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.204/sql/item_sum.cc 2007-03-29 19:20:31 +03:00
+++ 1.205/sql/item_sum.cc 2007-04-26 10:46:12 +03:00
@@ -175,13 +175,25 @@ bool Item_sum::check_sum_func(THD *thd,
MYF(0));
return TRUE;
}
- if (in_sum_func && in_sum_func->nest_level == nest_level)
+ if (in_sum_func)
{
/*
If the set function is nested adjust the value of
max_sum_func_level for the nesting set function.
+ We take into account only enclosed set functions that are to be
+ aggregated on the same level or above of the nest level of
+ the enclosing set function.
+ But we must always pass up the max_sum_func_level because it is
+ the maximum nested level of all directly and indirectly enclosed
+ set functions. We must do that even for set functions that are
+ aggregated inside of their enclosing set function's nest level
+ because the enclosing function may contain another enclosing
+ function that is to be aggregated outside or on the same level
+ as its parent's nest level.
*/
- set_if_bigger(in_sum_func->max_sum_func_level, aggr_level);
+ if (in_sum_func->nest_level >= aggr_level)
+ set_if_bigger(in_sum_func->max_sum_func_level, aggr_level);
+ set_if_bigger(in_sum_func->max_sum_func_level, max_sum_func_level);
}
update_used_tables();
thd->lex->in_sum_func= in_sum_func;
--- 1.182/mysql-test/r/subselect.result 2007-03-27 19:48:07 +03:00
+++ 1.183/mysql-test/r/subselect.result 2007-04-26 10:46:12 +03:00
@@ -3947,3 +3947,26 @@ WHERE (SELECT COUNT(t0.b) FROM t1 t WHER
ERROR HY000: Invalid use of group function
SET @@sql_mode=default;
DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 values (1),(1),(1),(1);
+CREATE TABLE t2 (x INT);
+INSERT INTO t1 values (1000),(1001),(1002);
+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 DUAL;
+COUNT(1)
+1
+SELECT SUM( (SELECT AVG( (SELECT t1.a FROM t2) ) FROM DUAL) ) 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;
+End of 5.0 tests.
--- 1.147/mysql-test/t/subselect.test 2007-03-27 19:48:07 +03:00
+++ 1.148/mysql-test/t/subselect.test 2007-04-26 10:46:12 +03:00
@@ -2809,3 +2809,33 @@ SELECT a FROM t1 t0
SET @@sql_mode=default;
DROP TABLE t1;
+
+#
+# Bug #27363: nested aggregates in outer, subquery / sum(select
+# count(outer))
+#
+CREATE TABLE t1 (a INT); INSERT INTO t1 values (1),(1),(1),(1);
+CREATE TABLE t2 (x INT); INSERT INTO t1 values (1000),(1001),(1002);
+
+--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 DUAL;
+
+--error ER_INVALID_GROUP_FUNC_USE
+SELECT SUM( (SELECT AVG( (SELECT t1.a FROM t2) ) FROM DUAL) ) 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;
+
+--echo End of 5.0 tests.
| Thread |
|---|
| • bk commit into 5.0 tree (gkodinov:1.2424) BUG#27363 | kgeorge | 26 Apr |
