After upgrading to MariaDB 10.6.4, we noticed an issue with system versioned tables where it inserts a new row for update commands that don't change anything. Previous versions such as 10.5.5 would ignore such dummy updates and not change the SVT history. Small test case to illustrate:
- CREATE TABLE t(
start_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
end_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp)
) WITH SYSTEM VERSIONING;
- "INSERT INTO t( x ) VALUES (1);"
- "UPDATE t SET x = 1 WHERE x = 1;" # no change, dummy update
With MariaDB 10.5.5, the above prints:
Rows matched: 1 Changed: 0 Inserted: 0 Warnings: 0
With MariaDB 10.6.4:
Rows matched: 1 Changed: 0 Inserted: 1 Warnings: 0
- "SELECT * FROM t for SYSTEM_TIME between (NOW() - INTERVAL 1 YEAR) AND NOW();"
The above will print 2 records (with same start_time but different end_time) for MariaDB 10.6.4 and only 1 record for MariaDB 10.5.5
Looks like the 10.6 version stores dummy updates which don't make any real change to the data. While that's okay, shouldn't the start time stamp also be updated for the new row? The current behavior is problematic because queries such as "SELECT * FROM t FOR SYSTEM_TIME AS OF ..." will print 2 records for certain timestamps which doesn't make sense.