innodb/t/foreign_key.test

--source include/have_innodb.inc
--source include/not_embedded.inc

--echo #
--echo # Bug #19027905 ASSERT RET.SECOND DICT_CREATE_FOREIGN_CONSTRAINTS_LOW
--echo # DICT_CREATE_FOREIGN_CONSTR
--echo #

create table t1 (f1 int primary key) engine=InnoDB;
--error ER_CANNOT_ADD_FOREIGN
create table t2 (f1 int primary key,
constraint c1 foreign key (f1) references t1(f1),
constraint c1 foreign key (f1) references t1(f1)) engine=InnoDB;
create table t2 (f1 int primary key,
   constraint c1 foreign key (f1) references t1(f1)) engine=innodb;

--replace_regex /#sql-[0-9a-f_]*'/#sql-temporary'/
--error ER_DUP_KEY
alter table t2 add constraint c1 foreign key (f1) references t1(f1);

set foreign_key_checks = 0;
--error ER_FK_DUP_NAME
alter table t2 add constraint c1 foreign key (f1) references t1(f1);

drop table t2, t1;

--echo #
--echo # Bug #20031243 CREATE TABLE FAILS TO CHECK IF FOREIGN KEY COLUMN
--echo # NULL/NOT NULL MISMATCH
--echo #

set foreign_key_checks = 1;
show variables like 'foreign_key_checks';

CREATE TABLE t1
(a INT NOT NULL,
 b INT NOT NULL,
 INDEX idx(a)) ENGINE=InnoDB;

CREATE TABLE t2
(a INT KEY,
 b INT,
 INDEX ind(b),
 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE CASCADE)
 ENGINE=InnoDB;

show create table t1;
show create table t2;

INSERT INTO t1 VALUES (1, 80);
INSERT INTO t1 VALUES (2, 81);
INSERT INTO t1 VALUES (3, 82);
INSERT INTO t1 VALUES (4, 83);
INSERT INTO t1 VALUES (5, 84);

INSERT INTO t2 VALUES (51, 1);
INSERT INTO t2 VALUES (52, 2);
INSERT INTO t2 VALUES (53, 3);
INSERT INTO t2 VALUES (54, 4);
INSERT INTO t2 VALUES (55, 5);

SELECT a, b FROM t1 ORDER BY a;
SELECT a, b FROM t2 ORDER BY a;

--error ER_NO_REFERENCED_ROW_2
INSERT INTO t2 VALUES (56, 6);

ALTER TABLE t1 CHANGE a id INT;

SELECT id, b FROM t1 ORDER BY id;
SELECT a, b FROM t2 ORDER BY a;

--echo # Operations on child table
--error ER_NO_REFERENCED_ROW_2
INSERT INTO t2 VALUES (56, 6);
--error ER_NO_REFERENCED_ROW_2
UPDATE t2 SET b = 99 WHERE a = 51;
DELETE FROM t2 WHERE a = 53;
SELECT id, b FROM t1 ORDER BY id;
SELECT a, b FROM t2 ORDER BY a;

--echo # Operations on parent table
DELETE FROM t1 WHERE id = 1;
UPDATE t1 SET id = 50 WHERE id = 5;
SELECT id, b FROM t1 ORDER BY id;
SELECT a, b FROM t2 ORDER BY a;

DROP TABLE t2, t1;

--echo #
--echo # bug#25126722 FOREIGN KEY CONSTRAINT NAME IS NULL AFTER RESTART
--echo # base bug#24818604 [GR]
--echo #

CREATE TABLE t1 (c1 INT PRIMARY KEY);
CREATE TABLE t2 (c1 INT PRIMARY KEY, FOREIGN KEY (c1) REFERENCES t1(c1));

INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1);

SELECT unique_constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't2';

--source include/restart_mysqld.inc

SELECT unique_constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't2';

SELECT * FROM t1;

SELECT unique_constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't2';

DROP TABLE t2;
DROP TABLE t1;

--echo #
--echo # Bug#33053297 VIRTUAL INDEX CORRUPTED DURING CASCADE UPDATE ON CHILD TABLE
--echo #

--echo #Test-Case 1
CREATE TABLE `emails` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
ROW_FORMAT=DYNAMIC;

CREATE TABLE `email_stats` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`email_id` int unsigned DEFAULT NULL,
`date_sent` datetime NOT NULL,
`generated_sent_date` date GENERATED ALWAYS AS
(concat(year(`date_sent`),'-',lpad(month(`date_sent`),2,'0'),
'-',lpad(dayofmonth(`date_sent`),2,'0'))) VIRTUAL,
PRIMARY KEY (`id`),
KEY `IDX_ES1` (`email_id`),
KEY `mautic_generated_sent_date_email_id`
(`generated_sent_date`,`email_id`),
CONSTRAINT `FK_EA1` FOREIGN KEY (`email_id`) REFERENCES
`emails` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
ROW_FORMAT=DYNAMIC;

INSERT INTO `emails` VALUES (1);
INSERT INTO `email_stats` (`id`, `email_id`,  `date_sent`) VALUES
(1,1,'2020-10-22 13:32:41');
SELECT * FROM `email_stats`;
DELETE FROM `emails`;
DELETE FROM `email_stats`;

#clean up.
DROP TABLE `email_stats`;
DROP TABLE `emails`;

--echo # Test-Case 2
CREATE TABLE `emails` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
ROW_FORMAT=DYNAMIC;

CREATE TABLE `email_stats` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`email_id` int unsigned DEFAULT NULL,
`date_sent` datetime NOT NULL,
`generated_sent_date` date GENERATED ALWAYS AS
(concat(year(`date_sent`),'-',lpad(month(`date_sent`),2,'0'),
'-',lpad(dayofmonth(`date_sent`),2,'0'))) VIRTUAL,
PRIMARY KEY (`id`),
KEY `IDX_ES1` (`email_id`),
KEY `mautic_generated_sent_date_email_id`
(`generated_sent_date`,`email_id`),
CONSTRAINT `FK_EA1` FOREIGN KEY (`email_id`) REFERENCES
`emails` (`id`) ON DELETE SET NULL ON UPDATE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
ROW_FORMAT=DYNAMIC;
INSERT INTO `emails` VALUES (1);
INSERT INTO `email_stats` (`id`, `email_id`,  `date_sent`) VALUES
(1,1,'2020-10-22 13:32:41');
UPDATE `emails` SET `id` = 2 where `id` = 1;
SELECT id FROM `email_stats` WHERE `generated_sent_date` IS NULL;
SELECT * FROM `email_stats`;
UPDATE `email_stats` SET `email_id`=2
WHERE DATE(`generated_sent_date`) = '2020-10-22';
SELECT * FROM `email_stats`;

#clean up.
DROP TABLE `email_stats`;
DROP TABLE `emails`;

--echo # test-case 3
CREATE TABLE `emails` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
ROW_FORMAT=DYNAMIC;
CREATE TABLE `email_stats` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`email_id` int unsigned DEFAULT NULL,
`date_sent` datetime NOT NULL,
`generated_sent_email` varchar(20) GENERATED ALWAYS AS
(CONCAT(YEAR(`date_sent`),'-', COALESCE(`email_id`, ' '))) VIRTUAL,
PRIMARY KEY (`id`),
KEY `idx_es1` (`email_id`),
KEY `mautic_generated_sent_date_email`
(`generated_sent_email`,`email_id`),
CONSTRAINT `fk_ea1` FOREIGN KEY (`email_id`) REFERENCES
`emails` (`id`) ON DELETE SET NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
ROW_FORMAT=DYNAMIC;
INSERT INTO `emails` VALUES (1);
INSERT INTO `email_stats` (`id`, `email_id`,  `date_sent`) VALUES
(1,1,'2020-10-22 13:32:41');
SELECT * FROM `email_stats`;
SELECT `date_sent` FROM `email_stats` WHERE `generated_sent_email` = '2020-1';
DELETE FROM `emails`;
SELECT * FROM `email_stats`;
SELECT `date_sent` FROM `email_stats` WHERE `generated_sent_email` = '2020-';

#clean up.
DROP TABLE `email_stats`;
DROP TABLE `emails`;

--echo # test-case 4
CREATE TABLE `emails` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
ROW_FORMAT=DYNAMIC;
CREATE TABLE `email_stats` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`email_id` int unsigned DEFAULT NULL,
`date_sent` datetime NOT NULL,
`generated_sent_email` varchar(20) GENERATED ALWAYS AS
(CONCAT(YEAR(`date_sent`),'-', COALESCE(`email_id`, ' '))) VIRTUAL,
PRIMARY KEY (`id`),
KEY `idx_es1` (`email_id`),
KEY `mautic_generated_sent_date_email`
(`generated_sent_email`,`email_id`),
CONSTRAINT `fk_ea1` FOREIGN KEY (`email_id`) REFERENCES
`emails` (`id`) ON UPDATE SET NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
ROW_FORMAT=DYNAMIC;
INSERT INTO `emails` VALUES (1);
INSERT INTO `email_stats` (`id`, `email_id`,  `date_sent`) VALUES
(1,1,'2020-10-22 13:32:41');
SELECT * FROM `email_stats`;
SELECT `date_sent` FROM `email_stats` WHERE `generated_sent_email` = '2020-1';
UPDATE `emails` SET `id` = 2 WHERE `id` = 1;
SELECT * FROM `email_stats`;
SELECT `date_sent` FROM `email_stats` WHERE `generated_sent_email` = '2020-';

#clean up.
DROP TABLE `email_stats`;
DROP TABLE `emails`;

--echo #
--echo # PS-7940 :ON DELETE CASCADE with generated column crashes in innobase_get_computed_value
--echo #

CREATE TABLE `t1` (`t1_id` int unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY(`t1_id`)) ENGINE=InnoDB;
INSERT INTO `t1` VALUES (10);
CREATE TABLE `t2` (
  `t2_id` int unsigned NOT NULL AUTO_INCREMENT,
  `c1` decimal(14,2) NOT NULL DEFAULT '0.00',
  `t1_id` int unsigned DEFAULT NULL,
  `testycol` bit(1) GENERATED ALWAYS AS (`c1` <> 0) VIRTUAL,
  PRIMARY KEY (`t2_id`),
  UNIQUE KEY `t1_id` (`t1_id`),
  KEY `testycol` (`testycol`),
  CONSTRAINT `t2_ibfk_3` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`t1_id`) ON DELETE CASCADE
) ENGINE=InnoDB;
INSERT INTO `t2` (`t2_id`, `c1`, `t1_id`) VALUES (1,5.00,10);

--source include/restart_mysqld.inc

DELETE FROM `t1` WHERE `t1_id`=10;

DROP TABLE `t2`;
DROP TABLE `t1`;