Bug #121184 Adding an unrelated element to a mixed-type temporal IN list flips the match result of other elements

• Submitted: 28 Aug 8:45
• Reporter: Chunling Qin
• Status: Open
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 9.7.2
• OS: Any
• CPU Architecture: Any

[28 Aug 8:45] Chunling Qin

Description:
Whether a string matches a TIME constant in an IN list depends on the TYPES of the OTHER elements in the list. Adding a DATE or TIMESTAMP element — which does not itself match — causes a previously matching TIME element to stop matching:

SELECT '10:00:00' IN (TIME'10:00:00');                    -- 1  (match)
SELECT '10:00:00' IN (TIME'10:00:00', TIME'11:00:00');    -- 1  (match)
SELECT '10:00:00' IN (DATE'2024-01-01', TIME'10:00:00');  -- 0  <-- flipped!
SELECT '10:00:00' IN (TIMESTAMP'2024-01-01 10:00:00',
                      TIME'10:00:00');                    -- 0  <-- flipped!
The query semantics of one element pair is changed by the presence of an unrelated, non-matching element.

How to repeat:
SELECT '10:00:00' IN (TIME'10:00:00');                    -- 1
SELECT '10:00:00' IN (DATE'2024-01-01', TIME'10:00:00');  -- 0 (inconsistent)
SELECT CAST('10:00:00' AS DATETIME);                      -- trunk non-strict: 2010-00-00 00:00:00
SELECT '2024-01-01' IN (DATE'2024-01-01',
                        TIMESTAMP'2024-01-01 00:00:00');  -- 1 (date+ts aggregation works)
SELECT 20240101 IN (DATE'2024-01-01', TIME'10:00:00');    -- 1 (numeric works)

Suggested fix:
When aggregating mixed temporal types in an IN list to DATETIME, string LHS values that cannot be parsed as DATETIME but CAN be parsed as the type of a list element should not silently become garbage/NULL; at minimum the match of '10:00:00' against TIME'10:00:00' must not depend on the rest of the list. Consider per-element typed comparison or refusing lenient datetime parsing of time-only strings (warn + no-match with a deterministic rule).