Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.9.5, 10.10.3
-
mariadb Ver 15.1 Distrib 10.10.3-MariaDB, for Linux (x86_64) Server+Client running on Manjaro Linux Sikaris 22.0.4 with Kernel 5.15.94-1-MANJARO
Description
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.
Attachments
Issue Links
- relates to
-
MDEV-16226 TRX_ID-based System Versioning refactoring
- Stalled
-
MDEV-25644 UPDATE not working properly on transaction precise system versioned table
- Closed
-
MDEV-29723 ROW START not always set correctly in transaction-precise tables
- Open