Bug #118640 TIMEDIFF is not consistent with documentation

• Submitted: 11 Jul 2025 7:29
• Modified: 13 Mar 10:59
• Reporter: Roy Lyseng
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Any
• CPU Architecture: Any

[11 Jul 2025 7:29] Roy Lyseng

Description:
Quoting documentation for TIMEDIFF:

TIMEDIFF() returns expr1 − expr2 expressed as a time value. expr1 and expr2 are strings
which are converted to TIME or DATETIME expressions; these must be of the same type following
conversion. Returns NULL if expr1 or expr2 is NULL.

Thus, arguments should be either two TIME expressions, or two DATETIME expressions.
It is therefore expected that using different types of expressions should cause an exception or return NULL,
however this isn't the case.

How to repeat:
CREATE TABLE t(t2 TIME, t4 DATETIME);
INSERT INTO t VALUES('1:01:01', '2001-02-01 01:01:01');
SELECT t2, t4, TIMEDIFF(t2, t4) AS v FROM t;
SELECT TIMEDIFF('01:01:01', '2001-02-01 01:01:01');
SELECT TIMEDIFF(TIME'01:01:01', TIMESTAMP'2001-02-01 01:01:01');

Suggested fix:
Return NULL for these cases.

[13 Mar 10:59] Edward Gilmore

Posted by developer:
 
Added the following note to the MySQL Server 9.7.0 release notes:
		
TIMEDIFF did not return NULL if unsupported values were used.