Bug #117765 Query result may be wrong if using compound order by inside GROUP_CONCAT

Submitted: 21 Mar 2025 3:09 Modified: 21 Mar 2025 9:01
Reporter: Moad Elfatihi Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:9.2.0, 8.0.41, 8.4.4 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[21 Mar 2025 3:09] Moad Elfatihi
Description:
Hello MySql Team,

The following query that use group_concat does not order rows by DESC :

Result           = 5,6,7,13,5

Expected Result  = 5,13,7,6,5 

I tested against 8.4 version and I have had the expected result 5,13,7,6,5.

The query = select group_concat(theString 
			        order by											       
                                        case when (identifier) is null then 0 
                                        else 1 end,
                                identifier desc separator ','
                                 ) 
	     from Entity_Of_Basics ;

How to repeat:
CREATE TABLE Entity_Of_Basics (
    identifier INT PRIMARY KEY AUTO_INCREMENT,  
    theString VARCHAR(255)             
);
INSERT INTO Entity_Of_Basics ( theString) VALUES
('5'),  -- id = 1
('6'), -- id = 2
('7'),  -- id = 3
('13'),  -- id = 4
('5');  -- id = 5
[21 Mar 2025 7:14] MySQL Verification Team
Hello Moad Elfatihi,

Thank you for the report and test case.
Observed that issue seen from 8.038+.

Can you please confirm which 8.4.x version you checked and is not affected? Thank you.

regards,
Umesh
[21 Mar 2025 9:01] Moad Elfatihi
Hello Team,
Thanks for your reply 

The version that is not affected is 8.2.0

Best regards

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.048 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.