[MDEV-26928] Column-inclusive WITH SYSTEM VERSIONING doesn't work with explicit system fields - Jira

Geoff Montee (Inactive) created issue - 2021-10-27 19:05

Geoff Montee (Inactive) made changes - 2021-10-27 19:05

Field	Original Value	New Value
Link		This issue is caused by ~~MDEV-12894~~ [ ~~MDEV-12894~~ ]

Geoff Montee (Inactive) made changes - 2021-10-27 19:05

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 made changes - 2021-10-28 14:10

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 made changes - 2021-10-28 14:12

Assignee

Ralf Gebhardt [ ralf.gebhardt@mariadb.com ]

Aleksey Midenkov [ midenok ]

Aleksey Midenkov made changes - 2021-11-02 00:47

Summary

Implement column inclusion for transaction-precise tables

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

Aleksey Midenkov made changes - 2021-11-02 00:48

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.

Aleksey Midenkov made changes - 2021-11-02 00:49

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

Aleksey Midenkov made changes - 2021-11-02 01:26

Fix Version/s

10.3 [ 22126 ]

Aleksey Midenkov made changes - 2021-11-02 01:26

Status

Open [ 1 ]

In Progress [ 3 ]

Aleksey Midenkov added a comment - 2021-11-02 01:27

Please review bb-10.3-midenok

Aleksey Midenkov added a comment - 2021-11-02 01:27 Please review bb-10.3-midenok

Aleksey Midenkov made changes - 2021-11-02 01:27

Assignee	Aleksey Midenkov [ midenok ]	Nikita Malyavin [ nikitamalyavin ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Nikita Malyavin added a comment - 2021-11-02 08:27

Posted some demands to the test: https://github.com/MariaDB/server/commit/746ca3d9f7c3fa50615488203e994dc76381f783

Ok to push after fulfillment

Nikita Malyavin added a comment - 2021-11-02 08:27 Posted some demands to the test: https://github.com/MariaDB/server/commit/746ca3d9f7c3fa50615488203e994dc76381f783 Ok to push after fulfillment

Nikita Malyavin made changes - 2021-11-02 08:27

Assignee	Nikita Malyavin [ nikitamalyavin ]	Aleksey Midenkov [ midenok ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Aleksey Midenkov made changes - 2021-11-02 10:02

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 ]

Aleksey Midenkov made changes - 2021-11-02 10:03

Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Daniel Bartholomew made changes - 2021-11-08 19:29

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 ]

Sergei Golubchik made changes - 2021-12-06 21:54

Workflow

MariaDB v3 [ 126770 ]

MariaDB v4 [ 159812 ]

Elena Stepanova made changes - 2022-01-05 23:17

Link

This issue causes ~~MDEV-27430~~ [ ~~MDEV-27430~~ ]

Elena Stepanova made changes - 2022-01-09 17:07

Link

This issue causes ~~MDEV-27452~~ [ ~~MDEV-27452~~ ]

MariaDB Server

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

Details

Description

Reproduce

Result

Expected

Attachments

Issue Links

Activity

People

Dates

Git Integration