| Bug #71322 | DATEDIFF Returns Erroneously In SELECT IF Query | ||
|---|---|---|---|
| Submitted: | 9 Jan 2014 5:21 | Modified: | 12 Nov 2019 22:40 |
| Reporter: | Michael McNally | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.1.44-community, 5.1.73,5.5.35, 5.6.15, 5.7.3 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | DATEDIFF, IF | ||
[9 Jan 2014 11:12]
Valeriy Kravchuk
It seems that all versions are affected:
mysql> SELECT
-> TIMEDIFF(DATE_ADD(NOW(), INTERVAL 1 HOUR), NOW()) AS `A
`,
-> IF(FALSE, NOW(), TIMEDIFF(DATE_ADD(NOW(), INTERVAL 1 HOUR), NOW())) AS `B
`;
+----------+---------------------+
| A | B |
+----------+---------------------+
| 01:00:00 | 2014-01-09 01:00:00 |
+----------+---------------------+
1 row in set (0.03 sec)
mysql> SELECT
-> TIMEDIFF(DATE_ADD(NOW(), INTERVAL 1 HOUR), NOW()) AS `A`
,
-> IF(FALSE, NULL, TIMEDIFF(DATE_ADD(NOW(), INTERVAL 1 HOUR), NOW())) AS `B`
;
+----------+----------+
| A | B |
+----------+----------+
| 01:00:00 | 01:00:00 |
+----------+----------+
1 row in set (0.00 sec)
mysql> select version();
+------------+
| version() |
+------------+
| 5.6.15-log |
+------------+
1 row in set (0.00 sec)
[9 Jan 2014 11:21]
MySQL Verification Team
Hello Michael, Thank you for the report. Verified as described. Thanks, Umesh
[12 Nov 2019 22:40]
Roy Lyseng
Posted by developer: This is not a bug. In the IF statement, NOW() returns type DATETIME, and TIMEDIFF returns type TIME. However, the returned type of the IF is the aggregated type of those two types, which is DATETIME. If a TIME return type is wanted from the IF, use the TIME() function with NOW() as argument to return a TIME value, like this: SELECT TIMEDIFF(DATE_ADD(NOW(), INTERVAL 1 HOUR), NOW()) AS `A`, IF(FALSE, TIME(NOW()), TIMEDIFF(DATE_ADD(NOW(), INTERVAL 1 HOUR), NOW())) AS `B`
Description: In a MySQL query, I am trying to select either a DATEDIFF() or NOW() based on an IF() condition. In comparison to the result of selecting the DATEDIFF inside versus outside of the IF, the resultant behavior of MySQL is inconsistent. How to repeat: This query run in phpMyAdmin has some strange results. The values should be identical, but they are not. SELECT TIMEDIFF(DATE_ADD(NOW(), INTERVAL 1 HOUR), NOW()) AS `A`, IF(FALSE, NOW(), TIMEDIFF(DATE_ADD(NOW(), INTERVAL 1 HOUR), NOW())) AS `B` A ----------------- | B ----------------- 01:00:00 ---------- | 2001-00-00 00:00:00 If I try this query, changing NOW() to NULL in the first option of the IF condition, it shows the proper result for the TIMEDIFF. Why is this? SELECT TIMEDIFF(DATE_ADD(NOW(), INTERVAL 1 HOUR), NOW()) AS `A`, IF(FALSE, NULL, TIMEDIFF(DATE_ADD(NOW(), INTERVAL 1 HOUR), NOW())) AS `B` A ----------------- | B ----------------- 01:00:00 ---------- | 01:00:00 So then my question is: how is NOW() versus NULL affecting the IF return? Suggested fix: This appears to work fine, but does not solve the IF (TRUE, DATEDIFF, NOW()) issue. SELECT TIMEDIFF(DATE_ADD(NOW(), INTERVAL 1 HOUR), NOW()) AS `A`, CASE WHEN TRUE THEN TIMEDIFF(DATE_ADD(NOW(), INTERVAL 1 HOUR), NOW()) END AS `B` A ----------------- | B ----------------- 01:00:00 ---------- | 01:00:00