Bug #121255 UCASE() returns different results for the same non-NULL FLOAT value when the value is obtained through equivalent scalar

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.

Thank you for the bug report.

However, this is not a bug.

The queries involve implicit conversions from FLOAT values to character string, which may cause slightly different results due to different approximations in the implementations.

This doesn't seem normal because when I placed this query in another database, such as TiDB, the results were consistent.

Roy, can you expand further on your answer ?

> The queries involve implicit conversions from FLOAT values to character string, which may cause slightly different results due to different approximations in the implementations.

I understand that UCASE implicitly converts its argument from FLOAT to a string.  What I do not understand is how the same FLOAT value, converted to a string, can explain the observed difference in results, which I do not reproduce below.  Would this conversion be non-deterministic or architecture-specific, or are there different code paths that may produce different string representations of the same FLOAT value ?  For example, the output types below differ (varchar(12) vs varchar(23)) for UCASE(FLOAT) and UCASE(COALESCE(FLOAT, FLOAT)).

Note that How to repeat contains invalid SQL.

v=mysql_26.7.0
dbdeployer deploy single $v
cd ~/sandboxes/msb_${v//./_}

./use <<< "DROP DATABASE IF EXISTS test_jfg ; CREATE DATABASE test_jfg;"
./use -N test_jfg <<< "
  DROP TABLE IF EXISTS t0;
  CREATE TABLE t0 (c0 FLOAT NULL, c1 FLOAT NULL);
  INSERT INTO t0 (c0, c1) VALUES (-757242000, 1220030000);"

./use test_jfg <<< "
  SELECT UCASE((SELECT t0.c0                  WHERE 1)) AS ref0,
         UCASE((SELECT COALESCE(t0.c0, t0.c0) WHERE 1)) AS ref1;"
ERROR 1054 (42S22) at line 1: Unknown column 't0.c0' in 'field list'

If I fix and simplify above, I reproduce a loss of precision (-757242000 inserted and -757241984 returned after UCASE; and further down below shows -757242000 returned without UCASE and COALESCE), but I do not reproduce the different results (run on an AWS m6id.large; perhaps a different architecture would reproduce the reported results).

./use -N test_jfg <<< "
  SELECT UCASE((SELECT c0               FROM t0)) AS ref0,
         UCASE((SELECT COALESCE(c0, c0) FROM t0)) AS ref1;"
-757241984      -757241984

As an aside, the loss of precision is still not fully clear to me. I am not sure what I should conclude from the results below.  It is unclear to me whether the underlying stored FLOAT in c0 is -757242000 or -757241984, how the various conversions and display operations produce the different values shown below, and whether running the MySQL client on a different host/architecture than the server would lead to different results.

./use -N test_jfg <<< "
  SELECT 10, CAST(-757242000 AS FLOAT);   -- -757242000
  SELECT 11, CAST(-757242000 AS DOUBLE);  -- -757242000

  SELECT 20, CAST(-757241984 AS FLOAT);   -- -757242000
  SELECT 21, CAST(-757241984 AS DOUBLE);  -- -757241984

  SELECT 30, c0                 FROM t0;  -- -757242000
  SELECT 31, CAST(c0 AS FLOAT)  FROM t0;  -- -757242000
  SELECT 32, CAST(c0 AS DOUBLE) FROM t0;  -- -757241984
"
10      -757242000
11      -757242000
20      -757242000
21      -757241984
30      -757242000
31      -757242000
32      -757241984

Going back to types, the argument to UCASE is a FLOAT in both expressions.

./use -N test_jfg <<< "
  create temporary table foo
    SELECT (SELECT c0               FROM t0) AS ref0,
           (SELECT COALESCE(c0, c0) FROM t0) AS ref1;
  desc foo;
"
ref0    float   YES             NULL    NULL
ref1    float   YES             NULL    NULL

Interestingly, after adding UCASE, the types differ.  Perhaps this is related to the behaviour being discussed.

./use -N test_jfg <<< "
  create temporary table foo
    SELECT UCASE((SELECT c0               FROM t0)) AS ref0,
           UCASE((SELECT COALESCE(c0, c0) FROM t0)) AS ref1;
  desc foo;
"
ref0    varchar(12)     YES             NULL    NULL
ref1    varchar(23)     YES             NULL    NULL

It is interesting that UCASE(FLOAT) and UCASE(COALESCE(FLOAT, FLOAT)) produce different types, despite both expressions appearing to be FLOAT before the call to UCASE.  The same behaviour is visible when replacing UCASE() with an explicit CAST(... AS CHAR).

./use -N test_jfg <<< "
  create temporary table foo
    SELECT CAST((SELECT c0               FROM t0) AS CHAR) AS ref0,
           CAST((SELECT COALESCE(c0, c0) FROM t0) AS CHAR) AS ref1;
  desc foo;
"
ref0    varchar(12)     YES             NULL    NULL
ref1    varchar(23)     YES             NULL    NULL

So I am still missing the mechanism by which the implicit FLOAT to string conversion could lead to different results.  In particular, I do not see where two different string representations of the same value would arise.  The FLOAT to CHAR conversion leading to different VARCHAR lengths is also puzzling.

Sorry about the mistake in my previous comment: I was wrong about invalid SQL, and I can reproduce the issue.

./use <<< "DROP DATABASE IF EXISTS test_jfg ; CREATE DATABASE test_jfg;"
./use -N test_jfg <<< "
  DROP TABLE IF EXISTS t0;
  CREATE TABLE t0 (c0 FLOAT NULL, c1 FLOAT NULL);
  INSERT INTO t0 VALUES (-757242000, 1220030000);"

./use -N test_jfg <<< "
  SELECT UCASE((SELECT t0.c0                  WHERE 1)) AS ref0,
         UCASE((SELECT COALESCE(t0.c0, t0.c0) WHERE 1)) AS ref1
  FROM t0"
-757242000      -757241984

However, the varchar(12) vs varchar(23) behaviour is still puzzling.

./use -N test_jfg <<< "
  create temporary table foo
    SELECT UCASE((SELECT t0.c0                  WHERE 1)) AS ref0,
           UCASE((SELECT COALESCE(t0.c0, t0.c0) WHERE 1)) AS ref1
    FROM t0;
  desc foo;
"
ref0    varchar(12)     YES             NULL    NULL
ref1    varchar(23)     YES             NULL    NULL

./use -N test_jfg <<< "
  create temporary table foo
    SELECT (SELECT t0.c0                  WHERE 1) AS ref0,
           (SELECT COALESCE(t0.c0, t0.c0) WHERE 1) AS ref1
    FROM t0;
  desc foo;
"
ref0    float   YES             NULL    NULL
ref1    float   YES             NULL    NULL

Most of my questions still stand, in particular regarding the mechanism by which the implicit FLOAT-to-string conversion could lead to different string representations of what appears to be the same FLOAT value.

Just tracing that Bug#121261 and Bug#121265 have been marked as duplicate of this one.

Roy, I noted your comment in Bug#121265 about section B.3.4.8 (“Problems with Floating-Point Values”), I am reading this right now.

Jean-Francois,

you can also check the explanation I gave for bug#121248.

> you can also check the explanation I gave for bug#121248

Thanks Roy, I commented there.  Your comment explains why varchar(12) vs varchar(23), but I am not yet sure I agree with Not a Bug.

Let's continue the discussion in Bug#121248.  And maybe we can close this bug (Bug#121255) as a duplicate of Bug#121248.

Thank you all for the detailed discussion and technical support. These discussions have helped me better understand MySQL's behavior with FLOAT values.

Bug #121248 and Bug #121255 were reported by members of the same team. From the perspective of database testing, we conduct extensive testing on MySQL, SQLite, TiDB, CockroachDB, and other database systems.

During our testing, we found that similar inconsistencies can occur with other functions and SQL expressions involving FLOAT. We have not submitted many of these additional cases because, after seeing similar reports classified as duplicates or "Not a Bug", we did not want to create unnecessary duplicate reports.

From a user's and tester's perspective, however, if this behavior is considered expected, it is still somewhat concerning because FLOAT is a very common numeric type. The same FLOAT value producing different observable results depending on how the expression is written could potentially affect many SQL functions and use cases.

We fully understand that FLOAT is an approximate numeric type, and we are not expecting exact arithmetic. Our concern is mainly about understanding how much result variation users should reasonably expect from different but equivalent ways of expressing the same FLOAT computation.

We would therefore appreciate any guidance on the expected scope of this behavior. This would help us better distinguish normal FLOAT behavior from potential issues in our future testing.

I agree that FLOAT is a fairly common data type, however, it is rarely needed to perform implicit conversions between FLOAT and STRING, and expect an exact result. 

For that purpose, using DECIMAL is much more precise, if you can accept there is no floating exponent.

Another alternative is to use DOUBLE, as it will never be implicitly converted to FLOAT internally, if the exact conversion is a concern.

Thank you for the explanation.
However, I still believe the Float-to-String issue represents a bug rather than "Not a Bug." It might be better categorized as having a lower Severity rather than being dismissed outright. As other commenters have mentioned, my primary concern is the current Status assigned to this issue.
Thank you again for your time. Moving forward, I will be more selective with submitting low-severity issues of this nature.