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

Storing DATETIME-alike VARCHAR data into TIME produces wrong results

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5, 10.0, 10.1, 10.2, 10.3
    • 10.3.5
    • Temporal Types
    • 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

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