Description:
he behavior of the IN predicate is incorrect in a WHERE clause when used to compare the result of a float-returning mathematical function (COS()) against a TINYINT (INT1) column. Logically, the result of COS(39) (approx. 0.79) is not equal to the column value of 0, so the IN expression should be FALSE, and no rows should be returned. However, the first query incorrectly returns a row, indicating the WHERE clause evaluated the expression to TRUE. In contrast, when the exact same expression is moved to the SELECT list of a derived table, it is correctly evaluated as FALSE, leading to the aggregate query returning 0
How to repeat:
CREATE TABLE t326 (c1 INT1, KEY (c1));
INSERT t326 () VALUES (0);
SELECT 0 FROM t326 WHERE (COS(39) IN (t326.c1));
-- 0
SELECT SUM(count) FROM (SELECT ((COS(39) IN (t326.c1))) IS TRUE AS count FROM t326) AS ta_norec;
-- 0
