Bug #94795 wrong results with GROUP_CONCAT
Submitted: 27 Mar 7:07 Modified: 27 Mar 9:16
Reporter: data vast Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.7.22, 5.7.25 OS:Oracle Linux (6.8)
Assigned to: CPU Architecture:x86
Tags: regression

[27 Mar 7:07] data vast
Description:
Server version: 5.7.22
mysql> SELECT * FROM branch b WHERE Branch_name IN (SELECT GROUP_CONCAT(account.Branch_name) as 'new_name'  FROM account   GROUP BY  Branch_name ORDER BY account.Branch_name);

Empty set (0.00 sec)

select with GROUP_CONCAT return wrong result when run in version 5.7.
But it is work when run in version 5.6、8.0.

How to repeat:
CREATE TABLE branch (
 Branch_name varchar(10) DEFAULT NULL,
 Branch_city varchar(10) DEFAULT NULL,
 Assert int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO branch VALUES('Perryridge','A-201',900);
INSERT INTO branch VALUES('Perryridge','A-102',400);
INSERT INTO branch VALUES('Brighton','A-215',750);
INSERT INTO branch VALUES('Brighton','A-217',750);
INSERT INTO branch VALUES('Redwood','A-222',700);
INSERT INTO branch VALUES('Redwood','A-305',350);

CREATE TABLE account (
 Balance int(11) DEFAULT NULL,
 Branch_name varchar(20) DEFAULT NULL,
 Account_number varchar(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO account VALUES(500,'Downtown','A-101');
INSERT INTO account VALUES(400,'Perryridge','A-102');
INSERT INTO account VALUES(900,'Brighton','A-201');
INSERT INTO account VALUES(700,'Redwood','A-222');
INSERT INTO account VALUES(750,'Brighton','A-217');
INSERT INTO account VALUES(700,'Mians','A-215');
INSERT INTO account VALUES(350,'RoundHill','A-305');

SELECT * FROM branch b WHERE Branch_name IN (SELECT GROUP_CONCAT(account.Branch_name) as 'new_name'  FROM account   GROUP BY  Branch_name ORDER BY account.Branch_name);
[27 Mar 9:16] Umesh Shastry
Hello!

Thank you for the report and test case.
Verified as described with 5.7.25 build.

thanks,
Umesh