| Bug #94267 | LEAST and GREATEST return wrong result when mixing integer and string values | ||
|---|---|---|---|
| Submitted: | 9 Feb 2019 10:08 | Modified: | 13 Feb 2019 17:27 |
| Reporter: | Domen Kermc | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 8.0.14, 8.0.15 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | regression | ||
[9 Feb 2019 10:21]
MySQL Verification Team
Hello Domen Kermc, Thank you for the report. regards, Umesh
[13 Feb 2019 14:46]
Paul DuBois
Posted by developer: In MySQL 8.0. Bug#83895 caused a behavior change. In https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#function_least, have changed: * If the arguments comprise a mix of numbers and strings, they are compared as numbers. To: * If the arguments comprise a mix of numbers and strings, they are compared as strings.
[13 Feb 2019 17:27]
Domen Kermc
Thank you for fixing this. Maybe it would make sense to include this backwards-incompatible behaviour in release notes? How I stumbled across this issue: when binding values to prepared statements with PHP PDO driver using PDOStatement::execute, method assumes that all values are strings and passes them as that. Consequently an issue can occur when mixing arithmetic functions and LEAST/GREATEST in SQL query. To make calculation directly in SQL, one could use expression GREATEST(LEAST(ROUND(`column` * ?), ?), ?) with values 6.666, 65535 and 1 to limit value of unsigned SMALLINT field. Because PDOStatement::execute passes numbers as string and ROUND returns numeric value, the following is the result (let's say `column` is 60000): GREATEST(LEAST(399960, '65535'), '1'). In previous version of MySQL value 399960 would be correctly reduced to 65535, but in 8.0 result is 399960 (which is out of bounds for unsigned SMALLINT). Not to mention possible combination of integer column and string value, for example when using daily cron job to limit integer column to some predefined value with UPDATE ... SET `column` = LEAST(`column`, '0'). I know that there are many workarounds (casting to integer in SQL, using PDOStatement::bindValue with PDO::PARAM_INT, calculating result using PHP functions round/min/max instead of ROUND/LEAST/GREATEST) but query that worked correctly in MySQL 5.7 (and adhered to documentation, as far as I'm aware) does not any more in 8.0, which I regard as an incompatible change.
Description: Functions LEAST and GREATEST return wrong result when entered values are mix of integers and strings. It looks like values are compared as strings instead of as numbers. Documentation for LEAST has not changed between 5.7 and 8.0. It states (in that order): * If the arguments comprise a mix of numbers and strings, they are compared as numbers. * If any argument is a nonbinary (character) string, the arguments are compared as nonbinary strings. How to repeat: Execute: SELECT LEAST('2', 10), GREATEST(2, '10') Expected result: 2 and 10 Actual result: 10 and 2 MySQL 5.7.22 returns expected result, that is 2 and 10. Both tested servers run on Microsoft Windows, but I assume this bug is OS independent.