Description:
After the fix for Bug#39649755 (commit 91bd696d40e), In_vector_date/time:: find_item returns "no match" (and swallows the conversion error) when the LHS string cannot be converted to the temporal type of the IN list. However, a single-element IN is rewritten to a plain = comparison, which in strict mode raises ERROR 1525 for the very same predicate. Therefore two semantically equivalent predicates produce different outcomes depending only on the number of elements in the IN list:
-- default (strict) sql_mode:
SELECT 'abc' IN (DATE'2024-02-29', DATE'2026-06-25'); -- 0 (silent, In_vector path)
SELECT 'abc' NOT IN (DATE'2024-02-29', TIME'01:00:00');-- 1 (silent)
SELECT 'abc' NOT IN (DATE'2024-02-29');
-- ERROR 1525 (HY000): Incorrect DATE value: 'abc' <-- single element, `=` rewrite
Empty strings and binary literals as LHS trigger the same divergence ('' IN (DATE'...'), b'1' IN (DATE'...'), 0x61 IN (DATE'...')).
NULL-LHS semantics were verified correct in all shapes (returns NULL).
How to repeat:
SELECT 'abc' IN (DATE'2024-02-29', DATE'2026-06-25'); -- 0
SELECT 'abc' NOT IN (DATE'2024-02-29'); -- ERROR 1525
SELECT 'abc' NOT IN (DATE'2024-02-29', TIME'01:00:00'); -- 1
SELECT '' IN (DATE'2024-02-29'); -- ERROR 1525
SELECT b'1' IN (DATE'2024-02-29'); -- ERROR 1525
Suggested fix:
Align the two paths: either the In_vector path should propagate the strict- mode conversion error like the = path (check current_thd->is_error() after find_item() in Item_func_in::val_int()), or the = path should degrade consistently. As-is, the result of a predicate depends on an internal rewrite detail (element count), which also affects NOT IN truth tables.
© 2026, Oracle Corporation and/or its affiliates
Description: After the fix for Bug#39649755 (commit 91bd696d40e), In_vector_date/time:: find_item returns "no match" (and swallows the conversion error) when the LHS string cannot be converted to the temporal type of the IN list. However, a single-element IN is rewritten to a plain = comparison, which in strict mode raises ERROR 1525 for the very same predicate. Therefore two semantically equivalent predicates produce different outcomes depending only on the number of elements in the IN list: -- default (strict) sql_mode: SELECT 'abc' IN (DATE'2024-02-29', DATE'2026-06-25'); -- 0 (silent, In_vector path) SELECT 'abc' NOT IN (DATE'2024-02-29', TIME'01:00:00');-- 1 (silent) SELECT 'abc' NOT IN (DATE'2024-02-29'); -- ERROR 1525 (HY000): Incorrect DATE value: 'abc' <-- single element, `=` rewrite Empty strings and binary literals as LHS trigger the same divergence ('' IN (DATE'...'), b'1' IN (DATE'...'), 0x61 IN (DATE'...')). NULL-LHS semantics were verified correct in all shapes (returns NULL). How to repeat: SELECT 'abc' IN (DATE'2024-02-29', DATE'2026-06-25'); -- 0 SELECT 'abc' NOT IN (DATE'2024-02-29'); -- ERROR 1525 SELECT 'abc' NOT IN (DATE'2024-02-29', TIME'01:00:00'); -- 1 SELECT '' IN (DATE'2024-02-29'); -- ERROR 1525 SELECT b'1' IN (DATE'2024-02-29'); -- ERROR 1525 Suggested fix: Align the two paths: either the In_vector path should propagate the strict- mode conversion error like the = path (check current_thd->is_error() after find_item() in Item_func_in::val_int()), or the = path should degrade consistently. As-is, the result of a predicate depends on an internal rewrite detail (element count), which also affects NOT IN truth tables.