[MDEV-29833] CREATE ... SELECT system_versioned_table causes invalid defaults Created: 2022-10-20  Updated: 2022-10-26  Resolved: 2022-10-26

Status: Closed
Project: MariaDB Server
Component/s: Versioned Tables
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.11.1

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

Issue Links:
Blocks
blocks MDEV-16546 System versioning setting to allow hi... Closed

 Description   

Fields AS ROW START and AS ROW END appear to have a default value of 0.
It matters when one creates a new table out of system versioned table using
CREATE ... SELECT. But the default value avoids validation, these fields are created with zero defaults even if sql_mode=NO_ZERO_DATE. Examples are in versioning.create test which succeeds even in the traditional mode.



 Comments   
Comment by Sergei Golubchik [ 2022-10-21 ]

in bb-10.11-serg

Comment by Elena Stepanova [ 2022-10-25 ]

I haven't got anything that would not allow it to be pushed. Please go ahead.

The behavior is still weird in some ways, e.g. it is strange when you can do insert (f) values (default), but select default(f) causes an error, as in

MariaDB [test]> create or replace table t (a int, s bigint unsigned as row start, e bigint unsigned as row end, period for system_time(s,e)) with system versioning;
Query OK, 0 rows affected (0.074 sec)
 
MariaDB [test]> insert into t values (1,default,default);
Query OK, 1 row affected (0.010 sec)
 
MariaDB [test]> select default(a), default(s), default(e) from t;
ERROR 1364 (HY000): Field 's' doesn't have a default value

But it's not weirder than it was before (and still is with "regular" virtual columns), when select default(f) returns something completely different from what insert (default) inserts, so it can't be an obstacle.

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