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

Questionable semantics of INTERVAL values in PARTITION BY system_time: converted into seconds

    Details

      Description

      INTERVAL values other than SECOND in PARTITION BY system_time are converted into seconds.

      It's fine as long as intervals are such that the number of seconds is deterministic (MINUTE, HOUR, DAY, WEEK), but it becomes strange with MONTH and YEAR.

      CREATE OR REPLACE TABLE t1 (i INT) ENGINE=MyISAM WITH SYSTEM VERSIONING
      PARTITION BY system_time INTERVAL 1 MONTH (
        PARTITION p0 VERSIONING, 
        PARTITION pn AS OF CURRENT_TIMESTAMP
      );
      

      MariaDB [test]> SHOW CREATE TABLE t1 \G
      *************************** 1. row ***************************
             Table: t1
      Create Table: CREATE TABLE `t1` (
        `i` int(11) DEFAULT NULL,
        `sys_trx_start` timestamp(6) GENERATED ALWAYS AS ROW START,
        `sys_trx_end` timestamp(6) GENERATED ALWAYS AS ROW END,
        PERIOD FOR SYSTEM_TIME (`sys_trx_start`, `sys_trx_end`)
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
       PARTITION BY SYSTEM_TIME INTERVAL 2592000 SECOND 
      (PARTITION `p0` VERSIONING ENGINE = MyISAM,
       PARTITION `pn` AS OF CURRENT_TIMESTAMP ENGINE = MyISAM)
      1 row in set (0.00 sec)
      

      That is, 1 MONTH equals 30 days.

      Usually the context for INTERVAL allows to keep its natural meaning, e.g. in DATE_ADD interval MONTH actually means a month, not 30 days:

      MariaDB [test]> SELECT DATE_ADD('2012-02-20', INTERVAL 1 MONTH);
      +------------------------------------------+
      | DATE_ADD('2012-02-20', INTERVAL 1 MONTH) |
      +------------------------------------------+
      | 2012-03-20                               |
      +------------------------------------------+
      1 row in set (0.00 sec)
      

      I think the different semantics in the system time partitioning clause can come unexpected for users, but I don't have a good suggestion for how it should work instead, and I couldn't find anything about it in the standard. I'm okay with it being just a documentation issue.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                serg Sergei Golubchik
                Reporter:
                elenst Elena Stepanova
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: