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

TIME(int_zerofill_column) returns a wrong result

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

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-8658 DATE(zerofill_column) and DATE(COALESCE(zerofill_column)) return different results

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov created issue -
            bar Alexander Barkov made changes -
            Field Original Value New Value
            Description {code}
            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;
            {code}
            returns
            {noformat}
            +----------+----------+
            | TIME(a) | TIME(b) |
            +----------+----------+
            | 00:00:09 | 00:00:00 |
            +----------+----------+
            1 row in set, 1 warning (0.00 sec)
            {noformat}
            with a warning:
            {noformat}
            +---------+------+---------------------------------------------------+
            | Level | Code | Message |
            +---------+------+---------------------------------------------------+
            | Warning | 1292 | Truncated incorrect time value: '000000000000009' |
            +---------+------+---------------------------------------------------+
            {noformat}

            The expected behavior is to return '00:00:09' for both columns, with no warnings.
            		 {code}
            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;
            {code}
            returns
            {noformat}
            +----------+----------+
            | TIME(a) | TIME(b) |
            +----------+----------+
            | 00:00:09 | 00:00:00 |
            +----------+----------+
            1 row in set, 1 warning (0.00 sec)
            {noformat}
            with a warning:
            {noformat}
            +---------+------+---------------------------------------------------+
            | Level | Code | Message |
            +---------+------+---------------------------------------------------+
            | Warning | 1292 | Truncated incorrect time value: '000000000000009' |
            +---------+------+---------------------------------------------------+
            {noformat}

            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:
            {code}
            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;
            {code}
            bar Alexander Barkov made changes -
            Description {code}
            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;
            {code}
            returns
            {noformat}
            +----------+----------+
            | TIME(a) | TIME(b) |
            +----------+----------+
            | 00:00:09 | 00:00:00 |
            +----------+----------+
            1 row in set, 1 warning (0.00 sec)
            {noformat}
            with a warning:
            {noformat}
            +---------+------+---------------------------------------------------+
            | Level | Code | Message |
            +---------+------+---------------------------------------------------+
            | Warning | 1292 | Truncated incorrect time value: '000000000000009' |
            +---------+------+---------------------------------------------------+
            {noformat}

            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:
            {code}
            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;
            {code}
            		 {code}
            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;
            {code}
            returns
            {noformat}
            +----------+----------+
            | TIME(a) | TIME(b) |
            +----------+----------+
            | 00:00:09 | 00:00:00 |
            +----------+----------+
            1 row in set, 1 warning (0.00 sec)
            {noformat}
            with a warning:
            {noformat}
            +---------+------+---------------------------------------------------+
            | Level | Code | Message |
            +---------+------+---------------------------------------------------+
            | Warning | 1292 | Truncated incorrect time value: '000000000000009' |
            +---------+------+---------------------------------------------------+
            {noformat}

            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:
            {code}
            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;
            {code}


            More related problems:
            {code}
            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;
            {code}
            returns:
            {noformat}
            +-----------------+------------------------------------+
            | CAST(a AS TIME) | CAST(-9223372036854775808 AS TIME) |
            +-----------------+------------------------------------+
            | NULL | -838:59:59 |
            +-----------------+------------------------------------+
            {noformat}
            bar Alexander Barkov made changes -
            Description {code}
            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;
            {code}
            returns
            {noformat}
            +----------+----------+
            | TIME(a) | TIME(b) |
            +----------+----------+
            | 00:00:09 | 00:00:00 |
            +----------+----------+
            1 row in set, 1 warning (0.00 sec)
            {noformat}
            with a warning:
            {noformat}
            +---------+------+---------------------------------------------------+
            | Level | Code | Message |
            +---------+------+---------------------------------------------------+
            | Warning | 1292 | Truncated incorrect time value: '000000000000009' |
            +---------+------+---------------------------------------------------+
            {noformat}

            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:
            {code}
            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;
            {code}


            More related problems:
            {code}
            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;
            {code}
            returns:
            {noformat}
            +-----------------+------------------------------------+
            | CAST(a AS TIME) | CAST(-9223372036854775808 AS TIME) |
            +-----------------+------------------------------------+
            | NULL | -838:59:59 |
            +-----------------+------------------------------------+
            {noformat}
            		 {code}
            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;
            {code}
            returns
            {noformat}
            +----------+----------+
            | TIME(a) | TIME(b) |
            +----------+----------+
            | 00:00:09 | 00:00:00 |
            +----------+----------+
            1 row in set, 1 warning (0.00 sec)
            {noformat}
            with a warning:
            {noformat}
            +---------+------+---------------------------------------------------+
            | Level | Code | Message |
            +---------+------+---------------------------------------------------+
            | Warning | 1292 | Truncated incorrect time value: '000000000000009' |
            +---------+------+---------------------------------------------------+
            {noformat}

            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:
            {code}
            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;
            {code}


            More related problems:
            {code}
            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;
            {code}
            returns:
            {noformat}
            +-----------------+------------------------------------+
            | CAST(a AS TIME) | CAST(-9223372036854775808 AS TIME) |
            +-----------------+------------------------------------+
            | NULL | -838:59:59 |
            +-----------------+------------------------------------+
            {noformat}
            Notice, a field and a literal produce different results for the same value.
            bar Alexander Barkov made changes -
            Rank Ranked higher
            bar Alexander Barkov made changes -
            Rank Ranked higher
            bar Alexander Barkov made changes -
            Rank Ranked higher
            bar Alexander Barkov made changes -
            Sprint 10.1.8-3 [ 15 ]
            bar Alexander Barkov made changes -
            Rank Ranked lower
            bar Alexander Barkov made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            bar Alexander Barkov made changes -
            Labels upstream-not-affected
            bar Alexander Barkov added a comment -

            Not repeatable in MySQL-5.7.8

            bar Alexander Barkov added a comment - Not repeatable in MySQL-5.7.8
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Component/s Temporal Types [ 11000 ]
            Fix Version/s 10.1.8 [ 19605 ]
            Fix Version/s 10.1 [ 16100 ]
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 71167 ] MariaDB v4 [ 149500 ]

            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.