Description:
When the left operand of `IN` is a **non-temporal expression** (a string literal or a `VARCHAR` column)
whose text **carries a time part**, and the `IN` list contains **two or more** `DATE` or `TIME` constants,
MySQL wrongly returns `TRUE`.
The same predicate expressed in its **equivalent** form returns `FALSE`:
* the `OR`-expansion `expr = v1 OR expr = v2` → `0` (correct)
* the equivalent searched/simple `CASE` expression → `0` (correct)
* a single-element `IN` (which the optimizer rewrites to `=`) → `0` (correct)
So `expr IN (v1,…,vn)` is **not equivalent to** `expr = v1 OR … OR expr = vn`, and the result
**changes with the number of list elements**.
This also affects `NOT IN` (in the opposite direction) and therefore silently changes the rows
matched by `UPDATE` and `DELETE`.
How to repeat:
```sql
-- (1) Result depends on the number of list elements
SELECT
'2024-01-01 10:00:00' IN (DATE'2024-01-01') AS one_elem, -- 0
'2024-01-01 10:00:00' IN (DATE'2024-01-01', DATE'2025-01-01') AS two_elem; -- 1 <-- wrong
-- (2) IN is not equivalent to its OR expansion
SELECT
'2024-01-01 10:00:00' IN (DATE'2024-01-01', DATE'2025-01-01') AS in_res, -- 1 <-- wrong
('2024-01-01 10:00:00' = DATE'2024-01-01'
OR '2024-01-01 10:00:00' = DATE'2025-01-01') AS or_res; -- 0 correct
-- (3) Same defect with a TIME constant list
SELECT
'2024-01-01 10:00:00' IN (TIME'10:00:00', TIME'11:00:00') AS in_res, -- 1 <-- wrong
('2024-01-01 10:00:00' = TIME'10:00:00'
OR '2024-01-01 10:00:00' = TIME'11:00:00') AS or_res; -- 0 correct
-- (4) Reverse shape: temporal left operand, string constant list
SELECT
TIME'10:00:00' IN ('2024-01-01 10:00:00', '2025-01-01 10:00:00') AS in_res, -- 1 <-- wrong
(TIME'10:00:00' = '2024-01-01 10:00:00'
OR TIME'10:00:00' = '2025-01-01 10:00:00') AS or_res; -- 0 correct
-- (5) Real table: WHERE returns 4 rows instead of 1
DROP TABLE IF EXISTS tv;
CREATE TABLE tv (id INT PRIMARY KEY, v VARCHAR(30));
INSERT INTO tv VALUES
(1,'2024-01-01 10:00:00'),(2,'2024-01-01'),
(3,'2025-06-25 08:30:00'),(4,'2024-01-01 00:00:01');
SELECT COUNT(*) FROM tv WHERE v IN (DATE'2024-01-01', DATE'2025-06-25'); -- 4 <-- wrong
SELECT COUNT(*) FROM tv WHERE (v = DATE'2024-01-01' OR v = DATE'2025-06-25'); -- 1 correct
-- (6) NOT IN: the opposite error
SELECT id, v,
v NOT IN (DATE'2024-01-01', DATE'2025-06-25') AS notin_res, -- all 0
NOT (v = DATE'2024-01-01' OR v = DATE'2025-06-25') AS notor_res -- 1,0,1,1
FROM tv ORDER BY id;
-- (7) DML impact
DROP TABLE IF EXISTS tu;
CREATE TABLE tu (id INT PRIMARY KEY, v VARCHAR(30), flag INT DEFAULT 0);
INSERT INTO tu VALUES (1,'2024-01-01 10:00:00',0),(2,'2024-01-01',0),(3,'2025-06-25 08:30:00',0);
UPDATE tu SET flag = 1 WHERE v IN (DATE'2024-01-01', DATE'2025-06-25');
SELECT COUNT(*) FROM tu WHERE flag = 1; -- 3 <-- wrong (2 extra rows)
DELETE FROM tu WHERE v NOT IN (DATE'2024-01-01', DATE'2025-06-25');
SELECT COUNT(*) FROM tu; -- 3 <-- wrong (2 rows should be deleted)
```
## Expected result
`'2024-01-01 10:00:00'` is not equal to `DATE'2024-01-01'`
(`SELECT '2024-01-01 10:00:00' = DATE'2024-01-01'` returns `0`), therefore every `IN` above must return `0`.
`IN`, its `OR` expansion, its `CASE` form and the single-element form must all agree.
## Actual result
With **two or more** `DATE`/`TIME` constants the `IN` form returns `1`.
Only `id = 2` should match in (5), but all four rows match; note that a time part of only one second
(`'2024-01-01 00:00:01'`) is enough to trigger it, and a fractional-seconds value such as
`'2024-01-01 10:00:00.5'` triggers it too.
## Analysis
`sql/item_cmpfunc.cc`:
```cpp
bool In_vector_date::set(uint pos, Item *item) { // builds the list element
(void)item->val_date(&m_base[pos], TIME_EXACT_DATE_ONLY); // strict: rejects a non-zero time part
return current_thd->is_error() || item->null_value;
}
bool In_vector_date::find_item(Item *item) { // probes the left operand
if (m_used_size == 0) return false;
Date_val date;
if (item->val_date(&date, 0)) return false; // flags = 0: lenient, truncates the time part
return std::binary_search(m_base.begin(), m_base.begin() + m_used_size, date);
}
```
`sql/sql_time.cc:803`:
```cpp
if ((flags & TIME_EXACT_DATE_ONLY) && non_zero_time(mt)) { /* reject */ }
```
The two ends of the same comparison use **different parsing rules**. With `flags = 0` the left operand
`'2024-01-01 10:00:00'` is silently truncated to `2024-01-01` and then matches the strictly parsed list
element `DATE'2024-01-01'`. The equivalent `=` comparison promotes both sides to `DATETIME` and does
**not** truncate, hence `0`.
`In_vector_time` has the mirror problem: `set()` and `find_item()` both use the lenient `val_time()`,
which extracts the time part from a datetime string, while `expr = TIME'...'` compares as strings and
does not — again two different conversion rules for the same predicate.
Note this is the **coverage gap** left by Bug#39649755 (`91bd696d40e`): that fix changed
`return true` → `return false` for the branch where `val_date()`/`val_time()` **fails**, but did not
address the branch where the conversion **succeeds and silently discards information**. The regression
test added there covers only `'abc'` (unparsable) and `'2024-02-29'`/`'2026-06-25'` (pure dates, no time
part), so inputs carrying a time part were never exercised.
Description: When the left operand of `IN` is a **non-temporal expression** (a string literal or a `VARCHAR` column) whose text **carries a time part**, and the `IN` list contains **two or more** `DATE` or `TIME` constants, MySQL wrongly returns `TRUE`. The same predicate expressed in its **equivalent** form returns `FALSE`: * the `OR`-expansion `expr = v1 OR expr = v2` → `0` (correct) * the equivalent searched/simple `CASE` expression → `0` (correct) * a single-element `IN` (which the optimizer rewrites to `=`) → `0` (correct) So `expr IN (v1,…,vn)` is **not equivalent to** `expr = v1 OR … OR expr = vn`, and the result **changes with the number of list elements**. This also affects `NOT IN` (in the opposite direction) and therefore silently changes the rows matched by `UPDATE` and `DELETE`. How to repeat: ```sql -- (1) Result depends on the number of list elements SELECT '2024-01-01 10:00:00' IN (DATE'2024-01-01') AS one_elem, -- 0 '2024-01-01 10:00:00' IN (DATE'2024-01-01', DATE'2025-01-01') AS two_elem; -- 1 <-- wrong -- (2) IN is not equivalent to its OR expansion SELECT '2024-01-01 10:00:00' IN (DATE'2024-01-01', DATE'2025-01-01') AS in_res, -- 1 <-- wrong ('2024-01-01 10:00:00' = DATE'2024-01-01' OR '2024-01-01 10:00:00' = DATE'2025-01-01') AS or_res; -- 0 correct -- (3) Same defect with a TIME constant list SELECT '2024-01-01 10:00:00' IN (TIME'10:00:00', TIME'11:00:00') AS in_res, -- 1 <-- wrong ('2024-01-01 10:00:00' = TIME'10:00:00' OR '2024-01-01 10:00:00' = TIME'11:00:00') AS or_res; -- 0 correct -- (4) Reverse shape: temporal left operand, string constant list SELECT TIME'10:00:00' IN ('2024-01-01 10:00:00', '2025-01-01 10:00:00') AS in_res, -- 1 <-- wrong (TIME'10:00:00' = '2024-01-01 10:00:00' OR TIME'10:00:00' = '2025-01-01 10:00:00') AS or_res; -- 0 correct -- (5) Real table: WHERE returns 4 rows instead of 1 DROP TABLE IF EXISTS tv; CREATE TABLE tv (id INT PRIMARY KEY, v VARCHAR(30)); INSERT INTO tv VALUES (1,'2024-01-01 10:00:00'),(2,'2024-01-01'), (3,'2025-06-25 08:30:00'),(4,'2024-01-01 00:00:01'); SELECT COUNT(*) FROM tv WHERE v IN (DATE'2024-01-01', DATE'2025-06-25'); -- 4 <-- wrong SELECT COUNT(*) FROM tv WHERE (v = DATE'2024-01-01' OR v = DATE'2025-06-25'); -- 1 correct -- (6) NOT IN: the opposite error SELECT id, v, v NOT IN (DATE'2024-01-01', DATE'2025-06-25') AS notin_res, -- all 0 NOT (v = DATE'2024-01-01' OR v = DATE'2025-06-25') AS notor_res -- 1,0,1,1 FROM tv ORDER BY id; -- (7) DML impact DROP TABLE IF EXISTS tu; CREATE TABLE tu (id INT PRIMARY KEY, v VARCHAR(30), flag INT DEFAULT 0); INSERT INTO tu VALUES (1,'2024-01-01 10:00:00',0),(2,'2024-01-01',0),(3,'2025-06-25 08:30:00',0); UPDATE tu SET flag = 1 WHERE v IN (DATE'2024-01-01', DATE'2025-06-25'); SELECT COUNT(*) FROM tu WHERE flag = 1; -- 3 <-- wrong (2 extra rows) DELETE FROM tu WHERE v NOT IN (DATE'2024-01-01', DATE'2025-06-25'); SELECT COUNT(*) FROM tu; -- 3 <-- wrong (2 rows should be deleted) ``` ## Expected result `'2024-01-01 10:00:00'` is not equal to `DATE'2024-01-01'` (`SELECT '2024-01-01 10:00:00' = DATE'2024-01-01'` returns `0`), therefore every `IN` above must return `0`. `IN`, its `OR` expansion, its `CASE` form and the single-element form must all agree. ## Actual result With **two or more** `DATE`/`TIME` constants the `IN` form returns `1`. Only `id = 2` should match in (5), but all four rows match; note that a time part of only one second (`'2024-01-01 00:00:01'`) is enough to trigger it, and a fractional-seconds value such as `'2024-01-01 10:00:00.5'` triggers it too. ## Analysis `sql/item_cmpfunc.cc`: ```cpp bool In_vector_date::set(uint pos, Item *item) { // builds the list element (void)item->val_date(&m_base[pos], TIME_EXACT_DATE_ONLY); // strict: rejects a non-zero time part return current_thd->is_error() || item->null_value; } bool In_vector_date::find_item(Item *item) { // probes the left operand if (m_used_size == 0) return false; Date_val date; if (item->val_date(&date, 0)) return false; // flags = 0: lenient, truncates the time part return std::binary_search(m_base.begin(), m_base.begin() + m_used_size, date); } ``` `sql/sql_time.cc:803`: ```cpp if ((flags & TIME_EXACT_DATE_ONLY) && non_zero_time(mt)) { /* reject */ } ``` The two ends of the same comparison use **different parsing rules**. With `flags = 0` the left operand `'2024-01-01 10:00:00'` is silently truncated to `2024-01-01` and then matches the strictly parsed list element `DATE'2024-01-01'`. The equivalent `=` comparison promotes both sides to `DATETIME` and does **not** truncate, hence `0`. `In_vector_time` has the mirror problem: `set()` and `find_item()` both use the lenient `val_time()`, which extracts the time part from a datetime string, while `expr = TIME'...'` compares as strings and does not — again two different conversion rules for the same predicate. Note this is the **coverage gap** left by Bug#39649755 (`91bd696d40e`): that fix changed `return true` → `return false` for the branch where `val_date()`/`val_time()` **fails**, but did not address the branch where the conversion **succeeds and silently discards information**. The regression test added there covers only `'abc'` (unparsable) and `'2024-02-29'`/`'2026-06-25'` (pure dates, no time part), so inputs carrying a time part were never exercised.