Bug #112102 The ifnull function query result is not as expected

• Submitted: 18 Aug 2023 1:48
• Modified: 20 Aug 2023 12:35
• Reporter: Sakurajima Mai
• Status: Not a Bug
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Any
• CPU Architecture: Any

[18 Aug 2023 1:48] Sakurajima Mai

Description:
create table t1(date_col date DEFAULT NULL,time_col time DEFAULT NULL);
insert into t1 values('1000-01-01','00:00:00.000000');
mysql> select ifnull(time_col,date_col) from t1;
+---------------------------+
| ifnull(time_col,date_col) |
+---------------------------+
| 2023-08-18 00:00:00       |
+---------------------------+
1 row in set (0.00 sec)

expect result is:1000-01-01 00:00:00

How to repeat:
create table t1(date_col date DEFAULT NULL,time_col time DEFAULT NULL);
insert into t1 values('1000-01-01','00:00:00.000000');
mysql> select ifnull(time_col,date_col) from t1;
+---------------------------+
| ifnull(time_col,date_col) |
+---------------------------+
| 2023-08-18 00:00:00       |
+---------------------------+
1 row in set (0.00 sec)

expect result is:1000-01-01 00:00:00

Suggested fix:
select ifnull(time_col,date_col) from t1;
expect result is:1000-01-01 00:00:00

[18 Aug 2023 13:16] MySQL Verification Team

Hi Mr. Mai,

Thank you for your bug report.

We have managed to repeat the behaviour that you reported.

We first ran a SELECT on the row and then the SELECT with IFNULL() function. We have got this:

date_col	time_col
1000-01-01	00:00:00

ifnull(time_col,date_col)
2023-08-18 00:00:00

We have repeated your results with latest 8.0 and 8.1. This is definitely wrong behaviour.

This is now a verified bug report for the latest 8.0 and 8.1.

[20 Aug 2023 12:35] Roy Lyseng

Posted by developer:
 
This is correct behavior.
When IFNULL is called with two temporal arguments,
the result type is calculated as a common temporal type.
When the two arguments are TIME and DATE, the common type is DATETIME.
Thus the return value is the time value extended with
the value of CURRENT_DATE.

If you want the result as a character string, try this statement:

  select ifnull(cast(time_col as char),date_col) from t1;

When one argument is character string, the result is also a character string.