Description:
On MySQL 9.7.0, an IN() predicate whose value list consists of DATE or TIME constants returns TRUE for a left-operand string that is NOT a valid temporal value and does NOT equal any list element. The same expression returns the correct FALSE on 9.6.0.
This is a regression introduced by WL#16895 "Refactor DATE handling in server" (commit 876fd7ffe0db, 2026-02-17). The new Item_func_in::resolve_type() derives a temporal_type by scanning all arguments, so a STRING_RESULT left operand against a list of temporal constants is routed into the In_vector_date / In_vector_time bisection classes (sql/item_cmpfunc.cc, ~lines 5762-5778 and 5833-5847) instead of In_vector_string. In those classes, find_item() maps the val_date()/val_time() return value directly to "match found":
sql/item_cmpfunc.cc:5048 In_vector_date::find_item: if (item->val_date(&date, 0)) return true;
sql/item_cmpfunc.cc:5028 In_vector_time::find_item: if (item->val_time(&time)) return true;
Per the documented contract (sql/item.h:2592 and 2613) val_date()/val_time() return true when the value is NULL OR a conversion error. Treating that as a successful match makes any left operand that fails temporal conversion (e.g. 'abc') spuriously match. The sibling classes correctly return false in this case (In_vector_double:5097, In_vector_decimal:5123).
The buggy lines are still present at the current 9.7 branch HEAD, so this still ships in 9.7.1.
Thank you,
Yakir Gibraltar
How to repeat:
-- 9.7.0 returns 1,1 (wrong); 9.6.0 returns 0,0 (correct):
SELECT 'abc' IN (DATE'2024-02-29', DATE'2026-06-25') AS date_bug,
'abc' IN (TIME'01:00:00', TIME'02:00:00') AS time_bug;
-- Independent oracle: IN must equal its OR-expansion. Both expressions below return 0 on
-- BOTH 9.7.0 and 9.6.0, so the two-element IN returning 1 above is impossible under correct
-- IN semantics (proves 9.7.0 is wrong without reference to 9.6.0):
SELECT ('abc'=TIME'01:00:00' OR 'abc'=TIME'02:00:00') AS or_expansion,
'abc' IN (TIME'01:00:00') AS single_element;
-- Real-world impact (silent wrong rows in a WHERE clause over a string column):
CREATE TABLE s (id INT, v VARCHAR(20));
INSERT INTO s VALUES (1,'abc'),(2,'2024-02-29'),(3,'xyz'),(4,'2026-06-25');
SELECT id, v FROM s WHERE v IN (DATE'2024-02-29', DATE'2026-06-25');
-- 9.7.0: returns ALL 4 rows (incl. 'abc' and 'xyz'); 9.6.0: returns only rows 2 and 4.
Suggested fix:
In In_vector_date::find_item and In_vector_time::find_item, return false (not true) when val_date()/val_time() reports NULL/error, matching the In_vector_double and In_vector_decimal siblings. Alternatively, avoid routing a STRING_RESULT left operand against a temporal constant list into temporal bisection in Item_func_in::resolve_type().
