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

Storing DATETIME-alike VARCHAR data into TIME produces wrong results

    Details

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

      Description

      I create a table with a VARCHAR column and populate it with a DATETIME-alike data:

      SET sql_mode='';
      CREATE OR REPLACE TABLE t0 (d VARCHAR(64));
      INSERT INTO t0 VALUES ('0000-00-01 10:20:30');
      INSERT INTO t0 VALUES ('0000-01-00 10:20:30');
      INSERT INTO t0 VALUES ('0000-01-01 10:20:30');
      INSERT INTO t0 VALUES ('0001-00-00 10:20:30');
      INSERT INTO t0 VALUES ('0001-00-01 10:20:30');
      INSERT INTO t0 VALUES ('0001-01-00 10:20:30');
      INSERT INTO t0 VALUES ('0001-01-01 10:20:30');
      

      Now I create a new table t1 with three fields at populate it from t0:

      SET @@global.mysql56_temporal_format=1;
      CREATE OR REPLACE TABLE t1 (d VARCHAR(64), t0 TIME(0), t1 TIME(1));
      INSERT INTO t1 SELECT d,d,d FROM t0;
      SELECT * FROM t1;
      

      It returns the following result:

      +---------------------+----------+------------+
      | d                   | t0       | t1         |
      +---------------------+----------+------------+
      | 0000-00-01 10:20:30 | 34:20:30 | 34:20:30.0 |
      | 0000-01-00 10:20:30 | 10:20:30 | 10:20:30.0 |
      | 0000-01-01 10:20:30 | 10:20:30 | 10:20:30.0 |
      | 0001-00-00 10:20:30 | 10:20:30 | 10:20:30.0 |
      | 0001-00-01 10:20:30 | 10:20:30 | 10:20:30.0 |
      | 0001-01-00 10:20:30 | 10:20:30 | 10:20:30.0 |
      | 0001-01-01 10:20:30 | 10:20:30 | 10:20:30.0 |
      +---------------------+----------+------------+
      

      This result looks OK:

      • The first record has a zero YYYY-MM part, the value is considered as a TIME interval '1 10:20:30', so days are added to hours on conversion to TIME.
      • The other records have a non-zero YYYY-MM part, to the value is considered as a fuzzy DATE, the entire YYYY-MM-DD part is thrown away on conversion to TIME.

      Now I run the same with the obsolete MariaDB-5.3 temporal format:

      SET @@global.mysql56_temporal_format=0;
      CREATE OR REPLACE TABLE t1 (d VARCHAR(64), t0 TIME(0), t1 TIME(1));
      INSERT INTO t1 SELECT d,d,d FROM t0;
      SELECT * FROM t1;
      

      It returns exactly the same result. So far so good. Both formats produce the same result.

      Now I add 'x' to the end of the values, to force warnings, and run with the modern format:

      SET @@global.mysql56_temporal_format=1;
      CREATE OR REPLACE TABLE t1 (d VARCHAR(64), t0 TIME(0), t1 TIME(1));
      INSERT INTO t1 SELECT CONCAT(d,'x'),CONCAT(d,'x'),CONCAT(d,'x') FROM t0;
      SELECT * FROM t1;
      

      It correctly produces 14 warnings (7 records * 2 TIME columns), and returns this result:

      +----------------------+----------+------------+
      | d                    | t0       | t1         |
      +----------------------+----------+------------+
      | 0000-00-01 10:20:30x | 34:20:30 | 34:20:30.0 |
      | 0000-01-00 10:20:30x | 10:20:30 | 10:20:30.0 |
      | 0000-01-01 10:20:30x | 10:20:30 | 10:20:30.0 |
      | 0001-00-00 10:20:30x | 10:20:30 | 10:20:30.0 |
      | 0001-00-01 10:20:30x | 34:20:30 | 34:20:30.0 |
      | 0001-01-00 10:20:30x | 10:20:30 | 10:20:30.0 |
      | 0001-01-01 10:20:30x | 10:20:30 | 10:20:30.0 |
      +----------------------+----------+------------+
      

      Notice, the TIME values in the record '0001-00-01 10:20:30x' have changed from 10:20:30 to 34:20:30.
      Looks wrong! I expect exactly the same results with the first script, just with warnings about trailing 'x' truncation.

      Now I run the same script, using the obsolete MariaDB-5.3 format:

      SET @@global.mysql56_temporal_format=0;
      CREATE OR REPLACE TABLE t1 (d VARCHAR(64), t0 TIME(0), t1 TIME(1));
      INSERT INTO t1 SELECT CONCAT(d,'x'),CONCAT(d,'x'),CONCAT(d,'x') FROM t0;
      SELECT * FROM t1;
      

      +----------------------+----------+-------------+
      | d                    | t0       | t1          |
      +----------------------+----------+-------------+
      | 0000-00-01 10:20:30x | 34:20:30 | 34:20:30.0  |
      | 0000-01-00 10:20:30x | 10:20:30 | 778:20:30.0 |
      | 0000-01-01 10:20:30x | 34:20:30 | 802:20:30.0 |
      | 0001-00-00 10:20:30x | 10:20:30 | 10:20:30.0  |
      | 0001-00-01 10:20:30x | 34:20:30 | 34:20:30.0  |
      | 0001-01-00 10:20:30x | 10:20:30 | 778:20:30.0 |
      | 0001-01-01 10:20:30x | 34:20:30 | 802:20:30.0 |
      +----------------------+----------+-------------+
      

      Notice:

      • the TIME(0) column produced even more unexpected records with 34:20:30.
      • the TIME(1) column produced something really unexpected.

      It should be fixed to make all scripts produce results as in the very first script.

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: