Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2(EOL), 10.3(EOL)
-
None
Description
DATE_ADD
DATE_ADD() is a hybrid function. Its return data type can be TIME, DATE or DATETIME in case of a temporal argument, but when the first argument is non-temporal, its return type is set to VARCHAR. For example:
DROP TABLE IF EXISTS t1; |
CREATE TABLE t1 AS |
SELECT DATE_ADD('2001-01-01',INTERVAL 1 DAY) AS c; |
SHOW CREATE TABLE t1; |
SELECT * FROM t1; |
+-------+--------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+--------------------------------------------------------------------------------------------------------------+
|
| t1 | CREATE TABLE `t1` (
|
`c` varchar(19) CHARACTER SET utf8 DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+--------------------------------------------------------------------------------------------------------------+
|
+------------+
|
| c |
|
+------------+
|
| 2001-01-02 |
|
+------------+
|
Notice, it created a column of type varchar(19) and stored a string '2001-01-02' to it. Looks fine so far.
Now if I store the result of the same DATE_ADD function into a numeric column, I get a wrong result:
SET sql_mode=''; |
DROP TABLE IF EXISTS t2; |
CREATE TABLE t2 (c INT); |
INSERT INTO t2 SELECT DATE_ADD('2001-01-01',INTERVAL 1 DAY); |
INSERT INTO t2 VALUES ('2001-01-02'); |
SELECT * FROM t2; |
+----------+
|
| c |
|
+----------+
|
| 20010102 |
|
| 2001 |
|
+----------+
|
Notice, DATE_ADD worked as a DATE function and inserted the value 20010101, while the string literal with the same value 2001-01-01 inserted 2001. The value inserted by DATE_ADD is wrong. It should insert 2001, similar to what string literal 2001-01-01 does.
ADDTIME
The same problem is repeatable with a similar function ADDTIME:
DROP TABLE IF EXISTS t1; |
CREATE TABLE t1 AS |
SELECT ADDTIME('10:20:30',1) AS c; |
SHOW CREATE TABLE t1; |
SELECT * FROM t1; |
+-------+--------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+--------------------------------------------------------------------------------------------------------------+
|
| t1 | CREATE TABLE `t1` (
|
`c` varchar(26) CHARACTER SET utf8 DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+--------------------------------------------------------------------------------------------------------------+
|
+----------+
|
| c |
|
+----------+
|
| 10:20:31 |
|
+----------+
|
Notice, its return data type is varchar(26).
Now I insert its result to a numeric column:
SET sql_mode=''; |
DROP TABLE IF EXISTS t2; |
CREATE TABLE t2 (a INT); |
INSERT INTO t2 VALUES (ADDTIME('10:20:30',1)); |
INSERT INTO t2 VALUES ('10:20:31'); |
SELECT * FROM t2; |
+--------+
|
| a |
|
+--------+
|
| 102031 |
|
| 10 |
|
+--------+
|
Notice, ADDTIME erroneously worked as a TIME function. It should insert 10, similar to what the string literal 10:20:31 does.
Attachments
Issue Links
- relates to
-
MDEV-12514 Split Item_temporal_func::fix_length_and_dec()
-
- Closed
-