MySQL Bugs: #114181: MySQL Crashes on query with row_number over order by concat with cast to char

Bug #114181	MySQL Crashes on query with row_number over order by concat with cast to char
Submitted:	1 Mar 2024 9:43	Modified:	1 Mar 2024 11:45
Reporter:	Giveme Jdk	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.31	OS:	Windows (Server 2022 Standard (21H2))
Assigned to:		CPU Architecture:	x86 (Xeon Gold 6242 CPU * 2)
Tags:	cast, char, character, collation concat, concat, crash, order by, OVER, row_number, SET

Description:
When executing a SELECT query that includes a ROW_NUMBER() function with an ORDER BY clause that concatenates a VARCHAR column (user_login) and an INT column (object_owner) after casting it to CHAR, MySQL crashes. 

This issue arises under a specific scenario where the character set for the connection is not set to 'utf8mb4' explicitly (using SET NAMES). The crash does not occur if the character set is explicitly set to 'utf8mb4' at the start of the session or if the object_owner is explicitly converted to CHAR with the 'utf8mb4' character set within the CONCAT function. This suggests that the crash may be related to character set handling within the CONCAT function when mixing character sets.

How to repeat:
1. Create a table named test with the structure provided below and insert the sample data into it:

CREATE TABLE IF NOT EXISTS `test` (
  `n_code` int NOT NULL DEFAULT '0',
  `user_login` varchar(50) DEFAULT NULL,
  `object_owner` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `test` (`n_code`, `user_login`, `object_owner`) VALUES
	(310532, 'tm31099', 1415),
	(310533, 'tm31099', 1415);

2. Execute the problematic SELECT query without setting the character set to 'utf8mb4' at the start of the session:

SELECT 
    ROW_NUMBER() OVER(ORDER BY CONCAT(user_login, '-', CAST(object_owner AS CHAR)) ASC) AS will_not_work,
    CAST(object_owner AS CHAR CHARACTER SET UTF8MB4),
    CONCAT(user_login, '-', CAST(object_owner AS CHAR)),
    CONCAT(user_login, '-', object_owner)
FROM test
GROUP BY object_owner, user_login
ORDER BY 1;

Observe that the query causes a crash or fails to execute properly.

Changing the cast to include the character set will fix this.
    CAST(object_owner AS CHAR CHARACTER SET UTF8MB4)

You can also set the character set to 'utf8mb4' at the beginning of the session using SET NAMES 'utf8mb4'; and observe that the query executes without issues.

Suggested fix:
If this crash is due to lack of configuration, I would expect an error message, not a complete crash.

If this crash is due to it being unable to automatically deduce the charset or collation, then I don't know what would fix this or how to proceed with finding the issue.

updated tags

Hi Mr. Jdk,

Thank you so much for your bug report.

However, we can not repeat the crash with bug-fix release 8.0.36. We tried with your test case as in the text and with your table dump.

We ran both queries, the one that does not work for your and the one that does work and we got 100 % the same results:

will_not_work	CAST(object_owner AS CHAR CHARACTER SET UTF8MB4)	CONCAT(user_login, '-', CAST(object_owner AS CHAR))	CONCAT(user_login, '-', object_owner)
1	1415	tm31099-1415	tm31099-1415
will_work	CAST(object_owner AS CHAR CHARACTER SET UTF8MB4)	CONCAT(user_login, '-', CAST(object_owner AS CHAR))	CONCAT(user_login, '-', object_owner)
1	1415	tm31099-1415	tm31099-1415

Hence, all you need to do is upgrade to 8.0.36.

Can't repeat.