|
While experimenting with temporal tables, I encountered what appears to be a bug with updating fields without system versioning in otherwise temporal tables.
The bug
When using `UPDATE` to only update a field without system versioning, everything works as expected (i.e. not creating a new row), except when using `ON DUPLICATE KEY UPDATE`. By doing so, a new row gets created, even though only a non-versioned field gets updated. Additionally and even worse, the row_start does not get set properly but instead is copied from the most recent row. On the other hand, when using `ON DUPLICATE KEY UPDATE` to update the value of a system versioned field, a new row gets created with the proper timestamp for the row_start, as expected.
Example
MariaDB [test]> create table test_table (id int unsigned not null primary key, n int unsigned not null without system versioning) with system versioning;
|
Query OK, 0 rows affected (0,027 sec)
|
|
MariaDB [test]> insert into test_table (id, n) values (1, 1) on duplicate key update n=2;
|
Query OK, 1 row affected (0,007 sec)
|
|
MariaDB [test]> insert into test_table (id, n) values (1, 1) on duplicate key update n=2;
|
Query OK, 3 rows affected (0,005 sec)
|
|
MariaDB [test]> select *, row_start, row_end from test_table for system_time all;
|
+----+---+----------------------------+----------------------------+
|
| id | n | row_start | row_end |
|
+----+---+----------------------------+----------------------------+
|
| 1 | 1 | 2023-02-21 12:17:21.303652 | 2023-02-21 12:17:23.935926 |
|
| 1 | 2 | 2023-02-21 12:17:21.303652 | 2038-01-19 04:14:07.999999 |
|
+----+---+----------------------------+----------------------------+
|
2 rows in set (0,000 sec)
|
Misc
This issue may well be related to MDEV-29723 and/or MDEV-25644.
|