[MDEV-29721] Inconsistency upon inserting history with visible system versioning columns Created: 2022-10-06  Updated: 2022-10-26  Resolved: 2022-10-26

Status: Closed
Project: MariaDB Server
Component/s: Versioned Tables
Affects Version/s: N/A
Fix Version/s: 10.11.1

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
PartOf
includes MDEV-29722 History modification requires specify... Closed
includes MDEV-29738 REPLACE under system_versioning_inser... Closed
Problem/Incident
is caused by MDEV-16546 System versioning setting to allow hi... Closed
Relates

 Description   

Filing as 10.11v1- critical as previously discussed, to make it show up on the list for consideration. Please feel free to downgrade or close as "won't fix".

For a table with visible versioning columns, setting system_versioning_insert_history variable changes the behavior of inserting default values (restricts it).

create or replace table t (a int auto_increment primary key, s timestamp(6) as row start, e timestamp(6) as row end, period for system_time (s,e)) with system versioning;
 
set system_versioning_insert_history=off;
 
insert into t () values (),();
 
insert into t (a) values (3),(4);
 
set system_versioning_insert_history=on;
 
insert into t (a) values (5),(6);
 
insert into t () values (),();

Both INSERTs with system_versioning_insert_history=off work, with a values inserted according to the auto-increment or explicit values, s to the current time, and e to the max timestamp.
The third INSERT, first with system_versioning_insert_history=on also works, the same way.
But the last INSERT fails:

bb-10.11-MDEV-16546 2b1d32429

 
query 'insert into t () values (),()' failed: ER_WRONG_VALUE (1525): Incorrect s value: '0000-00-00 00:00:00.000000'

There seems to be no good reason for this.

 Comments   
Comment by Elena Stepanova [ 2022-10-16 ]

With the fix 623159a6, the following (still?) does not work:

create table t (a int, s timestamp(6) as row start, e timestamp(6) as row end, period for system_time(s,e)) with system versioning;
 
set system_versioning_insert_history=on;
 
insert into t (a,s,e) values (1,default,default);

bb-10.11-MDEV-16546 32090722c

 
ERROR 1525 (HY000): Incorrect s value: '0000-00-00 00:00:00.000000'

With system_versioning_insert_history=off it works:

MariaDB [test]> set system_versioning_insert_history=off;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> insert into t (a,s,e) values (1,default,default);
 
Query OK, 1 row affected (0.015 sec)
 
 
 
MariaDB [test]> select * from t;
 
+------+----------------------------+----------------------------+
 
| a    | s                          | e                          |
 
+------+----------------------------+----------------------------+
 
|    1 | 2022-10-17 01:22:04.887098 | 2038-01-19 05:14:07.999999 |
 
+------+----------------------------+----------------------------+
 
1 row in set (0.001 sec)

Again, there seems to be no logical reason why it shouldn't work with the variable set to ON.

I think we should generally avoid (whenever possible) changes which make previously working statements fail, unless it's the purpose of the change. It makes various OM=>NS, rolling galera upgrade and such scenarios even more problematic than they already are.

Comment by Sergei Golubchik [ 2022-10-17 ]

made default and ignore work as expected, that is as if the column wasn't specified in the list at all

Comment by Elena Stepanova [ 2022-10-17 ]

Yet another thing (sorry they are not coming up all at once):

create or replace table t (a int, s timestamp(6) as row start, e timestamp(6) as row end, period for system_time(s,e)) with system versioning;
 
set system_versioning_insert_history=on;
 
insert into t (a,s) values (1,null);
 
insert into t (a,e) values (2,null);
 
set system_versioning_insert_history=off;
 
insert into t (a,s) values (3,null);
 
insert into t (a,e) values (4,null);

bb-10.11-MDEV-16546 2f5bd1bfa

 
MariaDB [test]> set system_versioning_insert_history=on;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> insert into t (a,s) values (1,null);
 
Query OK, 1 row affected (0.017 sec)
 
 
 
MariaDB [test]> insert into t (a,e) values (2,null);
 
ERROR 1525 (HY000): Incorrect s value: '2022-10-18 02:04:40.156877'


MariaDB [test]> set system_versioning_insert_history=off;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> insert into t (a,s) values (3,null);
 
Query OK, 1 row affected (0.013 sec)
 
 
 
MariaDB [test]> insert into t (a,e) values (4,null);
 
Query OK, 1 row affected (0.013 sec)

So, with system_versioning_insert_history=on NULL can be inserted into ROW START, but not into ROW END. With off, both work.

Comment by Sergei Golubchik [ 2022-10-18 ]

it's a standard behavior of all TIMESTAMP fields, on NULL they store the CURRENT_TIMESTAMP. Incidentally, this is what ROW START column would've got anyway. But you cannot have ROW END timestamp equal to the ROW START timestamp.

Generated at Thu Feb 08 10:10:47 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.