Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5(EOL), 10.0(EOL), 10.1(EOL)
-
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
-
MDEV-8658 DATE(zerofill_column) and DATE(COALESCE(zerofill_column)) return different results
- Closed