Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Duplicate
-
10.5.9
-
None
Description
When updating a row in a system-versioned table and updating a column's value with the exact current value, a new historical row is written, that has the same ROW_START value as well as the same value for any other column as the previous one, while a ON UPDATE CURRENT_TIMESTAMP() column is not updated on the current row.
I also experienced that issue to not exist when updating a ENUM column with the exact current value, while existing when updating a INT column with the exact current value. However, in the example below the columns all behave the same.
CREATE TABLE Foobar ( |
id INT(11) NOT NULL, |
a INT(11) NOT NULL, |
b ENUM('foo', 'bar') NOT NULL, |
c VARCHAR(255) NOT NULL, |
ts DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP(), |
PRIMARY KEY (id) |
) WITH SYSTEM VERSIONING; |
INSERT INTO Foobar (id, a, b, c) VALUES (1, 1, 'foo', 'hello'); |
-- 1 row
|
SELECT *, ROW_START, ROW_END FROM Foobar FOR SYSTEM_TIME ALL WHERE id = 1; |
UPDATE Foobar SET b = 'foo' WHERE id = 1; |
-- 2 rows with the exact same values except for ROW_END
|
SELECT *, ROW_START, ROW_END FROM Foobar FOR SYSTEM_TIME ALL WHERE id = 1; |
UPDATE Foobar SET a = 1 WHERE id = 1; |
-- 3 rows with the exact same values except for ROW_END
|
SELECT *, ROW_START, ROW_END FROM Foobar FOR SYSTEM_TIME ALL WHERE id = 1; |
SELECT *, ROW_START, ROW_END FROM Foobar FOR SYSTEM_TIME AS OF '<row_start_value>' WHERE id = 1 will also return 3 rows, while I would expect only 1.
I would expect that a historical row is only written when data in the row really change, just like a ON UPDATE column behaves.
Attachments
Issue Links
- is duplicated by
-
MDEV-31944 UPDATE creates new row in system-versioned tables even if there is no value to change
- Closed
-
MDEV-32124 System-Versioned Tables, extra rows with UPDATE
- Closed
- is part of
-
MDEV-26778 row_start is not updated in current row for InnoDB
- Closed