Bug #119364 Incorrect Evaluation of = ANY Subquery with NULL on Indexed DECIMAL Column

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

[12 Nov 3:01] zz z

Description:
The boolean logic of an = ANY subquery is evaluated incorrectly when it operates on an indexed DECIMAL column and the subquery's result set contains a NULL value. According to SQL's three-valued logic, the comparison 0 = ANY ({NULL, ...}) should result in UNKNOWN (NULL), which should not satisfy a WHERE condition. However, the expression is incorrectly evaluated as TRUE

How to repeat:
CREATE TABLE t487 (c2 DECIMAL, PRIMARY KEY (c2));
INSERT IGNORE t487 () VALUES ();
INSERT t487 () VALUES (1.009);
INSERT t487 () VALUES (-2124959);
INSERT t487 () VALUES (952);

SELECT * FROM t487 WHERE ((CAST(CAST(0 AS DATETIME) AS DECIMAL) / t487.c2) = ANY (SELECT t487.c2 FROM t487));
-- Returned Row Count: 3
SELECT SUM(count) FROM (SELECT (((CAST(CAST(0 AS DATETIME) AS DECIMAL) / t487.c2) = ANY (SELECT t487.c2 FROM t487))) IS TRUE AS count FROM t487) AS ta_norec;
-- 0