Bug #121258 UPPER(COALESCE(c0, c0)) produces a different result from the original non-NULL FLOAT ZEROFILL value.

• Submitted: 10 Sep 2:47
• Modified: 10 Sep 7:33
• Reporter: Wang Ojiken
• Status: Duplicate
• Category: MySQL Server
• Severity: S3 (Non-critical)
• OS: Any
• CPU Architecture: Any

[10 Sep 2:47] Wang Ojiken

Description:
MySQL produces an unexpected result when UPPER() is applied to COALESCE(c0, c0) where c0 is a non-NULL FLOAT ZEROFILL column.
For a non-NULL value,:
COALESCE(c0, c0)
should return the value of c0.
In the following test case, both c0 and COALESCE(c0, c0) are displayed as:
0.33323
However, applying UPPER() to the COALESCE() expression produces:
0.33322998881340027
Thus, wrapping an expression whose displayed value is unchanged in UPPER() causes a significant change in its resulting numeric/string representation.

How to repeat:
Run the following statements on MySQL Community Server 26.7.0:
DROP TABLE IF EXISTS t0;

CREATE TABLE t0 (
    c0 FLOAT ZEROFILL
);

INSERT INTO t0 (c0)
VALUES (0.33323);
Then execute:
SELECT
    t0.c0 AS r0,
    COALESCE(t0.c0, t0.c0) AS r1,
    UPPER(COALESCE(t0.c0, t0.c0)) AS r2
FROM t0;
Observed result:
r0       r1       r2
-------  -------  --------------------
0.33323  0.33323  0.33322998881340027
Expected Result
Since t0.c0 is non-NULL,:
COALESCE(t0.c0, t0.c0)
should be equivalent to:
t0.c0
Therefore, applying UPPER() to the COALESCE() expression should not unexpectedly change the representation of the value.
The result should remain consistent with the original value:
r0       r1       r2
-------  -------  -------
0.33323  0.33323  0.33323
Actual Result
MySQL returns:
r0       r1       r2
-------  -------  --------------------
0.33323  0.33323  0.33322998881340027
The intermediate COALESCE() result is displayed as 0.33323, but applying UPPER() to that expression exposes a different representation:
0.33322998881340027

[10 Sep 7:24] Wang Ojiken

The results of executing different subqueries separately are consistent, but when placed in a function, the results are different. This transformation is not reasonable, and it is in line with normal expectations in other peer databases. Only in MySQL is it different.

[10 Sep 7:33] Roy Lyseng

Duplicate of bug#121255.

The only change from the original bug is using a different function that causes a different implicit cast to be performed. The use of ZEROFILL does not affect the result.

It is also not a reasonable cause that other database systems have slightly deviating implementations.