MySQL Bugs: #121265: DISTINCT changes the FLOAT-to-CHAR conversion result of a correlated scalar subquery.

Bug #121265	DISTINCT changes the FLOAT-to-CHAR conversion result of a correlated scalar subquery.
Submitted:	10 Sep 11:54	Modified:	10 Sep 12:32
Reporter:	Wang Ojiken	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
MySQL produces different results for the same FLOAT value when DISTINCT is added to a query containing a correlated scalar subquery whose WHERE condition references the outer FLOAT column.

The issue is reproducible with a minimal example using FLOAT value 0.33.
Without DISTINCT, the following expression consistently converts the FLOAT value to one CHAR representation:
SELECT
    CAST((SELECT t0.c0 WHERE t0.c0 > 0) AS CHAR) AS result
FROM t0;
However, adding DISTINCT changes the resulting string:
SELECT DISTINCT
    CAST((SELECT t0.c0 WHERE t0.c0 > 0) AS CHAR) AS result
FROM t0;

The only semantic difference between the two queries is the addition of DISTINCT, whose purpose is to eliminate duplicate result rows. It should not change the value or string representation produced by the projected expression.
I performed several controls to isolate the trigger:
1.Replacing the correlated predicate t0.c0 > 0 with the constant predicate 0.33 > 0 makes the two queries produce the same result.
2.Changing the column type from FLOAT to DOUBLE makes the queries produce the same result.
3.Explicitly converting the subquery result to DOUBLE also makes the results consistent between the DISTINCT and non-DISTINCT queries.
4.The original AES_ENCRYPT() / AES_DECRYPT() testcase exhibits a corresponding observable result difference, but the issue can already be reproduced without AES functions.

These observations suggest that the problem is not simply the normal limited precision of FLOAT. The same stored FLOAT value can be converted consistently when the correlated reference is removed, when DOUBLE is used, or when the value is explicitly converted to DOUBLE. The inconsistency specifically appears when a correlated FLOAT reference is converted to CHAR under a DISTINCT projection.
Therefore, it appears that DISTINCT causes the correlated scalar subquery to take a different execution or type-conversion path, resulting in an inconsistent FLOAT-to-string representation.

How to repeat:
DROP TABLE IF EXISTS t0;

CREATE TABLE t0 (
    c0 FLOAT
);

INSERT INTO t0 (c0) VALUES (0.33);

-- Query 1: without DISTINCT
SELECT
    CAST((SELECT t0.c0 WHERE t0.c0 > 0) AS CHAR) AS result
FROM t0;

-- Query 2: with DISTINCT
SELECT DISTINCT
    CAST((SELECT t0.c0 WHERE t0.c0 > 0) AS CHAR) AS result
FROM t0;

-- Control 1: non-correlated predicate
SELECT
    CAST((SELECT t0.c0 WHERE 0.33 > 0) AS CHAR) AS result
FROM t0;

SELECT DISTINCT
    CAST((SELECT t0.c0 WHERE 0.33 > 0) AS CHAR) AS result
FROM t0;
Query 1 and Query 2 produce different results.
In contrast, Query 3 and Query 4 produce the same result.
As an additional control, changing the table definition to:
CREATE TABLE t0 (
    c0 DOUBLE
);
makes the DISTINCT and non-DISTINCT versions produce consistent results.
Likewise, converting the correlated subquery result to DOUBLE before the final conversion produces consistent results.
The issue can also be observed through the following expression:
SELECT
    AES_DECRYPT(
        AES_ENCRYPT(
            (SELECT t0.c0 WHERE t0.c0 > 0),
            'str42'
        ),
        'str42'
    )
FROM t0;
Adding DISTINCT changes the observable result of this expression as well. However, the simpler CAST(... AS CHAR) testcase above demonstrates the issue without relying on AES encryption behavior.

I kindly ask the Technical Support Team to carefully examine the specific reproduction and control experiments rather than treating the two cases as identical. If this behavior is expected in MySQL, I would also appreciate a reference to the relevant documentation or an explanation of the expected behavior, as I would like to better understand MySQL's semantics.

This is a duplicate of bug#121255.

The semantics of the operations are all the same, however the precision may vary based on the internal algorithm carried out.

The chapter Floating-Point Types (Approximate Value) - FLOAT, DOUBLE notes:

Because floating-point values are approximate and not stored as exact values, attempts to treat them
as exact in comparisons may lead to problems. They are also subject to platform or implementation
dependencies. For more information, see Section B.3.4.8, “Problems with Floating-Point Values”.

The above also has implications for implicit and explicit cast to a character string.

In particular, FLOAT values have more precision issues than DOUBLE, because many internal operations are carried out in double precision, and thus a FLOAT value necessarily has to be converted, with some implications to the precision.