bk commit into 5.0 tree (gkodinov:1.2261)

[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:27:14+03:00, gkodinov@rakia.(none) +4 -0
  Merge gkodinov@stripped:/home/bk/mysql-5.0-opt
  into  rakia.(none):/home/kgeorge/mysql/autopush/B21540-5.0-opt
  MERGE: 1.2252.1.1

  mysql-test/r/func_group.result@stripped, 2006-09-08 19:20:29+03:00, gkodinov@rakia.(none) +0
-0
    Auto merged
    MERGE: 1.49.1.1

  mysql-test/r/subselect.result@stripped, 2006-09-08 19:26:14+03:00, gkodinov@rakia.(none)
+25 -25
    MERGE: 1.154.1.1

  mysql-test/t/subselect.test@stripped, 2006-09-08 19:27:10+03:00, gkodinov@rakia.(none) +26
-26
    MERGE: 1.125.1.1

  sql/item_sum.cc@stripped, 2006-09-08 19:20:29+03:00, gkodinov@rakia.(none) +0 -0
    Auto merged
    MERGE: 1.179.1.1

# 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:	rakia.(none)
# Root:	/home/kgeorge/mysql/autopush/B21540-5.0-opt/RESYNC

--- 1.180/sql/item_sum.cc	2006-09-08 19:27:20 +03:00
+++ 1.181/sql/item_sum.cc	2006-09-08 19:27:20 +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.156/mysql-test/r/subselect.result	2006-09-08 19:27:20 +03:00
+++ 1.157/mysql-test/r/subselect.result	2006-09-08 19:27:20 +03:00
@@ -3393,3 +3393,64 @@ id	select_type	table	type	possible_keys	
 4	UNION	t12	system	NULL	NULL	NULL	NULL	0	const row not found
 NULL	UNION RESULT	<union2,4>	ALL	NULL	NULL	NULL	NULL	NULL	
 DROP TABLE t1;
+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.126/mysql-test/t/subselect.test	2006-09-08 19:27:20 +03:00
+++ 1.127/mysql-test/t/subselect.test	2006-09-08 19:27:20 +03:00
@@ -2306,3 +2306,34 @@ explain select * from t1 where not exist
   ((select t11.i from t1 t11) union (select t12.i from t1 t12));
 
 DROP TABLE t1;
+
+#
+# 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.51/mysql-test/r/func_group.result	2006-09-08 19:27:20 +03:00
+++ 1.52/mysql-test/r/func_group.result	2006-09-08 19:27:20 +03:00
@@ -1013,7 +1013,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;