Description:
QUOTE() produces different results for two expressions that should return the same non-NULL FLOAT value.
The table contains a single non-NULL FLOAT value:
152155000
The following query produces different results:
SELECT QUOTE((SELECT t0.c0 WHERE 152155000)) AS ref0,
QUOTE((SELECT t0.c0
WHERE COALESCE(t0.c0, 152155000))) AS ref1
FROM t0;
The observed result is:
ref0 ref1
---------- ----------
152155000 152155008
In this test case, t0.c0 is non-NULL. Therefore:
COALESCE(t0.c0, 152155000)
should evaluate to t0.c0, without using the fallback value.
Consequently, the two scalar subqueries should select the same FLOAT value, and passing those values to QUOTE() should produce the same string representation.
Instead, QUOTE() produces 152155000 for the direct expression and 152155008 for the expression involving COALESCE().
How to repeat:
Run the following statements on MySQL Community Server 26.7.0:
DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (
c0 FLOAT
);
INSERT INTO t0 (c0)
VALUES (152155000);
Then execute:
SELECT QUOTE((SELECT t0.c0 WHERE 152155000)) AS ref0,
QUOTE((SELECT t0.c0
WHERE COALESCE(t0.c0, 152155000))) AS ref1
FROM t0;
Observed result:
ref0 ref1
---------- ----------
152155000 152155008
Expected Result
Both expressions should produce the same result:
ref0 ref1
---------- ----------
152155000 152155000
Since t0.c0 is non-NULL, COALESCE(t0.c0, 152155000) should return the value of t0.c0.
Therefore, the value passed to QUOTE() should be the same in both cases.
Actual Result
MySQL returns:
ref0 ref1
---------- ----------
152155000 152155008
The two string representations differ by 8.
Suggested fix:
We conducted tests on other databases (such as TiDB, etc.), and the results were in line with our expectations.
Description: QUOTE() produces different results for two expressions that should return the same non-NULL FLOAT value. The table contains a single non-NULL FLOAT value: 152155000 The following query produces different results: SELECT QUOTE((SELECT t0.c0 WHERE 152155000)) AS ref0, QUOTE((SELECT t0.c0 WHERE COALESCE(t0.c0, 152155000))) AS ref1 FROM t0; The observed result is: ref0 ref1 ---------- ---------- 152155000 152155008 In this test case, t0.c0 is non-NULL. Therefore: COALESCE(t0.c0, 152155000) should evaluate to t0.c0, without using the fallback value. Consequently, the two scalar subqueries should select the same FLOAT value, and passing those values to QUOTE() should produce the same string representation. Instead, QUOTE() produces 152155000 for the direct expression and 152155008 for the expression involving COALESCE(). How to repeat: Run the following statements on MySQL Community Server 26.7.0: DROP TABLE IF EXISTS t0; CREATE TABLE t0 ( c0 FLOAT ); INSERT INTO t0 (c0) VALUES (152155000); Then execute: SELECT QUOTE((SELECT t0.c0 WHERE 152155000)) AS ref0, QUOTE((SELECT t0.c0 WHERE COALESCE(t0.c0, 152155000))) AS ref1 FROM t0; Observed result: ref0 ref1 ---------- ---------- 152155000 152155008 Expected Result Both expressions should produce the same result: ref0 ref1 ---------- ---------- 152155000 152155000 Since t0.c0 is non-NULL, COALESCE(t0.c0, 152155000) should return the value of t0.c0. Therefore, the value passed to QUOTE() should be the same in both cases. Actual Result MySQL returns: ref0 ref1 ---------- ---------- 152155000 152155008 The two string representations differ by 8. Suggested fix: We conducted tests on other databases (such as TiDB, etc.), and the results were in line with our expectations.