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

Rounding vs truncation for TIME, DATETIME, TIMESTAMP

    XMLWordPrintable

Details

    Description

      This task is a requirement for MDEV-8894.

      Currently MariaDB truncates fractional seconds when changing precision to smaller. This is different from how other databases work. Under terms of this task we'll add a way to do rounding instead of truncation. This will include

      • New C++ functions/methods to actually perform rounding for the temporal data types
      • A new session sql_mode flag TIME_ROUND_FRACTIONAL to force rounding vs truncation

      Note, MDEV-8894 will be fixed by a separate patch after this task.

      In the new sql_mode, MariaDB will consistently do all operations (e.g. SET, CAST, ALTER) as follows:

      • Round when converting to TIME
      • Round when converting to DATETIME and TIMESTAMP
      • Truncate when converting to DATE

      This is slightly different from how other databases work, but we believe this will give more intuitively expected results in most cases.

      MySQL compatibility

      The proposed way will give:

      • compatible behavior when converting to TIME, DATETIME, TIMESTAMP
      • incompatible behavior when converting to DATE

      Later, when working on MDEV-8894, we'll introduce a new sql_mode=MYSQL, which will emulate MySQL's rounding behavior precisely.
      The current task is to introduce MariaDB's "native" mode for temporal rounding.

      Oracle compatibility

      Emulating precise Oracle rounding behavior when sql_mode is set to 'ORACLE,TIME_ROUND_FRACTIONAL' is out of scope of this task and will be done separately.

      Summary on data type conversion in other databases

      • TD - target data type
      • SD - source data type
      to TIME
      Operation TD SD MySQL Oracle PostgreSQL SQL Standard
      SET time(3) time(4) round N/A round Implementation defined
      SET time(3) time(4)-in-varchar round N/A error(wrong types) error
      SET time(3) time(7)-in-varchar round N/A error(wrong types) error
      SET time(3) time(4)-in-decimal round N/A error(wrong types) error
      SET time(3) time(7)-in-decimal round N/A error(wrong types) error
      ALTER time(3) time(4) round N/A round error
      ALTER time(3) time(4)-in-varchar round N/A error(wrong types) error
      ALTER time(3) time(7)-in-varchar round N/A error(wrong types) error
      ALTER time(3) time(4)-in-decimal round N/A error(wrong types) error
      ALTER time(3) time(7)-in-decimal round N/A error(wrong types) error
      ALTER time(6) time(7)-in-decimal round N/A error(wrong types) error
      CAST time(3) time(4) literal round N/A round Implementation defined
      CAST time(3) time(4)-in-string round N/A round error
      CAST time(3) time(7)-in-string round N/A round error
      CAST time(3) time(4)-in-decimal round N/A error(wrong types) error
      CAST time(3) time(7)-in-decimal round N/A error(wrong types) error
      to TIMESTAMP
      Operation TD SD MySQL Oracle PostgreSQL SQL Standard
      SET timestamp(3) timestamp(4) round round round Implementation defined
      SET timestamp(3) timestamp(4)-in-varchar round round error(wrong types) error
      SET timestamp(3) timestamp(7)-in-varchar round round error(wrong types) error
      SET timestamp(3) timestamp(4)-in-decimal round error(wrong types) error(wrong types) error
      SET timestamp(3) timestamp(7)-in-decimal round error(wrong types) error(wrong types) error
      ALTER timestamp(3) timestamp(4) round error round error
      ALTER timestamp(3) timestamp(4)-in-varchar round error(column must be empty) error(wrong types) error
      ALTER timestamp(3) timestamp(7)-in-varchar round error(column must be empty) error(wrong types) error
      ALTER timestamp(3) timestamp(4)-in-decimal round error(column must be empty) error(wrong types) error
      ALTER timestamp(3) timestamp(7)-in-decimal round error(column must be empty) error(wrong types) error
      CAST timestamp(3) timestamp(4) literal round round round Implementation defined
      CAST timestamp(3) timestamp(4)-in-string round round round error
      CAST timestamp(3) timestamp(7)-in-string round round round error
      CAST timestamp(3) timestamp(4)-in-decimal round error(wrong types) error(wrong types) error
      CAST timestamp(3) timestamp(7)-in-decimal round error(wrong types error(wrong types) error
      to DATE
      Operation TD SD MySQL Oracle PostgreSQL SQL Standard
      SET date timestamp(4) round truncate truncate error
      SET date timestamp(4)-in-varchar round error(wrong format) error(wrong types) error
      SET date timestamp(7)-in-varchar round error(wrong format) error(wrong types) error
      SET date timestamp(4)-in-decimal round error(wrong types) error(wrong types) error
      SET date timestamp(7)-in-decimal round error(wrong format) error(wrong types) error
      ALTER date timestamp(4) round round truncate error
      ALTER date timestamp(4)-in-varchar round error(must be empty) error(wrong types) error
      ALTER date timestamp(7)-in-varchar round error(must be empty) error(wrong types) error
      ALTER date timestamp(4)-in-decimal round error(must be empty) error(wrong types) error
      ALTER date timestamp(7)-in-decimal round error(must be empty) error(wrong types) error
      CAST date timestamp(4) literal truncate truncate truncate truncate
      CAST date timestamp(4)-in-string truncate error(wrong format) truncate error
      CAST date timestamp(7)-in-string round! error(wrong format) truncate error
      CAST date timestamp(4)-in-decimal truncate error(wrong types) error(wrong types) error
      CAST date timestamp(7)-in-decimal round! error(wrong types) error(wrong types) error

      Observations:

      • MySQL is not consistent about conversion from varchar to date (reported as MySQL Bug #92475):
        • implicit cast always rounds
        • explicit CAST truncates for FSP<=6
        • explicit CAST rounds for FSP>6
      • MySQL: CAST(AS DATE) truncates microseconds but rounds nanoseconds

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              1 Vote for this issue
              Watchers:
              3 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.