mariadb/mysql-test/suite/galera/t/galera_create_table_as_select.test

#
# CREATE TABLE AS SELECT tests
#

--source include/galera_cluster.inc
--source include/have_debug_sync.inc
--source include/have_debug.inc

--connection node_1
SET SESSION default_storage_engine=InnoDB;

# Left table already exists

CREATE TABLE t1 (f1 INTEGER) ENGINE=InnoDB;
CREATE TABLE t2 (f1 INTEGER) ENGINE=InnoDB;
--error ER_TABLE_EXISTS_ERROR
CREATE TABLE t1 AS SELECT * FROM t2;
DROP TABLE t1,t2;

# Right table does not exist
--error ER_NO_SUCH_TABLE
CREATE TABLE t1 AS SELECT * FROM t2;

# No right table at all
CREATE TABLE t1 AS SELECT 1 FROM DUAL;

--connection node_2
SELECT COUNT(*) = 1 FROM t1;

--connection node_1
DROP TABLE t1;

# Empty right table
--connection node_1
CREATE TABLE t2 (f1 INTEGER) ENGINE=InnoDB;
CREATE TABLE t1 AS SELECT * FROM t2;

--connection node_2
SELECT COUNT(*) = 0 FROM t1;

--connection node_1
DROP TABLE t1,t2;

# Right table is MyISAM

CREATE TABLE t2 (f1 INTEGER) ENGINE=MyISAM;
INSERT INTO t2 VALUES (1),(2),(3),(4),(5);
CREATE TABLE t1 AS SELECT * FROM t2;
SELECT COUNT(*) = 5 FROM t1;

--connection node_2
SELECT COUNT(*) = 5 FROM t1;

--connection node_1
DROP TABLE t1,t2;

# Right side is a subquery

--connection node_1
CREATE TABLE t2 (f1 INTEGER) ENGINE=InnoDB;
INSERT INTO t2 VALUES (1),(2),(3),(4),(5);
CREATE TABLE t1 AS SELECT MAX(f1) AS f1 FROM t2;

--connection node_2
SELECT COUNT(*) = 1 FROM t1;
SELECT f1 = 5 FROM t1;

--connection node_1
DROP TABLE t1,t2;

# Inside a stored procedure

--connection node_1
DELIMITER |;
CREATE PROCEDURE sp1 ()
BEGIN
	CREATE TABLE t2 (f1 INTEGER) ENGINE=InnoDB;
	INSERT INTO t2 VALUES (1),(2),(3),(4),(5);
	CREATE TABLE t1 AS SELECT * FROM t2;
END|
DELIMITER ;|
CALL sp1();
SELECT COUNT(*) = 5 FROM t1;

--connection node_2
SELECT COUNT(*) = 5 FROM t1;

--connection node_1
DROP TABLE t1, t2;
DROP PROCEDURE sp1;

# Inside a prepared statement

--connection node_1
CREATE TABLE t2 (f1 INTEGER) ENGINE=InnoDB;
INSERT INTO t2 VALUES (1),(2),(3),(4),(5);

PREPARE stmt FROM 'CREATE TABLE t1 AS SELECT * FROM t2';
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
DROP TABLE t1, t2;

#
# Multi-master conflict
#

--connection node_1
# Pause applying CTAS command from the other node
# This cases sync.wsrep_apply_cb_reached to be sent in Wsrep_applier_service::apply_write_set
# and wait for signal.wsrep_apply_cb
SET @save_debug_dbug=@@global.DEBUG_DBUG;
SET GLOBAL DEBUG_DBUG = '+d,sync.wsrep_apply_cb';

CREATE TABLE t2 (f1 INTEGER) ENGINE=InnoDB;
INSERT INTO t2 VALUES (1),(2),(3),(4),(5);

# Wait until local CTAS grabs MDL lock and let applied CTAS BF-abort it
SET DEBUG_SYNC = 'create_table_select_before_create WAIT_FOR sync.wsrep_apply_cb_reached';
SET DEBUG_SYNC = 'create_table_select_before_lock SIGNAL signal.wsrep_apply_cb WAIT_FOR bf_abort';
--send CREATE TABLE t1 AS SELECT * FROM t2;

--connection node_2
SELECT COUNT(*) = 5 FROM t2;
CREATE TABLE t1 AS SELECT * FROM t2;

--connection node_1
--error ER_QUERY_INTERRUPTED
--reap

SET GLOBAL DEBUG_DBUG = @save_debug_dbug;
SET DEBUG_SYNC = 'RESET';
DROP TABLE t1, t2;

#
# Temporary table
#

CREATE TABLE t2 (f1 INTEGER) ENGINE=InnoDB;
INSERT INTO t2 VALUES (1),(2),(3),(4),(5);

CREATE TEMPORARY TABLE t1 AS SELECT * FROM t2;

--connection node_2
--error ER_NO_SUCH_TABLE
SELECT * FROM t1;

CALL mtr.add_suppression("Slave SQL: Error 'Unknown table 'test.t1'' on query");

--connection node_1
DROP TABLE t1, t2;