mariadb/mysql-test/main/check_table_references.test

--source include/have_innodb.inc

create table t1 (x int primary key) engine=innodb;

create table t2 (x int, y int,
                 foreign key (x) references t1(x)) engine=innodb;

set statement foreign_key_checks = 0 for
  drop table t1;

check table t1 extended;
check table t2 extended;

set statement foreign_key_checks = 0 for
  create table t1 (x int) engine=innodb;

check table t1 extended;
check table t2 extended;

set statement foreign_key_checks = 0 for
  drop index x on t2;

check table t1 extended;
check table t2 extended;

set statement foreign_key_checks = 0 for
  create unique index p on t1(x);

check table t1 extended;
check table t2 extended;

set statement foreign_key_checks = 0 for
create index x on t2(x);

check table t1 extended;
check table t2 extended;

set statement foreign_key_checks = 0 for
drop table t2;

check table t1 extended;
check table t2 extended;

--echo # ======================================================================

set statement foreign_key_checks = 0 for
create table t2 (x int, y int, key(x),
                 foreign key (x) references t1(x)) engine=innodb;


insert into t1 (x) values (1);

check table t1 extended;
check table t2 extended;

insert into t2 (x, y) values (1, 10);

check table t1 extended;
check table t2 extended;

set statement foreign_key_checks = 0 for
    insert into t2 (x, y) values (2, 20), (2, 20), (3, 30);

check table t1 extended;
check table t2 extended;

insert into t1 (x) values (2);

check table t1 extended;
check table t2 extended;


insert into t1 (x) values (3);

check table t1 extended;
check table t2 extended;

drop table t2, t1;



create table t1 (x int primary key, y int, z int,
                 key(x, z), unique(y)) engine=innodb;


insert into t1 (x, y, z) values (1, 10, 100);

create table t2 (x int, t text, y int, z int,
                 foreign key (x, y) references t1(x, z),
                 foreign key(z) references t1(y)) engine=innodb;

show create table t2;

set statement foreign_key_checks = 0 for
    insert into t2 (x, y, z, t) values (2, 2, 200, "two, two, 200"),
                                       (2, 22, 202, "two, twenty two, 202"),
                                       (3, 30, 300, "three, thirty, 300"),
                                       (0, 0, 10, NULL);

create table t3 (x int, t text, y int, z int
                #, foreign key (x) references t2(x)
                ) engine=innodb;

check table t1 extended;
check table t2 extended;
check table t3 extended;


drop table t3, t2, t1;

--echo # MDEV-35733 ASAN errors in row_mysql_store_col_in_innobase_format

create table t (pk int primary key, a int, b int, key(b),
                foreign key (a) references t (b)) engine=innodb;
insert into t values (1,10,10);
check table t extended;

drop table t;

--echo # MDEV-35730 Server crashes in Field::ptr_in_record

CREATE TABLE t (id INT, a TIME, b BIT, KEY(a,id)) ENGINE=InnoDB;
INSERT INTO t VALUES (1,'00:00:00',0);
SET FOREIGN_KEY_CHECKS = OFF;
ALTER TABLE t ADD FOREIGN KEY (b) REFERENCES t (a);
SET FOREIGN_KEY_CHECKS = ON;
CHECK TABLE t EXTENDED;
DROP TABLE t;

--echo # MDEV-35731 Assertion `(mem_root->flags & 4) == 0' failed upon 2nd
--echo # execution of CHECK TABLE with FK

create table t1 (a int) engine=innodb;
set foreign_key_checks = off;
alter table t1 add foreign key (a) references x(x);
set foreign_key_checks = on;
prepare stmt from 'check table t1 extended';
execute stmt;
execute stmt;

create table t2 (a int) engine=innodb;
set foreign_key_checks = off;
alter table t2 add foreign key (a) references x(x);
set foreign_key_checks = on;
prepare stmt from 'check table t1, t2, nosuchtable extended';
execute stmt;
execute stmt;

drop table t2;
drop table t1;

--echo # MDEV-35737 MSAN errors in check_table_referential_checks_needed upon
--echo # LOAD INDEX

create table t (pk int primary key) engine=innodb;
load index into cache t;
drop table t;

--echo # MDEV-35908 Unexpected error, crash, MSAN errors, assertion failures
--echo # upon CHECK

create table t (pk int primary key, a varchar(15), b char(15), key(a), key(b),
                foreign key (a) references t(b) on delete cascade)
               engine=innodb;

--error ER_NO_REFERENCED_ROW_2
insert into t values (1,'foo', 'foo');

set foreign_key_checks = off;
insert into t values (1,'foo', 'foo');
insert into t values (2,'fee', 'qwe');
insert into t values (3,'fii', 'fiii');

--echo # This one is really confusing, but it's a valid reference to 'foo'
--echo # See the select results after b='foo' record is deleted.
insert into t values (4,'foo ', 'floo');
set foreign_key_checks = on;

check table t extended;

drop table t;

create table t (pk int primary key, a char(15), b varchar(15), key(b), foreign key (a) references t(b)) engine=innodb;
insert into t values (1,'foo', 'foo');
check table t extended;
drop table t;


--echo # Bogus table: length of `b` is bigger than length of `a`
create table t (a time, b inet6, key(a)) engine=innodb;
alter table t add foreign key (b) references t (a);
insert into t values ('00:00:00','8000:0020:2020:2020:2020:2020:2020:2020');
check table t extended;

drop table t;


--echo # In a->b relation, `a` key_part can be shorter than `b`
create table t (a varchar(1), b varchar(8), key(b),
                foreign key(a) references t(b)) engine=innodb;
set foreign_key_checks = off;
insert into t values ('d','dd');
set foreign_key_checks = on;
check table t extended;
drop table t;


--echo # MDEV-35936 More ASAN errors in check_key_referential_integrity

create table t1 (a char(64)) engine=innodb;
insert into t1 values ('foo');
create table t2 (b char(128), key(b)) engine=innodb;
insert into t2 values ('foo');
alter table t1 add foreign key (a) references t2 (b);
check table t1 extended;

drop table t1;
drop table t2;