[MDEV-27827] TIMEDIFF() function can return wrong values Created: 2022-02-13  Updated: 2022-02-15

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.6.5
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Robert Dyas Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None
Environment:

debian buster


 Description   

If the difference between two datetime values is too large TIMEDIFF() returns the wrong value.

Example:
SELECT TIMEDIFF('2022:01:01 00:00:00', '2022:07:01 01:35:00');

This appears to be because there is an upper limit on a time value. It would be better if calling TIMEDIFF() on two datetimes that overflows this value caused an error so the wrong values are not accidently used.

 Comments   
Comment by Alice Sherepa [ 2022-02-15 ] 

MariaDB [(none)]> SELECT TIMEDIFF('2022:01:01 00:00:00', '2022:07:01 01:35:00');
 
+--------------------------------------------------------+
 
| TIMEDIFF('2022:01:01 00:00:00', '2022:07:01 01:35:00') |
 
+--------------------------------------------------------+
 
| -838:59:59                                             |
 
+--------------------------------------------------------+
 
1 row in set, 1 warning (0.001 sec)
 
 
 
Warning (Code 1292): Truncated incorrect time value: '-4345:35:00'

Comment by Robert Dyas [ 2022-02-15 ]

Yes, but this doesn't seem like the best behavior to me as bugs could easily creep in. I would think Error or NULL returned if warning is issued would be better. Easier to catch mistakes.

Generated at Thu Feb 08 09:55:53 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.