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

Support DATETIME(6) for ROW START, ROW END

    XMLWordPrintable

    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

            Activity

              People

              Assignee:
              midenok Aleksey Midenkov
              Reporter:
              f_razzoli Federico Razzoli
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated: