Description:
MySQL returns an incorrect result for an IN expression involving a CONCAT() expression containing a DECIMAL literal and a scalar subquery.
The following query returns 1 on the affected version:
DROP TABLE IF EXISTS t0;
CREATE TABLE t0
(
c1 DECIMAL
);
INSERT INTO t0 (c1)
VALUES (1);
SELECT 1 IN
("-1", CONCAT(1.0, (SELECT t0.c1 FROM t0))) AS r1
FROM t0;
The expected result is 0.
The scalar subquery returns the DECIMAL value 1, and CONCAT() produces the string value '1.01'. Therefore, the comparison is effectively equivalent to checking whether 1 is equal to either -1 or 1.01.
Neither comparison should be true:
1 = -1 -> FALSE
1 = 1.01 -> FALSE
Therefore:
1 IN ('-1', '1.01') -> 0
However, MySQL returns:
r1
1
This appears to indicate an unexpected type-conversion or comparison behavior in the evaluation of IN when the list contains the result of CONCAT() involving a scalar subquery.
Notably, this testcase does not involve a FLOAT column or a floating-point value. Both 1.0 and t0.c1 are DECIMAL values, so the unexpected result does not appear to be attributable to floating-point precision.
How to repeat:
DROP TABLE IF EXISTS t0;
CREATE TABLE t0
(
c1 DECIMAL
);
INSERT INTO t0 (c1)
VALUES (1);
SELECT 1 IN
("-1", CONCAT(1.0, (SELECT t0.c1 FROM t0))) AS r1
FROM t0;
Actual result:
r1
1
Expected result:
r1
0
For comparison, the intermediate expression can be evaluated independently:
SELECT CONCAT(1.0, (SELECT t0.c1 FROM t0));
which produces:
1.01
Thus, the IN expression should evaluate to false.
Description: MySQL returns an incorrect result for an IN expression involving a CONCAT() expression containing a DECIMAL literal and a scalar subquery. The following query returns 1 on the affected version: DROP TABLE IF EXISTS t0; CREATE TABLE t0 ( c1 DECIMAL ); INSERT INTO t0 (c1) VALUES (1); SELECT 1 IN ("-1", CONCAT(1.0, (SELECT t0.c1 FROM t0))) AS r1 FROM t0; The expected result is 0. The scalar subquery returns the DECIMAL value 1, and CONCAT() produces the string value '1.01'. Therefore, the comparison is effectively equivalent to checking whether 1 is equal to either -1 or 1.01. Neither comparison should be true: 1 = -1 -> FALSE 1 = 1.01 -> FALSE Therefore: 1 IN ('-1', '1.01') -> 0 However, MySQL returns: r1 1 This appears to indicate an unexpected type-conversion or comparison behavior in the evaluation of IN when the list contains the result of CONCAT() involving a scalar subquery. Notably, this testcase does not involve a FLOAT column or a floating-point value. Both 1.0 and t0.c1 are DECIMAL values, so the unexpected result does not appear to be attributable to floating-point precision. How to repeat: DROP TABLE IF EXISTS t0; CREATE TABLE t0 ( c1 DECIMAL ); INSERT INTO t0 (c1) VALUES (1); SELECT 1 IN ("-1", CONCAT(1.0, (SELECT t0.c1 FROM t0))) AS r1 FROM t0; Actual result: r1 1 Expected result: r1 0 For comparison, the intermediate expression can be evaluated independently: SELECT CONCAT(1.0, (SELECT t0.c1 FROM t0)); which produces: 1.01 Thus, the IN expression should evaluate to false.