Description:
One of the stored procedures in our system uses group_concat to build a string that is then assigned to a global variable.
The string appears correctly the first time, but on the second execution the results are wrong, typically null or from the end of the concatenated data set.
The problem does not manifest with local variables, nor outside a store procedure.
This did not happen with 8.0.21 but does with 8.0.22
In our case we have to use the global variable, as this is then fed to a PREPARE statement. The bug breaks one of our main forms, although we have coded a workaround using an intermediary local variable.
How to repeat:
Execute the following script in MySQLWorkbench or mysql command line:
[code]
DROP TABLE IF EXISTS `TestSequence`;
CREATE TABLE `TestSequence` (
`num` VARCHAR(10) PRIMARY KEY
) ENGINE=INNODB DEFAULT CHARSET=UTF8MB4 COLLATE = UTF8MB4_BIN;
INSERT INTO `TestSequence`(`num`) VALUES
('0:ZERO'),
('1:ONE'),
('2:TWO'),
('3:THREE'),
('4:FOUR'),
('5:FIVE'),
('6:SIX'),
('7:SEVEN'),
('8:EIGHT'),
('9:NINE');
DROP PROCEDURE IF EXISTS `p_test`;
DELIMITER //
CREATE PROCEDURE `p_test`()
BEGIN
set @ss = (SELECT GROUP_CONCAT(TS.`num`)
FROM `catdb_dev`.`TestSequence` `TS`);
select @ss;
END//
DELIMITER ;
call p_test();
call p_test();
[/code]
Expected Outcome
----------------
@ss
0:ZERO,1:ONE,2:TWO,3:THREE,4:FOUR,5:FIVE,6:SIX,7:SEVEN,8:EIGHT,9:NINE
@ss
0:ZERO,1:ONE,2:TWO,3:THREE,4:FOUR,5:FIVE,6:SIX,7:SEVEN,8:EIGHT,9:NINE
Actual Outcome
--------------
@ss
0:ZERO,1:ONE,2:TWO,3:THREE,4:FOUR,5:FIVE,6:SIX,7:SEVEN,8:EIGHT,9:NINE
@ss
9:NINE,9:NINE,9:NINE,9:NINE,9:NINE,9:NINE,9:NINE,9:NINE,9:NINE,9:NINE
Workaround
----------
Fill a local variable and assign it to the global variable if needed.
Description: One of the stored procedures in our system uses group_concat to build a string that is then assigned to a global variable. The string appears correctly the first time, but on the second execution the results are wrong, typically null or from the end of the concatenated data set. The problem does not manifest with local variables, nor outside a store procedure. This did not happen with 8.0.21 but does with 8.0.22 In our case we have to use the global variable, as this is then fed to a PREPARE statement. The bug breaks one of our main forms, although we have coded a workaround using an intermediary local variable. How to repeat: Execute the following script in MySQLWorkbench or mysql command line: [code] DROP TABLE IF EXISTS `TestSequence`; CREATE TABLE `TestSequence` ( `num` VARCHAR(10) PRIMARY KEY ) ENGINE=INNODB DEFAULT CHARSET=UTF8MB4 COLLATE = UTF8MB4_BIN; INSERT INTO `TestSequence`(`num`) VALUES ('0:ZERO'), ('1:ONE'), ('2:TWO'), ('3:THREE'), ('4:FOUR'), ('5:FIVE'), ('6:SIX'), ('7:SEVEN'), ('8:EIGHT'), ('9:NINE'); DROP PROCEDURE IF EXISTS `p_test`; DELIMITER // CREATE PROCEDURE `p_test`() BEGIN set @ss = (SELECT GROUP_CONCAT(TS.`num`) FROM `catdb_dev`.`TestSequence` `TS`); select @ss; END// DELIMITER ; call p_test(); call p_test(); [/code] Expected Outcome ---------------- @ss 0:ZERO,1:ONE,2:TWO,3:THREE,4:FOUR,5:FIVE,6:SIX,7:SEVEN,8:EIGHT,9:NINE @ss 0:ZERO,1:ONE,2:TWO,3:THREE,4:FOUR,5:FIVE,6:SIX,7:SEVEN,8:EIGHT,9:NINE Actual Outcome -------------- @ss 0:ZERO,1:ONE,2:TWO,3:THREE,4:FOUR,5:FIVE,6:SIX,7:SEVEN,8:EIGHT,9:NINE @ss 9:NINE,9:NINE,9:NINE,9:NINE,9:NINE,9:NINE,9:NINE,9:NINE,9:NINE,9:NINE Workaround ---------- Fill a local variable and assign it to the global variable if needed.