#########################################
# ==== Purpose ====
#
# Test various scenarios involved in creation of foreign key (FK)
# when executing CREATE TABLE ... SELECT (DDL_CTAS).
#
# ==== Requirements ====
#
# R1 The behavior of non-atomic DDL_CTAS remains the same
#    with request to create FK.
#
# R2 The behavior of atomic CTAS and request to create FK is rejected if
#    following conditions are true,
#    - SE supports foreign keys.
#    - SE supports atomic DDL.
#    - The binlogging is enabled.
#    - The binlog format is 'row'.
#
# ==== Implementation ====
#
# TC1 The behavior of non-atomic CTAS remains the same
#     with request to create FK.
# 1) Allow creation of FK on table using MyISAM engine. Using rows which
#    violate FK constraint. Make sure that the FK constraint is ignored.
# 2) Allow creation of FK on table using MyISAM engine. Using rows which
#    do not violate FK constraint.
#
# TC2 The behavior of atomic CTAS, with sql_log_bin OFF.
# 1) Attempt to create FK on table using InnoDB engine. Using rows which
#    violate FK constraint. Make sure we get ER_NO_REFERENCED_ROW_2.
# 2) Allow creation of FK on table using InnoDB engine. Using rows which
#    do not violate FK constraint.
#
# TC3 The behavior of CTAS, with sql_log_bin ON and format STATEMENT.
# 1) Attempt to create FK on table using InnoDB engine. Using rows which
#    violate FK constraint. Make sure we get ER_NO_REFERENCED_ROW_2.
# 2) Allow creation of FK on table using InnoDB engine. Using rows which
#    do not violate FK constraint.
#
# TC4 The behavior of CTAS, with sql_log_bin ON and format MIXED.
# 1) Steps are same as TC3 and the behavior too would be same.
#
# TC5 The behavior of CTAS, with sql_log_bin ON and format ROW.
# 1) Attempt to create FK on table using InnoDB engine and using rows which
#    violate FK constraint results in
#    ER_FOREIGN_KEY_WITH_ATOMIC_CREATE_SELECT.
# 2) Attempt to create FK on table using InnoDB engine and using rows which
#    do not violate FK constraint results in
#    ER_FOREIGN_KEY_WITH_ATOMIC_CREATE_SELECT.
# 3) Attempt to create FK on table using InnoDB engine with CREATE TABLE ...
#    START TRANSACTION is rejected with
#    ER_FOREIGN_KEY_WITH_ATOMIC_CREATE_SELECT.
#
# ==== References ====
#
# WL#13355 Make CREATE TABLE...SELECT atomic and crash-safe
#

# Skip ps protocol because CREATE TABLE ... START TRANSACTION is not
# allowed to be run with ps protocol.
--source include/no_ps_protocol.inc
--source include/have_log_bin.inc

SET @saved_sql_log_bin = @@SESSION.sql_log_bin;

--echo #
--echo # CREATE TABLE AS SELECT (CTAS) and foreign key (FK).
--echo #

CREATE TABLE t0 (f1 INT PRIMARY KEY);
INSERT INTO t0 VALUES (1),(2),(3),(4);

--echo #
--echo # CASE 1 The behavior of non-atomic CTAS remains the same
--echo # with request to create FK. There is no engine which do
--echo # not support atomic DDL, but supports foreign keys.

--echo # MyISAM does not support foreign keys, so there is no error
--echo # as it ignore FK constraints silently.
CREATE TABLE myisam_table1 (m INT, n INT, FOREIGN KEY (n) REFERENCES t0(f1))
  ENGINE=MyISAM AS SELECT 101 as m, 5 as n;
SHOW CREATE TABLE myisam_table1;
CREATE TABLE myisam_table2 (m INT, n INT, FOREIGN KEY (n) REFERENCES t0(f1))
  ENGINE=MyISAM AS SELECT 101 as m, 2 as n;
SHOW CREATE TABLE myisam_table2;

DROP TABLE myisam_table1;
DROP TABLE myisam_table2;

--echo #
--echo # CASE 2 The behavior of atomic CTAS, with sql_log_bin OFF.
--echo #

SET sql_log_bin = OFF;
--error ER_NO_REFERENCED_ROW_2
CREATE TABLE innodb_table1 (m INT, n INT, FOREIGN KEY (n) REFERENCES t0(f1))
  AS SELECT 101 as m, 5 as n;
CREATE TABLE innodb_table1 (m INT, n INT, FOREIGN KEY (n) REFERENCES t0(f1))
  AS SELECT 101 as m, 2 as n;
CREATE TABLE innodb_table2 as SELECT m, 4 FROM innodb_table1;
SHOW CREATE TABLE innodb_table1;
SHOW CREATE TABLE innodb_table2;
DROP TABLE innodb_table1;
DROP TABLE innodb_table2;

--echo #
--echo # CASE 3 The behavior of CTAS, with sql_log_bin ON and format STATEMENT.
--echo #

SET sql_log_bin = ON;
SET @@SESSION.binlog_format=STATEMENT;
--error ER_NO_REFERENCED_ROW_2
CREATE TABLE innodb_table1 (m INT, n INT, FOREIGN KEY (n) REFERENCES t0(f1))
  AS SELECT 101 as m, 5 as n;

CREATE TABLE innodb_table1 (m INT, n INT, FOREIGN KEY (n) REFERENCES t0(f1))
  AS SELECT 101 as m, 2 as n;
DROP TABLE innodb_table1;

--echo #
--echo # CASE 4 The behavior of CTAS, with sql_log_bin ON and format MIXED.
--echo # The behavior would be same as case 3 above.

SET @@SESSION.binlog_format=MIXED;
--error ER_NO_REFERENCED_ROW_2
CREATE TABLE innodb_table1 (m INT, n INT, FOREIGN KEY (n) REFERENCES t0(f1))
  AS SELECT 101 as m, 5 as n;

CREATE TABLE innodb_table1 (m INT, n INT, FOREIGN KEY (n) REFERENCES t0(f1))
  AS SELECT 101 as m, 2 as n;
DROP TABLE innodb_table1;

--echo #
--echo # CASE 5 The behavior of CTAS, with sql_log_bin ON and format ROW.
--echo #

SET @@SESSION.binlog_format=ROW;
--error ER_FOREIGN_KEY_WITH_ATOMIC_CREATE_SELECT
CREATE TABLE innodb_table1 (m INT, n INT, FOREIGN KEY (n) REFERENCES t0(f1))
  AS SELECT 101 as m, 5 as n;

--error ER_FOREIGN_KEY_WITH_ATOMIC_CREATE_SELECT
CREATE TABLE innodb_table1 (m INT, n INT, FOREIGN KEY (n) REFERENCES t0(f1))
  AS SELECT 101 as m, 2 as n;

--error ER_FOREIGN_KEY_WITH_ATOMIC_CREATE_SELECT
CREATE TABLE innodb_table1 (m INT, n INT,
  FOREIGN KEY (n) REFERENCES t0(f1)) START TRANSACTION;

--echo #
--echo # Bug#35553557 Table creation with foreign key assertion error
--echo #

SET sql_log_bin = OFF;
CREATE TABLE innodb_table1(m INT, FOREIGN KEY (m) REFERENCES t0(f1))
  AS SELECT f1 AS m FROM t0;
--error ER_ROW_IS_REFERENCED_2
DELETE FROM t0 WHERE f1 = 1;

DROP TABLE innodb_table1;

CREATE TABLE self_referencing_table(pk INT PRIMARY KEY, fk INT,
  FOREIGN KEY (fk) REFERENCES self_referencing_table(pk))
  AS SELECT 1 AS pk, 1 as fk;

INSERT INTO self_referencing_table VALUES (2,1), (3, NULL), (4,3);

--error ER_ROW_IS_REFERENCED_2
DELETE FROM self_referencing_table WHERE pk=1;
SELECT pk, fk FROM self_referencing_table ORDER BY pk;
--error ER_ROW_IS_REFERENCED_2
DELETE FROM self_referencing_table WHERE pk=3;
DELETE FROM self_referencing_table WHERE pk=4;
SELECT pk, fk FROM self_referencing_table ORDER BY pk;
DELETE FROM self_referencing_table WHERE pk=3;
SELECT pk, fk FROM self_referencing_table ORDER BY pk;

DROP TABLE self_referencing_table;
SET sql_log_bin = @saved_sql_log_bin;
DROP TABLE t0;