Description:
GROUP_CONCAT function returns cached\incorrect results from the previous query when joining tables from different databases.
Suggesting S2 serious severity as server returns unreliable\incorrect results because of this bug.
How to repeat:
DROP DATABASE IF EXISTS A;
DROP DATABASE IF EXISTS B;
CREATE DATABASE A;
CREATE DATABASE B;
CREATE TABLE A.`A` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`prospect_id` VARCHAR(50) NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `prospect_id` (`prospect_id`)
)
;
CREATE TABLE B.`B` (
`uuid` VARCHAR(50) NOT NULL,
PRIMARY KEY (`uuid`))
;
INSERT INTO A.A (`id`, `prospect_id`) VALUES (1, '1');
INSERT INTO A.A (`id`, `prospect_id`) VALUES (2, '2');
INSERT INTO B.B (`uuid`) VALUES ('1');
DROP PROCEDURE IF EXISTS A.`Group_test`;
DELIMITER \\
CREATE PROCEDURE A.`Group_test`( IN `oid_` INT)
BEGIN
SELECT NOW(), oid_, GROUP_CONCAT(CONCAT('{"id": "',oid_, '","timestamp":"', NOW(), '"}'))
FROM A
INNER JOIN B.B ON B.uuid = A.prospect_id
WHERE A.id = oid_;
END\\
DELIMITER ;
USE A;
-- First call returns correct results
CALL Group_test(1);
DO SLEEP(2); -- to clearly separate timestamps
-- Second call doesn't have corresponding row in table B.B so shouldn't return any value
CALL Group_test(2);
-- But in fact returns value from PREVIOUS query!
Description: GROUP_CONCAT function returns cached\incorrect results from the previous query when joining tables from different databases. Suggesting S2 serious severity as server returns unreliable\incorrect results because of this bug. How to repeat: DROP DATABASE IF EXISTS A; DROP DATABASE IF EXISTS B; CREATE DATABASE A; CREATE DATABASE B; CREATE TABLE A.`A` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `prospect_id` VARCHAR(50) NULL DEFAULT NULL, PRIMARY KEY (`id`), INDEX `prospect_id` (`prospect_id`) ) ; CREATE TABLE B.`B` ( `uuid` VARCHAR(50) NOT NULL, PRIMARY KEY (`uuid`)) ; INSERT INTO A.A (`id`, `prospect_id`) VALUES (1, '1'); INSERT INTO A.A (`id`, `prospect_id`) VALUES (2, '2'); INSERT INTO B.B (`uuid`) VALUES ('1'); DROP PROCEDURE IF EXISTS A.`Group_test`; DELIMITER \\ CREATE PROCEDURE A.`Group_test`( IN `oid_` INT) BEGIN SELECT NOW(), oid_, GROUP_CONCAT(CONCAT('{"id": "',oid_, '","timestamp":"', NOW(), '"}')) FROM A INNER JOIN B.B ON B.uuid = A.prospect_id WHERE A.id = oid_; END\\ DELIMITER ; USE A; -- First call returns correct results CALL Group_test(1); DO SLEEP(2); -- to clearly separate timestamps -- Second call doesn't have corresponding row in table B.B so shouldn't return any value CALL Group_test(2); -- But in fact returns value from PREVIOUS query!