Bug #119347 IN predicate incorrectly evaluates RADIANS() and TINYINT with key

• Submitted: 10 Nov 10:43
• Modified: 20 Nov 12:47
• Reporter: zz z
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Any
• CPU Architecture: Any

[10 Nov 10:43] zz z

Description:
The behavior of the IN predicate is incorrect in a WHERE clause when used to compare the result of a float-returning function (RADIANS()) against a TINYINT column. Logically, the result of RADIANS(11) (approx. 0.19) 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 t94 (c2 TINYINT, KEY (c2));
INSERT t94 () VALUES (0);
SELECT * FROM t94 WHERE (RADIANS(11) IN (t94.c2));
-- 0 
SELECT SUM(count) FROM (SELECT ((RADIANS(11) IN (t94.c2))) IS TRUE AS count FROM t94) AS ta_norec;
-- 0

[20 Nov 12:47] Roy Lyseng

Verified as described in 8.0, 8.4 and 9.5.