| Bug #119365 | Incorrect NOT IN Evaluation for NULL on Indexed TEXT Column | ||
|---|---|---|---|
| Submitted: | 12 Nov 2025 3:14 | Modified: | 6 Dec 2025 22:25 |
| Reporter: | zz z | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 9.4.0 8.4.6 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[12 Nov 2025 3:17]
zz z
The first query in the test case is invalid, unnecessary and typed incorrectly
[6 Dec 2025 22:25]
Roy Lyseng
Verified as described.
Description: An expression is evaluated incorrectly when its left-hand operand for a NOT IN clause is a function chain involving STR_TO_DATE and TIMESTAMPDIFF. In this case, STR_TO_DATE operates on a NULL TEXT column, correctly propagating NULL to the TIMESTAMPDIFF function, so the result of the entire TIMESTAMPDIFF expression should be NULL. According to SQL's three-valued logic, the final result of NULL NOT IN (...) should also be NULL. How to repeat: CREATE TABLE t2295 ( c2 TEXT); CREATE TABLE t2295 ( c2 TEXT, PRIMARY KEY (c2(3))); INSERT IGNORE t2295 () VALUES (null); SELECT '' FROM t2295 WHERE (TIMESTAMPDIFF(MICROSECOND, (SUBTIME('20:33:54', '2025-11-04')), (STR_TO_DATE(t2295.c2, '%H:%i:%s'))) NOT IN (5129077)); -- empty set SELECT SUM(count) FROM (SELECT ((TIMESTAMPDIFF(MICROSECOND, (SUBTIME('20:33:54', '2025-11-04')), (STR_TO_DATE(t2295.c2, '%H:%i:%s'))) NOT IN (5129077))) IS TRUE AS count FROM t2295) AS ta_norec; -- 1