[MDEV-17452] ALTERing a non-versioned column should always be allowed Created: 2018-10-14  Updated: 2019-02-07

Status: Open
Project: MariaDB Server
Component/s: Versioned Tables
Fix Version/s: None

Type: Task Priority: Major
Reporter: Federico Razzoli Assignee: Aleksey Midenkov
Resolution: Unresolved Votes: 0
Labels: None


 Description    

MariaDB [test]> CREATE OR REPLACE TABLE t (
 
    ->     id INT UNSIGNED NOT NULL AUTO_INCREMENT,
 
    ->     label VARCHAR(50) NOT NULL WITHOUT SYSTEM VERSIONING,
 
    ->
 
    ->     valid_from TIMESTAMP(6)
 
    ->         GENERATED ALWAYS AS ROW START,
 
    ->     valid_to TIMESTAMP(6)
 
    ->         GENERATED ALWAYS AS ROW END,
 
    ->     PERIOD FOR SYSTEM_TIME (valid_from, valid_to),
 
    ->
 
    ->     PRIMARY KEY (id)
 
    -> )
 
    ->     WITH SYSTEM VERSIONING,
 
    ->     ENGINE InnoDB
 
    -> ;
 
Query OK, 0 rows affected (0.009 sec)
 
 
 
MariaDB [test]> ALTER TABLE label label VARCHAR(100) NOT NULL WITHOUT 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 'VARCHAR(100) NOT NULL WITHOUT SYSTEM VERSIONING' at line 1

Note that `label` is defined as `WITHOUT SYSTEM VERSIONING`. While this doesn't mean that the column is not versioned (an UPDATE which modifies both id and label is indeed versioned), excluding a column from system versioning would be more useful if it will allow ALTERing the column. The cost is intuitive (inaccurate history. But I can't imagine a situation where this is a problem, if the user defined the column as `WITHOUT SYSTEM VERSIONING`.

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

The failure in the description has nothing to do with system versioning limitations, it's an obvious syntax error (two errors actually, one is the syntax error, another one is the wrong table name). I assume what you meant was this:

MariaDB [test]>  ALTER TABLE t MODIFY label VARCHAR(100) NOT NULL WITHOUT SYSTEM VERSIONING;
 
ERROR 4119 (HY000): Not allowed for system-versioned `test`.`t`. Change @@system_versioning_alter_history to proceed with ALTER.

I'm converting it into a feature request.

Comment by Federico Razzoli [ 2018-10-15 ]

Yes, that is what I meant. Mistake in copy/pasting, apologies.

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