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
• Status: Verified
• Category: MySQL Server: Data Types
• Severity: S2 (Serious)
• Version: 5.6.10
• OS: Any
• 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)