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

Column-inclusive WITH SYSTEM VERSIONING doesn't work with explicit system fields

Details

    Description

      Reproduce

      create or replace table t1 (
      		 
          x int, y int with system versioning,
      		 
          row_start timestamp(6) as row start,
      		 
          row_end timestamp(6) as row end,
      		 
          period for system_time(row_start, row_end));

      Result

      ERROR 4125 (HY000): Wrong parameters for `t1`: missing 'WITH SYSTEM VERSIONING'

      Expected

      Command succeeds.

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27430 Assertion `field.is_sane()' failed in Protocol_text::store_field_metadata upon column-specific versioning

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27452 TIMESTAMP(0) system field is allowed for certain creation of system-versioned table

          • Major - Major loss of function.
          • Closed
          is caused by

          Task - A task that needs to be done. MDEV-12894 System-versioned tables

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            GeoffMontee Geoff Montee (Inactive) created issue -
            GeoffMontee Geoff Montee (Inactive) made changes -
            Field Original Value New Value
            GeoffMontee Geoff Montee (Inactive) made changes -
            Description It looks like transaction-precise tables do not currently support column inclusion:

            {code:sql}
            MariaDB [test]> CREATE TABLE accounts_col (
                -> id SERIAL PRIMARY KEY,
                -> name VARCHAR(255),
                -> amount INT WITH SYSTEM VERSIONING,
                -> start_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW START INVISIBLE,
                -> end_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW END INVISIBLE,
                -> PERIOD FOR SYSTEM_TIME(start_trxid, end_trxid)
                -> );
            ERROR 4125 (HY000): Wrong parameters for `accounts_col`: missing 'WITH SYSTEM VERSIONING'
            {code}

            However, the same can be implemented using column exclusion instead:

            {code:sql}
            MariaDB [test]> CREATE TABLE accounts_col (
                -> id SERIAL PRIMARY KEY WITHOUT SYSTEM VERSIONING,
                -> name VARCHAR(255) WITHOUT SYSTEM VERSIONING,
                -> amount INT,
                -> start_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW START INVISIBLE,
                -> end_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW END INVISIBLE,
                -> PERIOD FOR SYSTEM_TIME(start_trxid, end_trxid)
                -> ) WITH SYSTEM VERSIONING;
            Query OK, 0 rows affected (0.020 sec)
            {code}             		Unlike system-versioned tables, it looks like transaction-precise tables do not currently support column inclusion:

            {code:sql}
            MariaDB [test]> CREATE TABLE accounts_col (
                -> id SERIAL PRIMARY KEY,
                -> name VARCHAR(255),
                -> amount INT WITH SYSTEM VERSIONING,
                -> start_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW START INVISIBLE,
                -> end_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW END INVISIBLE,
                -> PERIOD FOR SYSTEM_TIME(start_trxid, end_trxid)
                -> );
            ERROR 4125 (HY000): Wrong parameters for `accounts_col`: missing 'WITH SYSTEM VERSIONING'
            {code}

            However, the same can be implemented using column exclusion instead:

            {code:sql}
            MariaDB [test]> CREATE TABLE accounts_col (
                -> id SERIAL PRIMARY KEY WITHOUT SYSTEM VERSIONING,
                -> name VARCHAR(255) WITHOUT SYSTEM VERSIONING,
                -> amount INT,
                -> start_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW START INVISIBLE,
                -> end_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW END INVISIBLE,
                -> PERIOD FOR SYSTEM_TIME(start_trxid, end_trxid)
                -> ) WITH SYSTEM VERSIONING;
            Query OK, 0 rows affected (0.020 sec)
            {code}
            ralf.gebhardt Ralf Gebhardt made changes -
            Component/s Versioned Tables [ 14303 ]
            Component/s Versioned Tables [ 15003 ]
            Key MENT-1350 MDEV-26928
            Affects Version/s 10.3 [ 22126 ]
            Affects Version/s 10.4 [ 22408 ]
            Affects Version/s 10.5 [ 23123 ]
            Affects Version/s 10.6 [ 24028 ]
            Affects Version/s 10.4 [ 23604 ]
            Affects Version/s 10.3 [ 23605 ]
            Affects Version/s 10.5 [ 23608 ]
            Affects Version/s 10.6 [ 24027 ]
            Issue Type Task [ 3 ] Bug [ 1 ]
            Project MariaDB Enterprise [ 11500 ] MariaDB Server [ 10000 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            Assignee Ralf Gebhardt [ ralf.gebhardt@mariadb.com ] Aleksey Midenkov [ midenok ]
            midenok Aleksey Midenkov made changes -
            Summary Implement column inclusion for transaction-precise tables Column inclusion WITH SYSTEM VERSIONING doesn't work with explicit system fields
            midenok Aleksey Midenkov made changes -
            Description Unlike system-versioned tables, it looks like transaction-precise tables do not currently support column inclusion:

            {code:sql}
            MariaDB [test]> CREATE TABLE accounts_col (
                -> id SERIAL PRIMARY KEY,
                -> name VARCHAR(255),
                -> amount INT WITH SYSTEM VERSIONING,
                -> start_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW START INVISIBLE,
                -> end_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW END INVISIBLE,
                -> PERIOD FOR SYSTEM_TIME(start_trxid, end_trxid)
                -> );
            ERROR 4125 (HY000): Wrong parameters for `accounts_col`: missing 'WITH SYSTEM VERSIONING'
            {code}

            However, the same can be implemented using column exclusion instead:

            {code:sql}
            MariaDB [test]> CREATE TABLE accounts_col (
                -> id SERIAL PRIMARY KEY WITHOUT SYSTEM VERSIONING,
                -> name VARCHAR(255) WITHOUT SYSTEM VERSIONING,
                -> amount INT,
                -> start_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW START INVISIBLE,
                -> end_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW END INVISIBLE,
                -> PERIOD FOR SYSTEM_TIME(start_trxid, end_trxid)
                -> ) WITH SYSTEM VERSIONING;
            Query OK, 0 rows affected (0.020 sec)
            {code}             		h3. Reproduce

            {code:sql}
            create or replace table t1 (
                x int, y int with system versioning,
                row_start timestamp(6) as row start,
                row_end timestamp(6) as row end,
                period for system_time(row_start, row_end));
            {code}

            h3. Result
            {code}
            ERROR 4125 (HY000): Wrong parameters for `t1`: missing 'WITH SYSTEM VERSIONING'
            {code}

            h3. Expected
            Command succeeds.
            midenok Aleksey Midenkov made changes -
            Summary Column inclusion WITH SYSTEM VERSIONING doesn't work with explicit system fields Column-inclusive WITH SYSTEM VERSIONING doesn't work with explicit system fields
            midenok Aleksey Midenkov made changes -
            Fix Version/s 10.3 [ 22126 ]
            midenok Aleksey Midenkov made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            midenok Aleksey Midenkov made changes -
            Assignee Aleksey Midenkov [ midenok ] Nikita Malyavin [ nikitamalyavin ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            nikitamalyavin Nikita Malyavin made changes -
            Assignee Nikita Malyavin [ nikitamalyavin ] Aleksey Midenkov [ midenok ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            midenok Aleksey Midenkov made changes -
            Fix Version/s 10.3.32 [ 26029 ]
            Fix Version/s 10.4.22 [ 26031 ]
            Fix Version/s 10.5.13 [ 26026 ]
            Fix Version/s 10.6.5 [ 26034 ]
            Fix Version/s 10.3 [ 22126 ]
            midenok Aleksey Midenkov made changes -
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            dbart Daniel Bartholomew made changes -
            Fix Version/s 10.3.33 [ 26805 ]
            Fix Version/s 10.4.23 [ 26807 ]
            Fix Version/s 10.5.14 [ 26809 ]
            Fix Version/s 10.6.6 [ 26811 ]
            Fix Version/s 10.5.13 [ 26026 ]
            Fix Version/s 10.3.32 [ 26029 ]
            Fix Version/s 10.4.22 [ 26031 ]
            Fix Version/s 10.6.5 [ 26034 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 126770 ] MariaDB v4 [ 159812 ]
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -

            People

              midenok Aleksey Midenkov
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.