From 9827c5e1031ca5ebe1c6c7d4f5eac3cce5749d8e Mon Sep 17 00:00:00 2001 From: Sachin Date: Tue, 26 Jun 2018 11:33:58 +0530 Subject: MDEV-16192 Table 't' is specified twice, both as a target for 'CREATE' and... as a separate source for data Actually MDEV-15867 and MDEV-16192 are same, Slave adds "or replace" to create table stmt. So create table t1 is create or replace on slave. So this bug is not because of replication, We can get this bug on general server if we manually add or replace to create query. Problem:- So if we try to create table t1 (same name as of temp table t1 ) via CREATE or replace TABLE t AS SELECT * FROM t; Since in this query we are creating table from select * from t1 , we call unique_table function to see whether if source and destination table are same. But there is one issue unique_table does not account if source table is tmp table in this case source and destination table can be same. Solution:- We will change find_dup_table to not to look for temp table if CHECK_DUP_SKIP_TEMP_TABLE flag is on. --- mysql-test/r/create_replace_tmp.result | 4 ++++ mysql-test/suite/rpl/r/rpl_15867.result | 9 +++++++++ mysql-test/suite/rpl/t/rpl_15867.test | 11 +++++++++++ mysql-test/t/create_replace_tmp.test | 4 ++++ 4 files changed, 28 insertions(+) create mode 100644 mysql-test/r/create_replace_tmp.result create mode 100644 mysql-test/suite/rpl/r/rpl_15867.result create mode 100644 mysql-test/suite/rpl/t/rpl_15867.test create mode 100644 mysql-test/t/create_replace_tmp.test (limited to 'mysql-test') diff --git a/mysql-test/r/create_replace_tmp.result b/mysql-test/r/create_replace_tmp.result new file mode 100644 index 00000000000..0239f4d4817 --- /dev/null +++ b/mysql-test/r/create_replace_tmp.result @@ -0,0 +1,4 @@ +CREATE TEMPORARY TABLE t (i INT); +CREATE or replace TABLE t AS SELECT * FROM t; +DROP TEMPORARY TABLE t; +DROP TABLE t; diff --git a/mysql-test/suite/rpl/r/rpl_15867.result b/mysql-test/suite/rpl/r/rpl_15867.result new file mode 100644 index 00000000000..9cb63266a29 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_15867.result @@ -0,0 +1,9 @@ +include/master-slave.inc +[connection master] +CREATE TEMPORARY TABLE t (i INT); +CREATE TABLE t AS SELECT * FROM t; +connection slave; +connection master; +DROP TEMPORARY TABLE t; +DROP TABLE t; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_15867.test b/mysql-test/suite/rpl/t/rpl_15867.test new file mode 100644 index 00000000000..6de39041bb1 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_15867.test @@ -0,0 +1,11 @@ +--source include/master-slave.inc +CREATE TEMPORARY TABLE t (i INT); +CREATE TABLE t AS SELECT * FROM t; + +--sync_slave_with_master + +# Cleanup +--connection master +DROP TEMPORARY TABLE t; +DROP TABLE t; +--source include/rpl_end.inc diff --git a/mysql-test/t/create_replace_tmp.test b/mysql-test/t/create_replace_tmp.test new file mode 100644 index 00000000000..0239f4d4817 --- /dev/null +++ b/mysql-test/t/create_replace_tmp.test @@ -0,0 +1,4 @@ +CREATE TEMPORARY TABLE t (i INT); +CREATE or replace TABLE t AS SELECT * FROM t; +DROP TEMPORARY TABLE t; +DROP TABLE t; -- cgit v1.2.1 From d0d073b1aaa30997307bc7aa686d3715f8c22da0 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Fri, 20 Jul 2018 19:32:28 -0700 Subject: Corrected and added back the test case for MDEV-15151. --- mysql-test/r/cte_recursive_not_embedded.result | 23 ++++++++++++++ mysql-test/t/cte_recursive_not_embedded.test | 42 ++++++++++++++++++++++++++ 2 files changed, 65 insertions(+) create mode 100644 mysql-test/r/cte_recursive_not_embedded.result create mode 100644 mysql-test/t/cte_recursive_not_embedded.test (limited to 'mysql-test') diff --git a/mysql-test/r/cte_recursive_not_embedded.result b/mysql-test/r/cte_recursive_not_embedded.result new file mode 100644 index 00000000000..202864be159 --- /dev/null +++ b/mysql-test/r/cte_recursive_not_embedded.result @@ -0,0 +1,23 @@ +# +# MDEV-15151: function with recursive CTE using no base tables +# (duplicate of MDEV-16661) +# +connection default; +CREATE TABLE t1 (id int KEY); +INSERT INTO t1 VALUES (0), (1),(2); +CREATE OR REPLACE FUNCTION func() RETURNS int +RETURN +( +WITH recursive cte AS +(SELECT 1 a UNION SELECT cte.* FROM cte natural join t1) +SELECT * FROM cte limit 1 +); +connect con1,localhost,root,,test; +SELECT func(); +connect con2,localhost,root,,test; +disconnect con2; +connection con1; +disconnect con1; +connection default; +DROP FUNCTION func; +DROP TABLE t1; diff --git a/mysql-test/t/cte_recursive_not_embedded.test b/mysql-test/t/cte_recursive_not_embedded.test new file mode 100644 index 00000000000..4dadf400681 --- /dev/null +++ b/mysql-test/t/cte_recursive_not_embedded.test @@ -0,0 +1,42 @@ +--source include/not_embedded.inc + +--echo # +--echo # MDEV-15151: function with recursive CTE using no base tables +--echo # (duplicate of MDEV-16661) +--echo # + +--connection default + +CREATE TABLE t1 (id int KEY); +INSERT INTO t1 VALUES (0), (1),(2); + +CREATE OR REPLACE FUNCTION func() RETURNS int +RETURN +( + WITH recursive cte AS + (SELECT 1 a UNION SELECT cte.* FROM cte natural join t1) + SELECT * FROM cte limit 1 +); + +--connect (con1,localhost,root,,test) + +--let $conid= `SELECT CONNECTION_ID()` +--send SELECT func() + +--connect (con2,localhost,root,,test) +--disable_query_log +--eval KILL QUERY $conid +--enable_query_log +--disconnect con2 + +--disable_result_log +--connection con1 +--error 0,ER_QUERY_INTERRUPTED +--reap +--disconnect con1 +--enable_result_log + +--connection default + +DROP FUNCTION func; +DROP TABLE t1; -- cgit v1.2.1