[#MDEV-31944] UPDATE creates new row in system-versioned tables even if there is no value to change

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.

[MDEV-31944] UPDATE creates new row in system-versioned tables even if there is no value to change Created: 2023-08-18 Updated: 2023-09-08 Resolved: 2023-09-05
Status:	Closed
Project:	MariaDB Server
Component/s:	Versioned Tables
Affects Version/s:	10.6.15
Fix Version/s:	N/A

[MDEV-31944] UPDATE creates new row in system-versioned tables even if there is no value to change Created: 2023-08-18 Updated: 2023-09-08 Resolved: 2023-09-05