Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
N/A
-
None
Description
I don't really know what to declare the "expected behavior" here, other than throwing a dedicated error as further indicated in the text, but it seems to be a very remote nice-to-have for such an uncommon use case. I want it at least filed for posterity, as it took me long to even start understanding what is happening here, it will probably be very confusing for users (if anyone ever attempts it).
Please feel free to change the priority as you deem fit, or close it as not-a-bug or won't fix.
When INSERT .. ON DUPLICATE KEY UPDATE is attempted on historical records, it may behave in a way which is difficult to comprehend.
The test case uses a table with visible user-defined row start/end, to minimize the hidden logic. With default system versioning, it's basically the same, just more cryptic.
create or replace table t (pk int primary key, f int, s timestamp(6) as row start, e timestamp(6) as row end, period for system_time(s,e)) with system versioning; |
insert into t (pk,f) values (1,10); |
show keys in t; |
select * from t for system_time all; |
|
|
set system_versioning_insert_history= on; |
|
|
insert into t (pk,f,s,e) select pk,f,s,e from t for system_time all where f=10 on duplicate key update f=20; |
select * from t for system_time all; |
insert into t (pk,f,s,e) select pk,f,s,e from t for system_time all where f=10 on duplicate key update f=30; |
delete from t; |
select * from t for system_time all; |
insert into t (pk,f,s,e) select pk,f,s,e from t for system_time all where f=10 on duplicate key update f=40; |
select * from t for system_time all; |
As usual, the table is created with PK (pk,e)
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ |
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ |
| t | 0 | PRIMARY | 1 | pk | A | 1 | NULL | NULL | | BTREE | | | NO | |
| t | 0 | PRIMARY | 2 | e | A | 1 | NULL | NULL | | BTREE | | | NO | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ |
After the initial INSERT, it contains
MariaDB [test]> select * from t for system_time all; |
+----+------+----------------------------+----------------------------+ |
| pk | f | s | e |
|
+----+------+----------------------------+----------------------------+ |
| 1 | 10 | 2022-10-17 16:02:48.537463 | 2038-01-19 05:14:07.999999 |
|
+----+------+----------------------------+----------------------------+ |
When we do ODKU for the first time, we are in fact trying to insert a new current record (1,10,2022-..,2038-..) which of course violates the PK, so instead it updates f on that record. The old row becomes a history, the new record becomes current
+----+------+----------------------------+----------------------------+ |
| pk | f | s | e |
|
+----+------+----------------------------+----------------------------+ |
| 1 | 10 | 2022-10-17 16:04:46.966665 | 2022-10-17 16:04:46.979929 |
|
| 1 | 20 | 2022-10-17 16:04:46.979929 | 2038-01-19 05:14:07.999999 |
|
+----+------+----------------------------+----------------------------+ |
Fine.
But then, the next ODKU in fact tries to insert a historical record (1,10,2022-...,2022-), which again violates the PK.
What should happen next is unclear. Normally, ODKU should update the existing (1,10...) record with the new f=30; with versioning, it should probably also create a new historical record for (1,10...) indicating the change.
But updating the historical record is forbidden (or at least it's supposed to be), and a new historical record (1,30,...) would have an overlap with (1,10,...) with any reasonable values of (s,e).
Ideally, it should just throw an error at this point "You can't do it", but that's the remote nice-to-have as mentioned before.
What it seemingly shouldn't do is try to create a new current record, that's not what ODKU asked at all. But that's exactly what it does.
So, the second ODKU fails with
|
bb-10.11-MDEV-16546 32090722 |
ERROR 1062 (23000): Duplicate entry '1-2038-01-19 05:14:07.999999' for key 'PRIMARY' |
After we realize what it means and remove the existing current record (1,20), we have
MariaDB [test]> delete from t; |
Query OK, 1 row affected (0.007 sec)
|
|
|
MariaDB [test]> select * from t for system_time all; |
+----+------+----------------------------+----------------------------+ |
| pk | f | s | e |
|
+----+------+----------------------------+----------------------------+ |
| 1 | 10 | 2022-10-17 16:36:21.777172 | 2022-10-17 16:36:21.790093 |
|
| 1 | 20 | 2022-10-17 16:36:21.790093 | 2022-10-17 16:36:21.801080 |
|
+----+------+----------------------------+----------------------------+ |
2 rows in set (0.001 sec) |
And the next ODKU succeeds and inserts a new current record (1,40) which it wasn't asked for, and updates an existing historical record (1,10), which it should not do:
MariaDB [test]> insert into t (pk,f,s,e) select pk,f,s,e from t for system_time all where f=10 on duplicate key update f=40; |
Query OK, 3 rows affected (0.006 sec) |
Records: 1 Duplicates: 1 Warnings: 0
|
|
|
MariaDB [test]> select * from t for system_time all; |
+----+------+----------------------------+----------------------------+ |
| pk | f | s | e |
|
+----+------+----------------------------+----------------------------+ |
| 1 | 20 | 2022-10-17 16:36:21.790093 | 2022-10-17 16:36:21.801080 |
|
| 1 | 10 | 2022-10-17 16:36:21.777172 | 2022-10-17 16:36:21.811909 |
|
| 1 | 40 | 2022-10-17 16:36:21.811909 | 2038-01-19 05:14:07.999999 |
|
+----+------+----------------------------+----------------------------+ |
3 rows in set (0.001 sec) |
Attachments
Issue Links
- is caused by
-
MDEV-16546 System versioning setting to allow history modification
-
- Closed
-