diff options
38 files changed, 301 insertions, 131 deletions
diff --git a/mysql-test/main/foreign_key.result b/mysql-test/main/foreign_key.result index a82151ddec0..d925dfa250d 100644 --- a/mysql-test/main/foreign_key.result +++ b/mysql-test/main/foreign_key.result @@ -1,4 +1,6 @@ drop table if exists t1,t2; +create table t2(c int); +create table t3(c int, d int); create table t1 ( a int not null references t2, b int not null references t2 (c), @@ -12,7 +14,7 @@ foreign key (a,b) references t3 (c,d) on delete set default, foreign key (a,b) references t3 (c,d) on update set null); create index a on t1 (a); create unique index b on t1 (a,b); -drop table t1; +drop table t1, t2, t3; create table t1 (id int primary key) engine = innodb; create table t2 (id int PRIMARY KEY, FOREIGN KEY (id) REFERENCES t1(id)) engine=innodb; insert into t1 values (1), (2), (3), (4), (5), (6); diff --git a/mysql-test/main/foreign_key.test b/mysql-test/main/foreign_key.test index 17c93332cb3..1f1d11a8310 100644 --- a/mysql-test/main/foreign_key.test +++ b/mysql-test/main/foreign_key.test @@ -8,6 +8,9 @@ drop table if exists t1,t2; --enable_warnings +create table t2(c int); +create table t3(c int, d int); + create table t1 ( a int not null references t2, b int not null references t2 (c), @@ -22,7 +25,7 @@ create table t1 ( create index a on t1 (a); create unique index b on t1 (a,b); -drop table t1; +drop table t1, t2, t3; # End of 4.1 tests diff --git a/mysql-test/main/func_regexp_pcre.result b/mysql-test/main/func_regexp_pcre.result index e030df99756..abcbbd05e46 100644 --- a/mysql-test/main/func_regexp_pcre.result +++ b/mysql-test/main/func_regexp_pcre.result @@ -887,7 +887,9 @@ Warnings: Warning 1139 Got error 'pcre_exec: recursion limit of NUM exceeded' from regexp SELECT CONCAT(REPEAT('100,',60),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$'; CONCAT(REPEAT('100,',60),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$' -1 +0 +Warnings: +Warning 1139 Got error 'pcre_exec: recursion limit of 75 exceeded' from regexp SELECT CONCAT(REPEAT('100,',200),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$'; CONCAT(REPEAT('100,',200),'101') RLIKE '^(([1-9][0-9]*),)*[1-9][0-9]*$' 0 @@ -895,7 +897,9 @@ Warnings: Warning 1139 Got error 'pcre_exec: recursion limit of NUM exceeded' from regexp SELECT REGEXP_INSTR(CONCAT(REPEAT('100,',60),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$'); REGEXP_INSTR(CONCAT(REPEAT('100,',60),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$') -1 +0 +Warnings: +Warning 1139 Got error 'pcre_exec: recursion limit of 75 exceeded' from regexp SELECT REGEXP_INSTR(CONCAT(REPEAT('100,',200),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$'); REGEXP_INSTR(CONCAT(REPEAT('100,',200),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$') 0 @@ -903,7 +907,9 @@ Warnings: Warning 1139 Got error 'pcre_exec: recursion limit of NUM exceeded' from regexp SELECT LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',60),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')); LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',60),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')) -243 +0 +Warnings: +Warning 1139 Got error 'pcre_exec: recursion limit of 75 exceeded' from regexp SELECT LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',200),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')); LENGTH(REGEXP_SUBSTR(CONCAT(REPEAT('100,',200),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$')) 0 @@ -911,7 +917,9 @@ Warnings: Warning 1139 Got error 'pcre_exec: recursion limit of NUM exceeded' from regexp SELECT LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',60),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')); LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',60),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')) -0 +243 +Warnings: +Warning 1139 Got error 'pcre_exec: recursion limit of 75 exceeded' from regexp SELECT LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',200),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')); LENGTH(REGEXP_REPLACE(CONCAT(REPEAT('100,',200),'101'), '^(([1-9][0-9]*),)*[1-9][0-9]*$', '')) 803 diff --git a/mysql-test/main/insert_notembedded.result b/mysql-test/main/insert_notembedded.result index 8dd4aa7d71e..927a3fa520b 100644 --- a/mysql-test/main/insert_notembedded.result +++ b/mysql-test/main/insert_notembedded.result @@ -15,10 +15,10 @@ CREATE TABLE table_target2 ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY ( CREATE TABLE table_target3 ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id)); CREATE VIEW view_target2 AS SELECT mexs_id,messzeit FROM table_target2; CREATE SQL SECURITY INVOKER VIEW view_target3 AS SELECT mexs_id,messzeit FROM table_target3; -CREATE TABLE table_stations ( mexs_id VARCHAR(8), icao VARCHAR(4), country CHAR(2), PRIMARY KEY (mexs_id), UNIQUE KEY icao (icao), KEY country (country), CONSTRAINT stations_ibfk_8 FOREIGN KEY (country) REFERENCES countries (country) ON UPDATE CASCADE); -INSERT INTO table_stations VALUES ('87654321','XXXX','YY'); CREATE TABLE table_countries ( country CHAR(2), iso_short_en VARCHAR(64), PRIMARY KEY (country)); INSERT INTO table_countries VALUES ('YY','Entenhausen'); +CREATE TABLE table_stations ( mexs_id VARCHAR(8), icao VARCHAR(4), country CHAR(2), PRIMARY KEY (mexs_id), UNIQUE KEY icao (icao), KEY country (country), CONSTRAINT stations_ibfk_8 FOREIGN KEY (country) REFERENCES table_countries (country) ON UPDATE CASCADE); +INSERT INTO table_stations VALUES ('87654321','XXXX','YY'); CREATE ALGORITHM=MERGE SQL SECURITY INVOKER VIEW view_stations AS select table_stations.mexs_id AS mexs_id, table_stations.icao AS icao, table_stations.country AS landescode from (table_stations join table_countries on((table_stations.country = table_countries.country))); CREATE TABLE table_source ( id varchar(4), datetime TIMESTAMP, PRIMARY KEY (id)); INSERT INTO table_source VALUES ('XXXX','2006-07-12 07:50:00'); diff --git a/mysql-test/main/insert_notembedded.test b/mysql-test/main/insert_notembedded.test index 2769aee8d8a..9d1d42ed920 100644 --- a/mysql-test/main/insert_notembedded.test +++ b/mysql-test/main/insert_notembedded.test @@ -38,12 +38,12 @@ CREATE TABLE table_target3 ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY ( CREATE VIEW view_target2 AS SELECT mexs_id,messzeit FROM table_target2; CREATE SQL SECURITY INVOKER VIEW view_target3 AS SELECT mexs_id,messzeit FROM table_target3; -CREATE TABLE table_stations ( mexs_id VARCHAR(8), icao VARCHAR(4), country CHAR(2), PRIMARY KEY (mexs_id), UNIQUE KEY icao (icao), KEY country (country), CONSTRAINT stations_ibfk_8 FOREIGN KEY (country) REFERENCES countries (country) ON UPDATE CASCADE); -INSERT INTO table_stations VALUES ('87654321','XXXX','YY'); - CREATE TABLE table_countries ( country CHAR(2), iso_short_en VARCHAR(64), PRIMARY KEY (country)); INSERT INTO table_countries VALUES ('YY','Entenhausen'); +CREATE TABLE table_stations ( mexs_id VARCHAR(8), icao VARCHAR(4), country CHAR(2), PRIMARY KEY (mexs_id), UNIQUE KEY icao (icao), KEY country (country), CONSTRAINT stations_ibfk_8 FOREIGN KEY (country) REFERENCES table_countries (country) ON UPDATE CASCADE); +INSERT INTO table_stations VALUES ('87654321','XXXX','YY'); + CREATE ALGORITHM=MERGE SQL SECURITY INVOKER VIEW view_stations AS select table_stations.mexs_id AS mexs_id, table_stations.icao AS icao, table_stations.country AS landescode from (table_stations join table_countries on((table_stations.country = table_countries.country))); CREATE TABLE table_source ( id varchar(4), datetime TIMESTAMP, PRIMARY KEY (id)); diff --git a/mysql-test/main/partition.result b/mysql-test/main/partition.result index 5a7795394ec..09de0dce9b4 100644 --- a/mysql-test/main/partition.result +++ b/mysql-test/main/partition.result @@ -303,10 +303,12 @@ create index i2 on t1 (a); Warnings: Note 1831 Duplicate index `i2`. This is deprecated and will be disallowed in a future release drop table t1; +CREATE TABLE t0(a int); CREATE TABLE t1 (a INT, FOREIGN KEY (a) REFERENCES t0 (a)) ENGINE=MyISAM PARTITION BY HASH (a); ERROR HY000: Foreign key clause is not yet supported in conjunction with partitioning +DROP TABLE t0; CREATE TABLE t1 ( pk INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (pk) diff --git a/mysql-test/main/partition.test b/mysql-test/main/partition.test index 89db3e92162..b4f2d1a3d1b 100644 --- a/mysql-test/main/partition.test +++ b/mysql-test/main/partition.test @@ -290,10 +290,12 @@ drop table t1; # # Bug#36001: Partitions: spelling and using some error messages # +CREATE TABLE t0(a int); --error ER_FOREIGN_KEY_ON_PARTITIONED CREATE TABLE t1 (a INT, FOREIGN KEY (a) REFERENCES t0 (a)) ENGINE=MyISAM PARTITION BY HASH (a); +DROP TABLE t0; # # Bug#40954: Crash if range search and order by. diff --git a/mysql-test/main/query_cache_innodb.result b/mysql-test/main/query_cache_innodb.result index 62424ac362b..1f2996f50ca 100644 --- a/mysql-test/main/query_cache_innodb.result +++ b/mysql-test/main/query_cache_innodb.result @@ -85,7 +85,7 @@ t2id id use test; drop database `#mysql50#-`; SET NAMES default; -FOUND 8 /\[ERROR\] Invalid \(old\?\) table or database name/ in mysqld.1.err +FOUND 16 /\[ERROR\] Invalid \(old\?\) table or database name/ in mysqld.1.err set global query_cache_type=DEFAULT; set global query_cache_size=@save_query_cache_size; End of 10.2 tests diff --git a/mysql-test/main/union.result b/mysql-test/main/union.result index e3a69f54949..8b58e671bb4 100644 --- a/mysql-test/main/union.result +++ b/mysql-test/main/union.result @@ -577,7 +577,7 @@ NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL drop table t1,t2; create table t1 ( id int not null auto_increment, primary key (id) ,user_name text ); create table t2 ( id int not null auto_increment, primary key (id) ,group_name text ); -create table t3 ( id int not null auto_increment, primary key (id) ,user_id int ,index user_idx (user_id) ,foreign key (user_id) references users(id) ,group_id int ,index group_idx (group_id) ,foreign key (group_id) references groups(id) ); +create table t3 ( id int not null auto_increment, primary key (id) ,user_id int ,index user_idx (user_id) ,foreign key (user_id) references t1(id) ,group_id int ,index group_idx (group_id) ,foreign key (group_id) references t2(id) ); insert into t1 (user_name) values ('Tester'); insert into t2 (group_name) values ('Group A'); insert into t2 (group_name) values ('Group B'); diff --git a/mysql-test/main/union.test b/mysql-test/main/union.test index a90d27e161d..e1637211dfe 100644 --- a/mysql-test/main/union.test +++ b/mysql-test/main/union.test @@ -349,7 +349,7 @@ explain (select * from t1 where a=1) union (select * from t1 where b=1); drop table t1,t2; create table t1 ( id int not null auto_increment, primary key (id) ,user_name text ); create table t2 ( id int not null auto_increment, primary key (id) ,group_name text ); -create table t3 ( id int not null auto_increment, primary key (id) ,user_id int ,index user_idx (user_id) ,foreign key (user_id) references users(id) ,group_id int ,index group_idx (group_id) ,foreign key (group_id) references groups(id) ); +create table t3 ( id int not null auto_increment, primary key (id) ,user_id int ,index user_idx (user_id) ,foreign key (user_id) references t1(id) ,group_id int ,index group_idx (group_id) ,foreign key (group_id) references t2(id) ); insert into t1 (user_name) values ('Tester'); insert into t2 (group_name) values ('Group A'); insert into t2 (group_name) values ('Group B'); diff --git a/mysql-test/suite/gcol/inc/gcol_keys.inc b/mysql-test/suite/gcol/inc/gcol_keys.inc index 97c9e41b5e8..8eef551d0e7 100644 --- a/mysql-test/suite/gcol/inc/gcol_keys.inc +++ b/mysql-test/suite/gcol/inc/gcol_keys.inc @@ -130,33 +130,33 @@ if (!$skip_spatial_index_check) --echo # FOREIGN KEY --echo # Rejected FK options. ---error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN +--error ER_NO_SUCH_TABLE create table t1 (a int, b int generated always as (a+1) stored, foreign key (b) references t2(a) on update set null); ---error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN +--error ER_NO_SUCH_TABLE create table t1 (a int, b int generated always as (a+1) stored, foreign key (b) references t2(a) on update cascade); ---error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN +--error ER_NO_SUCH_TABLE create table t1 (a int, b int generated always as (a+1) stored, foreign key (b) references t2(a) on delete set null); create table t1 (a int, b int generated always as (a+1) stored); ---error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN +--error ER_NO_SUCH_TABLE alter table t1 add foreign key (b) references t2(a) on update set null; ---error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN +--error ER_NO_SUCH_TABLE alter table t1 add foreign key (b) references t2(a) on update cascade; ---error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN +--error ER_NO_SUCH_TABLE alter table t1 add foreign key (b) references t2(a) on delete set null; drop table t1; if(!$skip_foreign_key_check) { ---error ER_CANT_CREATE_TABLE +--error ER_NO_SUCH_TABLE create table t1 (a int, b int generated always as (a+1) virtual, foreign key (b) references t2(a)); create table t1 (a int, b int generated always as (a+1) virtual); ---error ER_CANT_CREATE_TABLE +--error ER_NO_SUCH_TABLE alter table t1 add foreign key (b) references t2(a); drop table t1; } diff --git a/mysql-test/suite/gcol/r/gcol_keys_innodb.result b/mysql-test/suite/gcol/r/gcol_keys_innodb.result index a1cc26a8f06..a8564e9c95c 100644 --- a/mysql-test/suite/gcol/r/gcol_keys_innodb.result +++ b/mysql-test/suite/gcol/r/gcol_keys_innodb.result @@ -133,27 +133,27 @@ drop table t1; # Rejected FK options. create table t1 (a int, b int generated always as (a+1) stored, foreign key (b) references t2(a) on update set null); -ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column +ERROR 42S02: Table 'test.t2' doesn't exist create table t1 (a int, b int generated always as (a+1) stored, foreign key (b) references t2(a) on update cascade); -ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column +ERROR 42S02: Table 'test.t2' doesn't exist create table t1 (a int, b int generated always as (a+1) stored, foreign key (b) references t2(a) on delete set null); -ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column +ERROR 42S02: Table 'test.t2' doesn't exist create table t1 (a int, b int generated always as (a+1) stored); alter table t1 add foreign key (b) references t2(a) on update set null; -ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column +ERROR 42S02: Table 'test.t2' doesn't exist alter table t1 add foreign key (b) references t2(a) on update cascade; -ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column +ERROR 42S02: Table 'test.t2' doesn't exist alter table t1 add foreign key (b) references t2(a) on delete set null; -ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column +ERROR 42S02: Table 'test.t2' doesn't exist drop table t1; create table t1 (a int, b int generated always as (a+1) virtual, foreign key (b) references t2(a)); -ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") +ERROR 42S02: Table 'test.t2' doesn't exist create table t1 (a int, b int generated always as (a+1) virtual); alter table t1 add foreign key (b) references t2(a); -ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") +ERROR 42S02: Table 'test.t2' doesn't exist drop table t1; # Allowed FK options. create table t2 (a int primary key, b char(5)); diff --git a/mysql-test/suite/gcol/r/gcol_keys_myisam.result b/mysql-test/suite/gcol/r/gcol_keys_myisam.result index 91bd8fcdb78..74f8c065abf 100644 --- a/mysql-test/suite/gcol/r/gcol_keys_myisam.result +++ b/mysql-test/suite/gcol/r/gcol_keys_myisam.result @@ -140,20 +140,20 @@ drop table t1; # Rejected FK options. create table t1 (a int, b int generated always as (a+1) stored, foreign key (b) references t2(a) on update set null); -ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column +ERROR 42S02: Table 'test.t2' doesn't exist create table t1 (a int, b int generated always as (a+1) stored, foreign key (b) references t2(a) on update cascade); -ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column +ERROR 42S02: Table 'test.t2' doesn't exist create table t1 (a int, b int generated always as (a+1) stored, foreign key (b) references t2(a) on delete set null); -ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column +ERROR 42S02: Table 'test.t2' doesn't exist create table t1 (a int, b int generated always as (a+1) stored); alter table t1 add foreign key (b) references t2(a) on update set null; -ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column +ERROR 42S02: Table 'test.t2' doesn't exist alter table t1 add foreign key (b) references t2(a) on update cascade; -ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column +ERROR 42S02: Table 'test.t2' doesn't exist alter table t1 add foreign key (b) references t2(a) on delete set null; -ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column +ERROR 42S02: Table 'test.t2' doesn't exist drop table t1; # Allowed FK options. create table t2 (a int primary key, b char(5)); diff --git a/mysql-test/suite/gcol/r/innodb_virtual_fk.result b/mysql-test/suite/gcol/r/innodb_virtual_fk.result index d5b4755e3c5..a5cf7db6910 100644 --- a/mysql-test/suite/gcol/r/innodb_virtual_fk.result +++ b/mysql-test/suite/gcol/r/innodb_virtual_fk.result @@ -702,7 +702,7 @@ v3 TIME AS (c3) VIRTUAL, v4 CHAR(10) AS (c4) VIRTUAL ) ENGINE=InnoDB; ALTER TABLE t1 ADD CONSTRAINT fk FOREIGN KEY (v4) REFERENCES nosuch(col); -ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") +ERROR 42S02: Table 'test.nosuch' doesn't exist SET foreign_key_checks=0; ALTER TABLE t1 ADD CONSTRAINT fk FOREIGN KEY (v4) REFERENCES nosuch(col); ERROR HY000: Failed to add the foreign key constaint. Missing index for constraint 'fk' in the foreign table 't1' @@ -725,7 +725,7 @@ t1 CREATE TABLE `t1` ( ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ALTER TABLE t1 DROP FOREIGN KEY fk; ALTER TABLE t1 ADD CONSTRAINT fk FOREIGN KEY (v4) REFERENCES nosuch(col); -ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") +ERROR 42S02: Table 'test.nosuch' doesn't exist SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( diff --git a/mysql-test/suite/gcol/t/innodb_virtual_fk.test b/mysql-test/suite/gcol/t/innodb_virtual_fk.test index c484bb5dc0c..6dd88383481 100644 --- a/mysql-test/suite/gcol/t/innodb_virtual_fk.test +++ b/mysql-test/suite/gcol/t/innodb_virtual_fk.test @@ -590,7 +590,7 @@ CREATE TABLE t1 ( v3 TIME AS (c3) VIRTUAL, v4 CHAR(10) AS (c4) VIRTUAL ) ENGINE=InnoDB; ---error ER_CANT_CREATE_TABLE +--error ER_NO_SUCH_TABLE ALTER TABLE t1 ADD CONSTRAINT fk FOREIGN KEY (v4) REFERENCES nosuch(col); SET foreign_key_checks=0; --error ER_FK_NO_INDEX_CHILD @@ -600,7 +600,7 @@ ALTER TABLE t1 ADD CONSTRAINT fk FOREIGN KEY (v4) REFERENCES nosuch(col); SET foreign_key_checks=1; SHOW CREATE TABLE t1; ALTER TABLE t1 DROP FOREIGN KEY fk; ---error ER_CANT_CREATE_TABLE +--error ER_NO_SUCH_TABLE ALTER TABLE t1 ADD CONSTRAINT fk FOREIGN KEY (v4) REFERENCES nosuch(col); SHOW CREATE TABLE t1; # Cleanup diff --git a/mysql-test/suite/innodb/r/foreign_key.result b/mysql-test/suite/innodb/r/foreign_key.result index 288b6bb835d..82b72939c9a 100644 --- a/mysql-test/suite/innodb/r/foreign_key.result +++ b/mysql-test/suite/innodb/r/foreign_key.result @@ -259,12 +259,10 @@ DROP TABLE t1; # CREATE TABLE t1 (a INT) ENGINE=InnoDB; ALTER IGNORE TABLE t1 ADD FOREIGN KEY (a) REFERENCES t2 (b); -ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") +ERROR 42S02: Table 'test.t2' doesn't exist SHOW WARNINGS; Level Code Message -Warning 150 Alter table `test`.`t1` with foreign key constraint failed. Referenced table `test`.`t2` not found in the data dictionary near 'FOREIGN KEY (a) REFERENCES t2 (b)'. -Error 1005 Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") -Warning 1215 Cannot add foreign key constraint for `t1` +Error 1146 Table 'test.t2' doesn't exist DROP TABLE t1; # # MDEV-18139 ALTER IGNORE ... ADD FOREIGN KEY causes bogus error diff --git a/mysql-test/suite/innodb/r/innodb-fk-warnings.result b/mysql-test/suite/innodb/r/innodb-fk-warnings.result index 21e7c23d249..81e12a27c91 100644 --- a/mysql-test/suite/innodb/r/innodb-fk-warnings.result +++ b/mysql-test/suite/innodb/r/innodb-fk-warnings.result @@ -48,12 +48,10 @@ Warning 1215 Cannot add foreign key constraint for `t2` drop table t2, t1; create table t1 (f1 integer primary key) engine=innodb; alter table t1 add constraint c1 foreign key (f1) references t11(f1); -ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") +ERROR 42S02: Table 'test.t11' doesn't exist show warnings; Level Code Message -Warning 150 Alter table `test`.`t1` with foreign key constraint failed. Referenced table `test`.`t11` not found in the data dictionary near ' foreign key (f1) references t11(f1)'. -Error 1005 Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") -Warning 1215 Cannot add foreign key constraint for `t1` +Error 1146 Table 'test.t11' doesn't exist drop table t1; create temporary table t1(a int not null primary key, b int, key(b)) engine=innodb; create temporary table t2(a int, foreign key(a) references t1(a)) engine=innodb; diff --git a/mysql-test/suite/innodb/r/innodb-fk.result b/mysql-test/suite/innodb/r/innodb-fk.result index a7b667b3839..0a1b6c65029 100644 --- a/mysql-test/suite/innodb/r/innodb-fk.result +++ b/mysql-test/suite/innodb/r/innodb-fk.result @@ -49,13 +49,10 @@ PRIMARY KEY (`id`), CONSTRAINT fk2 FOREIGN KEY (f2) REFERENCES t1 (`id`) ON DELETE CASCADE, CONSTRAINT fk3 FOREIGN KEY (f3) REFERENCES t3 (id) ON DELETE CASCADE ) ENGINE=InnoDB; -ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") +ERROR 42S02: Table 'test.t3' doesn't exist show warnings; Level Code Message -Warning 150 Create table `test`.`t2` with foreign key constraint failed. Referenced table `test`.`t3` not found in the data dictionary near ' FOREIGN KEY (f3) REFERENCES t3 (id) ON DELETE CASCADE -) ENGINE=InnoDB'. -Error 1005 Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") -Warning 1215 Cannot add foreign key constraint for `t2` +Error 1146 Table 'test.t3' doesn't exist CREATE TABLE t2 ( id int(11) NOT NULL AUTO_INCREMENT, f2 int(11) NOT NULL, @@ -64,12 +61,10 @@ PRIMARY KEY (`id`), CONSTRAINT fk2 FOREIGN KEY (f2) REFERENCES t1 (`id`) ON DELETE CASCADE ) ENGINE=InnoDB; ALTER TABLE t2 ADD CONSTRAINT fk3 FOREIGN KEY (f3) REFERENCES t3 (id) ON DELETE CASCADE; -ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") +ERROR 42S02: Table 'test.t3' doesn't exist show warnings; Level Code Message -Warning 150 Alter table `test`.`t2` with foreign key constraint failed. Referenced table `test`.`t3` not found in the data dictionary near ' FOREIGN KEY (f3) REFERENCES t3 (id) ON DELETE CASCADE'. -Error 1005 Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") -Warning 1215 Cannot add foreign key constraint for `t2` +Error 1146 Table 'test.t3' doesn't exist drop table t2; drop table t1; CREATE DATABASE kg_test1; @@ -97,7 +92,7 @@ CREATE TABLE `kg_test2`.`person2` ( PRIMARY KEY (`Id`), CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `group` (`Id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8; -ERROR HY000: Can't create table `kg_test2`.`person2` (errno: 150 "Foreign key constraint is incorrectly formed") +ERROR 42S02: Table 'kg_test2.group' doesn't exist CREATE TABLE `kg_test2`.`person2` ( `Id` INT(11) NOT NULL AUTO_INCREMENT, `Name` VARCHAR(50) NOT NULL, diff --git a/mysql-test/suite/innodb/r/instant_alter_index_rename.result b/mysql-test/suite/innodb/r/instant_alter_index_rename.result index 52051eff0bd..5281be7dd68 100644 --- a/mysql-test/suite/innodb/r/instant_alter_index_rename.result +++ b/mysql-test/suite/innodb/r/instant_alter_index_rename.result @@ -180,6 +180,8 @@ drop table rename_column_and_index; # MDEV-19189: ASAN memcpy-param-overlap in fill_alter_inplace_info upon adding indexes # CREATE TABLE t1 (f1 INT, f2 INT, f3 INT); +CREATE TABLE xx (f2 INT); ALTER TABLE t1 ADD FOREIGN KEY f (f2) REFERENCES xx(f2); ALTER TABLE t1 ADD FOREIGN KEY (f2) REFERENCES t1(f2), ADD KEY (f3), ADD KEY (f1); DROP TABLE t1; +DROP TABLE xx; diff --git a/mysql-test/suite/innodb/r/stored_fk.result b/mysql-test/suite/innodb/r/stored_fk.result index 35524d5a88f..953cce5b85f 100644 --- a/mysql-test/suite/innodb/r/stored_fk.result +++ b/mysql-test/suite/innodb/r/stored_fk.result @@ -1,7 +1,7 @@ # Create statement with FK on base column of stored column create table t1(f1 int, f2 int as(f1) stored, foreign key(f1) references t2(f1) on delete cascade)engine=innodb; -ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") +ERROR 42S02: Table 'test.t2' doesn't exist # adding new stored column during alter table copy operation. create table t1(f1 int primary key) engine=innodb; create table t2(f1 int not null, f2 int as (f1) virtual, diff --git a/mysql-test/suite/innodb/t/foreign_key.test b/mysql-test/suite/innodb/t/foreign_key.test index 06cab983656..a8511acb700 100644 --- a/mysql-test/suite/innodb/t/foreign_key.test +++ b/mysql-test/suite/innodb/t/foreign_key.test @@ -243,7 +243,7 @@ DROP TABLE t1; --echo # in ib_push_warning / dict_create_foreign_constraints_low --echo # CREATE TABLE t1 (a INT) ENGINE=InnoDB; ---error ER_CANT_CREATE_TABLE +--error ER_NO_SUCH_TABLE ALTER IGNORE TABLE t1 ADD FOREIGN KEY (a) REFERENCES t2 (b); SHOW WARNINGS; DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/innodb-fk-warnings.test b/mysql-test/suite/innodb/t/innodb-fk-warnings.test index 55284179692..990667b6ab3 100644 --- a/mysql-test/suite/innodb/t/innodb-fk-warnings.test +++ b/mysql-test/suite/innodb/t/innodb-fk-warnings.test @@ -58,7 +58,7 @@ drop table t2, t1; # create table t1 (f1 integer primary key) engine=innodb; ---error 1005 +--error ER_NO_SUCH_TABLE alter table t1 add constraint c1 foreign key (f1) references t11(f1); show warnings; drop table t1; diff --git a/mysql-test/suite/innodb/t/innodb-fk.test b/mysql-test/suite/innodb/t/innodb-fk.test index 8809dc55966..fc3f13d1c54 100644 --- a/mysql-test/suite/innodb/t/innodb-fk.test +++ b/mysql-test/suite/innodb/t/innodb-fk.test @@ -95,7 +95,7 @@ CREATE TABLE t1 ( CONSTRAINT fk1 FOREIGN KEY (f1) REFERENCES t1 (id) ON DELETE CASCADE ) ENGINE=InnoDB; ---error 1005 +--error ER_NO_SUCH_TABLE CREATE TABLE t2 ( id int(11) NOT NULL AUTO_INCREMENT, f2 int(11) NOT NULL, @@ -115,7 +115,7 @@ CREATE TABLE t2 ( CONSTRAINT fk2 FOREIGN KEY (f2) REFERENCES t1 (`id`) ON DELETE CASCADE ) ENGINE=InnoDB; ---error 1005 +--error ER_NO_SUCH_TABLE ALTER TABLE t2 ADD CONSTRAINT fk3 FOREIGN KEY (f3) REFERENCES t3 (id) ON DELETE CASCADE; show warnings; @@ -143,7 +143,7 @@ CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `group` (`Id`) show create table `kg_test1`.`person`; ---error 1005 +--error ER_NO_SUCH_TABLE CREATE TABLE `kg_test2`.`person2` ( `Id` INT(11) NOT NULL AUTO_INCREMENT, `Name` VARCHAR(50) NOT NULL, diff --git a/mysql-test/suite/innodb/t/instant_alter_index_rename.test b/mysql-test/suite/innodb/t/instant_alter_index_rename.test index 3a608a00837..38113ff230e 100644 --- a/mysql-test/suite/innodb/t/instant_alter_index_rename.test +++ b/mysql-test/suite/innodb/t/instant_alter_index_rename.test @@ -191,6 +191,8 @@ drop table rename_column_and_index; --echo # CREATE TABLE t1 (f1 INT, f2 INT, f3 INT); +CREATE TABLE xx (f2 INT); ALTER TABLE t1 ADD FOREIGN KEY f (f2) REFERENCES xx(f2); ALTER TABLE t1 ADD FOREIGN KEY (f2) REFERENCES t1(f2), ADD KEY (f3), ADD KEY (f1); DROP TABLE t1; +DROP TABLE xx; diff --git a/mysql-test/suite/innodb/t/stored_fk.test b/mysql-test/suite/innodb/t/stored_fk.test index b9c7c934555..2b711ed0efa 100644 --- a/mysql-test/suite/innodb/t/stored_fk.test +++ b/mysql-test/suite/innodb/t/stored_fk.test @@ -1,7 +1,7 @@ --source include/have_innodb.inc --echo # Create statement with FK on base column of stored column ---error ER_CANT_CREATE_TABLE +--error ER_NO_SUCH_TABLE create table t1(f1 int, f2 int as(f1) stored, foreign key(f1) references t2(f1) on delete cascade)engine=innodb; diff --git a/mysql-test/suite/period/r/fk.result b/mysql-test/suite/period/r/fk.result index 32d56df045a..a230eed9b10 100644 --- a/mysql-test/suite/period/r/fk.result +++ b/mysql-test/suite/period/r/fk.result @@ -150,5 +150,46 @@ foreign key(id, x, period no_such_p) references t(id, x, period p) on delete restrict); ERROR HY000: Period `no_such_p` is not found in table +create or replace table s (id int, x int, s date, e date, period for fp(s,e), +foreign key(id, x, period fp) +references t(id, x, period no_such_p) +on delete restrict); +ERROR HY000: Period `no_such_p` is not found in referenced table `test`.`t` +create or replace table t1 (id int, x int, s timestamp, e timestamp, period for p(s,e), +unique(id, x, p without overlaps)); +create or replace table s (id int, x int, s date, e date, period for fp(s,e), +foreign key(id, x, period fp) +references t1(id, x, period p) +on delete restrict); +ERROR HY000: Fields of `fp` and `test`.`t1`.`p` have different types +create or replace table s (id int, x int, s timestamp(6), e timestamp(6), +period for fp(s,e), +foreign key(id, x, period fp) +references t1(id, x, period p) +on delete restrict); +ERROR HY000: Fields of `fp` and `test`.`t1`.`p` have different types +create or replace table s (id int, x int, s date, e date); +alter table s add period for fp(s,e), +add foreign key(id, x, period fp) +references t1(id, x, period no_such_p) +on delete restrict; +ERROR HY000: Period `no_such_p` is not found in referenced table `test`.`t1` +alter table s add period for fp(s,e), +add foreign key(id, x, period fp) +references t1(id, x, period p) +on delete restrict; +ERROR HY000: Fields of `fp` and `test`.`t1`.`p` have different types +alter table s change s s timestamp(6), change e e timestamp(6), +add period for fp(s, e), +add foreign key(id, x, period fp) +references t1(id, x, period p) +on delete restrict; +ERROR HY000: Fields of `fp` and `test`.`t1`.`p` have different types +alter table s change s s timestamp(6), change e e timestamp(6), +add period for fp(s, e); +alter table s add foreign key(id, x, period fp) +references t1(id, x, period p) +on delete restrict; +ERROR HY000: Fields of `fp` and `test`.`t1`.`p` have different types drop database test; create database test; diff --git a/mysql-test/suite/period/t/fk.test b/mysql-test/suite/period/t/fk.test index 586de9c1840..7d9352494c9 100644 --- a/mysql-test/suite/period/t/fk.test +++ b/mysql-test/suite/period/t/fk.test @@ -134,5 +134,55 @@ create or replace table s (id int, x int, s date, e date, period for fp(s,e), references t(id, x, period p) on delete restrict); +--error ER_PERIOD_FK_NOT_FOUND +create or replace table s (id int, x int, s date, e date, period for fp(s,e), + foreign key(id, x, period fp) + references t(id, x, period no_such_p) + on delete restrict); + + +create or replace table t1 (id int, x int, s timestamp, e timestamp, period for p(s,e), + unique(id, x, p without overlaps)); + +--error ER_PERIOD_FK_TYPES_MISMATCH +create or replace table s (id int, x int, s date, e date, period for fp(s,e), + foreign key(id, x, period fp) + references t1(id, x, period p) + on delete restrict); + +--error ER_PERIOD_FK_TYPES_MISMATCH +create or replace table s (id int, x int, s timestamp(6), e timestamp(6), + period for fp(s,e), + foreign key(id, x, period fp) + references t1(id, x, period p) + on delete restrict); + +create or replace table s (id int, x int, s date, e date); +--error ER_PERIOD_FK_NOT_FOUND +alter table s add period for fp(s,e), + add foreign key(id, x, period fp) + references t1(id, x, period no_such_p) + on delete restrict; + +--error ER_PERIOD_FK_TYPES_MISMATCH +alter table s add period for fp(s,e), + add foreign key(id, x, period fp) + references t1(id, x, period p) + on delete restrict; + +--error ER_PERIOD_FK_TYPES_MISMATCH +alter table s change s s timestamp(6), change e e timestamp(6), + add period for fp(s, e), + add foreign key(id, x, period fp) + references t1(id, x, period p) + on delete restrict; + +alter table s change s s timestamp(6), change e e timestamp(6), + add period for fp(s, e); +--error ER_PERIOD_FK_TYPES_MISMATCH +alter table s add foreign key(id, x, period fp) + references t1(id, x, period p) + on delete restrict; + drop database test; create database test; diff --git a/mysql-test/suite/vcol/inc/vcol_keys.inc b/mysql-test/suite/vcol/inc/vcol_keys.inc index 8ec89daff0b..6a78bc7dd3b 100644 --- a/mysql-test/suite/vcol/inc/vcol_keys.inc +++ b/mysql-test/suite/vcol/inc/vcol_keys.inc @@ -103,31 +103,31 @@ if (!$skip_spatial_index_check) --echo # FOREIGN KEY --echo # Rejected FK options. ---error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN +--error ER_NO_SUCH_TABLE create table t1 (a int, b int as (a+1) persistent, foreign key (b) references t2(a) on update set null); ---error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN +--error ER_NO_SUCH_TABLE create table t1 (a int, b int as (a+1) persistent, foreign key (b) references t2(a) on update cascade); ---error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN +--error ER_NO_SUCH_TABLE create table t1 (a int, b int as (a+1) persistent, foreign key (b) references t2(a) on delete set null); create table t1 (a int, b int as (a+1) persistent); ---error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN +--error ER_NO_SUCH_TABLE alter table t1 add foreign key (b) references t2(a) on update set null; ---error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN +--error ER_NO_SUCH_TABLE alter table t1 add foreign key (b) references t2(a) on update cascade; ---error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN +--error ER_NO_SUCH_TABLE alter table t1 add foreign key (b) references t2(a) on delete set null; drop table t1; if ($with_foreign_keys) { ---error ER_CANT_CREATE_TABLE +--error ER_NO_SUCH_TABLE create table t1 (a int, b int as (a+1), foreign key (b) references t2(a)); create table t1 (a int, b int as (a+1)); ---error ER_CANT_CREATE_TABLE +--error ER_NO_SUCH_TABLE alter table t1 add foreign key (b) references t2(a); drop table t1; } diff --git a/mysql-test/suite/vcol/r/vcol_keys_innodb.result b/mysql-test/suite/vcol/r/vcol_keys_innodb.result index 23f9cfa0a0b..4b0d4a45c06 100644 --- a/mysql-test/suite/vcol/r/vcol_keys_innodb.result +++ b/mysql-test/suite/vcol/r/vcol_keys_innodb.result @@ -107,26 +107,26 @@ drop table t1; # Rejected FK options. create table t1 (a int, b int as (a+1) persistent, foreign key (b) references t2(a) on update set null); -ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column +ERROR 42S02: Table 'test.t2' doesn't exist create table t1 (a int, b int as (a+1) persistent, foreign key (b) references t2(a) on update cascade); -ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column +ERROR 42S02: Table 'test.t2' doesn't exist create table t1 (a int, b int as (a+1) persistent, foreign key (b) references t2(a) on delete set null); -ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column +ERROR 42S02: Table 'test.t2' doesn't exist create table t1 (a int, b int as (a+1) persistent); alter table t1 add foreign key (b) references t2(a) on update set null; -ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column +ERROR 42S02: Table 'test.t2' doesn't exist alter table t1 add foreign key (b) references t2(a) on update cascade; -ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column +ERROR 42S02: Table 'test.t2' doesn't exist alter table t1 add foreign key (b) references t2(a) on delete set null; -ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column +ERROR 42S02: Table 'test.t2' doesn't exist drop table t1; create table t1 (a int, b int as (a+1), foreign key (b) references t2(a)); -ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") +ERROR 42S02: Table 'test.t2' doesn't exist create table t1 (a int, b int as (a+1)); alter table t1 add foreign key (b) references t2(a); -ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") +ERROR 42S02: Table 'test.t2' doesn't exist drop table t1; # Allowed FK options. create table t2 (a int primary key, b char(5)); diff --git a/mysql-test/suite/vcol/r/vcol_keys_myisam.result b/mysql-test/suite/vcol/r/vcol_keys_myisam.result index 9400127211c..a55c54dc27f 100644 --- a/mysql-test/suite/vcol/r/vcol_keys_myisam.result +++ b/mysql-test/suite/vcol/r/vcol_keys_myisam.result @@ -107,20 +107,20 @@ drop table t1; # Rejected FK options. create table t1 (a int, b int as (a+1) persistent, foreign key (b) references t2(a) on update set null); -ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column +ERROR 42S02: Table 'test.t2' doesn't exist create table t1 (a int, b int as (a+1) persistent, foreign key (b) references t2(a) on update cascade); -ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column +ERROR 42S02: Table 'test.t2' doesn't exist create table t1 (a int, b int as (a+1) persistent, foreign key (b) references t2(a) on delete set null); -ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column +ERROR 42S02: Table 'test.t2' doesn't exist create table t1 (a int, b int as (a+1) persistent); alter table t1 add foreign key (b) references t2(a) on update set null; -ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column +ERROR 42S02: Table 'test.t2' doesn't exist alter table t1 add foreign key (b) references t2(a) on update cascade; -ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column +ERROR 42S02: Table 'test.t2' doesn't exist alter table t1 add foreign key (b) references t2(a) on delete set null; -ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column +ERROR 42S02: Table 'test.t2' doesn't exist drop table t1; # Allowed FK options. create table t2 (a int primary key, b char(5)); diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt index bb2f0fc5296..3ceb50b16f3 100644 --- a/sql/share/errmsg-utf8.txt +++ b/sql/share/errmsg-utf8.txt @@ -7949,3 +7949,7 @@ ER_PERIOD_WITHOUT_OVERLAPS_PARTITIONED eng "Period WITHOUT OVERLAPS is not implemented for partitioned tables" ER_PERIOD_WITHOUT_OVERLAPS_NON_UNIQUE eng "Period WITHOUT OVERLAPS is only allowed for unique keys" +ER_PERIOD_FK_TYPES_MISMATCH + eng "Fields of %`s and %`s.%`s.%`s have different types" +ER_PERIOD_FK_NOT_FOUND + eng "Period %`s is not found in referenced table %`s.%`s"
\ No newline at end of file diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 414184e254b..53dc3d33f4c 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -192,7 +192,9 @@ Foreign_key::Foreign_key(const Foreign_key &rhs, MEM_ROOT *mem_root) :Key(rhs,mem_root), ref_db(rhs.ref_db), ref_table(rhs.ref_table), + ref_table_list(rhs.ref_table_list), ref_columns(rhs.ref_columns,mem_root), + ref_period(rhs.ref_period), delete_opt(rhs.delete_opt), update_opt(rhs.update_opt), match_opt(rhs.match_opt) diff --git a/sql/sql_class.h b/sql/sql_class.h index 869a1848fb1..60b1056515e 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -397,19 +397,22 @@ public: FK_MATCH_PARTIAL, FK_MATCH_SIMPLE}; LEX_CSTRING ref_db; LEX_CSTRING ref_table; + TABLE_LIST *ref_table_list; List<Key_part_spec> ref_columns; Lex_ident ref_period; enum enum_fk_option delete_opt, update_opt; enum fk_match_opt match_opt; Foreign_key(const LEX_CSTRING *name_arg, List<Key_part_spec> *cols, - const LEX_CSTRING *ref_db_arg, const LEX_CSTRING *ref_table_arg, + const LEX_CSTRING ref_db_arg, const LEX_CSTRING ref_table_arg, + TABLE_LIST *ref_table, List<Key_part_spec> *ref_cols, Lex_ident ref_period, enum_fk_option delete_opt_arg, enum_fk_option update_opt_arg, fk_match_opt match_opt_arg, DDL_options ddl_options) :Key(FOREIGN_KEY, name_arg, &default_key_create_info, 0, cols, NULL, ddl_options), - ref_db(*ref_db_arg), ref_table(*ref_table_arg), ref_columns(*ref_cols), + ref_db(ref_db_arg), ref_table(ref_table_arg), + ref_table_list(ref_table), ref_columns(*ref_cols), ref_period(ref_period), delete_opt(delete_opt_arg), update_opt(update_opt_arg), match_opt(match_opt_arg) { diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index a7e6ecd3387..ed937640d76 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -11168,3 +11168,54 @@ sp_condition_value *LEX::stmt_signal_value(const Lex_ident_sys_st &ident) } return cond; } + + +int add_foreign_key_to_list(LEX *lex, LEX_CSTRING *name, + Table_ident *ref_table_name, + DDL_options ddl_options) +{ + THD *thd= lex->thd; + if (ref_table_name->db.str == NULL) + ref_table_name->db= lex->query_tables->db; + + if (ref_table_name->db.str == NULL) + lex->copy_db_to(&ref_table_name->db); + TABLE_LIST *table= find_table_in_list(lex->query_tables, + &TABLE_LIST::next_global, + &ref_table_name->db, + &ref_table_name->table); + + if (!(thd->variables.option_bits & OPTION_NO_FOREIGN_KEY_CHECKS)) + { + if (table == NULL) + table= lex->first_select_lex()->add_table_to_list(thd, + ref_table_name, + NULL, 0, TL_READ, + MDL_SHARED_READ); + if (unlikely(table == NULL)) + return 1; + } + Key *key= new (thd->mem_root) Foreign_key(name, + &lex->last_key->columns, + ref_table_name->db, + ref_table_name->table, + table, + &lex->ref_list, + lex->fk_ref_period, + lex->fk_delete_opt, + lex->fk_update_opt, + lex->fk_match_option, + ddl_options); + if (unlikely(key == NULL)) + return 1; + + key->period= lex->last_key->period; + /* + handle_if_exists_options() expectes the two keys in this order: + the Foreign_key, followed by its auto-generated Key. + */ + lex->alter_info.key_list.push_back(key, thd->mem_root); + lex->alter_info.key_list.push_back(lex->last_key, thd->mem_root); + + return 0; +} diff --git a/sql/sql_lex.h b/sql/sql_lex.h index b15c7c9e917..75b2a612427 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -4951,6 +4951,12 @@ bool sp_create_assignment_instr(THD *thd, bool no_lookahead, bool need_set_keyword= true); void mark_or_conds_to_avoid_pushdown(Item *cond); +TABLE_LIST *find_table_in_list(TABLE_LIST *table, + TABLE_LIST *TABLE_LIST::*link, + const LEX_CSTRING *db_name, + const LEX_CSTRING *table_name); +int add_foreign_key_to_list(LEX *lex, LEX_CSTRING *name, Table_ident *table_name, + DDL_options ddl_options); #endif /* MYSQL_SERVER */ #endif /* SQL_LEX_INCLUDED */ diff --git a/sql/sql_table.cc b/sql/sql_table.cc index c96a3273567..3c101b3e86c 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -3702,6 +3702,38 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info, ER_THD(thd, ER_KEY_REF_DO_NOT_MATCH_TABLE_REF)); DBUG_RETURN(TRUE); } + + if (fk_key->ref_period) + { + auto *ref_table= fk_key->ref_table_list->table->s; + if (!fk_key->ref_period.streq(ref_table->period.name)) + { + my_error(ER_PERIOD_FK_NOT_FOUND, MYF(0), fk_key->ref_period.str, + ref_table->db.str, ref_table->table_name.str); + } + + Create_field *period_start= NULL; + List_iterator_fast<Create_field> fit(alter_info->create_list); + while(auto *f= fit++) + { + if (create_info->period_info.period.start.streq(f->field_name)) + { + period_start= f; + break; + } + } + DBUG_ASSERT(period_start); + + auto *ref_period_start= ref_table->period.start_field(ref_table); + + if (ref_period_start->type_handler() != period_start->type_handler() + || ref_period_start->pack_length() != period_start->pack_length) + { + my_error(ER_PERIOD_FK_TYPES_MISMATCH, MYF(0), fk_key->period.str, + ref_table->db.str, ref_table->table_name.str, + ref_table->period.name.str); + } + } continue; } (*key_count)++; @@ -4579,6 +4611,9 @@ static bool append_system_key_parts(THD *thd, HA_CREATE_INFO *create_info, } else if (key->period) { + if (key->type == Key::FOREIGN_KEY) + continue; // there should be another key matching this foreign key. + if (!create_info->period_info.is_set() || !key->period.streq(create_info->period_info.name)) { diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index c05bd4e16e5..c340c5209a7 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -5776,30 +5776,13 @@ key_def: } '(' key_list_fk ')' references { - LEX *lex=Lex; - Key *key= (new (thd->mem_root) - Foreign_key($5.str ? &$5 : &$1, - &lex->last_key->columns, - &$10->db, - &$10->table, - &lex->ref_list, - lex->fk_ref_period, - lex->fk_delete_opt, - lex->fk_update_opt, - lex->fk_match_option, - $4)); - if (unlikely(key == NULL)) + auto *key_name= $5.str ? &$5 : &$1; + if (unlikely(add_foreign_key_to_list(Lex, key_name, $10, $4) != 0)) MYSQL_YYABORT; - /* - handle_if_exists_options() expectes the two keys in this order: - the Foreign_key, followed by its auto-generated Key. - */ - lex->alter_info.key_list.push_back(key, thd->mem_root); - lex->alter_info.key_list.push_back(Lex->last_key, thd->mem_root); - lex->option_list= NULL; + Lex->option_list= NULL; /* Only used for ALTER TABLE. Ignored otherwise. */ - lex->alter_info.flags|= ALTER_ADD_FOREIGN_KEY; + Lex->alter_info.flags|= ALTER_ADD_FOREIGN_KEY; } ; diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy index 2627c2ce77d..3d88f42e697 100644 --- a/sql/sql_yacc_ora.yy +++ b/sql/sql_yacc_ora.yy @@ -5775,30 +5775,13 @@ key_def: } '(' key_list_fk ')' references { - LEX *lex=Lex; - Key *key= (new (thd->mem_root) - Foreign_key($5.str ? &$5 : &$1, - &lex->last_key->columns, - &$10->db, - &$10->table, - &lex->ref_list, - lex->fk_ref_period, - lex->fk_delete_opt, - lex->fk_update_opt, - lex->fk_match_option, - $4)); - if (unlikely(key == NULL)) + auto *key_name= $5.str ? &$5 : &$1; + if (unlikely(add_foreign_key_to_list(Lex, key_name, $10, $4) != 0)) MYSQL_YYABORT; - /* - handle_if_exists_options() expectes the two keys in this order: - the Foreign_key, followed by its auto-generated Key. - */ - lex->alter_info.key_list.push_back(key, thd->mem_root); - lex->alter_info.key_list.push_back(Lex->last_key, thd->mem_root); - lex->option_list= NULL; + Lex->option_list= NULL; /* Only used for ALTER TABLE. Ignored otherwise. */ - lex->alter_info.flags|= ALTER_ADD_FOREIGN_KEY; + Lex->alter_info.flags|= ALTER_ADD_FOREIGN_KEY; } ; |