Bug #105725 Query result is not expected

• Submitted: 27 Nov 2021 7:07
• Modified: 27 Nov 2021 8:31
• Reporter: ni zhang
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 5.7, 8.0, 5.7.36, 8.0.27
• OS: Any
• CPU Architecture: Any

[27 Nov 2021 7:07] ni zhang

Description:
query1:
select * from t_time where time_col in('23:23:59.000000');

query2:
select * from t_time where time_col in('23:23:59.000000',null);

suppose query1 and query2 get the same query result. But query2 get the empty result.

How to repeat:
drop table if exists t_time;
create table t_time(time_col time);
insert into t_time select '23:23:59.000000';
insert into t_time select '23:23:59.000000';

select * from t_time where time_col in('23:23:59.000000');
select * from t_time where time_col in('23:23:59.000000',null);

[27 Nov 2021 8:31] MySQL Verification Team

Hello ni zhang,

Thank you for the report and test case.
Verified as described.

regards,
Umesh