Bug #117245 Get incorrect result when comparing TIME type with IN value list that contains NULL

• Submitted: 20 Jan 9:58
• Modified: 20 Jan 11:27
• Reporter: Menghong Huang
• Status: Verified
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Any
• CPU Architecture: Any
• Tags: IN clause, null, time

[20 Jan 9:58] Menghong Huang

Description:
Hi, I found a bug about the IN clause with NULL value in the list. The query like `time_column IN ('09:00:00', NULL)` always get NULL result regardless of whether the other non-NULL elements exist in the "time_column". It specially has wrong result for the TIME data type. The datetime and date don't have this problem.

This problem is also existed in 8.0.37.

How to repeat:
create table t0(
`datetime_col` datetime(6) DEFAULT NULL,
`time_col` time(6) DEFAULT NULL
);
insert into t0 values ('1992-03-04 12:21:42.000000', '23:23:59.000000');

mysql> select count(*) from t0 where time_col IN ('23:23:59.000000');
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

-- as long as NULL value exist in IN clause, it returns NULL when compare with Time type
mysql> select count(*) from t0 where time_col IN ('23:23:59.000000', NULL);
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from t0 where datetime_col IN ('1992-03-04 12:21:42.000000');
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

-- DATETIME doesn't have this problem.
mysql> select count(*) from t0 where datetime_col IN ('1992-03-04 12:21:42.000000', NULL);
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

Suggested fix:
Normally, the cmp_type is adjusted to INT as TIME type is regarded as convertiable to integer. However, the NULL value cannot pass the convertibility check. Thurs, the comparision type is still STRING.

Suggest to skip the converting for NULL arg in the code block `if (field_item->field->can_be_compared_as_longlong())`  in function `Item_func_in::resolve_type`.

[20 Jan 11:27] MySQL Verification Team

Hi Mr. Huang,

Thank you for your bug report.

We tested your example with 8.0.40.

A queries of the type:

x IN (a,b,c,d...) are always resolved as :

x = a OR x = b OR x = c .......

Any expression of the type:

x = NULL

should resolve as NULL and should not be counted.

We have also tried with table where both data types had full  lengths and we have again repeated your results.

Hence, this is a bug in the version 8.0 and all higher higher versions.

Verified as reported.

However,