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

TIME(int_zerofill_column) returns a wrong result

    XMLWordPrintable

Details

    • 10.1.8-3

    Description

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a BIGINT(15), b BIGINT(15) ZEROFILL);
      INSERT INTO t1 VALUES (9,9);
      SELECT TIME(a),TIME(b) FROM t1;
      SHOW WARNINGS;

      returns

      +----------+----------+
      | TIME(a)  | TIME(b)  |
      +----------+----------+
      | 00:00:09 | 00:00:00 |
      +----------+----------+
      1 row in set, 1 warning (0.00 sec)

      with a warning:

      +---------+------+---------------------------------------------------+
      | Level   | Code | Message                                           |
      +---------+------+---------------------------------------------------+
      | Warning | 1292 | Truncated incorrect time value: '000000000000009' |
      +---------+------+---------------------------------------------------+

      The expected behavior is to return '00:00:09' for both columns, with no warnings.

      The same problem is repeatable with the DECIMAL data type:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a DECIMAL(15,0), b DECIMAL(15,0) ZEROFILL);
      INSERT INTO t1 VALUES (9,9);
      SELECT TIME(a),TIME(b) FROM t1;
      SHOW WARNINGS;

      More related problems:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a BIGINT);
      INSERT INTO t1 VALUES (-9223372036854775808);
      SELECT CAST(a AS TIME), CAST(-9223372036854775808 AS TIME) FROM t1; 

      returns:

      +-----------------+------------------------------------+
      | CAST(a AS TIME) | CAST(-9223372036854775808 AS TIME) |
      +-----------------+------------------------------------+
      | NULL            | -838:59:59                         |
      +-----------------+------------------------------------+

      Notice, a field and a literal produce different results for the same value.

      Attachments

        Issue Links

          Activity

            People

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