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}
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}