Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 13.1, 12.3.3
-
Linux x86_64, MariaDB.org Ubuntu 24.04 binary distribution
Description
With SIMULTANEOUS_ASSIGNMENT enabled, a no-op BEFORE UPDATE trigger changes the result of an INSERT ... ON DUPLICATE KEY UPDATE assignment. The triggered row ends as a=30,b=30, while the same duplicate-key update without the trigger and an ordinary UPDATE both produce a=20,b=30.
This occurs when INSERT ... ON DUPLICATE KEY UPDATE uses SIMULTANEOUS_ASSIGNMENT on a table with a BEFORE UPDATE trigger.
Reproducer
The following was reproduced on MariaDB 12.3.3. The database name must not already exist.
Save this as case.sql:
CREATE DATABASE assignment_test; |
USE assignment_test; |
SET SESSION sql_mode='STRICT_ALL_TABLES,SIMULTANEOUS_ASSIGNMENT'; |
|
|
CREATE TABLE plain_row ( |
id INT PRIMARY KEY, |
a INT NOT NULL, |
b INT NOT NULL |
) ENGINE=InnoDB;
|
CREATE TABLE triggered_row LIKE plain_row; |
|
|
CREATE TRIGGER before_update |
BEFORE UPDATE ON triggered_row |
FOR EACH ROW SET NEW.b=NEW.b; |
|
|
INSERT INTO plain_row VALUES (1,10,20); |
INSERT INTO triggered_row VALUES (1,10,20); |
|
|
INSERT INTO plain_row VALUES (1,99,99) |
ON DUPLICATE KEY UPDATE b=30,a=b; |
INSERT INTO triggered_row VALUES (1,99,99) |
ON DUPLICATE KEY UPDATE b=30,a=b; |
|
|
SELECT 'plain' AS path,a,b FROM plain_row; |
SELECT 'triggered' AS path,a,b FROM triggered_row; |
SELECT 'equivalent' AS oracle, |
p.a=t.a AND p.b=t.b AS passed |
FROM plain_row p |
JOIN triggered_row t USING(id); |
|
|
UPDATE triggered_row SET a=10,b=20; |
UPDATE triggered_row SET b=30,a=b; |
SELECT 'ordinary_update' AS path,a,b FROM triggered_row; |
|
|
DROP DATABASE assignment_test; |
Run it with a MariaDB client:
mariadb < case.sql |
Decisive observation
The recorded result was:
path a b
|
plain 20 30
|
path a b
|
triggered 30 30
|
oracle passed
|
equivalent 0
|
path a b
|
ordinary_update 20 30
|
Semantic mismatch
Under SIMULTANEOUS_ASSIGNMENT, the right-hand side of a=b uses the row value from before the assignment list. Starting from a=10,b=20, b=30,a=b therefore produces a=20,b=30. The plain duplicate-key path and the ordinary UPDATE both demonstrate that result.
The trigger only assigns NEW.b to itself, so it should not change those assignment semantics. The triggered duplicate-key path should also produce a=20,b=30, and equivalent should be 1. Instead, it evaluates a=b as 30 and stores a=30,b=30.