John Jove added a comment - 2025-02-27 08:56 I try other integer data types like follows, in which the `BIGINT` data type returns the expected data type. CREATE TABLE t1 (c1 SMALLINT ); INSERT INTO t1 (c1) VALUES (1); SELECT c1 FROM t1 WHERE ((c1) NOT IN (FROM_DAYS(0))) NOT IN (1); -- actual: {1}, expected: {} CREATE TABLE t1 (c1 INT ); INSERT INTO t1 (c1) VALUES (1); SELECT c1 FROM t1 WHERE ((c1) NOT IN (FROM_DAYS(0))) NOT IN (1); -- actual: {1}, expected: {} CREATE TABLE t1 (c1 BIGINT ); INSERT INTO t1 (c1) VALUES (1); SELECT c1 FROM t1 WHERE ((c1) NOT IN (FROM_DAYS(0))) NOT IN (1); -- actual: {}, expected: {}

Sergei Golubchik added a comment - 2025-02-27 09:00 Already incorrect result with SELECT 1 IN ( date '0000-00-00' );

Sergei Golubchik added a comment - 2025-02-27 09:37 the last example is oversimplified, it's a different bug. It fails because Item_cache_temporal::cache_value ignores null_value that was set in the failed datetime conversion, can be fixed with --- a/sql/item.cc +++ b/sql/item.cc @@ -10637,7 +10637,7 @@ bool Item_cache_temporal::cache_value() return false; value_cached= true; value= example->val_datetime_packed_result(current_thd); - null_value_inside= null_value= example->null_value; + null_value_inside= null_value; return true; } This test create table t1 (c1 tinyint); insert into t1 (c1) values (1); select c1 in ( date '0000-00-00' ) from t1; drop table t1; fails differently. It's because Item_field::val_datetime_packed() invokes Field::val_datetime_packed() which cannot return NULL for failed datetime conversion. Item_field::get_date() is correct, though.

John Jove added a comment - 2025-02-27 09:43 Thanks for the quick response. Shall we change the bug status into confirmed?