Bug #119346 CASE expression evaluates incorrectly

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

[10 Nov 9:43] zz z

Description:
A nested CASE expression with a logically constant outcome produces an incorrect result when used for boolean evaluation in a WHERE clause. The CASE expression should always return CAST(1 AS TIME) (i.e., '00:00:01'). Consequently, the condition (... IS FALSE) should evaluate to FALSE, and the query should return no rows. However, the query incorrectly returns one row. Interestingly, when the exact same expression is moved into the SELECT list of a derived table, it is evaluated correctly, causing the second aggregate query to return the correct result of 0. This indicates a flaw in how MySQL handles CASE expressions in the WHERE clause, specifically when they involve TIME type casting and an unevaluated branch with a FLOAT type cast

How to repeat:
CREATE TABLE t111 (c1 FLOAT);
INSERT t111 () VALUES (2.8058683E38);

SELECT * FROM t111 WHERE ((((CASE WHEN TRUE THEN (CASE WHEN NOT(1) THEN ('2019' + INTERVAL '36:25' MINUTE_SECOND) ELSE CAST(1 AS TIME) END) ELSE CAST((t111.c1) AS DATETIME) END)) IS FALSE));
-- 2.8058683E38
SELECT SUM(count) FROM (SELECT (((((CASE WHEN TRUE THEN (CASE WHEN NOT(1) THEN ('2019' + INTERVAL '36:25' MINUTE_SECOND) ELSE CAST(1 AS TIME) END) ELSE CAST((t111.c1) AS DATETIME) END)) IS FALSE))) IS TRUE AS count FROM t111) AS ta_norec;
-- 0

[20 Nov 12:39] Roy Lyseng

Verified as described on 8.0, 8.4 and 9.5