Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11.19, 11.4.13, 11.8.9, 12.3.3, 13.0.2, 13.1.1
-
None
-
None
-
Official Docker images mariadb:10.11.19, 11.4.13, 11.8.9, 12.3.3, 13.0.2, 13.1.1, x86_64, default configuration
-
Unexpected results
Description
Through a join view, INSERT ... VALUES ... ON DUPLICATE KEY UPDATE fails with ERROR 1393 when the UPDATE part reads a column of the other base table. The INSERT ... SELECT form with the same ON DUPLICATE KEY UPDATE clause succeeds and sets f1 to NULL.
CREATE TABLE t1 (f1 INT UNIQUE, f2 INT); |
CREATE TABLE t2 (f3 INT, f4 INT); |
CREATE VIEW v1 AS SELECT * FROM t1, t2 WHERE f1 = f3; |
INSERT INTO t1 VALUES (1, 11); |
INSERT INTO t2 VALUES (1, 12); |
INSERT INTO v1 (f1) VALUES (1) ON DUPLICATE KEY UPDATE f1 = f3 + 10; -- ERROR 1393 (HY000): Can not modify more than one base table through a join view 'test.v1' |
INSERT INTO v1 (f1) SELECT 1 ON DUPLICATE KEY UPDATE f1 = f3 + 10; -- Query OK, 2 rows affected |
SELECT * FROM t1; -- (NULL, 11) |
Expected: the INSERT ... SELECT fails with ERROR 1393 like the INSERT ... VALUES, and t1 stays (1, 11).