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

TIME(int_zerofill_column) returns a wrong result

        Details

        • Sprint:
          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.

            Gliffy Diagrams

              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

                  Hide
                  Permalink
                  bar Alexander Barkov added a comment -

                  Not repeatable in MySQL-5.7.8

                  Show
                  bar Alexander Barkov added a comment - Not repeatable in MySQL-5.7.8

                    People

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

                      Dates

                      • Created:
                        Updated:
                        Resolved:

                        Agile