MySQL Bugs: #116582: to_char+concat+to_date+time_format, time format conversion trigger crash

Bug #116582	to_char+concat+to_date+time_format, time format conversion trigger crash
Submitted:	7 Nov 2024 10:08	Modified:	7 Nov 2024 10:26
Reporter:	Suantai Rousi	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.24	OS:	Linux
Assigned to:		CPU Architecture:	x86 (x86 or arm)
Tags:	crash, time_format

Description:
select to_char(concat(to_date(test_date,'yyyy-mm-dd'), ' ', time_format(test_time,'%h:%i:%s')), 'yyyy-mm-dd hh24:mi:ss') from testtb;
The above time conversion statement will cause MySQL service crash.

How to repeat:
show create table testtb\G;
*************************** 1. row ***************************
       Table: testtb
Create Table: CREATE TABLE `testtb` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `test_time` char(6) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `test_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

 select * from testtb;
+----+-----------+---------------------+
| id | test_time | test_date           |
+----+-----------+---------------------+
|  1 | 010101    | 2013-03-22 00:00:00 |
|  2 | 031123    | 2023-03-22 00:00:00 |
|  3 | NULL      | 2023-04-22 00:00:00 |
|  4 | NULL      | NULL                |
|  5 | 112311    | NULL                |
+----+-----------+---------------------+
5 rows in set (0.00 sec)

select to_char(concat(to_date(test_date,'yyyy-mm-dd'), ' ', time_format(test_time,'%h:%i:%s')), 'yyyy-mm-dd hh24:mi:ss') from testtb;	-- Crash!!!
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 2
ERROR: 
Can't connect to the server

select * from testtb;
No connection. Trying to reconnect...
Connection id:    60
Current database: testdb

+----+-----------+---------------------+
| id | test_time | test_date           |
+----+-----------+---------------------+
|  1 | 010101    | 2013-03-22 00:00:00 |
|  2 | 031123    | 2023-03-22 00:00:00 |
|  3 | NULL      | 2023-04-22 00:00:00 |
|  4 | NULL      | NULL                |
|  5 | 112311    | NULL                |
+----+-----------+---------------------+
5 rows in set (0.01 sec)

select to_char(concat(to_date(test_date,'yyyy-mm-dd'), ' ', time_format(test_time,'%h:%i:%s')), 'yyyy-mm-dd hh24:mi:ss') from testtb where id=1;
+-------------------------------------------------------------------------------------------------------------------+
| to_char(concat(to_date(test_date,'yyyy-mm-dd'), ' ', time_format(test_time,'%h:%i:%s')), 'yyyy-mm-dd hh24:mi:ss') |
+-------------------------------------------------------------------------------------------------------------------+
| 2013-03-22 01:01:01                                                                                               |
+-------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

select to_char(concat(to_date(test_date,'yyyy-mm-dd'), ' ', time_format(test_time,'%h:%i:%s')), 'yyyy-mm-dd hh24:mi:ss') from testtb where id=2;
+-------------------------------------------------------------------------------------------------------------------+
| to_char(concat(to_date(test_date,'yyyy-mm-dd'), ' ', time_format(test_time,'%h:%i:%s')), 'yyyy-mm-dd hh24:mi:ss') |
+-------------------------------------------------------------------------------------------------------------------+
| 2023-03-22 03:11:23                                                                                               |
+-------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

select to_char(concat(to_date(test_date,'yyyy-mm-dd'), ' ', time_format(test_time,'%h:%i:%s')), 'yyyy-mm-dd hh24:mi:ss') from testtb where id=3;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 2
ERROR: 
Can't connect to the server

Hi Mr. Rousi,

Thank you for your bug report.

However, release 8.0.24 is very , very old and it is not supported any more.

Please, use release 8.0.40, which is the latest bug fix release of the version 8.0.

If you manage to get the same problem with it, please provide us with a full test case, including CREATE TABLE for each of the tables, INSERT for all rows and all queries that lead to crashing the server.

Thanks very much in advance.

Unsupported.