Bug #111965 default NULL column leads to foreign key constraint failure
Submitted: 4 Aug 2023 2:43 Modified: 4 Aug 2023 7:31
Reporter: Huaxiong Song (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:8.0.34, 5.7.43 OS:Any
Assigned to: CPU Architecture:Any

[4 Aug 2023 2:43] Huaxiong Song
Description:
If the table contains default NULL fields, it will break through the foreign key constraint

How to repeat:
Run mtr test case:

# SHOW foreign_key_checks
SHOW VARIABLES LIKE "foreign_key_checks";

# CREATE TABLE t1 and t2 with columns DEFAULT NULL.
CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `my_key` (`id`,`name`)
);

create table t2 (
  `id_c` int NOT NULL AUTO_INCREMENT,
  `name_c` varchar(50) DEFAULT NULL,
 FOREIGN KEY (`id_c`,`name_c`) REFERENCES t1(`id`,`name`)
);

# Insert full fields, error happens.
--error 1452
INSERT INTO t2 value(1, "test");

# Insert default fields, no error found.
INSERT INTO t2 values();

# Do select
SELECT * FROM t1;

SELECT * FROM t2;

# Cleanup
DROP TABLE t2;
DROP TABLE t1;

Suggested fix:
Improve the handling of NULL fields. Currently, there are some places where NULL is special, leading to logical errors
[4 Aug 2023 7:31] MySQL Verification Team
Hello Huaxiong Song,

Thank you for the report and steps.
Verified as described.

regards,
Umesh
[7 Aug 2023 12:33] huahua xu
It is not a issue. 

If any of the foreign key fields in entry is SQL NULL, the innodb suppress the foreign key check which is compatible with Oracle. Reference the document: https://dev.mysql.com/doc/refman/8.0/en/ansi-diff-foreign-keys.html