[MDEV-14769] Temporary table can be altered into system versioning + system_versioning_alter_history has no effect Created: 2017-12-25  Updated: 2018-07-17  Resolved: 2018-01-04

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Temporary, Versioned Tables
Affects Version/s: N/A
Fix Version/s: 10.3.4

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Eugene Kosov (Inactive)
Resolution: Fixed Votes: 0
Labels: None
Environment:

bb-10.3-temporal ea49441c41

Issue Links:
Problem/Incident
causes MDEV-14770 Versioned temporary table is marked a... Closed

 Description   

Creation of a temporary table with system versioning is prohibited:

MariaDB [test]> CREATE OR REPLACE TEMPORARY TABLE t1 (i INT) WITH SYSTEM VERSIONING;
 
ERROR 4146 (HY000): WITH SYSTEM VERSIONING prohibited for TEMPORARY tables

However, such a table can be created in two steps:

MariaDB [test]> CREATE OR REPLACE TEMPORARY TABLE t1 (i INT);
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
MariaDB [test]> ALTER TABLE t1 ADD SYSTEM VERSIONING;
 
Query OK, 0 rows affected (0.01 sec)
 
Records: 0  Duplicates: 0  Warnings: 0

Please also note that system_versioning_alter_history has no effect here:

MariaDB [test]> CREATE OR REPLACE TEMPORARY TABLE t1 (i INT);
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
MariaDB [test]> SET system_versioning_alter_history= ERROR;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> ALTER TABLE t1 ADD SYSTEM VERSIONING;
 
Query OK, 0 rows affected (0.01 sec)
 
Records: 0  Duplicates: 0  Warnings: 0

Same test case for copy-pasting

 
--error ER_VERS_TEMPORARY
 
CREATE OR REPLACE TEMPORARY TABLE t1 (i INT) WITH SYSTEM VERSIONING;
 
 
 
CREATE OR REPLACE TEMPORARY TABLE t1 (i INT);
 
 
 
SET system_versioning_alter_history= ERROR;
 
--error ER_VERS_TEMPORARY
 
ALTER TABLE t1 ADD SYSTEM VERSIONING;



 Comments   
Comment by Eugene Kosov (Inactive) [ 2017-12-27 ]

It's always ok (and standard) to `ADD SYSTEM VERSIONING` and `DROP SYSTEM VERSIONING`. `system_versioning_alter_history` prohibits all other `ALTER` types.

Comment by Elena Stepanova [ 2017-12-27 ]

It's always ok (and standard) to `ADD SYSTEM VERSIONING` and `DROP SYSTEM VERSIONING`. `system_versioning_alter_history` prohibits all other `ALTER` types.

You are right, sorry for the confusion, please ignore the part that refers to system_versioning_alter_history behavior.

It initially came from a non-simplified test case, which was executing more complex statements, like 

ALTER TABLE t1 ADD SYSTEM VERSIONING, ADD COLUMN a INT

which I expected to fail, but I can now see that this is allowed for non-temporary tables as well, so I guess it's not a problem.

Comment by Eugene Kosov (Inactive) [ 2018-01-04 ]

Fixed in https://github.com/MariaDB/server/commit/157150cfcf5b95b19d332cb1373aeb5eb22d4f70

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