summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/create.result7
-rw-r--r--mysql-test/r/ps_ddl.result8
-rw-r--r--mysql-test/suite/rpl/r/rpl_create_if_not_exists.result34
-rw-r--r--mysql-test/suite/rpl/t/rpl_create_if_not_exists.test53
-rw-r--r--mysql-test/t/create.test9
-rw-r--r--mysql-test/t/ps_ddl.test9
-rw-r--r--sql/sql_parse.cc2
-rw-r--r--sql/sql_prepare.cc2
8 files changed, 106 insertions, 18 deletions
diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result
index b829ef30fb1..cfdf2565c9c 100644
--- a/mysql-test/r/create.result
+++ b/mysql-test/r/create.result
@@ -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;
diff --git a/mysql-test/r/ps_ddl.result b/mysql-test/r/ps_ddl.result
index c7e8812320c..375f31ef9c4 100644
--- a/mysql-test/r/ps_ddl.result
+++ b/mysql-test/r/ps_ddl.result
@@ -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;
diff --git a/mysql-test/suite/rpl/r/rpl_create_if_not_exists.result b/mysql-test/suite/rpl/r/rpl_create_if_not_exists.result
index 12a956a6ce6..fc53aca5136 100644
--- a/mysql-test/suite/rpl/r/rpl_create_if_not_exists.result
+++ b/mysql-test/suite/rpl/r/rpl_create_if_not_exists.result
@@ -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;
diff --git a/mysql-test/suite/rpl/t/rpl_create_if_not_exists.test b/mysql-test/suite/rpl/t/rpl_create_if_not_exists.test
index 5faf95a4d84..114f71af873 100644
--- a/mysql-test/suite/rpl/t/rpl_create_if_not_exists.test
+++ b/mysql-test/suite/rpl/t/rpl_create_if_not_exists.test
@@ -67,4 +67,57 @@ SHOW EVENTS in mysqltest;
connection master;
DROP DATABASE IF EXISTS mysqltest;
+
+#
+# BUG#47418 RBR fails, failure with mixup of base/temporary/view TABLE DDL
+#
+# Before the patch for this bug, 'CREATE TABLE IF NOT EXIST ... SELECT'
+# statement was binlogged as a TEMPORARY table if the object existed as
+# a temporary table. This was caused by that the temporary table was opened
+# and the results of the 'SELECT' was inserted into the temporary table if
+# a temporary table existed 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;
diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test
index 5ffa1b93929..692721b5955 100644
--- a/mysql-test/t/create.test
+++ b/mysql-test/t/create.test
@@ -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;
diff --git a/mysql-test/t/ps_ddl.test b/mysql-test/t/ps_ddl.test
index fee235cd36c..1543c757908 100644
--- a/mysql-test/t/ps_ddl.test
+++ b/mysql-test/t/ps_ddl.test
@@ -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:
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index eaea6e55b09..c3070c4a756 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -2659,6 +2659,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)))
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index e2a24f7da1e..5979f2ca17e 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -1596,6 +1596,8 @@ static bool mysql_test_create_table(Prepared_statement *stmt)
{
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))
View Lorry Controller Status