Bug #119354 IN subquery evaluates incorrectly when using IFNULL and CAST AS DATETIME

• Submitted: 10 Nov 15:51
• Reporter: zz z
• Status: Open
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 9.4.0 8.4.6
• OS: Any
• CPU Architecture: Any

[10 Nov 15:51] zz z

Description:
The evaluation of an IN subquery is incorrect when its left-hand operand is a complex expression composed of IFNULL and CAST(... AS DATETIME). Logically, IFNULL(CAST(0 AS DATETIME), '...') should resolve to the constant string '...', which would make the IN condition TRUE. However, when this expression is evaluated in the SELECT list of a derived table, it is incorrectly evaluated as FALSE or NULL. This causes the final SUM aggregation to return 0 instead of the expected 4. In contrast, a simple query using the equivalent constant string is handled correctly in the WHERE clause

How to repeat:
CREATE TABLE t198 (c1 LONGTEXT, c2 DECIMAL);
INSERT t198 () VALUES ('84JDYbHhwhRH1gYg7bve',162181);
INSERT t198 () VALUES ();
INSERT t198 () VALUES ();
INSERT t198 () VALUES ();
SELECT * FROM t198 WHERE ('2014-12-19 13:10:23' IN (SELECT '2014-12-19 13:10:23' FROM t198));
-- 4 ROWS
SELECT SUM(count) FROM (SELECT ((IFNULL(CAST(0 AS DATETIME), '2014-12-19 13:10:23') IN (SELECT '2014-12-19 13:10:23' FROM t198))) IS TRUE AS count FROM t198) AS ta_norec;
-- 0