bk commit into 5.0 tree (gkodinov:1.2422) BUG#27333

[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-03-29 15:15:11+03:00, gkodinov@stripped +7 -0
  Bug#27333:
   The optimizer counts the aggregate functions that 
   appear as top level expressions (in all_fields) in 
   the current subquery. Later it makes a list of these
   that it uses to actually execute the aggregates in
   end_send_group().
   That count is used in several places as a flag whether
   there are aggregates functions.
   While collection the above info it must not consider
   aggregates that are not aggregated in the current 
   context. It must treat them as normal expressions 
   instead. Not doing that leads to incorrect data about
   the query, e.g. running a query that actually has no
   aggregate functions as if it has them (and hence is
   expected to return only one row).
   Fixed by ignoring the aggregates that are not aggregated
   in the current context. 
   One other smaller omission discovered and fixed in the 
   process : the place of aggregation was not calculated for
   user defined functions. Fixed by calling 
   Item_sum::init_sum_func_check() and 
   Item_sum::check_sum_func() as it's done for the rest of 
   the aggregate functions.

  mysql-test/r/subselect3.result@stripped, 2007-03-29 15:15:09+03:00, gkodinov@stripped +9 -0
    Bug#27333: test case

  mysql-test/t/subselect3.test@stripped, 2007-03-29 15:15:09+03:00, gkodinov@stripped +18 -0
    Bug#27333: test case

  sql/item_subselect.cc@stripped, 2007-03-29 15:15:09+03:00, gkodinov@stripped +2 -1
    Bug#27333: need select_lex to filter out
     aggregates that are not aggregated in
     the current select. 

  sql/item_sum.cc@stripped, 2007-03-29 15:15:09+03:00, gkodinov@stripped +2 -2
    Bug#27333: need select_lex to filter out
     aggregates that are not aggregated in
     the current select. 

  sql/item_sum.h@stripped, 2007-03-29 15:15:09+03:00, gkodinov@stripped +8 -1
    Bug#27333: calculate the place of 
     aggregation for user defined functions.

  sql/sql_select.cc@stripped, 2007-03-29 15:15:09+03:00, gkodinov@stripped +26 -19
    Bug#27333: When counting the aggregated functions
     and collecting a list of them we must not consider
     the aggregates that are not aggregated in the local
     context as "local" : i.e. we must treat them as 
     normal functions and not add them to the aggregate
     functions list. 

  sql/sql_select.h@stripped, 2007-03-29 15:15:10+03:00, gkodinov@stripped +2 -2
    Bug#27333: need select_lex to filter out
     aggregates that are not aggregated in
     the current select. 

# 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/B27333-5.0-opt

--- 1.201/sql/item_sum.cc	2007-03-26 09:44:03 +03:00
+++ 1.202/sql/item_sum.cc	2007-03-29 15:15:09 +03:00
@@ -2469,7 +2469,7 @@ bool Item_sum_count_distinct::setup(THD 
   }
   if (always_null)
     return FALSE;
-  count_field_types(tmp_table_param,list,0);
+  count_field_types(select_lex, tmp_table_param,list,0);
   tmp_table_param->force_copy_fields= force_copy_fields;
   DBUG_ASSERT(table == 0);
   if (!(table= create_tmp_table(thd, tmp_table_param, list, (ORDER*) 0, 1,
@@ -3273,7 +3273,7 @@ bool Item_func_group_concat::setup(THD *
       setup_order(thd, args, context->table_list, list, all_fields, *order))
     DBUG_RETURN(TRUE);
 
-  count_field_types(tmp_table_param,all_fields,0);
+  count_field_types(select_lex, tmp_table_param,all_fields,0);
   tmp_table_param->force_copy_fields= force_copy_fields;
   DBUG_ASSERT(table == 0);
   /*

--- 1.112/sql/item_sum.h	2007-03-22 23:48:00 +02:00
+++ 1.113/sql/item_sum.h	2007-03-29 15:15:09 +03:00
@@ -966,8 +966,15 @@ public:
   bool fix_fields(THD *thd, Item **ref)
   {
     DBUG_ASSERT(fixed == 0);
+
+    if (init_sum_func_check(thd))
+      return TRUE;
+
     fixed= 1;
-    return udf.fix_fields(thd, this, this->arg_count, this->args);
+    if (udf.fix_fields(thd, this, this->arg_count, this->args))
+      return TRUE;
+
+    return check_sum_func(thd, ref);
   }
   enum Sumfunctype sum_func () const { return UDF_SUM_FUNC; }
   virtual bool have_field_update(void) const { return 0; }

--- 1.502/sql/sql_select.cc	2007-03-26 09:44:03 +03:00
+++ 1.503/sql/sql_select.cc	2007-03-29 15:15:09 +03:00
@@ -568,7 +568,7 @@ JOIN::prepare(Item ***rref_pointer_array
     goto err;					/* purecov: inspected */
 
   /* Init join struct */
-  count_field_types(&tmp_table_param, all_fields, 0);
+  count_field_types(select_lex, &tmp_table_param, all_fields, 0);
   ref_pointer_array_size= all_fields.elements*sizeof(Item*);
   this->group= group_list != 0;
   unit= unit_arg;
@@ -1698,7 +1698,7 @@ JOIN::exec()
       if (make_simple_join(curr_join, curr_tmp_table))
 	DBUG_VOID_RETURN;
       calc_group_buffer(curr_join, group_list);
-      count_field_types(&curr_join->tmp_table_param,
+      count_field_types(select_lex, &curr_join->tmp_table_param,
 			curr_join->tmp_all_fields1,
 			curr_join->select_distinct && !curr_join->group_list);
       curr_join->tmp_table_param.hidden_field_count= 
@@ -1818,11 +1818,13 @@ JOIN::exec()
     if (make_simple_join(curr_join, curr_tmp_table))
       DBUG_VOID_RETURN;
     calc_group_buffer(curr_join, curr_join->group_list);
-    count_field_types(&curr_join->tmp_table_param, *curr_all_fields, 0);
+    count_field_types(select_lex, &curr_join->tmp_table_param, 
+                      *curr_all_fields, 0);
     
   }
   if (procedure)
-    count_field_types(&curr_join->tmp_table_param, *curr_all_fields, 0);
+    count_field_types(select_lex, &curr_join->tmp_table_param, 
+                      *curr_all_fields, 0);
   
   if (curr_join->group || curr_join->tmp_table_param.sum_func_count ||
       (procedure && (procedure->flags & PROC_GROUP)))
@@ -13554,8 +13556,8 @@ create_distinct_group(THD *thd, Item **r
 *****************************************************************************/
 
 void
-count_field_types(TMP_TABLE_PARAM *param, List<Item> &fields,
-		  bool reset_with_sum_func)
+count_field_types(SELECT_LEX *select_lex, TMP_TABLE_PARAM *param, 
+                  List<Item> &fields, bool reset_with_sum_func)
 {
   List_iterator<Item> li(fields);
   Item *field;
@@ -13573,18 +13575,21 @@ count_field_types(TMP_TABLE_PARAM *param
       if (! field->const_item())
       {
 	Item_sum *sum_item=(Item_sum*) field->real_item();
-	if (!sum_item->quick_group)
-	  param->quick_group=0;			// UDF SUM function
-	param->sum_func_count++;
-        param->func_count++;
+        if (sum_item->aggr_sel == select_lex)
+        {
+          if (!sum_item->quick_group)
+            param->quick_group=0;			// UDF SUM function
+          param->sum_func_count++;
 
-	for (uint i=0 ; i < sum_item->arg_count ; i++)
-	{
-	  if (sum_item->args[0]->real_item()->type() == Item::FIELD_ITEM)
-	    param->field_count++;
-	  else
-	    param->func_count++;
-	}
+          for (uint i=0 ; i < sum_item->arg_count ; i++)
+          {
+            if (sum_item->args[0]->real_item()->type() == Item::FIELD_ITEM)
+              param->field_count++;
+            else
+              param->func_count++;
+          }
+        }
+        param->func_count++;
       }
     }
     else
@@ -14046,7 +14051,8 @@ bool JOIN::make_sum_func_list(List<Item>
   func= sum_funcs;
   while ((item=it++))
   {
-    if (item->type() == Item::SUM_FUNC_ITEM && !item->const_item())
+    if (item->type() == Item::SUM_FUNC_ITEM && !item->const_item()
&&
+        ((Item_sum *)item)->aggr_sel == select_lex)
       *func++= (Item_sum*) item;
   }
   if (before_group_by && rollup.state == ROLLUP::STATE_INITED)
@@ -14595,7 +14601,8 @@ bool JOIN::rollup_make_fields(List<Item>
 	ref_array= ref_array_start;
       }
 
-      if (item->type() == Item::SUM_FUNC_ITEM && !item->const_item())
+      if (item->type() == Item::SUM_FUNC_ITEM && !item->const_item()
&&
+          ((Item_sum *)item)->aggr_sel == select_lex)
       {
 	/*
 	  This is a top level summary function that must be replaced with

--- 1.117/sql/sql_select.h	2007-03-22 18:49:45 +02:00
+++ 1.118/sql/sql_select.h	2007-03-29 15:15:10 +03:00
@@ -462,8 +462,8 @@ TABLE *create_tmp_table(THD *thd,TMP_TAB
 			ulonglong select_options, ha_rows rows_limit,
 			char* alias);
 void free_tmp_table(THD *thd, TABLE *entry);
-void count_field_types(TMP_TABLE_PARAM *param, List<Item> &fields,
-		       bool reset_with_sum_func);
+void count_field_types(SELECT_LEX *select_lex, TMP_TABLE_PARAM *param, 
+                       List<Item> &fields, bool reset_with_sum_func);
 bool setup_copy_fields(THD *thd, TMP_TABLE_PARAM *param,
 		       Item **ref_pointer_array,
 		       List<Item> &new_list1, List<Item> &new_list2,

--- 1.154/sql/item_subselect.cc	2007-03-09 07:05:05 +02:00
+++ 1.155/sql/item_subselect.cc	2007-03-29 15:15:09 +03:00
@@ -937,7 +937,8 @@ Item_in_subselect::single_value_transfor
 	DBUG_RETURN(RES_ERROR);
       thd->lex->allow_sum_func= save_allow_sum_func; 
       /* we added aggregate function => we have to change statistic */
-      count_field_types(&join->tmp_table_param, join->all_fields, 0);
+      count_field_types(select_lex, &join->tmp_table_param, join->all_fields, 
+                        0);
 
       subs= new Item_singlerow_subselect(select_lex);
     }

--- 1.7/mysql-test/r/subselect3.result	2007-03-28 12:09:29 +03:00
+++ 1.8/mysql-test/r/subselect3.result	2007-03-29 15:15:09 +03:00
@@ -692,3 +692,12 @@ a	MAX(b)	test
 2	3	h
 3	4	i
 DROP TABLE t1, t2;
+CREATE TABLE t1 (a INTEGER, b INTEGER);
+CREATE TABLE t2 (x INTEGER);
+INSERT INTO t1 VALUES (1,11), (2,22), (2,22);
+INSERT INTO t2 VALUES (1), (2);
+SELECT a, COUNT(b), (SELECT COUNT(b) FROM t2) FROM t1 GROUP BY a;
+ERROR 21000: Subquery returns more than 1 row
+SELECT a, COUNT(b), (SELECT COUNT(b)+0 FROM t2) FROM t1 GROUP BY a;
+ERROR 21000: Subquery returns more than 1 row
+DROP TABLE t1,t2;

--- 1.7/mysql-test/t/subselect3.test	2007-03-28 12:09:29 +03:00
+++ 1.8/mysql-test/t/subselect3.test	2007-03-29 15:15:09 +03:00
@@ -531,3 +531,21 @@ SELECT a, MAX(b),
 
 
 DROP TABLE t1, t2;
+
+#
+# Bug #27333: subquery grouped for aggregate of outer query / no aggregate
+# of subquery
+#
+CREATE TABLE t1 (a INTEGER, b INTEGER);
+CREATE TABLE t2 (x INTEGER);
+INSERT INTO t1 VALUES (1,11), (2,22), (2,22);
+INSERT INTO t2 VALUES (1), (2);
+
+# wasn't failing, but should
+--error ER_SUBQUERY_NO_1_ROW
+SELECT a, COUNT(b), (SELECT COUNT(b) FROM t2) FROM t1 GROUP BY a;
+
+# fails as it should
+--error ER_SUBQUERY_NO_1_ROW
+SELECT a, COUNT(b), (SELECT COUNT(b)+0 FROM t2) FROM t1 GROUP BY a;
+DROP TABLE t1,t2;