| Bug #118390 | When the time type is compared with the datetime type, the returned result is error | ||
|---|---|---|---|
| Submitted: | 8 Jun 8:04 | Modified: | 10 Jun 11:11 |
| Reporter: | Alice Alice | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 8.0.41 | OS: | Linux |
| Assigned to: | CPU Architecture: | x86 | |
[9 Jun 5:20]
MySQL Verification Team
Hello Alice Alice, Thank you for the report and feedback. regards, Umesh
[10 Jun 11:11]
Roy Lyseng
Posted by developer: This is not a bug. strcmp() converts its arguments to string values before comparison. IF analyzes its arguments and sees that both are temporal, thus it returns a compatible common temporal type, which is DATETIME. In order to convert the TIME values to DATETIME, the current date is added. To ensure that values are preserved, cast them to character strings in the IF clause.
Description: When the time type is compared with the datetime type, the returned result is error in col3. mysql> drop table if exists tt; Query OK, 0 rows affected (0.01 sec) mysql> create table tt(time_col time, datetime_col datetime); Query OK, 0 rows affected (0.02 sec) mysql> insert into tt values("08:59:59.000000","2001-05-09 00:00:00.000000"),("09:09:59.000000", "2001-03-02 00:00:00.000000"),("10:59:59.000000","2001-02-01 00:00:00.000000"); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT col1,col2,if(strcmp(col1,col2), col1, col2) as col3 FROM (SELECT time_col col1,datetime_col col2 FROM tt GROUP BY 1,2 order by 1,2) a; +----------+---------------------+---------------------+ | col1 | col2 | col3 | +----------+---------------------+---------------------+ | 08:59:59 | 2001-05-09 00:00:00 | 2025-06-08 08:59:59 | | 09:09:59 | 2001-03-02 00:00:00 | 2025-06-08 09:09:59 | | 10:59:59 | 2001-02-01 00:00:00 | 2025-06-08 10:59:59 | +----------+---------------------+---------------------+ 3 rows in set (0.00 sec) mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.41 | +-----------+ 1 row in set (0.01 sec) How to repeat: drop table if exists tt; create table tt(time_col time, datetime_col datetime); insert into tt values("08:59:59.000000","2001-05-09 00:00:00.000000"),("09:09:59.000000", "2001-03-02 00:00:00.000000"),("10:59:59.000000","2001-02-01 00:00:00.000000"); SELECT col1,col2,if(strcmp(col1,col2), col1, col2) as col3 FROM (SELECT time_col col1,datetime_col col2 FROM tt GROUP BY 1,2 order by 1,2) a;