bk commit into 5.0 tree (gkodinov:1.2208) BUG#20466

[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-07-21 20:44:35+03:00, gkodinov@stripped +3 -0
  Bug #20466: a view is mixing data when there's a trigger on the table
    When making a place to store field values at the start of each group
    the real item (not the reference) must be used when deciding which column
    to copy.

  mysql-test/r/group_by.result@stripped, 2006-07-21 20:44:27+03:00, gkodinov@stripped +25 -0
    Bug #20466: a view is mixing data when there's a trigger on the table
     - test suite for the bug

  mysql-test/t/group_by.test@stripped, 2006-07-21 20:44:28+03:00, gkodinov@stripped +23 -0
    Bug #20466: a view is mixing data when there's a trigger on the table
     - test suite for the bug

  sql/sql_select.cc@stripped, 2006-07-21 20:44:29+03:00, gkodinov@stripped +9 -7
    Bug #20466: a view is mixing data when there's a trigger on the table
     - deal correctly with references

# 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.mshome.net
# Root:	/Users/kgeorge/mysql/work/B20466-5.0-opt

--- 1.434/sql/sql_select.cc	2006-07-21 20:44:50 +03:00
+++ 1.435/sql/sql_select.cc	2006-07-21 20:44:50 +03:00
@@ -13066,10 +13066,11 @@ setup_copy_fields(THD *thd, TMP_TABLE_PA
   param->copy_funcs.empty();
   for (i= 0; (pos= li++); i++)
   {
-    if (pos->real_item()->type() == Item::FIELD_ITEM)
+    Item *real_pos= pos->real_item();
+    if (real_pos->type() == Item::FIELD_ITEM)
     {
       Item_field *item;
-      pos= pos->real_item();
+      pos= real_pos;
       if (!(item= new Item_field(thd, ((Item_field*) pos))))
 	goto err;
       pos= item;
@@ -13108,12 +13109,13 @@ setup_copy_fields(THD *thd, TMP_TABLE_PA
       }				
       }
     }
-    else if ((pos->type() == Item::FUNC_ITEM ||
-	      pos->type() == Item::SUBSELECT_ITEM ||
-	      pos->type() == Item::CACHE_ITEM ||
-	      pos->type() == Item::COND_ITEM) &&
-	     !pos->with_sum_func)
+    else if ((real_pos->type() == Item::FUNC_ITEM ||
+	      real_pos->type() == Item::SUBSELECT_ITEM ||
+	      real_pos->type() == Item::CACHE_ITEM ||
+	      real_pos->type() == Item::COND_ITEM) &&
+	     !real_pos->with_sum_func)
     {						// Save for send fields
+      pos= real_pos;
       /* TODO:
 	 In most cases this result will be sent to the user.
 	 This should be changed to use copy_int or copy_real depending

--- 1.68/mysql-test/r/group_by.result	2006-07-21 20:44:51 +03:00
+++ 1.69/mysql-test/r/group_by.result	2006-07-21 20:44:51 +03:00
@@ -796,3 +796,28 @@ aaa
 show warnings;
 Level	Code	Message
 drop table t1, t2;
+CREATE TABLE t1 (a tinyint(3), b varchar(255), PRIMARY KEY  (a));
+INSERT INTO t1 VALUES (1,'-----'), (6,'Allemagne'), (17,'Autriche'), 
+(25,'Belgique'), (54,'Danemark'), (62,'Espagne'), (68,'France');
+CREATE TABLE t2 (a tinyint(3), b tinyint(3), PRIMARY KEY  (a), KEY b (b));
+INSERT INTO t2 VALUES (1,1), (2,1), (6,6), (18,17), (15,25), (16,25),
+(17,25), (10,54), (5,62),(3,68);
+CREATE VIEW v1 AS select t1.a, concat(t1.b,'') AS b, t1.b as real_b from t1;
+explain 
+SELECT straight_join sql_no_cache v1.a, v1.b, v1.real_b from t2, v1
+where t2.b=v1.a GROUP BY t2.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	index	b	b	2	NULL	10	Using index
+1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	1	test.t2.b	1	
+SELECT straight_join sql_no_cache v1.a, v1.b, v1.real_b from t2, v1
+where t2.b=v1.a GROUP BY t2.b;
+a	b	real_b
+1	-----	-----
+6	Allemagne	Allemagne
+17	Autriche	Autriche
+25	Belgique	Belgique
+54	Danemark	Danemark
+62	Espagne	Espagne
+68	France	France
+DROP VIEW v1;
+DROP TABLE t1,t2;

--- 1.56/mysql-test/t/group_by.test	2006-07-21 20:44:51 +03:00
+++ 1.57/mysql-test/t/group_by.test	2006-07-21 20:44:51 +03:00
@@ -632,3 +632,26 @@ group by t1.c1;
 show warnings;
 drop table t1, t2;
 
+#
+# Bug #20466: a view is mixing data when there's a trigger on the table
+#
+CREATE TABLE t1 (a tinyint(3), b varchar(255), PRIMARY KEY  (a));
+
+INSERT INTO t1 VALUES (1,'-----'), (6,'Allemagne'), (17,'Autriche'), 
+    (25,'Belgique'), (54,'Danemark'), (62,'Espagne'), (68,'France');
+
+CREATE TABLE t2 (a tinyint(3), b tinyint(3), PRIMARY KEY  (a), KEY b (b));
+
+INSERT INTO t2 VALUES (1,1), (2,1), (6,6), (18,17), (15,25), (16,25),
+ (17,25), (10,54), (5,62),(3,68);
+
+CREATE VIEW v1 AS select t1.a, concat(t1.b,'') AS b, t1.b as real_b from t1;
+
+explain 
+SELECT straight_join sql_no_cache v1.a, v1.b, v1.real_b from t2, v1
+where t2.b=v1.a GROUP BY t2.b;
+SELECT straight_join sql_no_cache v1.a, v1.b, v1.real_b from t2, v1
+where t2.b=v1.a GROUP BY t2.b;
+
+DROP VIEW v1;
+DROP TABLE t1,t2;