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.
|