Description:
mysql> SELECT s FROM v WHERE s IN ('abc'); -- ['abc'] ✓
+------+
| s |
+------+
| abc |
+------+
1 row in set (0.00 sec)
mysql> SELECT s FROM v WHERE s IN ('abc', DATE'2024-01-01'); -- ['2024-01-01'] ← the 'abc' row vanishes!
+------------+
| s |
+------------+
| 2024-01-01 |
+------------+
1 row in set, 2 warnings (0.00 sec)
mysql> SELECT s FROM v WHERE s = 'abc' OR s = DATE'2024-01-01'; -- ['2024-01-01','abc'] ✓ (OR form correct)
+------------+
| s |
+------------+
| abc |
| 2024-01-01 |
+------------+
2 rows in set (0.00 sec)
Path split: the subquery IN form (UNION derived table) correctly matches 'abc' — the same IN semantics via the enumeration path is wrong and via the subquery path is right.
Mechanism: In_vector type aggregation is hijacked wholesale by the DATE element (the entire comparison vector is DATE-ized); the other elements (strings/numbers) fail conversion to DATE and silently never match.
Impact: an IN list containing one date literal (a common pattern) silently drops rows for all other values, with no warning.
How to repeat:
CREATE TABLE v (s VARCHAR(20));
INSERT INTO v VALUES ('abc'), ('2024-01-01');
SELECT s FROM v WHERE s IN ('abc'); -- ['abc'] ✓
SELECT s FROM v WHERE s IN ('abc', DATE'2024-01-01'); -- ['2024-01-01'] ← the 'abc' row vanishes!
SELECT s FROM v WHERE s = 'abc' OR s = DATE'2024-01-01'; -- ['2024-01-01','abc'] ✓ (OR form correct)
Description: mysql> SELECT s FROM v WHERE s IN ('abc'); -- ['abc'] ✓ +------+ | s | +------+ | abc | +------+ 1 row in set (0.00 sec) mysql> SELECT s FROM v WHERE s IN ('abc', DATE'2024-01-01'); -- ['2024-01-01'] ← the 'abc' row vanishes! +------------+ | s | +------------+ | 2024-01-01 | +------------+ 1 row in set, 2 warnings (0.00 sec) mysql> SELECT s FROM v WHERE s = 'abc' OR s = DATE'2024-01-01'; -- ['2024-01-01','abc'] ✓ (OR form correct) +------------+ | s | +------------+ | abc | | 2024-01-01 | +------------+ 2 rows in set (0.00 sec) Path split: the subquery IN form (UNION derived table) correctly matches 'abc' — the same IN semantics via the enumeration path is wrong and via the subquery path is right. Mechanism: In_vector type aggregation is hijacked wholesale by the DATE element (the entire comparison vector is DATE-ized); the other elements (strings/numbers) fail conversion to DATE and silently never match. Impact: an IN list containing one date literal (a common pattern) silently drops rows for all other values, with no warning. How to repeat: CREATE TABLE v (s VARCHAR(20)); INSERT INTO v VALUES ('abc'), ('2024-01-01'); SELECT s FROM v WHERE s IN ('abc'); -- ['abc'] ✓ SELECT s FROM v WHERE s IN ('abc', DATE'2024-01-01'); -- ['2024-01-01'] ← the 'abc' row vanishes! SELECT s FROM v WHERE s = 'abc' OR s = DATE'2024-01-01'; -- ['2024-01-01','abc'] ✓ (OR form correct)