| Bug #89979 | GREATEST/LEAST function is inconsistent with time inside mysql function | ||
|---|---|---|---|
| Submitted: | 9 Mar 2018 9:56 | Modified: | 9 Mar 2018 13:54 |
| Reporter: | Arjun Nair | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.7.20 | OS: | Any (Oracle Linux Server release 7.4) |
| Assigned to: | CPU Architecture: | Any (x86_64) | |
| Tags: | FUNCTION, GREATEST, inconsistent, LEAST, MySQL | ||
[9 Mar 2018 10:36]
Arjun Nair
Also there is third temporary fix.
3. Use COALESCE function around the arguments. That is,
set `result` = GREATEST(coalesce(`test`), `test1`);
[9 Mar 2018 13:20]
MySQL Verification Team
Hi! Thank you for your report. However, it is not a bug. The behaviour of LEAST(...) and GREATEST (...) is explained in detail in our Reference Manual, chapter 12.3. There are data types, like numeric and some string types that do not need and CAST(). However, temporal types do require a CAST(). I hope that this is clear enough.
[9 Mar 2018 13:54]
Arjun Nair
Sinisa, As you can see I have set the variable after casting.
SET `test` = cast('15:00:00' as time);
SET `test1` = cast('11:00:00' as time);
The result is 11:00:00 when
SET `result` = GREATEST(`test`, `test1`);
And the result is 15:00:00 when
SET `result` = cast(GREATEST(`test`, `test1`) as time);
Can you explain this behaviour?
[9 Mar 2018 14:28]
MySQL Verification Team
Hi Arjun, Of course I can explain the behaviour. You must cast the variables within the LEAST() and GREATEST() functions. Or to put it in different terminology, you must cast parameters, just like our manual has it documented.
[9 Mar 2018 18:09]
Alexey Kopytov
I think this has been fixed in MySQL 8.0.4 with the fix for bug #83895
[12 Mar 2018 13:11]
MySQL Verification Team
Hi Kaamos, Thank you very much for your contribution ...
Description: The function GREATEST()/LEAST() works inconsistently when used inside a function. The arguments passed to it are of type TIME. When GREATEST/LEAST is used inside a function with variables of type TIME, it returns inconsistent result. In fact it was observed that it returns the second argument always. How to repeat: Run the following function in mysql workbench. drop function if exists `new_function`; DELIMITER ;; CREATE DEFINER=`root`@`%` FUNCTION `new_function`() RETURNS time BEGIN DECLARE `test` TIME; DECLARE `test1` TIME; DECLARE `result` TIME; SET `test` = cast('15:00:00' as time); SET `test1` = cast('11:00:00' as time); SET `result` = GREATEST(`test`, `test1`); RETURN `result`; END;; DELIMITER ; select new_function(); It returns the smaller time. In fact it always returns the second argument. The same can be observed with LEAST. Suggested fix: There are two temporary solutions which works but not sure why. 1. Casting the first argument as time. That is, set `result` = GREATEST(CAST(`test` as time), `test1`); 2. Casting the whole GREATEST function as time. That is, set `result` = CAST(GREATEST(`test`, `test1`) as time);