Description:
When inserting a JSON document through a writable JSON Duality View, a nested child object can be silently omitted if it conflicts with another child object on a secondary UNIQUE key.
The two child objects in the reproducer have distinct primary keys, but the same values for UNIQUE(customer_id, amount). The INSERT into the JSON Duality View returns success and SHOW WARNINGS returns no rows. The parent row is inserted, but only one child row is stored; the other child object is silently lost.
I reproduced this on MySQL Community Server 9.7.1 and 26.7.0 on Windows x64 with InnoDB. On both versions, the reproducer below stores only order_id=11.
As a control, inserting the conflicting rows directly into the base table is rejected with error 1062 (Duplicate entry). An analogous UPDATE through the JSON Duality View on 26.7.0 also rejects the unique-key conflict with error 1062.
Expected behavior:
The JSON Duality View INSERT should reject the document atomically with a duplicate-key error, rather than succeed after silently omitting one of the supplied child objects.
Actual behavior:
The INSERT succeeds without warnings, but one child object is missing from the base table and from the JSON document returned by the view.
How to repeat:
SELECT VERSION(), @@version_comment,
@@version_compile_os, @@version_compile_machine;
CREATE DATABASE mysql_jdv_unique_insert_repro_20261002;
USE mysql_jdv_unique_insert_repro_20261002;
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(30) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT NOT NULL,
amount DECIMAL(8,2) NOT NULL,
UNIQUE KEY uq_customer_amount (customer_id, amount),
FOREIGN KEY (customer_id) REFERENCES customers(id)
) ENGINE=InnoDB;
CREATE JSON DUALITY VIEW dv AS
SELECT JSON_DUALITY_OBJECT(WITH (INSERT, UPDATE, DELETE)
'_id': id,
'name': name,
'orders': (
SELECT JSON_ARRAYAGG(JSON_DUALITY_OBJECT(WITH (INSERT, UPDATE, DELETE)
'order_id': id,
'amount': amount
))
FROM orders
WHERE orders.customer_id = customers.id
)
)
FROM customers;
INSERT INTO dv VALUES
('{"_id":1,"name":"Alice","orders":[{"order_id":10,"amount":2.00},{"order_id":11,"amount":2.00}]}');
SHOW WARNINGS;
SELECT id, name FROM customers ORDER BY id;
SELECT id, customer_id, amount FROM orders ORDER BY id;
SELECT data FROM dv;
© 2026, Oracle Corporation and/or its affiliates
Description: When inserting a JSON document through a writable JSON Duality View, a nested child object can be silently omitted if it conflicts with another child object on a secondary UNIQUE key. The two child objects in the reproducer have distinct primary keys, but the same values for UNIQUE(customer_id, amount). The INSERT into the JSON Duality View returns success and SHOW WARNINGS returns no rows. The parent row is inserted, but only one child row is stored; the other child object is silently lost. I reproduced this on MySQL Community Server 9.7.1 and 26.7.0 on Windows x64 with InnoDB. On both versions, the reproducer below stores only order_id=11. As a control, inserting the conflicting rows directly into the base table is rejected with error 1062 (Duplicate entry). An analogous UPDATE through the JSON Duality View on 26.7.0 also rejects the unique-key conflict with error 1062. Expected behavior: The JSON Duality View INSERT should reject the document atomically with a duplicate-key error, rather than succeed after silently omitting one of the supplied child objects. Actual behavior: The INSERT succeeds without warnings, but one child object is missing from the base table and from the JSON document returned by the view. How to repeat: SELECT VERSION(), @@version_comment, @@version_compile_os, @@version_compile_machine; CREATE DATABASE mysql_jdv_unique_insert_repro_20261002; USE mysql_jdv_unique_insert_repro_20261002; CREATE TABLE customers ( id INT PRIMARY KEY, name VARCHAR(30) NOT NULL ) ENGINE=InnoDB; CREATE TABLE orders ( id INT PRIMARY KEY, customer_id INT NOT NULL, amount DECIMAL(8,2) NOT NULL, UNIQUE KEY uq_customer_amount (customer_id, amount), FOREIGN KEY (customer_id) REFERENCES customers(id) ) ENGINE=InnoDB; CREATE JSON DUALITY VIEW dv AS SELECT JSON_DUALITY_OBJECT(WITH (INSERT, UPDATE, DELETE) '_id': id, 'name': name, 'orders': ( SELECT JSON_ARRAYAGG(JSON_DUALITY_OBJECT(WITH (INSERT, UPDATE, DELETE) 'order_id': id, 'amount': amount )) FROM orders WHERE orders.customer_id = customers.id ) ) FROM customers; INSERT INTO dv VALUES ('{"_id":1,"name":"Alice","orders":[{"order_id":10,"amount":2.00},{"order_id":11,"amount":2.00}]}'); SHOW WARNINGS; SELECT id, name FROM customers ORDER BY id; SELECT id, customer_id, amount FROM orders ORDER BY id; SELECT data FROM dv;