MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:kgeorge Date:June 29 2007 7:39am
Subject:bk commit into 5.0 tree (gkodinov:1.2507) BUG#27333
View as plain text  
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-06-29 10:39:17+03:00, gkodinov@stripped +7 -0
  Bug#27333: subquery grouped for aggregate of outer 
  query / no aggregate of subquery
   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 collecting 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 some (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/subselect.result@stripped, 2007-06-29 10:39:15+03:00, gkodinov@stripped +33 -0
    Bug #27333: test case

  mysql-test/t/subselect.test@stripped, 2007-06-29 10:39:15+03:00, gkodinov@stripped +42 -0
    Bug #27333: test case

  sql/item_subselect.cc@stripped, 2007-06-29 10:39:15+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-06-29 10:39:15+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-06-29 10:39:15+03:00, gkodinov@stripped +8 -1
    Bug#27333: calculate the place of 
     aggregation for user defined functions.

  sql/sql_select.cc@stripped, 2007-06-29 10:39:15+03:00, gkodinov@stripped +30 -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-06-29 10:39:15+03:00, gkodinov@stripped +2 -2
    Bug#27333: need select_lex to filter out
     aggregates that are not aggregated in
     the current select. 

diff -Nrup a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
--- a/mysql-test/r/subselect.result	2007-06-08 08:35:28 +03:00
+++ b/mysql-test/r/subselect.result	2007-06-29 10:39:15 +03:00
@@ -4106,4 +4106,37 @@ d1
 1
 1
 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
+SELECT (SELECT SUM(t1.a)/AVG(t2.x) FROM t2) FROM t1;
+(SELECT SUM(t1.a)/AVG(t2.x) FROM t2)
+3.3333
+DROP TABLE t1,t2;
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 VALUES (1, 2), (1,3), (1,4), (2,1), (2,2);
+SELECT a1.a, COUNT(*) FROM t1 a1 WHERE a1.a = 1
+AND EXISTS( SELECT a2.a FROM t1 a2 WHERE a2.a = a1.a)
+GROUP BY a1.a;
+a	COUNT(*)
+1	3
+DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+INSERT INTO t2 VALUES (1),(2);
+SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=0) FROM t1;
+(SELECT SUM(t1.a) FROM t2 WHERE a=0)
+NULL
+SELECT (SELECT SUM(t1.a) FROM t2 WHERE a!=0) FROM t1;
+ERROR 21000: Subquery returns more than 1 row
+SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=1) FROM t1;
+(SELECT SUM(t1.a) FROM t2 WHERE a=1)
+3
+DROP TABLE t1,t2;
 End of 5.0 tests.
diff -Nrup a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
--- a/mysql-test/t/subselect.test	2007-06-08 08:35:28 +03:00
+++ b/mysql-test/t/subselect.test	2007-06-29 10:39:15 +03:00
@@ -2945,4 +2945,46 @@ SELECT (SELECT 1 FROM  t1 WHERE t1.a=t2.
 
 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;
+
+SELECT (SELECT SUM(t1.a)/AVG(t2.x) FROM t2) FROM t1;
+DROP TABLE t1,t2;
+
+# second test case from 27333
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 VALUES (1, 2), (1,3), (1,4), (2,1), (2,2);
+
+-- returns no rows, when it should
+SELECT a1.a, COUNT(*) FROM t1 a1 WHERE a1.a = 1
+AND EXISTS( SELECT a2.a FROM t1 a2 WHERE a2.a = a1.a)
+GROUP BY a1.a;
+DROP TABLE t1;
+
+#test cases from 29297
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+INSERT INTO t2 VALUES (1),(2);
+SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=0) FROM t1;
+--error ER_SUBQUERY_NO_1_ROW
+SELECT (SELECT SUM(t1.a) FROM t2 WHERE a!=0) FROM t1;
+SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=1) FROM t1;
+DROP TABLE t1,t2;
+
 --echo End of 5.0 tests.
diff -Nrup a/sql/item_subselect.cc b/sql/item_subselect.cc
--- a/sql/item_subselect.cc	2007-05-21 10:20:24 +03:00
+++ b/sql/item_subselect.cc	2007-06-29 10:39:15 +03:00
@@ -942,7 +942,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);
     }
diff -Nrup a/sql/item_sum.cc b/sql/item_sum.cc
--- a/sql/item_sum.cc	2007-05-22 15:45:56 +03:00
+++ b/sql/item_sum.cc	2007-06-29 10:39:15 +03:00
@@ -2461,7 +2461,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,
@@ -3265,7 +3265,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);
   /*
diff -Nrup a/sql/item_sum.h b/sql/item_sum.h
--- a/sql/item_sum.h	2007-03-22 23:48:00 +02:00
+++ b/sql/item_sum.h	2007-06-29 10:39:15 +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; }
diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc	2007-06-27 15:02:28 +03:00
+++ b/sql/sql_select.cc	2007-06-29 10:39:15 +03:00
@@ -611,7 +611,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;
@@ -1766,7 +1766,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= 
@@ -1886,11 +1886,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)))
@@ -13678,8 +13680,8 @@ next_item:
 *****************************************************************************/
 
 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;
@@ -13697,18 +13699,22 @@ 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->depended_from() ||
+            sum_item->depended_from() == 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
@@ -14170,7 +14176,9 @@ 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)->depended_from() ||
+         ((Item_sum *)item)->depended_from() == select_lex))
       *func++= (Item_sum*) item;
   }
   if (before_group_by && rollup.state == ROLLUP::STATE_INITED)
@@ -14752,7 +14760,10 @@ 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)->depended_from() ||
+           ((Item_sum *)item)->depended_from() == select_lex))
+          
       {
 	/*
 	  This is a top level summary function that must be replaced with
diff -Nrup a/sql/sql_select.h b/sql/sql_select.h
--- a/sql/sql_select.h	2007-05-12 05:37:30 +03:00
+++ b/sql/sql_select.h	2007-06-29 10:39:15 +03:00
@@ -470,8 +470,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,
Thread
bk commit into 5.0 tree (gkodinov:1.2507) BUG#27333kgeorge29 Jun