Description:
When a **perfectly valid `DATE` value** — e.g. `CAST('2024-01-02 10:00:00' AS DATE)`, whose value is
`2024-01-02` — is used as an element of an `IN` constant list, MySQL treats that element as `NULL`.
Consequently the whole `IN` expression returns `NULL` instead of `1` or `0`, and `NOT IN` also returns
`NULL`, which makes `WHERE col NOT IN (...)` **silently lose its filtering effect entirely**.
How to repeat:
-- The element itself is a legal, non-NULL DATE value:
SELECT CAST('2024-01-02 10:00:00' AS DATE) AS elem_value; -- 2024-01-02
-- Two-element list: element is wrongly NULL
SELECT '2024-01-02' IN (DATE'2024-01-01',
CAST('2024-01-02 10:00:00' AS DATE)) AS in_res; -- NULL <-- wrong
SELECT ('2024-01-02' = DATE'2024-01-01'
OR '2024-01-02' = CAST('2024-01-02 10:00:00' AS DATE)) AS or_res; -- 1 correct
## Expected result
`in_res` = `1`, `notin_res` = `0`, `in_res2` = `0` (matching the `OR` expansions).
Suggested fix:
## Analysis
`sql/item_cmpfunc.cc`:
```cpp
bool In_vector_date::set(uint pos, Item *item) {
(void)item->val_date(&m_base[pos], TIME_EXACT_DATE_ONLY);
return current_thd->is_error() || item->null_value;
}
```
`sql/sql_time.cc:803`:
```cpp
if ((flags & TIME_EXACT_DATE_ONLY) && non_zero_time(mt)) { /* reject */ }
```
`CAST('2024-01-02 10:00:00' AS DATE)` is an `Item_func`. Evaluating it with
`TIME_EXACT_DATE_ONLY` traces back to the **original string** `2024-01-02 10:00:00`, sees a non-zero
time part, and **declares the conversion failed**. `set()` then reports the element as NULL via
`current_thd->is_error() || item->null_value`.
But the very same expression evaluated through the ordinary `=` path (which does not go through
`In_vector`) yields the correct `DATE` value `2024-01-02` and the comparison succeeds.
**The server produces two different values for the same expression depending on the code path** —
a self-contradiction.
The `TIME_EXACT_DATE_ONLY` flag is meaningful when validating a *raw textual* input against the DATE
domain; applying it to an expression that is **already typed as `MYSQL_TYPE_DATE`** is a second,
redundant validation that wrongly rejects valid values.
Description: When a **perfectly valid `DATE` value** — e.g. `CAST('2024-01-02 10:00:00' AS DATE)`, whose value is `2024-01-02` — is used as an element of an `IN` constant list, MySQL treats that element as `NULL`. Consequently the whole `IN` expression returns `NULL` instead of `1` or `0`, and `NOT IN` also returns `NULL`, which makes `WHERE col NOT IN (...)` **silently lose its filtering effect entirely**. How to repeat: -- The element itself is a legal, non-NULL DATE value: SELECT CAST('2024-01-02 10:00:00' AS DATE) AS elem_value; -- 2024-01-02 -- Two-element list: element is wrongly NULL SELECT '2024-01-02' IN (DATE'2024-01-01', CAST('2024-01-02 10:00:00' AS DATE)) AS in_res; -- NULL <-- wrong SELECT ('2024-01-02' = DATE'2024-01-01' OR '2024-01-02' = CAST('2024-01-02 10:00:00' AS DATE)) AS or_res; -- 1 correct ## Expected result `in_res` = `1`, `notin_res` = `0`, `in_res2` = `0` (matching the `OR` expansions). Suggested fix: ## Analysis `sql/item_cmpfunc.cc`: ```cpp bool In_vector_date::set(uint pos, Item *item) { (void)item->val_date(&m_base[pos], TIME_EXACT_DATE_ONLY); return current_thd->is_error() || item->null_value; } ``` `sql/sql_time.cc:803`: ```cpp if ((flags & TIME_EXACT_DATE_ONLY) && non_zero_time(mt)) { /* reject */ } ``` `CAST('2024-01-02 10:00:00' AS DATE)` is an `Item_func`. Evaluating it with `TIME_EXACT_DATE_ONLY` traces back to the **original string** `2024-01-02 10:00:00`, sees a non-zero time part, and **declares the conversion failed**. `set()` then reports the element as NULL via `current_thd->is_error() || item->null_value`. But the very same expression evaluated through the ordinary `=` path (which does not go through `In_vector`) yields the correct `DATE` value `2024-01-02` and the comparison succeeds. **The server produces two different values for the same expression depending on the code path** — a self-contradiction. The `TIME_EXACT_DATE_ONLY` flag is meaningful when validating a *raw textual* input against the DATE domain; applying it to an expression that is **already typed as `MYSQL_TYPE_DATE`** is a second, redundant validation that wrongly rejects valid values.