Description:
A JSON Duality View document update that removes a nested parent group and reassigns its
children to another group is legitimate document semantics and is directly achievable with plain
SQL, but the JDV orchestration rejects it with a foreign-key error.
Model: course -> hgroup -> hole, with hole.hgroup_id referencing hgroup.id.
Initial document: G1(hole1, hole2), G2(hole3).
Requested update: dissolve G1 and move its holes into G2.
The same final state is reachable by hand (see "How to repeat"), so the failure is caused by the
orchestration order, not by the relational constraints.
Verified on trunk 26.7.0-debug @ 23e360a7fac and on the mysql:9.7.2 official image (both fail),
so this is not a trunk-only regression.
How to repeat:
DROP TABLE IF EXISTS hole, hgroup, course;
CREATE TABLE course (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL);
CREATE TABLE hgroup (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL,
course_id INT UNSIGNED, KEY ix(course_id),
CONSTRAINT fk1 FOREIGN KEY (course_id) REFERENCES course(id));
CREATE TABLE hole (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, num INT NOT NULL,
hgroup_id INT UNSIGNED, KEY ix(hgroup_id),
CONSTRAINT fk2 FOREIGN KEY (hgroup_id) REFERENCES hgroup(id));
CREATE JSON RELATIONAL DUALITY VIEW cdv AS
SELECT JSON_DUALITY_OBJECT(
WITH (INSERT,UPDATE,DELETE)
'_id': course.id, 'name': course.name,
'groups': (SELECT JSON_ARRAYAGG(JSON_DUALITY_OBJECT(
WITH (INSERT,UPDATE,DELETE)
'id': hgroup.id, 'name': hgroup.name,
'holes': (SELECT JSON_ARRAYAGG(JSON_DUALITY_OBJECT(
WITH (INSERT,UPDATE,DELETE) 'id': hole.id, 'num': hole.num))
FROM hole WHERE hole.hgroup_id = hgroup.id)))
FROM hgroup WHERE hgroup.course_id = course.id)) AS data
FROM course;
INSERT INTO cdv VALUES (
'{"_id":1,"name":"C1","groups":[{"id":1,"name":"G1","holes":[{"id":1,"num":1},{"id":2,"num":2}]},
{"id":2,"name":"G2","holes":[{"id":3,"num":3}]}]}');
-- dissolve G1, move its holes into G2
UPDATE cdv SET data = '{"_id":1,"name":"C1","groups":[{"id":2,"name":"G2","holes":[{"id":3,"num":3},{"id":1,"num":1},{"id":2,"num":2}]}]}'
WHERE JSON_EXTRACT(data, '$._id') = 1;
-- ACTUAL: ERROR 1451 (23000): Cannot delete or update a parent row:
-- a foreign key constraint fails (`hole`, CONSTRAINT `fk2` ...)
-- EXPECTED: success; final state hgroup=[2], hole=[1@2, 2@2, 3@2]
-- Feasibility proof: the identical final state is reachable by hand
UPDATE hole SET hgroup_id = 2 WHERE hgroup_id = 1; -- re-parent children first
DELETE FROM hgroup WHERE id = 1; -- then delete the parent => succeeds
-- Variants (all fail with 1451): reverse direction (delete G2, move hole3 into G1);
-- swapped document array order; partial re-assignment.
-- Control: moving a child without deleting its source group succeeds.
Suggested fix:
The orchestration introduced by e6183490d33 (Bug#39566804) performs deletes first and in
reverse order, before inserts and updates. That fixed the original "insert violates FK" problem but
left the opposite boundary unhandled: the child's UPDATE (re-parenting) is ordered after the
parent's DELETE, so a legal "re-parent then delete" document update is rejected by FK RESTRICT.
A child's reference change must be applied before its parent row is deleted, or the parent delete
must be deferred until after re-parenting.
Description: A JSON Duality View document update that removes a nested parent group and reassigns its children to another group is legitimate document semantics and is directly achievable with plain SQL, but the JDV orchestration rejects it with a foreign-key error. Model: course -> hgroup -> hole, with hole.hgroup_id referencing hgroup.id. Initial document: G1(hole1, hole2), G2(hole3). Requested update: dissolve G1 and move its holes into G2. The same final state is reachable by hand (see "How to repeat"), so the failure is caused by the orchestration order, not by the relational constraints. Verified on trunk 26.7.0-debug @ 23e360a7fac and on the mysql:9.7.2 official image (both fail), so this is not a trunk-only regression. How to repeat: DROP TABLE IF EXISTS hole, hgroup, course; CREATE TABLE course (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL); CREATE TABLE hgroup (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL, course_id INT UNSIGNED, KEY ix(course_id), CONSTRAINT fk1 FOREIGN KEY (course_id) REFERENCES course(id)); CREATE TABLE hole (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, num INT NOT NULL, hgroup_id INT UNSIGNED, KEY ix(hgroup_id), CONSTRAINT fk2 FOREIGN KEY (hgroup_id) REFERENCES hgroup(id)); CREATE JSON RELATIONAL DUALITY VIEW cdv AS SELECT JSON_DUALITY_OBJECT( WITH (INSERT,UPDATE,DELETE) '_id': course.id, 'name': course.name, 'groups': (SELECT JSON_ARRAYAGG(JSON_DUALITY_OBJECT( WITH (INSERT,UPDATE,DELETE) 'id': hgroup.id, 'name': hgroup.name, 'holes': (SELECT JSON_ARRAYAGG(JSON_DUALITY_OBJECT( WITH (INSERT,UPDATE,DELETE) 'id': hole.id, 'num': hole.num)) FROM hole WHERE hole.hgroup_id = hgroup.id))) FROM hgroup WHERE hgroup.course_id = course.id)) AS data FROM course; INSERT INTO cdv VALUES ( '{"_id":1,"name":"C1","groups":[{"id":1,"name":"G1","holes":[{"id":1,"num":1},{"id":2,"num":2}]}, {"id":2,"name":"G2","holes":[{"id":3,"num":3}]}]}'); -- dissolve G1, move its holes into G2 UPDATE cdv SET data = '{"_id":1,"name":"C1","groups":[{"id":2,"name":"G2","holes":[{"id":3,"num":3},{"id":1,"num":1},{"id":2,"num":2}]}]}' WHERE JSON_EXTRACT(data, '$._id') = 1; -- ACTUAL: ERROR 1451 (23000): Cannot delete or update a parent row: -- a foreign key constraint fails (`hole`, CONSTRAINT `fk2` ...) -- EXPECTED: success; final state hgroup=[2], hole=[1@2, 2@2, 3@2] -- Feasibility proof: the identical final state is reachable by hand UPDATE hole SET hgroup_id = 2 WHERE hgroup_id = 1; -- re-parent children first DELETE FROM hgroup WHERE id = 1; -- then delete the parent => succeeds -- Variants (all fail with 1451): reverse direction (delete G2, move hole3 into G1); -- swapped document array order; partial re-assignment. -- Control: moving a child without deleting its source group succeeds. Suggested fix: The orchestration introduced by e6183490d33 (Bug#39566804) performs deletes first and in reverse order, before inserts and updates. That fixed the original "insert violates FK" problem but left the opposite boundary unhandled: the child's UPDATE (re-parenting) is ordered after the parent's DELETE, so a legal "re-parent then delete" document update is rejected by FK RESTRICT. A child's reference change must be applied before its parent row is deleted, or the parent delete must be deferred until after re-parenting.