Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
N/A
-
None
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.
Attachments
Issue Links
- includes
-
-
- Closed
-
-
-
- Closed
-
- is caused by
-
MDEV-16546 System versioning setting to allow history modification
-
- Closed
-
Activity
made default and ignore work as expected, that is as if the column wasn't specified in the list at all
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.
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.
With the fix 623159a6, the following (still?) does not work:
bb-10.11-MDEV-16546 32090722c
With system_versioning_insert_history=off it works:
Query OK, 1 row affected (0.015 sec)| a | s | e || 1 | 2022-10-17 01:22:04.887098 | 2038-01-19 05:14:07.999999 |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.