List:Commits« Previous MessageNext Message »
From:kgeorge Date:August 31 2006 12:43pm
Subject:bk commit into 5.0 tree (gkodinov:1.2253) BUG#21540
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, 2006-08-31 15:43:26+03:00, gkodinov@stripped +4 -0
  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.
   Note that for unions it must set the item referenced in the outermost 
   union level.

  mysql-test/r/func_group.result@stripped, 2006-08-31 15:43:17+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-08-31 15:43:19+03:00, gkodinov@stripped +28 -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-08-31 15:43:19+03:00, gkodinov@stripped +16 -0
    Bug #21540: Subqueries with no from and aggregate functions return
                wrong results
     - test cases

  sql/item_sum.cc@stripped, 2006-08-31 15:43:20+03:00, gkodinov@stripped +13 -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-08-31 15:43:43 +03:00
+++ 1.180/sql/item_sum.cc	2006-08-31 15:43:43 +03:00
@@ -246,7 +246,19 @@ bool Item_sum::register_sum_func(THD *th
       aggr_sl->inner_sum_func_list->next= this;
     }
     aggr_sl->inner_sum_func_list= this;
-      
+
+    /* find the innermost non-union level above the current level */
+    st_select_lex_unit *top_union_unit, *last_union_unit;
+    for (last_union_unit= top_union_unit= 
+         thd->lex->current_select->master_unit(); 
+         top_union_unit && top_union_unit->first_select()->next_select();
+         top_union_unit= top_union_unit->outer_select()->master_unit())
+      last_union_unit= top_union_unit;
+    /* 
+      and mark it as having an aggregate that is calculated in an outer
+      level.
+    */
+    last_union_unit->item->with_sum_func= 1;
   }
   return FALSE;
 }

--- 1.154/mysql-test/r/subselect.result	2006-08-31 15:43:43 +03:00
+++ 1.155/mysql-test/r/subselect.result	2006-08-31 15:43:43 +03:00
@@ -3368,3 +3368,31 @@ 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
+DROP TABLE t1,t2;

--- 1.125/mysql-test/t/subselect.test	2006-08-31 15:43:43 +03:00
+++ 1.126/mysql-test/t/subselect.test	2006-08-31 15:43:43 +03:00
@@ -2280,3 +2280,19 @@ 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;
+DROP TABLE t1,t2;  

--- 1.49/mysql-test/r/func_group.result	2006-08-31 15:43:43 +03:00
+++ 1.50/mysql-test/r/func_group.result	2006-08-31 15:43:43 +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;
Thread
bk commit into 5.0 tree (gkodinov:1.2253) BUG#21540kgeorge31 Aug