On 03/02/18 13:42, Oliver Schonrock wrote:
How to reproduce: (example reduced to the bare essentials with 3 tables with 1 record each).
I have managed to simplify the test case further, by eliminating the "circular" ON DELETE SET NULL Contraints and their fields. Now it's a simple: parent => child1 => child2 with ON DELETE SET NULL FK to child1 to complete a "triangle" New test case SQL and results: -- Server version: 10.2.12-MariaDB FreeBSD Ports SET FOREIGN_KEY_CHECKS=0; CREATE TABLE member ( id int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) ENGINE=InnoDB; INSERT INTO member VALUES (1); CREATE TABLE address ( id int(11) NOT NULL AUTO_INCREMENT, member_id int(11) NOT NULL, PRIMARY KEY (id), KEY address_FI_1 (member_id), CONSTRAINT address_FK_1 FOREIGN KEY (member_id) REFERENCES member (id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB; INSERT INTO address VALUES (2,1); CREATE TABLE payment_method ( id int(11) NOT NULL AUTO_INCREMENT, member_id int(11) NOT NULL, cardholder_address_id int(11) DEFAULT NULL, PRIMARY KEY (id), KEY payment_method_FI_1 (member_id), KEY payment_method_FI_2 (cardholder_address_id), CONSTRAINT payment_method_FK_1 FOREIGN KEY (member_id) REFERENCES member (id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT payment_method_FK_2 FOREIGN KEY (cardholder_address_id) REFERENCES address (id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB; INSERT INTO payment_method VALUES (3,1,2); SET FOREIGN_KEY_CHECKS=1; SELECT * FROM member; +----+ | id | +----+ | 1 | +----+ SELECT * FROM address; +----+-----------+ | id | member_id | +----+-----------+ | 2 | 1 | +----+-----------+ SELECT * FROM payment_method; +----+-----------+-----------------------+ | id | member_id | cardholder_address_id | +----+-----------+-----------------------+ | 3 | 1 | 2 | +----+-----------+-----------------------+ DELETE FROM member WHERE id = 1; SELECT * FROM member; -- empty set, correct SELECT * FROM address; -- empty set, correct SELECT * FROM payment_method; +----+-----------+-----------------------+ | id | member_id | cardholder_address_id | +----+-----------+-----------------------+ | 3 | 1 | NULL | +----+-----------+-----------------------+ -- should be an empty set -- inconsistency continues during subsequent left join queries SELECT payment_method.id, member_id FROM payment_method LEFT JOIN member ON member.id=payment_method.member_id WHERE payment_method.member_id IS NOT NULL AND member.id IS NULL; -- empty set, when it should not be -- Oliver Schönrock