#At file:///home/anders/work/bzrwork/worktree3/mysql-5.1-bugteam/ based on revid:kent.boortz@stripped
3203 Li-Bing.Song@stripped 2009-11-22
BUG#47418 RBR fails, failure with mixup of base/temporary/view
'CREATE TABLE IF NOT EXIST ... SELECT' statement is binlogged as a TEMPORARY
table if the table exists as a temporary table. This is caused by that the
temporary table is opened and the results of the 'SELECT' is inserted into
the temporary table when a temporary exists with the same name.
After the patch for this bug, the base table is created and the results of
the 'SELECT' are inserted into it, even though a temporary table exists with
the same name and the statement is still binlogged as a base table.
modified:
mysql-test/r/create.result
mysql-test/r/ps_ddl.result
mysql-test/suite/rpl/r/rpl_create_if_not_exists.result
mysql-test/suite/rpl/t/rpl_create_if_not_exists.test
mysql-test/t/create.test
mysql-test/t/ps_ddl.test
sql/sql_parse.cc
sql/sql_prepare.cc
=== modified file 'mysql-test/r/create.result'
--- a/mysql-test/r/create.result 2009-09-04 06:57:10 +0000
+++ b/mysql-test/r/create.result 2009-11-22 06:30:09 +0000
@@ -820,16 +820,13 @@ i
drop table t1;
create temporary table t1 (j int);
create table if not exists t1 select 1;
-Warnings:
-Note 1050 Table 't1' already exists
select * from t1;
j
-1
drop temporary table t1;
select * from t1;
-ERROR 42S02: Table 'test.t1' doesn't exist
+1
+1
drop table t1;
-ERROR 42S02: Unknown table 't1'
create table t1 (i int);
insert into t1 values (1), (2);
lock tables t1 read;
=== modified file 'mysql-test/r/ps_ddl.result'
--- a/mysql-test/r/ps_ddl.result 2008-08-13 19:42:21 +0000
+++ b/mysql-test/r/ps_ddl.result 2009-11-22 06:30:09 +0000
@@ -1695,23 +1695,23 @@ SUCCESS
drop table t2;
create temporary table t2 (a int);
execute stmt;
-ERROR 42S01: Table 't2' already exists
call p_verify_reprepare_count(1);
SUCCESS
execute stmt;
ERROR 42S01: Table 't2' already exists
-call p_verify_reprepare_count(0);
+call p_verify_reprepare_count(1);
SUCCESS
drop temporary table t2;
execute stmt;
-call p_verify_reprepare_count(1);
+ERROR 42S01: Table 't2' already exists
+call p_verify_reprepare_count(0);
SUCCESS
drop table t2;
execute stmt;
-call p_verify_reprepare_count(0);
+call p_verify_reprepare_count(1);
SUCCESS
drop table t2;
=== modified file 'mysql-test/suite/rpl/r/rpl_create_if_not_exists.result'
--- a/mysql-test/suite/rpl/r/rpl_create_if_not_exists.result 2009-08-29 08:52:22 +0000
+++ b/mysql-test/suite/rpl/r/rpl_create_if_not_exists.result 2009-11-22 06:30:09 +0000
@@ -31,3 +31,37 @@ SHOW EVENTS in mysqltest;
Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
mysqltest e root@localhost SYSTEM ONE TIME # NULL NULL NULL NULL SLAVESIDE_DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
DROP DATABASE IF EXISTS mysqltest;
+-------------BUG#47418-------------
+USE test;
+DROP TABLE IF EXISTS t3;
+CREATE TABLE t3(c1 INTEGER);
+INSERT INTO t3 VALUES(33);
+CREATE TEMPORARY TABLE t1(c1 INTEGER);
+CREATE TEMPORARY TABLE t2(c1 INTEGER);
+INSERT INTO t1 VALUES(1);
+INSERT INTO t2 VALUES(1);
+CREATE TABLE IF NOT EXISTS t1(c1 INTEGER) SELECT c1 FROM t3;
+CREATE TABLE t2(c1 INTEGER) SELECT c1 FROM t3;
+SELECT * FROM t1;
+c1
+1
+SELECT * FROM t2;
+c1
+1
+SELECT * FROM t1;
+c1
+33
+SELECT * FROM t2;
+c1
+33
+DROP TEMPORARY TABLE t1;
+DROP TEMPORARY TABLE t2;
+SELECT * FROM t1;
+c1
+33
+SELECT * FROM t2;
+c1
+33
+DROP TABLE t1;
+DROP TABLE t2;
+DROP TABLE t3;
=== modified file 'mysql-test/suite/rpl/t/rpl_create_if_not_exists.test'
--- a/mysql-test/suite/rpl/t/rpl_create_if_not_exists.test 2009-08-13 02:48:57 +0000
+++ b/mysql-test/suite/rpl/t/rpl_create_if_not_exists.test 2009-11-22 06:30:09 +0000
@@ -67,4 +67,56 @@ SHOW EVENTS in mysqltest;
connection master;
DROP DATABASE IF EXISTS mysqltest;
+
+#
+# BUG#47418 RBR fails, failure with mixup of base/temporary/view TABLE DDL
+#
+# 'CREATE TABLE IF NOT EXIST ... SELECT' statement is binlogged as a TEMPORARY
+# table if the object exists as a temporary table. This is caused by that the
+# temporary table is opened and the results of the 'SELECT' is inserted into
+# the temporary table if a temporary exists with the same name.
+#
+# After the patch for this bug, the base table is created and the results of
+# the 'SELECT' are inserted into it, even though a temporary table exists with
+# the same name, and the statement is still binlogged as a base table.
+#
+
+echo -------------BUG#47418-------------;
+connection master;
+USE test;
+DROP TABLE IF EXISTS t3;
+--enable_warnings
+CREATE TABLE t3(c1 INTEGER);
+INSERT INTO t3 VALUES(33);
+
+CREATE TEMPORARY TABLE t1(c1 INTEGER);
+CREATE TEMPORARY TABLE t2(c1 INTEGER);
+INSERT INTO t1 VALUES(1);
+INSERT INTO t2 VALUES(1);
+
+CREATE TABLE IF NOT EXISTS t1(c1 INTEGER) SELECT c1 FROM t3;
+CREATE TABLE t2(c1 INTEGER) SELECT c1 FROM t3;
+
+# In these two statements, t1 and t2 are the temporary table. there is only
+# value '1' in them. The records of t2 are not inserted into them.
+SELECT * FROM t1;
+SELECT * FROM t2;
+sync_slave_with_master;
+
+# In these two statements, t1 and t2 are the base table. The recoreds of t2
+# are inserted into it when CREATE TABLE ... SELECT was executed.
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+connection master;
+DROP TEMPORARY TABLE t1;
+DROP TEMPORARY TABLE t2;
+#In these two statements, t1 and t2 are the base table.
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+DROP TABLE t1;
+DROP TABLE t2;
+DROP TABLE t3;
+
source include/master-slave-end.inc;
=== modified file 'mysql-test/t/create.test'
--- a/mysql-test/t/create.test 2009-09-04 06:57:10 +0000
+++ b/mysql-test/t/create.test 2009-11-22 06:30:09 +0000
@@ -721,16 +721,15 @@ drop table t1;
# Base vs temporary tables dillema (a.k.a. bug#24508 "Inconsistent
# results of CREATE TABLE ... SELECT when temporary table exists").
# In this situation we either have to create non-temporary table and
-# insert data in it or insert data in temporary table without creation
-# of permanent table. Since currently temporary tables always shadow
-# permanent tables we adopt second approach.
+# insert data in it or insert data in temporary table without creation of
+# permanent table. After patch for Bug#47418, we create the base table and
+# instert data into it, even though a temporary table exists with the same
+# name.
create temporary table t1 (j int);
create table if not exists t1 select 1;
select * from t1;
drop temporary table t1;
---error ER_NO_SUCH_TABLE
select * from t1;
---error ER_BAD_TABLE_ERROR
drop table t1;
=== modified file 'mysql-test/t/ps_ddl.test'
--- a/mysql-test/t/ps_ddl.test 2008-08-13 19:42:21 +0000
+++ b/mysql-test/t/ps_ddl.test 2009-11-22 06:30:09 +0000
@@ -1445,18 +1445,19 @@ call p_verify_reprepare_count(0);
drop table t2;
# Temporary table with name of table to be created exists
create temporary table t2 (a int);
---error ER_TABLE_EXISTS_ERROR
+# Temporary table and base table are not in the same name space.
execute stmt;
call p_verify_reprepare_count(1);
--error ER_TABLE_EXISTS_ERROR
execute stmt;
-call p_verify_reprepare_count(0);
+call p_verify_reprepare_count(1);
drop temporary table t2;
+--error ER_TABLE_EXISTS_ERROR
execute stmt;
-call p_verify_reprepare_count(1);
+call p_verify_reprepare_count(0);
drop table t2;
execute stmt;
-call p_verify_reprepare_count(0);
+call p_verify_reprepare_count(1);
drop table t2;
# View with name of table to be created exists
# Attention:
=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc 2009-10-31 10:50:25 +0000
+++ b/sql/sql_parse.cc 2009-11-22 06:30:09 +0000
@@ -2657,6 +2657,8 @@ mysql_execute_command(THD *thd)
{
lex->link_first_table_back(create_table, link_to_local);
create_table->create= TRUE;
+ /* Base table and temporary table are not in the same name space. */
+ create_table->skip_temporary= 1;
}
if (!(res= open_and_lock_tables(thd, lex->query_tables)))
=== modified file 'sql/sql_prepare.cc'
--- a/sql/sql_prepare.cc 2009-10-19 13:55:04 +0000
+++ b/sql/sql_prepare.cc 2009-11-22 06:30:09 +0000
@@ -1596,6 +1596,8 @@ static bool mysql_test_create_table(Prep
{
lex->link_first_table_back(create_table, link_to_local);
create_table->create= TRUE;
+ /* Base table and temporary table are not in the same name space. */
+ create_table->skip_temporary= true;
}
if (open_normal_and_derived_tables(stmt->thd, lex->query_tables, 0))
Attachment: [text/bzr-bundle] bzr/li-bing.song@sun.com-20091122063009-67g3htd89wbox505.bundle
