Bug#27219: Aggregate functions in ORDER BY.

Mixing aggregate functions and non-grouping columns is not allowed in the
ONLY_FULL_GROUP_BY mode. However in some cases the error wasn't thrown because
of insufficient check.

In order to check more thoroughly the new algorithm employs a list of outer
fields used in a sum function and a SELECT_LEX::full_group_by_flag.
Each non-outer field checked to find out whether it's aggregated or not and
the current select is marked accordingly.
All outer fields that are used under an aggregate function are added to the
Item_sum::outer_fields list and later checked by the Item_sum::check_sum_func
function.

1 files changed, 64 insertions, 0 deletions

diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index 3d6d46ab3f4..42c934261b0 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -66,6 +66,7 @@ bool Item_sum::init_sum_func_check(THD *thd)
   aggr_sel= NULL;
   max_arg_level= -1;
   max_sum_func_level= -1;
+  outer_fields.empty();
   return FALSE;
 }
 
@@ -175,6 +176,7 @@ bool Item_sum::check_sum_func(THD *thd, Item **ref)
                MYF(0));
     return TRUE;
   }
+
   if (in_sum_func)
   {
     /*
@@ -195,6 +197,68 @@ bool Item_sum::check_sum_func(THD *thd, Item **ref)
       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);
   }
+
+  /*
+    Check that non-aggregated fields and sum functions aren't mixed in the
+    same select in the ONLY_FULL_GROUP_BY mode.
+  */
+  if (outer_fields.elements)
+  {
+    Item_field *field;
+    /*
+      Here we compare the nesting level of the select to which an outer field
+      belongs to with the aggregation level of the sum function. All fields in
+      the outer_fields list are checked.
+
+      If the nesting level is equal to the aggregation level then the field is
+        aggregated by this sum function.
+      If the nesting level is less than the aggregation level then the field
+        belongs to an outer select. In this case if there is an embedding sum
+        function add current field to functions outer_fields list. If there is
+        no embedding function then the current field treated as non aggregated
+        and the select it belongs to is marked accordingly.
+      If the nesting level is greater than the aggregation level then it means
+        that this field was added by an inner sum function.
+        Consider an example:
+
+          select avg ( <-- we are here, checking outer.f1
+            select (
+              select sum(outer.f1 + inner.f1) from inner
+            ) from outer)
+          from most_outer;
+
+        In this case we check that no aggregate functions are used in the
+        select the field belongs to. If there are some then an error is
+        raised.
+    */
+    List_iterator<Item_field> of(outer_fields);
+    while ((field= of++))
+    {
+      SELECT_LEX *sel= field->cached_table->select_lex;
+      if (sel->nest_level < aggr_level)
+      {
+        if (in_sum_func)
+        {
+          /*
+            Let upper function decide whether this field is a non
+            aggregated one.
+          */
+          in_sum_func->outer_fields.push_back(field);
+        }
+        else
+          sel->full_group_by_flag|= NON_AGG_FIELD_USED;
+      }
+      if (sel->nest_level > aggr_level &&
+          (sel->full_group_by_flag & SUM_FUNC_USED) &&
+          !sel->group_list.elements)
+      {
+        my_message(ER_MIX_OF_GROUP_FUNC_AND_FIELDS,
+                   ER(ER_MIX_OF_GROUP_FUNC_AND_FIELDS), MYF(0));
+        return TRUE;
+      }
+    }
+  }
+  aggr_sel->full_group_by_flag|= SUM_FUNC_USED;
   update_used_tables();
   thd->lex->in_sum_func= in_sum_func;
   return FALSE;