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

Support DATETIME(6) for ROW START, ROW END

Details

    Description

      If I try to create a system-versioned table with ROW START and ROW END columns of type DATETIME(6), the table is created. This is different from what happens if I try, for example, VARCHAR, in which case I get a clear error message. But then, the table is unusable.

      MariaDB [test]> CREATE OR REPLACE TABLE t (
      		 
          ->     id INT UNSIGNED NOT NULL AUTO_INCREMENT,
      		 
          ->     label VARCHAR(50) NOT NULL,
      		 
          ->
      		 
          ->         PERIOD FOR SYSTEM_TIME (valid_from, valid_to),
      		 
          ->     valid_from DATETIME(6)
      		 
          ->         GENERATED ALWAYS AS ROW START,
      		 
          ->     valid_to DATETIME(6)
      		 
          ->         GENERATED ALWAYS AS ROW END,
      		 
          ->
      		 
          ->     PRIMARY KEY (id)
      		 
          -> )
      		 
          ->     WITH SYSTEM VERSIONING,
      		 
          ->     ENGINE InnoDB
      		 
          -> ;
      		 
      Query OK, 0 rows affected (0.010 sec)
      		 
       
      		 
      MariaDB [test]> INSERT INTO t (label) VALUES ('blah blah');
      		 
      Query OK, 1 row affected (0.001 sec)
      		 
       
      		 
      MariaDB [test]> SELECT * FROM t;
      		 
      Empty set (0.000 sec)

      And the reason is easy to find out:

      MariaDB [test]> SELECT * FROM t FOR SYSTEM_TIME ALL;
      		 
      +----+-----------+----------------------------+----------------------------+
      		 
      | id | label     | valid_from                 | valid_to                   |
      		 
      +----+-----------+----------------------------+----------------------------+
      		 
      |  1 | blah blah | 2018-10-14 19:56:08.057624 | 0000-00-00 00:00:00.000000 |
      		 
      +----+-----------+----------------------------+----------------------------+

      If DATETIME is not supposed to work, it shouldn't be allowed.

      Attachments

        Issue Links

          relates to

          New Feature - A new feature of the product, which has yet to be developed. MDEV-10018 Timestamp with time zone

          • Major - Major loss of function.
          • Open

          New Feature - A new feature of the product, which has yet to be developed. MDEV-11829 Please add support for datetime with time zone literals (ISO 8601)

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17798 System variable system_versioning_asof accepts wrong values

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-18512 using DATETIME(6) as row_start/row_end crashes server

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            midenok Aleksey Midenkov added a comment -

            It is already disallowed in 10.3. But `DATETIME` is not prohibited by standard, so it should be supported in 10.4.

            midenok Aleksey Midenkov added a comment - It is already disallowed in 10.3. But `DATETIME` is not prohibited by standard, so it should be supported in 10.4.
            bar Alexander Barkov added a comment - - edited

            4.41.3 Properties of SQL-transactions says:

            An SQL-transaction has a transaction timestamp, a value of an implementation-defined timestamp type that is
            used to set the values of system-time period start and system-time period end columns of rows, if any, modified
            by the execution of an SQL-data change statement in this SQL-transaction.

            We use MariaDB's TIMESTAMP data type (which is timestamp with local time zone) as this implementation defined type.
            Translation from TIMESTAMP to DATETIME is lossy:

            • DATETIME preserves the YYYYMMDDhhmmss.ff part of the TIMESTAMP
            • but it looses the timezone information (i.e. the value of the @@time_zone system variable which was set at the conversion time)

            It's not possible to restore the original TIMESTAMP value once it was converted to DATETIME, because the reverse conversion can already use a different @@time_zone value.

            For me it does not seem to be useful storing ROW START / ROW END data in DATETIME or DATE columns.

            We'll be adding TIMESTAMP WITH TIME ZONE soon. This new data type will support the full datetime range from '0001-01-01 00:00:00' to '9999-12-31 23:59:59' and will preserve both TIMESTAMP parts:

            • the YYYYMMDDhhmmss.ff part
            • the time zone part

            so round trip conversion TIMESTAMP -> TIMESTAMP WITH TIME ZONE -> TIMESTAMP will be non-lossy.
            It's a better candidate for ROW START and ROW END columns that DATETIME / DATE.

            bar Alexander Barkov added a comment - - edited 4.41.3 Properties of SQL-transactions says: An SQL-transaction has a transaction timestamp, a value of an implementation-defined timestamp type that is used to set the values of system-time period start and system-time period end columns of rows, if any, modified by the execution of an SQL-data change statement in this SQL-transaction. We use MariaDB's TIMESTAMP data type (which is timestamp with local time zone) as this implementation defined type. Translation from TIMESTAMP to DATETIME is lossy: DATETIME preserves the YYYYMMDDhhmmss.ff part of the TIMESTAMP but it looses the timezone information (i.e. the value of the @@time_zone system variable which was set at the conversion time) It's not possible to restore the original TIMESTAMP value once it was converted to DATETIME, because the reverse conversion can already use a different @@time_zone value. For me it does not seem to be useful storing ROW START / ROW END data in DATETIME or DATE columns. We'll be adding TIMESTAMP WITH TIME ZONE soon. This new data type will support the full datetime range from '0001-01-01 00:00:00' to '9999-12-31 23:59:59' and will preserve both TIMESTAMP parts: the YYYYMMDDhhmmss.ff part the time zone part so round trip conversion TIMESTAMP -> TIMESTAMP WITH TIME ZONE -> TIMESTAMP will be non-lossy. It's a better candidate for ROW START and ROW END columns that DATETIME / DATE.
            midenok Aleksey Midenkov added a comment -

            Possible solution for DATETIME row_start, row_end might be to store in UTC. System fields independent from time_zone can be an advantage for certain deployments.

            midenok Aleksey Midenkov added a comment - Possible solution for DATETIME row_start, row_end might be to store in UTC. System fields independent from time_zone can be an advantage for certain deployments.
            rpx Robert Palm added a comment - - edited

            Hi,

            I w'd love to see a time range extension.

            When is it going to happen?

            Thank you.

            rpx Robert Palm added a comment - - edited Hi, I w'd love to see a time range extension. When is it going to happen? Thank you.
            rjasdfiii Rick James added a comment -

            @Alexander – "TIMESTAMP data type (which is timestamp with local time zone)" – I thought that `TIMESTAMP` was stored in UTC, without any timezone. (When viewing (SELECTing) it, the current timezone is used to modify what you see.)

            That is, `TIMESTAMP` should be adequate, as is, for use in ROW_START, etc.

            rjasdfiii Rick James added a comment - @Alexander – "TIMESTAMP data type (which is timestamp with local time zone)" – I thought that `TIMESTAMP` was stored in UTC, without any timezone. (When viewing (SELECTing) it, the current timezone is used to modify what you see.) That is, `TIMESTAMP` should be adequate, as is, for use in ROW_START, etc.

            People

              midenok Aleksey Midenkov
              f_razzoli Federico Razzoli
              Votes:
              4 Vote for this issue
              Watchers:
              10 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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