Description:
I have encountered a scenario where a simple SELECT query returns a result, but a logically equivalent query using a derived table returns an empty set.
The issue involves a BIGINT column (with an index) and a comparison against a FLOAT value in an IN (...) clause.
Data: Table t0 has column c0 (BIGINT) with value 1.
Predicate: WHERE c0 IN (0.9)
Expected Behavior: Since 1 is not equal to 0.9, the result should be empty.
The direct query returns the row (Incorrect). It seems the optimizer performs an unsafe cast of 0.9 to 1 for the index lookup.
The derived table query returns an empty set (Correct).
Cross-Database Comparison:
I have verified this behavior on other database engines. MariaDB and TiDB behave correctly and consistently:
MariaDB: Both queries return an empty set.
TiDB: Both queries return an empty set.
MySQL is the only one returning a row for the direct query, indicating a bug in the optimizer/index usage.
----------------------------------------------------------------------------
mysql> SELECT t0.c0 AS ref0 FROM t0 WHERE TRUE AND ((t0.c0) IN (IFNULL(1.1, NULL)));-- cardinality: 1
+------+
| ref0 |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT ref0 FROM (SELECT t0.c0 AS ref0, TRUE AS ref1, (t0.c0) IN (IFNULL(1.1, NULL)) AS ref2 FROM t0) AS s WHERE ref1 AND ref2;-- cardinality: 0
Empty set (0.00 sec)
mysql>
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 9.6.0 |
+-----------+
1 row in set (0.01 sec)
----------------------------------------------------------------------------
How to repeat:
DROP DATABASE IF EXISTS database0;
CREATE DATABASE database0;
USE database0;
CREATE TABLE t0(c0 BIGINT);
CREATE TABLE t1(c0 FLOAT);
CREATE INDEX i0 ON t0(c0 ASC);
INSERT INTO t1(c0) VALUES(3);
INSERT INTO t0(c0) VALUES(-2);
UPDATE t0 SET c0=(t0.c0) NOT IN (TRUE);
SELECT t0.c0 AS ref0 FROM t0 WHERE TRUE AND ((t0.c0) IN (IFNULL(1.1, NULL)));-- cardinality: 1
SELECT ref0 FROM (SELECT t0.c0 AS ref0, TRUE AS ref1, (t0.c0) IN (IFNULL(1.1, NULL)) AS ref2 FROM t0) AS s WHERE ref1 AND ref2;-- cardinality: 0\n;
Description: I have encountered a scenario where a simple SELECT query returns a result, but a logically equivalent query using a derived table returns an empty set. The issue involves a BIGINT column (with an index) and a comparison against a FLOAT value in an IN (...) clause. Data: Table t0 has column c0 (BIGINT) with value 1. Predicate: WHERE c0 IN (0.9) Expected Behavior: Since 1 is not equal to 0.9, the result should be empty. The direct query returns the row (Incorrect). It seems the optimizer performs an unsafe cast of 0.9 to 1 for the index lookup. The derived table query returns an empty set (Correct). Cross-Database Comparison: I have verified this behavior on other database engines. MariaDB and TiDB behave correctly and consistently: MariaDB: Both queries return an empty set. TiDB: Both queries return an empty set. MySQL is the only one returning a row for the direct query, indicating a bug in the optimizer/index usage. ---------------------------------------------------------------------------- mysql> SELECT t0.c0 AS ref0 FROM t0 WHERE TRUE AND ((t0.c0) IN (IFNULL(1.1, NULL)));-- cardinality: 1 +------+ | ref0 | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> mysql> SELECT ref0 FROM (SELECT t0.c0 AS ref0, TRUE AS ref1, (t0.c0) IN (IFNULL(1.1, NULL)) AS ref2 FROM t0) AS s WHERE ref1 AND ref2;-- cardinality: 0 Empty set (0.00 sec) mysql> mysql> SELECT VERSION(); +-----------+ | VERSION() | +-----------+ | 9.6.0 | +-----------+ 1 row in set (0.01 sec) ---------------------------------------------------------------------------- How to repeat: DROP DATABASE IF EXISTS database0; CREATE DATABASE database0; USE database0; CREATE TABLE t0(c0 BIGINT); CREATE TABLE t1(c0 FLOAT); CREATE INDEX i0 ON t0(c0 ASC); INSERT INTO t1(c0) VALUES(3); INSERT INTO t0(c0) VALUES(-2); UPDATE t0 SET c0=(t0.c0) NOT IN (TRUE); SELECT t0.c0 AS ref0 FROM t0 WHERE TRUE AND ((t0.c0) IN (IFNULL(1.1, NULL)));-- cardinality: 1 SELECT ref0 FROM (SELECT t0.c0 AS ref0, TRUE AS ref1, (t0.c0) IN (IFNULL(1.1, NULL)) AS ref2 FROM t0) AS s WHERE ref1 AND ref2;-- cardinality: 0\n;