Bug #68864 INTERVAL treats null values of text columns as float/int
Submitted: 4 Apr 2013 8:17 Modified: 4 Apr 2013 8:37
Reporter: Jaik Dean Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:5.6.10 OS:Any
Assigned to: CPU Architecture:Any
Tags: FLOAT, interval, null

[4 Apr 2013 8:17] Jaik Dean
Description:
When using the null value of a joined column as an interval for date calculations, it is cast to a float.

How to repeat:
CREATE TABLE `a` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `data` text DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `a` (`data`) VALUES (null);

SELECT DATE_ADD('2013-04-03 12:00:00', INTERVAL null SECOND) AS `duration`;
/* returns NULL as expected */

SELECT DATE_ADD('2013-04-03 12:00:00', INTERVAL `a`.`data` SECOND) AS `duration` FROM `a`;
/* returns '2013-04-03 12:00:00' */
[4 Apr 2013 8:37] MySQL Verification Team
results for 5.0, 5.1, 5.5:
--------------------------
mysql> select date_add('2013-04-03 12:00:00', interval null second) as `duration`;
+----------+
| duration |
+----------+
| NULL     |
+----------+
1 row in set (0.00 sec)

mysql> select date_add('2013-04-03 12:00:00', interval `a`.`data` second) as `duration` from `a`;
+----------+
| duration |
+----------+
| NULL     |
+----------+
1 row in set (0.00 sec)

Results for 5.6, 5.7:
----------------------
mysql> select date_add('2013-04-03 12:00:00', interval null second) as `duration`;
+----------+
| duration |
+----------+
| NULL     |
+----------+
1 row in set (0.00 sec)

mysql> select date_add('2013-04-03 12:00:00', interval `a`.`data` second) as `duration` from `a`;
+---------------------+
| duration            |
+---------------------+
| 2013-04-03 12:00:00 |
+---------------------+
1 row in set (0.00 sec)