bzr commit into mysql-6.0 branch (kgeorge:2900)

[Georgi Kodinov]

#At file:///home/kgeorge/mysql/bzr/B33811-6.0-bugteam/

 2900 Georgi Kodinov	2008-11-03 [merge]
      merged bug 33811 -> 6.0-bugteam
modified:
  mysql-test/extra/rpl_tests/rpl_row_basic.test
  mysql-test/r/sp.result
  mysql-test/suite/rpl/r/rpl_row_basic_3innodb.result
  mysql-test/t/sp.test
  sql/sql_base.cc
  sql/sql_select.cc

=== modified file 'mysql-test/extra/rpl_tests/rpl_row_basic.test'
--- a/mysql-test/extra/rpl_tests/rpl_row_basic.test	2008-09-05 13:39:08 +0000
+++ b/mysql-test/extra/rpl_tests/rpl_row_basic.test	2008-11-03 12:12:00 +0000
@@ -471,3 +471,85 @@ source include/diff_tables.inc;
 connection master;
 drop table t1;
 sync_slave_with_master;
+
+#
+# Bug #38230  Differences between master and slave after 
+#             UPDATE or DELETE with LIMIT with pk
+#
+# the regression test verifies consistency via selecting
+
+--disable_abort_on_error
+
+--connection master
+
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2;
+--enable_warnings
+
+CREATE TABLE t1 (
+  `pk` int(11) NOT NULL AUTO_INCREMENT,
+  `int_nokey` int(11) NOT NULL,
+  `int_key` int(11) NOT NULL,
+  `date_key` date NOT NULL,
+  `date_nokey` date NOT NULL,
+  `time_key` time NOT NULL,
+  `time_nokey` time NOT NULL,
+  `datetime_key` datetime NOT NULL,
+  `datetime_nokey` datetime NOT NULL,
+  `varchar_key` varchar(1) NOT NULL,
+  `varchar_nokey` varchar(1) NOT NULL,
+  PRIMARY KEY (`pk`),
+  KEY `int_key` (`int_key`),
+  KEY `date_key` (`date_key`),
+  KEY `time_key` (`time_key`),
+  KEY `datetime_key` (`datetime_key`),
+  KEY `varchar_key` (`varchar_key`)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES (1,8,5,'0000-00-00','0000-00-00','10:37:38','10:37:38','0000-00-00
00:00:00','0000-00-00
00:00:00','p','p'),(2,0,9,'0000-00-00','0000-00-00','00:00:00','00:00:00','2007-10-14
00:00:00','2007-10-14 00:00:00','d','d');
+
+CREATE TABLE t2 (
+  `pk` int(11) NOT NULL AUTO_INCREMENT,
+  `int_nokey` int(11) NOT NULL,
+  `int_key` int(11) NOT NULL,
+  `date_key` date NOT NULL,
+  `date_nokey` date NOT NULL,
+  `time_key` time NOT NULL,
+  `time_nokey` time NOT NULL,
+  `datetime_key` datetime NOT NULL,
+  `datetime_nokey` datetime NOT NULL,
+  `varchar_key` varchar(1) NOT NULL,
+  `varchar_nokey` varchar(1) NOT NULL,
+  PRIMARY KEY (`pk`),
+  KEY `int_key` (`int_key`),
+  KEY `date_key` (`date_key`),
+  KEY `time_key` (`time_key`),
+  KEY `datetime_key` (`datetime_key`),
+  KEY `varchar_key` (`varchar_key`)
+) ENGINE=InnoDB;
+
+INSERT INTO t2 VALUES (1,1,6,'2005-12-23','2005-12-23','02:24:28','02:24:28','0000-00-00
00:00:00','0000-00-00
00:00:00','g','g'),(2,0,3,'2009-09-14','2009-09-14','00:00:00','00:00:00','2000-01-30
16:39:40','2000-01-30
16:39:40','q','q'),(3,0,3,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00
00:00:00','0000-00-00
00:00:00','c','c'),(4,1,6,'2007-03-29','2007-03-29','15:49:00','15:49:00','0000-00-00
00:00:00','0000-00-00
00:00:00','m','m'),(5,4,0,'2002-12-04','2002-12-04','00:00:00','00:00:00','0000-00-00
00:00:00','0000-00-00
00:00:00','o','o'),(6,9,0,'2005-01-28','2005-01-28','00:00:00','00:00:00','2001-05-18
00:00:00','2001-05-18
00:00:00','w','w'),(7,6,0,'0000-00-00','0000-00-00','06:57:25','06:57:25','0000-00-00
00:00:00','0000-00-00
00:00:00','m','m'),(8,0,0,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00
00:00:00','0000-00-00
00:00:00','z','z'),(9,4,6,'2006-08-15','2006-08-15','00:00:00','00:00:00','2002-04-12
14:44:25','2002-04-12 14:44:25','j'!
 ,'j'),(10,0,5,'2006-12-20','2006-12-20','10:13:53','10:13:53','2008-07-22
00:00:00','2008-07-22
00:00:00','y','y'),(11,9,7,'0000-00-00','0000-00-00','00:00:00','00:00:00','2004-07-05
00:00:00','2004-07-05
00:00:00','{','{'),(12,4,3,'2007-01-26','2007-01-26','23:00:51','23:00:51','2001-05-16
00:00:00','2001-05-16
00:00:00','f','f'),(13,7,0,'2004-03-27','2004-03-27','00:00:00','00:00:00','2005-01-24
03:30:37','2005-01-24
03:30:37','',''),(14,6,0,'2006-07-26','2006-07-26','18:43:57','18:43:57','0000-00-00
00:00:00','0000-00-00
00:00:00','{','{'),(15,0,6,'2000-01-14','2000-01-14','00:00:00','00:00:00','2000-09-21
00:00:00','2000-09-21
00:00:00','o','o'),(16,9,8,'0000-00-00','0000-00-00','21:15:08','21:15:08','0000-00-00
00:00:00','0000-00-00
00:00:00','a','a'),(17,2,0,'2004-10-27','2004-10-27','00:00:00','00:00:00','2004-03-24
22:13:43','2004-03-24
22:13:43','',''),(18,7,4,'0000-00-00','0000-00-00','08:38:27','08:38:27','2002-03-18
19:51:44','2002-03-18 19:51:44','t','t'),(19,5!
 ,3,'2008-03-07','2008-03-07','03:29:07','03:29:07','2007-12-01 18:44:44','2007-12-01
18:44:44','t','t'),(20,0,0,'2002-04-09','2002-04-09','16:06:03','16:06:03','2009-04-22
00:00:00','2009-04-22 00:00:00','n','n');
+
+DELETE FROM t2 WHERE `int_key` < 3 LIMIT 1;
+UPDATE t1 SET `int_key` = 3 ORDER BY `pk` LIMIT 4;
+DELETE FROM t2 WHERE `int_key` < 3 LIMIT 1;
+DELETE FROM t2 WHERE `pk` < 6 LIMIT 1;
+UPDATE t1 SET `int_key` = 6 ORDER BY `pk` LIMIT 3;
+DELETE FROM t2 WHERE `pk` < 6 LIMIT 1;
+UPDATE t1 SET `pk` = 6 ORDER BY `int_key` LIMIT 6;
+DELETE FROM t2 WHERE `pk` < 7 LIMIT 1;
+UPDATE t1 SET `int_key` = 4 ORDER BY `pk` LIMIT 6;
+
+--sync_slave_with_master
+--echo *** results: t2 must be consistent ****
+
+let $diff_table_1=master:test.t2;
+let $diff_table_2=master:test.t2;
+source include/diff_tables.inc;
+
+--connection master
+DROP TABLE t1, t2;
+
+--enable_abort_on_error
+
+--echo EOF OF TESTS

=== modified file 'mysql-test/r/sp.result'
--- a/mysql-test/r/sp.result	2008-10-24 13:21:52 +0000
+++ b/mysql-test/r/sp.result	2008-11-03 12:12:00 +0000
@@ -6802,6 +6802,24 @@ ttt
 2
 drop function func30787;
 drop table t1;
+CREATE TABLE t1 (id INT);
+INSERT INTO t1 VALUES (1),(2),(3),(4);
+CREATE PROCEDURE test_sp()
+SELECT t1.* FROM t1 RIGHT JOIN t1 t2 ON t1.id=t2.id;
+CALL test_sp();
+id
+1
+2
+3
+4
+CALL test_sp();
+id
+1
+2
+3
+4
+DROP PROCEDURE test_sp;
+DROP TABLE t1;
 create table t1(c1 INT);
 create function f1(p1 int) returns varchar(32)
 return 'aaa';

=== modified file 'mysql-test/suite/rpl/r/rpl_row_basic_3innodb.result'
--- a/mysql-test/suite/rpl/r/rpl_row_basic_3innodb.result	2008-09-05 13:39:08 +0000
+++ b/mysql-test/suite/rpl/r/rpl_row_basic_3innodb.result	2008-11-03 12:12:00 +0000
@@ -527,3 +527,58 @@ ERROR 23000: Duplicate entry '10' for ke
 INSERT INTO t1 VALUES (4);
 Comparing tables master:test.t1 and slave:test.t1
 drop table t1;
+DROP TABLE IF EXISTS t1, t2;
+CREATE TABLE t1 (
+`pk` int(11) NOT NULL AUTO_INCREMENT,
+`int_nokey` int(11) NOT NULL,
+`int_key` int(11) NOT NULL,
+`date_key` date NOT NULL,
+`date_nokey` date NOT NULL,
+`time_key` time NOT NULL,
+`time_nokey` time NOT NULL,
+`datetime_key` datetime NOT NULL,
+`datetime_nokey` datetime NOT NULL,
+`varchar_key` varchar(1) NOT NULL,
+`varchar_nokey` varchar(1) NOT NULL,
+PRIMARY KEY (`pk`),
+KEY `int_key` (`int_key`),
+KEY `date_key` (`date_key`),
+KEY `time_key` (`time_key`),
+KEY `datetime_key` (`datetime_key`),
+KEY `varchar_key` (`varchar_key`)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,8,5,'0000-00-00','0000-00-00','10:37:38','10:37:38','0000-00-00
00:00:00','0000-00-00
00:00:00','p','p'),(2,0,9,'0000-00-00','0000-00-00','00:00:00','00:00:00','2007-10-14
00:00:00','2007-10-14 00:00:00','d','d');
+CREATE TABLE t2 (
+`pk` int(11) NOT NULL AUTO_INCREMENT,
+`int_nokey` int(11) NOT NULL,
+`int_key` int(11) NOT NULL,
+`date_key` date NOT NULL,
+`date_nokey` date NOT NULL,
+`time_key` time NOT NULL,
+`time_nokey` time NOT NULL,
+`datetime_key` datetime NOT NULL,
+`datetime_nokey` datetime NOT NULL,
+`varchar_key` varchar(1) NOT NULL,
+`varchar_nokey` varchar(1) NOT NULL,
+PRIMARY KEY (`pk`),
+KEY `int_key` (`int_key`),
+KEY `date_key` (`date_key`),
+KEY `time_key` (`time_key`),
+KEY `datetime_key` (`datetime_key`),
+KEY `varchar_key` (`varchar_key`)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,1,6,'2005-12-23','2005-12-23','02:24:28','02:24:28','0000-00-00
00:00:00','0000-00-00
00:00:00','g','g'),(2,0,3,'2009-09-14','2009-09-14','00:00:00','00:00:00','2000-01-30
16:39:40','2000-01-30
16:39:40','q','q'),(3,0,3,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00
00:00:00','0000-00-00
00:00:00','c','c'),(4,1,6,'2007-03-29','2007-03-29','15:49:00','15:49:00','0000-00-00
00:00:00','0000-00-00
00:00:00','m','m'),(5,4,0,'2002-12-04','2002-12-04','00:00:00','00:00:00','0000-00-00
00:00:00','0000-00-00
00:00:00','o','o'),(6,9,0,'2005-01-28','2005-01-28','00:00:00','00:00:00','2001-05-18
00:00:00','2001-05-18
00:00:00','w','w'),(7,6,0,'0000-00-00','0000-00-00','06:57:25','06:57:25','0000-00-00
00:00:00','0000-00-00
00:00:00','m','m'),(8,0,0,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00
00:00:00','0000-00-00
00:00:00','z','z'),(9,4,6,'2006-08-15','2006-08-15','00:00:00','00:00:00','2002-04-12
14:44:25','2002-04-12 14:44:25','j'!
 ,'j'),(10,0,5,'2006-12-20','2006-12-20','10:13:53','10:13:53','2008-07-22
00:00:00','2008-07-22
00:00:00','y','y'),(11,9,7,'0000-00-00','0000-00-00','00:00:00','00:00:00','2004-07-05
00:00:00','2004-07-05
00:00:00','{','{'),(12,4,3,'2007-01-26','2007-01-26','23:00:51','23:00:51','2001-05-16
00:00:00','2001-05-16
00:00:00','f','f'),(13,7,0,'2004-03-27','2004-03-27','00:00:00','00:00:00','2005-01-24
03:30:37','2005-01-24
03:30:37','',''),(14,6,0,'2006-07-26','2006-07-26','18:43:57','18:43:57','0000-00-00
00:00:00','0000-00-00
00:00:00','{','{'),(15,0,6,'2000-01-14','2000-01-14','00:00:00','00:00:00','2000-09-21
00:00:00','2000-09-21
00:00:00','o','o'),(16,9,8,'0000-00-00','0000-00-00','21:15:08','21:15:08','0000-00-00
00:00:00','0000-00-00
00:00:00','a','a'),(17,2,0,'2004-10-27','2004-10-27','00:00:00','00:00:00','2004-03-24
22:13:43','2004-03-24
22:13:43','',''),(18,7,4,'0000-00-00','0000-00-00','08:38:27','08:38:27','2002-03-18
19:51:44','2002-03-18 19:51:44','t','t'),(19,5!
 ,3,'2008-03-07','2008-03-07','03:29:07','03:29:07','2007-12-01 18:44:44','2007-12-01
18:44:44','t','t'),(20,0,0,'2002-04-09','2002-04-09','16:06:03','16:06:03','2009-04-22
00:00:00','2009-04-22 00:00:00','n','n');
+DELETE FROM t2 WHERE `int_key` < 3 LIMIT 1;
+UPDATE t1 SET `int_key` = 3 ORDER BY `pk` LIMIT 4;
+DELETE FROM t2 WHERE `int_key` < 3 LIMIT 1;
+DELETE FROM t2 WHERE `pk` < 6 LIMIT 1;
+UPDATE t1 SET `int_key` = 6 ORDER BY `pk` LIMIT 3;
+DELETE FROM t2 WHERE `pk` < 6 LIMIT 1;
+UPDATE t1 SET `pk` = 6 ORDER BY `int_key` LIMIT 6;
+ERROR 23000: Duplicate entry '6' for key 'PRIMARY'
+DELETE FROM t2 WHERE `pk` < 7 LIMIT 1;
+UPDATE t1 SET `int_key` = 4 ORDER BY `pk` LIMIT 6;
+*** results: t2 must be consistent ****
+Comparing tables master:test.t2 and master:test.t2
+DROP TABLE t1, t2;
+EOF OF TESTS

=== modified file 'mysql-test/t/sp.test'
--- a/mysql-test/t/sp.test	2008-10-24 13:21:52 +0000
+++ b/mysql-test/t/sp.test	2008-11-03 12:12:00 +0000
@@ -7994,8 +7994,24 @@ select (select func30787(f1)) as ttt fro
 drop function func30787;
 drop table t1;
 
-###########################################################################
+#
+# Bug #33811: Call to stored procedure with SELECT * / RIGHT JOIN fails
+# after the first time
+#
+CREATE TABLE t1 (id INT);
+INSERT INTO t1 VALUES (1),(2),(3),(4);
+
+CREATE PROCEDURE test_sp()
+  SELECT t1.* FROM t1 RIGHT JOIN t1 t2 ON t1.id=t2.id;
+
+CALL test_sp();
+CALL test_sp();
 
+DROP PROCEDURE test_sp;
+DROP TABLE t1;
+
+
+###########################################################################
 #
 # Bug#38291 memory corruption and server crash with view/sp/function
 #

=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc	2008-10-13 10:44:56 +0000
+++ b/sql/sql_base.cc	2008-11-03 12:12:00 +0000
@@ -6964,6 +6964,10 @@ insert_fields(THD *thd, Name_resolution_
 
       if (!(item= field_iterator.create_item(thd)))
         DBUG_RETURN(TRUE);
+      DBUG_ASSERT(item->fixed);
+      /* cache the table for the Item_fields inserted by expanding stars */
+      if (item->type() == Item::FIELD_ITEM && tables->cacheable_table)
+        ((Item_field *)item)->cached_table= tables;
 
       if (!found)
       {

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2008-10-24 13:21:52 +0000
+++ b/sql/sql_select.cc	2008-11-03 12:12:00 +0000
@@ -10598,6 +10598,8 @@ simplify_joins(JOIN *join, List<TABLE_LI
     Flatten nested joins that can be flattened.
     no ON expression and not a semi-join => can be flattened.
   */
+  TABLE_LIST *right_neighbor= NULL;
+  bool fix_name_res= FALSE;
   li.rewind();
   while ((table= li++))
   {
@@ -10618,9 +10620,17 @@ simplify_joins(JOIN *join, List<TABLE_LI
       {
         tbl->embedding= table->embedding;
         tbl->join_list= table->join_list;
-      }      
+      }
       li.replace(nested_join->join_list);
-    }
+      /* Need to update the name resolution table chain when flattening joins */
+      fix_name_res= TRUE;
+      table= *li.ref();
+    }
+    if (fix_name_res)
+      table->next_name_resolution_table= right_neighbor ?
+        right_neighbor->first_leaf_for_name_resolution() :
+        NULL;
+    right_neighbor= table;
   }
   DBUG_RETURN(conds); 
 }