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

• Submitted: 21 Mar 3:09
• Modified: 21 Mar 9:01
• Reporter: Moad Elfatihi
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 9.2.0, 8.0.41, 8.4.4
• OS: Any
• CPU Architecture: Any
• Tags: regression

[21 Mar 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 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 9:01] Moad Elfatihi

Hello Team,
Thanks for your reply 

The version that is not affected is 8.2.0

Best regards