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
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