Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-14769

Temporary table can be altered into system versioning + system_versioning_alter_history has no effect

Details

    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;

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14770 Versioned temporary table is marked as crashed and should be repaired

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            kevg Eugene Kosov (Inactive) added a comment -

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

            kevg Eugene Kosov (Inactive) added a comment - It's always ok (and standard) to `ADD SYSTEM VERSIONING` and `DROP SYSTEM VERSIONING`. `system_versioning_alter_history` prohibits all other `ALTER` types.
            elenst Elena Stepanova added a comment -

            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.

            elenst Elena Stepanova added a comment - 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.
            kevg Eugene Kosov (Inactive) added a comment -

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

            kevg Eugene Kosov (Inactive) added a comment - Fixed in https://github.com/MariaDB/server/commit/157150cfcf5b95b19d332cb1373aeb5eb22d4f70

            People

              kevg Eugene Kosov (Inactive)
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.