Bug #117131 Query results don't match expectations with FIELD function in predicate.

• Submitted: 8 Jan 2025 7:52
• Modified: 9 Jan 2025 9:20
• Reporter: wang jack
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.4.1, 8.0.40
• OS: Windows (windows 11)
• CPU Architecture: x86 (x86_64)

[8 Jan 2025 7:52] wang jack

Description:
From my understanding, the first query below should return one row of data, but it is actually empty.

mysql> SELECT TRUE FROM t1 RIGHT OUTER JOIN t0 ON NULL WHERE FIELD(t0.c0, 0.0, CAST(t1.c0 AS FLOAT));
Empty set (0.01 sec)

mysql> SELECT FIELD(t0.c0, 0.0, CAST(t1.c0 AS FLOAT)) FROM t1 RIGHT OUTER JOIN t0 ON NULL;
+-----------------------------------------+
| FIELD(t0.c0, 0.0, CAST(t1.c0 AS FLOAT)) |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+
1 row in set (0.22 sec)

How to repeat:
DROP TABLE IF EXISTS t0;
DROP TABLE IF EXISTS t1;
CREATE TABLE t0(c0 NUMERIC UNSIGNED ZEROFILL , c1 BLOB(18) , c2 NUMERIC UNSIGNED);
CREATE TABLE t1 LIKE t0;
INSERT IGNORE  INTO t0 VALUES (-1757822699, 'wj', NULL);

SELECT TRUE FROM t1 RIGHT OUTER JOIN t0 ON NULL WHERE FIELD(t0.c0, 0.0, CAST(t1.c0 AS FLOAT)); -- empty
SELECT FIELD(t0.c0, 0.0, CAST(t1.c0 AS FLOAT)) FROM t1 RIGHT OUTER JOIN t0 ON NULL; -- {1}

[8 Jan 2025 9:52] MySQL Verification Team

Hello wang jack,

Thank you for the report and test case.

regards,
Umesh