Bug #121256 CONCAT_WS() produces different string representations for the same FLOAT value returned by equivalent scalar subqueries.

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

[10 Sep 2:44] Wang Ojiken

Description:
CONCAT_WS() produces different results when its argument is the same FLOAT column value returned through two equivalent scalar subqueries.
The following scalar subqueries return the same column value for the only row in the table:
(SELECT t2.c0 WHERE 1)

(SELECT t2.c0 WHERE t2.c0 > 0)
The inserted value is 619890000, and t2.c0 > 0 evaluates to true for this row.
However, when the scalar-subquery results are passed to CONCAT_WS(), MySQL produces different string representations:
ref1       ref2
---------- ----------
619890000  619889984
The difference is 16.
The CONCAT_WS() expressions differ only in the WHERE condition of the scalar subquery:
CONCAT_WS(1, (SELECT t2.c0 WHERE 1))

CONCAT_WS(1, (SELECT t2.c0 WHERE t2.c0 > 0))
Since both scalar subqueries return t2.c0 for the same row, I would expect CONCAT_WS() to produce the same string in both cases.

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

CREATE TABLE t2 (
    c0 FLOAT
);

INSERT INTO t2 (c0)
VALUES (619890000);
Then execute:
SELECT CONCAT_WS(1, (SELECT t2.c0 WHERE 1)) AS ref1,
       CONCAT_WS(1, (SELECT t2.c0
                     WHERE t2.c0 > 0)) AS ref2
FROM t2;
Observed result:
ref1       ref2
---------- ----------
619890000  619889984
For comparison, the two scalar subqueries independently return the same column value for this row:
SELECT t2.c0 WHERE 1;

SELECT t2.c0 WHERE t2.c0 > 0;
Expected Result
Both CONCAT_WS() expressions should produce the same string:
ref1       ref2
---------- ----------
619890000  619890000
Because t2.c0 > 0 is true for the inserted value, both scalar subqueries select the same FLOAT value from the same row.
Therefore, passing their results to CONCAT_WS() should produce identical string representations.
Actual Result
MySQL returns:
ref1       ref2
---------- ----------
619890000  619889984
The two string results differ by 16.

[10 Sep 7:15] Roy Lyseng

Duplicate of bug#121255, except with a different function.

[10 Sep 7:22] Wang Ojiken

Such a query is counterintuitive because the following query "DROP TABLE IF EXISTS t2;" is executed.

CREATE TABLE t2
(
c0 FLOAT
);

INSERT INTO t2 (c0)
VALUES (619890000);

SELECT (SELECT t2.c0 WHERE 1),
(SELECT t2.c0
WHERE t2.c0 > 0),
CONCAT_WS(1, (SELECT t2.c0 WHERE 1)) AS ref1,
CONCAT_WS(1, (SELECT t2.c0
WHERE t2.c0 > 0))      AS ref2
FROM t2;
The result of the query is
619890000, 619890000, 619890000, 619889984
This means that the results at the subquery level remain the same, but they have an impact on the function.