bk commit into 5.0 tree (gkodinov:1.2424) 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-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.