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

          blocks

          Task - A task that needs to be done. MDEV-6001 Merge tests for MySQL WL#946 TIME/DATETIME/TIMESTAMP with fractional precision

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Stalled

          Bug - A problem which impairs or prevents the functions of the product. MDEV-8894 Inserting fractional seconds into MySQL 5.6 master breaks consistency on MariaDB 10 slave

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          is blocked by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15406 NO_ZERO_IN_DATE erroneously affects how CAST(AS DATE) warns about fractional digit truncation

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-17175 Change Time/Datetime constructors to return warnings in MYSQL_TIME_STATUS

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-17182 Move fractional second truncation outside of Field_xxx::store_TIME_with_warn()

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-17203 Move fractional second truncation from Item_xxx_typecast::get_date() to Time and Datetime constructors

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17244 MAKETIME(900,0,0.111) returns a wrong result

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-17274 Split Field_temporal_with_date::store*() for Field_date_common and Field_datetime

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-17288 Replace Item_func::get_arg0_date() to Date/Datetime methods

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-17317 Add THD* parameter into Item::get_date() and stricter data type control to "fuzzydate"

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-17331 Reuse Temporal_hybrid in xxx_to_date_with_warn()

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17351 Wrong results for GREATEST,TIMESTAMP,ADDTIME with an out-of-range TIME-alike argument

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17400 The result of TIME('42949672965959-01') depends on architecture

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17417 TIME(99991231235959) returns 838:59:59 instead of 23:59:59

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17434 EXTRACT(DAY FROM negative_time) returns wrong result

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-17460 Move the code from Item_extract::val_int() to a new class Extract_source

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17477 Wrong result for TIME('-2001-01-01 10:20:30') and numerous other str-to-time conversion problems

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17478 Wrong result for TIME('+100:20:30')

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-17712 Remove C_TIME_FUZZY_DATES, C_TIME_DATETIME_ONLY, C_TIME_TIME_ONLY

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-17776 CAST(x AS INTERVAL DAY_SECOND(N))

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-17792 New class Timestamp and cleanups in Date, Datetime, Field for rounding

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17318 CAST(LEAST(zero_date,non_zero_date) AS numeric_data_type) returns a wrong result

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17325 NULL-ability problems with LEAST() in combination with NO_ZERO_DATE and NO_ZERO_IN_DATE

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17385 MICROSECOND() returns confusing results with an out-of-range TIME-alike argument

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17418 Inconsitent results for TIME(8395960) and TIME('839:59:60')

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-17836 Document Rounding vs truncation for TIME, DATETIME, TIMESTAMP

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MCOL-4421 Add TIME_ROUND_FRACTIONAL support in ColumnStore

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17329 Inconsistency of NULL date value conversion to zero date in comparison context

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-19502 TIME_ROUND_FRACTIONAL is not respected on TIME->BIGINT conversion

          • Major - Major loss of function.
          • Open
          links to

          Web Link MySQL Bug#92473 DATE_ADD rounds nanoseconds inconsistently

          Web Link MySQL Bug#92474 STR_TO_DATE truncates bad microseconds unexpectedly

          Web Link MySQL Bug#92475 CAST(AS DATE) truncates microseconds but rounds nanoseconds

          Web Link MySQL bug#92501 FROM_UNIXTIME accepts an out of range parameter through rounding

          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.