[MDEV-16937] Strict SQL with system versioned tables causes issues Created: 2018-08-10  Updated: 2020-05-29  Resolved: 2020-05-28

Status: Closed
Project: MariaDB Server
Component/s: Versioned Tables
Affects Version/s: 10.3.8
Fix Version/s: 10.5.4, 10.3.24, 10.4.14

Type: Bug Priority: Critical
Reporter: Vincent Milum Jr Assignee: Aleksey Midenkov
Resolution: Fixed Votes: 2
Labels: None
Environment:

FreeBSD 11.1-STABLE #0 r321665+e0c4ca60dfc(freenas/11.1-stable)

Issue Links:
Relates
relates to MDEV-17573 Assertion in federatedx on multi-update Closed
relates to MDEV-19597 Refactor TABLE::vers_update_fields() ... Stalled
relates to MDEV-19304 Segfault in ALTER TABLE after UPDATE ... Closed

 Description   

Strict SQL mode conflicts with System Versioned tables (only with some queries)

Example 1: cannot insert into a table if column names are specified, but okay if they're not

MariaDB [test]> CREATE TABLE broke (x int) WITH SYSTEM VERSIONING;
 
Query OK, 0 rows affected (0.005 sec)
 
 
 
MariaDB [test]> INSERT INTO broke VALUES (1);
 
Query OK, 1 row affected (0.002 sec)
 
 
 
MariaDB [test]> INSERT INTO broke (x) VALUES (2);
 
Query OK, 1 row affected (0.001 sec)
 
 
 
MariaDB [test]> SET @@SQL_MODE = CONCAT(@@SQL_MODE, ',TRADITIONAL');
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> INSERT INTO broke VALUES (3);
 
Query OK, 1 row affected (0.001 sec)
 
 
 
MariaDB [test]> INSERT INTO broke (x) VALUES (4);
 
ERROR 1978 (22007): Incorrect default value '0000-00-00 00:00:00.000000' for column 'row_start'
 
 
 
MariaDB [test]> SELECT * FROM broke;
 
+------+
 
| x    |
 
+------+
 
|    1 |
 
|    2 |
 
|    3 |
 
+------+
 
3 rows in set (0.000 sec)

Example 2: Cannot even create a system versioned table

MariaDB [test]> SET @@SQL_MODE = CONCAT(@@SQL_MODE, ',TRADITIONAL');
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> CREATE TABLE broke (x int) WITH SYSTEM VERSIONING;
 
ERROR 1067 (42000): Invalid default value for 'row_start'



 Comments   
Comment by Elena Stepanova [ 2018-08-10 ]

Thanks for the report.
NO_ZERO_DATE causes the trouble here (it is a part of the TRADITIONAL collection).

Comment by Aleksey Midenkov [ 2018-10-29 ]

`federatedx` still have issues. Investigating...

Comment by Aleksey Midenkov [ 2018-10-30 ]

federatedx bug is reproducible without System Versioning (MDEV-17573).

Comment by Luke Cousins [ 2019-10-28 ]

Is there any progress on this issue?

Comment by Vincent Milum Jr [ 2020-05-07 ]

This bug just hit me again at another job. Nearly 2 years later, any progress?

This isn't just about inserting data, either. ALTER TABLE `tbl` ADD SYSTEM VERSIONING also doesn't work, which is what I'm trying to do now for an ORM.

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