Description:
A query with a WHERE clause containing a complex IF function and multiple type conversions (INT -> DATETIME -> DECIMAL) should produce a deterministic result. Logically, the IF expression should always evaluate to 0, making the WHERE condition TRUE and returning one row. However, the actual behavior depends on whether a UNIQUE index exists on the column. When the column has no UNIQUE index, the query incorrectly returns an empty set. After adding a UNIQUE index to the column, the same query returns the correct result. This indicates that the MySQL optimizer chooses a faulty execution plan for the non-indexed table, which fails to correctly evaluate the complex expression
How to repeat:
drop TABLE if EXISTS t85;
CREATE TABLE t85 (c1 INT);
INSERT t85 () VALUES (0);
SELECT * FROM t85 WHERE (IF(t85.c1 LIKE '', 2, CAST(CAST(0 AS DATETIME) AS DECIMAL(15))) = ANY (SELECT t85.c1 FROM t85));
-- empty set
drop TABLE if EXISTS t85;
CREATE TABLE t85 (c1 INT UNIQUE);
INSERT t85 () VALUES (0);
SELECT * FROM t85 WHERE (IF(t85.c1 LIKE '', 2, CAST(CAST(0 AS DATETIME) AS DECIMAL(15))) = ANY (SELECT t85.c1 FROM t85));
-- 0
