bk commit into 5.0 tree (gkodinov:1.2253) BUG#21540

[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, 2006-09-08 19:04:46+03:00, gkodinov@stripped +4 -0
  Bug #21540: Subqueries with no from and aggregate functions return
              wrong results
   Mark the containing Item(s) (Item_subselect descendant usually) of 
   a subselect as containing aggregate functions if it has references
   to aggregates functions that are calculated outside its context.
   This tels end_send_group() not to make an Item_subselect descendant in
   select list a copy and causes the correct value being returned.

  mysql-test/r/func_group.result@stripped, 2006-09-08 19:04:38+03:00, gkodinov@stripped +1
-1
    Bug #21540: Subqueries with no from and aggregate functions return
                wrong results
     - test cases

  mysql-test/r/subselect.result@stripped, 2006-09-08 19:04:39+03:00, gkodinov@stripped +61
-0
    Bug #21540: Subqueries with no from and aggregate functions return
                wrong results
     - fixed the result of an existing testcase.

  mysql-test/t/subselect.test@stripped, 2006-09-08 19:04:40+03:00, gkodinov@stripped +31
-0
    Bug #21540: Subqueries with no from and aggregate functions return
                wrong results
     - test cases

  sql/item_sum.cc@stripped, 2006-09-08 19:04:41+03:00, gkodinov@stripped +21 -1
    Bug #21540: Subqueries with no from and aggregate functions return
                wrong results
     Mark the containing Item (Item_subselect descendant usually) of 
     a subselect as containing aggregate functions if it has references
     to aggregates functions that are calculated outside its context.
     This tels end_send_group() not to make an Item_subselect descendant in
     select list a copy and causes the correct value being returned.

# 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:	macbook.gmz
# Root:	/Users/kgeorge/mysql/work/B21540-5.0-opt

--- 1.179/sql/item_sum.cc	2006-09-08 19:05:03 +03:00
+++ 1.180/sql/item_sum.cc	2006-09-08 19:05:03 +03:00
@@ -246,7 +246,27 @@ bool Item_sum::register_sum_func(THD *th
       aggr_sl->inner_sum_func_list->next= this;
     }
     aggr_sl->inner_sum_func_list= this;
-      
+    aggr_sl->with_sum_func= 1;
+
+    /* 
+      Mark Item_subselect(s) as containing aggregate function all the way up
+      to aggregate function's calculation context.
+      Note that we must not mark the Item of calculation context itself
+      because with_sum_func on the calculation context st_select_lex is
+      already set above.
+
+      with_sum_func being set for an Item means that this Item refers 
+      (somewhere in it, e.g. one of its arguments if it's a function) directly
+      or through intermediate items to an aggregate function that is calculated
+      in a context "outside" of the Item (e.g. in the current or outer select).
+
+      with_sum_func being set for an st_select_lex means that this st_select_lex
+      has aggregate functions directly referenced (i.e. not through a sub-select).
+    */
+    for (sl= thd->lex->current_select; 
+         sl && sl != aggr_sl && sl->master_unit()->item;
+         sl= sl->master_unit()->outer_select() )
+      sl->master_unit()->item->with_sum_func= 1;
   }
   return FALSE;
 }

--- 1.154/mysql-test/r/subselect.result	2006-09-08 19:05:03 +03:00
+++ 1.155/mysql-test/r/subselect.result	2006-09-08 19:05:03 +03:00
@@ -3368,3 +3368,64 @@ ORDER BY t1.t DESC LIMIT 1);
 i1	i2	t	i1	i2	t
 24	1	2005-05-27 12:40:30	24	1	2006-06-20 12:29:40
 DROP TABLE t1, t2;
+CREATE TABLE t1 (a INT, b INT);
+CREATE TABLE t2 (a INT);
+INSERT INTO t2 values (1);
+INSERT INTO t1 VALUES (1,1),(1,2),(2,3),(3,4);
+SELECT (SELECT COUNT(DISTINCT t1.b) from t2) FROM t1 GROUP BY t1.a;
+(SELECT COUNT(DISTINCT t1.b) from t2)
+2
+1
+1
+SELECT (SELECT COUNT(DISTINCT t1.b) from t2 union select 1 from t2 where 12 < 3)
+FROM t1 GROUP BY t1.a;
+(SELECT COUNT(DISTINCT t1.b) from t2 union select 1 from t2 where 12 < 3)
+2
+1
+1
+SELECT COUNT(DISTINCT t1.b), (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a;
+COUNT(DISTINCT t1.b)	(SELECT COUNT(DISTINCT t1.b))
+2	2
+1	1
+1	1
+SELECT COUNT(DISTINCT t1.b), 
+(SELECT COUNT(DISTINCT t1.b) union select 1 from DUAL where 12 < 3)
+FROM t1 GROUP BY t1.a;
+COUNT(DISTINCT t1.b)	(SELECT COUNT(DISTINCT t1.b) union select 1 from DUAL where 12 <
3)
+2	2
+1	1
+1	1
+SELECT (
+SELECT (
+SELECT COUNT(DISTINCT t1.b)
+)
+) 
+FROM t1 GROUP BY t1.a;
+(
+SELECT (
+SELECT COUNT(DISTINCT t1.b)
+)
+)
+2
+1
+1
+SELECT (
+SELECT (
+SELECT (
+SELECT COUNT(DISTINCT t1.b)
+)
+) 
+FROM t1 GROUP BY t1.a LIMIT 1) 
+FROM t1 t2
+GROUP BY t2.a;
+(
+SELECT (
+SELECT (
+SELECT COUNT(DISTINCT t1.b)
+)
+) 
+FROM t1 GROUP BY t1.a LIMIT 1)
+2
+2
+2
+DROP TABLE t1,t2;

--- 1.125/mysql-test/t/subselect.test	2006-09-08 19:05:03 +03:00
+++ 1.126/mysql-test/t/subselect.test	2006-09-08 19:05:03 +03:00
@@ -2280,3 +2280,34 @@ SELECT * FROM t1,t2
                     ORDER BY t1.t DESC LIMIT 1);
 
 DROP TABLE t1, t2;
+
+#
+# Bug #21540: Subqueries with no from and aggregate functions return 
+#              wrong results
+CREATE TABLE t1 (a INT, b INT);
+CREATE TABLE t2 (a INT);
+INSERT INTO t2 values (1);
+INSERT INTO t1 VALUES (1,1),(1,2),(2,3),(3,4);
+SELECT (SELECT COUNT(DISTINCT t1.b) from t2) FROM t1 GROUP BY t1.a;
+SELECT (SELECT COUNT(DISTINCT t1.b) from t2 union select 1 from t2 where 12 < 3)
+  FROM t1 GROUP BY t1.a;
+SELECT COUNT(DISTINCT t1.b), (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a;
+SELECT COUNT(DISTINCT t1.b), 
+       (SELECT COUNT(DISTINCT t1.b) union select 1 from DUAL where 12 < 3)
+  FROM t1 GROUP BY t1.a;
+SELECT (
+    SELECT (
+      SELECT COUNT(DISTINCT t1.b)
+    )
+) 
+FROM t1 GROUP BY t1.a;
+SELECT (
+  SELECT (
+      SELECT (
+        SELECT COUNT(DISTINCT t1.b)
+      )
+  ) 
+  FROM t1 GROUP BY t1.a LIMIT 1) 
+FROM t1 t2
+GROUP BY t2.a;
+DROP TABLE t1,t2;  

--- 1.49/mysql-test/r/func_group.result	2006-09-08 19:05:03 +03:00
+++ 1.50/mysql-test/r/func_group.result	2006-09-08 19:05:03 +03:00
@@ -997,7 +997,7 @@ SELECT SQL_NO_CACHE 
 WHERE ttt.a = ccc.b AND ttt.a = t.a GROUP BY ttt.a) AS minid   
 FROM t1 t, t2 c WHERE t.a = c.b;
 minid
-NULL
+1
 DROP TABLE t1,t2;
 create table t1 select variance(0);
 show create table t1;