bk commit into 5.0 tree (gkodinov:1.2456) BUG#27363

[kgeorge]

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;