Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.6.15
-
None
Description
UPDATE shouldn't do anything if the values I'm setting are identical to current values. But this is not the case with temporal tables.
I create my system-versioned table:
CREATE OR REPLACE TABLE kv ( |
k VARCHAR(20) NOT NULL, |
v VARCHAR(20) NOT NULL, |
PRIMARY KEY (k) |
);
|
 |
ALTER TABLE kv |
ADD COLUMN valid_since TIMESTAMP(6) GENERATED ALWAYS AS ROW START INVISIBLE |
, ADD COLUMN valid_until TIMESTAMP(6) GENERATED ALWAYS AS ROW END INVISIBLE |
, ADD PERIOD FOR SYSTEM_TIME(valid_since, valid_until) |
, ADD SYSTEM VERSIONING |
;
|
I run the same INSERT ... ON DUPLICATE KEY UPDATE twice, and I'll have 2 fows.
INSERT INTO kv (k, v) VALUES ('x', '1') ON DUPLICATE KEY UPDATE v = '1'; |
INSERT INTO kv (k, v) VALUES ('x', '1') ON DUPLICATE KEY UPDATE v = '1'; |
SELECT *, valid_since, valid_until |
FROM kv FOR SYSTEM_TIME ALL; |
+---+---+----------------------------+----------------------------+ |
| k | v | valid_since | valid_until |
|
+---+---+----------------------------+----------------------------+ |
| x | 1 | 2023-08-18 01:35:12.193426 | 2023-08-18 01:35:13.245431 |
|
| x | 1 | 2023-08-18 01:35:13.245431 | 2038-01-19 03:14:07.999999 |
|
+---+---+----------------------------+----------------------------+ |
The same happens if I run an UPDATE:
UPDATE kv SET v = '1' WHERE k = 'x' LIMIT 1; |
SELECT *, valid_since, valid_until |
FROM kv FOR SYSTEM_TIME ALL; |
+---+---+----------------------------+----------------------------+ |
| k | v | valid_since | valid_until |
|
+---+---+----------------------------+----------------------------+ |
| x | 1 | 2023-08-18 01:35:12.193426 | 2023-08-18 01:35:13.245431 |
|
| x | 1 | 2023-08-18 01:35:13.245431 | 2023-08-18 01:41:57.249443 |
|
| x | 1 | 2023-08-18 01:41:57.249443 | 2038-01-19 03:14:07.999999 |
|
+---+---+----------------------------+----------------------------+ |
INSERT ... ON DUPLICATE KEY UPDATE is the logical way to implement a time-series-like workload. But the main advantage to do it in MariaDB with sysver tables is that storage is not supposed to grow until the values actually change, and avoid huge backups, frequent data rotation, etc. But this bug ruins the idea.
Attachments
Issue Links
- duplicates
-
-
- Closed
-
- is caused by
-
-
- Closed
-
- is duplicated by
-
-
- Closed
-