Description:
UCASE() returns different results for the same non-NULL FLOAT value depending on whether a scalar subquery directly returns the column or wraps the same column in COALESCE().
The following two scalar subqueries should return the same value for the row in this test case:
(SELECT t0.c0 WHERE 1)
(SELECT COALESCE(t0.c0, t0.c0) WHERE 1)
The column t0.c0 contains a non-NULL value, so:
COALESCE(t0.c0, t0.c0)
should return exactly the same value as:
t0.c0
However, when the two results are passed to UCASE(), MySQL returns different values:
ref0 ref1
---------- ----------
-757242000 -757241984
The difference is 16, even though both expressions ultimately operate on the same non-NULL FLOAT value.
This suggests that the scalar-subquery or expression/type-conversion path may incorrectly change the representation or value of the FLOAT argument before it is evaluated by UCASE().
How to repeat:
Run the following statements on MySQL Community Server 26.7.0:
DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (
c0 FLOAT NULL,
c1 FLOAT NULL
);
INSERT INTO t0 (c0, c1)
VALUES (-757242000, 1220030000);
Then execute:
SELECT UCASE((SELECT t0.c0 WHERE 1)) AS ref0,
UCASE((SELECT COALESCE(t0.c0, t0.c0)
WHERE 1)) AS ref1
FROM t0;
Observed result:
ref0 ref1
---------- ----------
-757242000 -757241984
Expected Result
Both expressions should return the same value:
ref0 ref1
---------- ----------
-757242000 -757242000
Because t0.c0 is non-NULL, the following expressions are expected to produce the same value:
t0.c0
COALESCE(t0.c0, t0.c0)
Consequently, applying UCASE() to the results of the two equivalent scalar subqueries should not produce different numeric values.
Actual Result
MySQL returns:
ref0 ref1
---------- ----------
-757242000 -757241984
The two results differ by 16.
Description: UCASE() returns different results for the same non-NULL FLOAT value depending on whether a scalar subquery directly returns the column or wraps the same column in COALESCE(). The following two scalar subqueries should return the same value for the row in this test case: (SELECT t0.c0 WHERE 1) (SELECT COALESCE(t0.c0, t0.c0) WHERE 1) The column t0.c0 contains a non-NULL value, so: COALESCE(t0.c0, t0.c0) should return exactly the same value as: t0.c0 However, when the two results are passed to UCASE(), MySQL returns different values: ref0 ref1 ---------- ---------- -757242000 -757241984 The difference is 16, even though both expressions ultimately operate on the same non-NULL FLOAT value. This suggests that the scalar-subquery or expression/type-conversion path may incorrectly change the representation or value of the FLOAT argument before it is evaluated by UCASE(). How to repeat: Run the following statements on MySQL Community Server 26.7.0: DROP TABLE IF EXISTS t0; CREATE TABLE t0 ( c0 FLOAT NULL, c1 FLOAT NULL ); INSERT INTO t0 (c0, c1) VALUES (-757242000, 1220030000); Then execute: SELECT UCASE((SELECT t0.c0 WHERE 1)) AS ref0, UCASE((SELECT COALESCE(t0.c0, t0.c0) WHERE 1)) AS ref1 FROM t0; Observed result: ref0 ref1 ---------- ---------- -757242000 -757241984 Expected Result Both expressions should return the same value: ref0 ref1 ---------- ---------- -757242000 -757242000 Because t0.c0 is non-NULL, the following expressions are expected to produce the same value: t0.c0 COALESCE(t0.c0, t0.c0) Consequently, applying UCASE() to the results of the two equivalent scalar subqueries should not produce different numeric values. Actual Result MySQL returns: ref0 ref1 ---------- ---------- -757242000 -757241984 The two results differ by 16.