[MDEV-26783] System-versioned table writes historical rows on update without any changed data Created: 2021-10-07  Updated: 2023-09-08  Resolved: 2021-10-07

Status: Closed
Project: MariaDB Server
Component/s: Versioned Tables
Affects Version/s: 10.5.9
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Lucas Krause Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None

Issue Links:
Duplicate
is duplicated by MDEV-31944 UPDATE creates new row in system-vers... Closed
is duplicated by MDEV-32124 System-Versioned Tables, extra rows w... Closed
PartOf
is part of MDEV-26778 row_start is not updated in current r... Closed

 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.

 Comments   
Comment by Alice Sherepa [ 2021-10-07 ]

Thank you for reporting the issue!

The decision to insert the historical row even if the row has not changed was intentional (MDEV-23446 ), but start_time should be updated accordingly.
This is a part of the same bug as MDEV-26778, so I suggest following the progress in that Jira ticket if you'd like, and to close this one. I added the test case there.

I also tried with enum type, the bug was also reproducible:

MariaDB [test]> CREATE or replace TABLE t1 ( id enum('a','b') , ts datetime ON UPDATE current_timestamp()) WITH SYSTEM versioning;
 
Query OK, 0 rows affected (0.059 sec)
 
 
 
MariaDB [test]> INSERT INTO t1 VALUES ('a',now());
 
Query OK, 1 row affected (0.005 sec)
 
 
 
MariaDB [test]> UPDATE t1 SET id = 'a' WHERE id = 'a';
 
Query OK, 0 rows affected (0.004 sec)
 
Rows matched: 1  Changed: 0  Inserted: 1  Warnings: 0
 
 
 
MariaDB [test]> UPDATE t1 SET id = 'a' WHERE id = 'a';
 
Query OK, 0 rows affected (0.004 sec)
 
Rows matched: 1  Changed: 0  Inserted: 1  Warnings: 0
 
 
 
MariaDB [test]> SELECT *, ROW_START, ROW_END FROM t1 FOR SYSTEM_TIME ALL;
 
+------+---------------------+----------------------------+----------------------------+
 
| id   | ts                  | ROW_START                  | ROW_END                    |
 
+------+---------------------+----------------------------+----------------------------+
 
| a    | 2021-10-07 18:05:41 | 2021-10-07 18:05:41.476392 | 2038-01-19 04:14:07.999999 |
 
| a    | 2021-10-07 18:05:41 | 2021-10-07 18:05:41.476392 | 2021-10-07 18:05:52.143934 |
 
| a    | 2021-10-07 18:05:41 | 2021-10-07 18:05:41.476392 | 2021-10-07 18:06:25.812918 |
 
+------+---------------------+----------------------------+----------------------------+
 
3 rows in set (0.001 sec)

Generated at Thu Feb 08 09:47:55 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.