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

Conversion from DOUBLE to DATETIME is inconsistent

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4
    • Fix Version/s: 10.4
    • Component/s: Temporal Types
    • Labels:
      None

      Description

      These scripts:

      SELECT CAST(20101112111417.766e0 AS DATETIME(3)) AS a;

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a DOUBLE);
      INSERT INTO t1 VALUES (20101112111417.766); 
      SELECT CAST(a AS DATETIME(3)) AS a FROM t1;

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a DATETIME(3), b DOUBLE);
      INSERT INTO t1 (b) VALUES(20101112111417.766e0);
      UPDATE t1 SET a=b;
      SELECT a FROM t1;

      DROP TABLE IF EXISTS t1,t2;
      CREATE TABLE t1 (a DATETIME(3));
      CREATE TABLE t2 (a DOUBLE);
      INSERT INTO t2 VALUES (20101112111417.766e0);
      INSERT INTO t1 SELECT * FROM t2;
      SELECT * FROM t1;

      return

      +-------------------------+
      | a                       |
      +-------------------------+
      | 2010-11-12 11:14:17.765 |
      +-------------------------+

      while

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a DOUBLE);
      INSERT INTO t1 VALUES (20101112111417.766); 
      ALTER TABLE t1 MODIFY a DATETIME(3);
      SELECT * FROM t1;

      returns

      +-------------------------+
      | a                       |
      +-------------------------+
      | 2010-11-12 11:14:17.766 |
      +-------------------------+

      The difference happens because:

      • all scripts (except ALTER) use val_real() of the source and then perform double-to-datetime conversion, as DECIMAL gives consistent results (see below).
      • the last script (with ALTER) uses val_str() of the source and then does string-to-datetime conversion

      The underlying code in double-to-datetime conversion effectively uses this expression to calculate fractional digits:

      MariaDB [test]> SELECT 20101112111417.766e0-FLOOR(20101112111417.766e0);
      +--------------------------------------------------+
      | 20101112111417.766e0-FLOOR(20101112111417.766e0) |
      +--------------------------------------------------+
      |                                         0.765625 |
      +--------------------------------------------------+

      Notice, it returns 0.765625 instead of just 0.766 which then truncates to 3 digits and gives 0.765

      Possible solutions:
      1. Use DECIMAL as an intermediate data type in double-to-datetime conversion, as DECIMAL returns consistent results (see below).
      2. Fix Item_datetime_typecast::get_date() to round fractional digits instead of truncation
      3. Fix the ALTER code to use val_real() instead of val_str() when altering DOUBLE to DATETIME. This will make all cases return .765 at the end, which is probably not so good, as .766 looks better and consistent with what DECIMAL does (see below).

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                bar Alexander Barkov
                Reporter:
                bar Alexander Barkov
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated: