maria-developers team mailing list archive

[Oliver Schonrock <oliver@xxxxxxxxxxxxxx>]

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

Attachment: signature.asc
Description: OpenPGP digital signature