| Bug #120114 | Inconsistent result when using MAX(c0) inside GREATEST compared to standalone evaluation | ||
|---|---|---|---|
| Submitted: | 19 Mar 12:05 | Modified: | 19 Mar 14:31 |
| Reporter: | Wang Ojiken | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 9.6.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | expression evaluation, FLOAT, GREATEST, inconsistency, MAX | ||
[19 Mar 14:31]
Roy Lyseng
Thank you for the bug report. However, this is not a bug. With GREATEST and LEAST, float values are promoted to double. Due to the approximate nature of floating point values, the least significant part of the DOUBLE value is changed compared to the FLOAT value.
Description: The same aggregate expression MAX(c0) produces inconsistent results depending on whether it is evaluated standalone or within another expression. When selecting MAX(c0) directly, MySQL returns a rounded value. However, when MAX(c0) is used as an argument to another function (e.g., GREATEST), the result reflects the underlying floating-point representation instead. This leads to inconsistent results for semantically equivalent expressions. How to repeat: DROP TABLE IF EXISTS t0; CREATE TABLE t0 ( c0 FLOAT UNSIGNED ZEROFILL NULL ); INSERT INTO t0 (c0) VALUES (1057490000); -- Query 1 SELECT MAX(c0) FROM t0; -- Query 2 SELECT GREATEST(0, MAX(c0)) FROM t0; Expected result: Both queries should return consistent results, as GREATEST(0, x) = x when x > 0. Actual result: Query 1 returns: 1057490000 Query 2 returns: 1057489984 Suggested fix: Ensure consistent evaluation or formatting of floating-point values across expression contexts, or clarify/document the difference between display formatting and expression evaluation results.