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

CREATE does not allow tables with versioning columns only, but ALTER does

    XMLWordPrintable

    Details

      Description

      One cannot CREATE a versioned table without any columns other than row start/end:

      create or replace table t1 (s timestamp(6) as row start, e timestamp(6) as row end, period for system_time (s,e)) with system versioning;
      # ERROR 4128 (HY000): Table `t1` must have at least one temporal column
      

      However, one can create such a table via CREATE + ALTER:

      create or replace table t1 (i int, s timestamp(6) as row start, e timestamp(6) as row end, period for system_time (s,e)) with system versioning;
      # Query OK, 0 rows affected (0.29 sec)
       
      set system_versioning_alter_history=keep;
      # Query OK, 0 rows affected (0.00 sec)
       
      alter table t1 drop i;
      # Query OK, 0 rows affected (0.69 sec)
      # Records: 0  Duplicates: 0  Warnings: 0
      

      MariaDB [test]> show create table t1 \G
      *************************** 1. row ***************************
             Table: t1
      Create Table: CREATE TABLE `t1` (
        `s` timestamp(6) GENERATED ALWAYS AS ROW START,
        `e` timestamp(6) GENERATED ALWAYS AS ROW END,
        PERIOD FOR SYSTEM_TIME (`s`, `e`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
      1 row in set (0.00 sec)
      

      So, either the limitation was bogus to begin with, or ALTER should be similarly restricted.

      (The bad error message was a subject of a different bug report, MDEV-14764, it's out of the scope of this report).

        Attachments

          Activity

            People

            Assignee:
            kevg Eugene Kosov (Inactive)
            Reporter:
            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.