[MDEV-14670] Cannot create a virtual column with/out system versioning Created: 2017-12-16  Updated: 2018-01-25  Resolved: 2018-01-25

Status: Closed
Project: MariaDB Server
Component/s: Documentation, Parser, Versioned Tables, Virtual Columns
Affects Version/s: N/A
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Ian Gilfillan
Resolution: Fixed Votes: 0
Labels: None


 Description   

It seems impossible to add WITH SYSTEM VERSIONING or WITHOUT SYSTEM VERSIONING clause for a virtual column.

This works, so the base syntax is correct:

 
MariaDB [db]> create or replace table t1 (i int, j int with system versioning);
 
Query OK, 0 rows affected (0.22 sec)
 
 
 
MariaDB [db]> create or replace table t1 (i int, j int as(i));
 
Query OK, 0 rows affected (0.24 sec)
 
 
 
MariaDB [db]> create or replace table t1 (i int, j int as(i) persistent);
 
Query OK, 0 rows affected (0.22 sec)

But none of this works:

 
MariaDB [db]> create or replace table t1 (i int, j int with system versioning as(i));
 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'as(i))' at line 1
 
MariaDB [db]> create or replace table t1 (i int, j int as(i) with system versioning);
 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'system versioning)' at line 1
 
MariaDB [db]> create or replace table t1 (i int, j int with system versioning as(i) persistent);
 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'as(i) persistent)' at line 1
 
MariaDB [db]> create or replace table t1 (i int, j int as(i) with system versioning persistent);
 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'system versioning persistent)' at line 1
 
MariaDB [db]> create or replace table t1 (i int, j int as(i) persistent with system versioning);
 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'system versioning)' at line 1



 Comments   
Comment by Aleksey Midenkov [ 2017-12-16 ]

Is this syntax needed?

Expected behavior:

create or replace table t1 (i int with system versioning, j int as(i));

I.e. there can't be non-temporal i and temporal j. OTOH there can't be temporal i and non-temporal j. So j always inherits temporal property from i.

Comment by Sergei Golubchik [ 2017-12-16 ]

I'd say the behavior is correct. Versioning clauses should not apply to generated columns.

Comment by Sergei Golubchik [ 2017-12-16 ]

This just needs to be documented.

Comment by Elena Stepanova [ 2017-12-16 ]

I don't disagree that it's not needed for virtual columns. I thought it would be better if an attempt to do it ended with a specific message rather than a syntax error, but it's not important.

I don't know yet who will be documenting versioning when it's ready, so I'm re-opening it for documentation purposes and keeping it on myself until I know who to assign it to.

Comment by Elena Stepanova [ 2018-01-23 ]

I think https://mariadb.com/kb/en/library/system-versioned-tables/ is missing the "limitations" section, and this could be one of those limitations.

Comment by Ian Gilfillan [ 2018-01-25 ]

Added this limitation to https://mariadb.com/kb/en/library/generated-columns/#limitations and https://mariadb.com/kb/en/library/system-versioned-tables/#limitations .

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