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);
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);