Bug #54309 Incorrect grouping on MyISAM table created inside stored procedure
Description:
A stored procedure creates a table via CREATE...SELECT, then joins that table to an existing table with a GROUP BY.
If the newly created table is MyISAM, and the procedure is called more than once, the grouping is done incorrectly.
How to repeat:
CREATE TABLE `ApplicationCategory` (
`ac_id` int,
`AppCategory` varchar(100)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `ApplicationCategory` VALUES (1,'GroupName'), (2,'GroupName');
CREATE TABLE `event` (
`Action` char(1),
`ro_id` int,
`bytes` bigint,
`ac_id` int
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `event` VALUES ('A',50,5,3);
INSERT INTO `event` VALUES ('A',40,5,2);
INSERT INTO `event` VALUES ('B',40,5,2);
INSERT INTO `event` VALUES ('B',20,5,3);
CREATE TABLE `ro` (
`id` int
) ENGINE=MyISAM;
INSERT INTO `ro` VALUES (20);
DELIMITER //
CREATE PROCEDURE `myProc`(extraTable tinyint)
BEGIN
IF (extraTable) THEN
CREATE TEMPORARY TABLE t AS
SELECT bytes, Action, ac_id FROM event JOIN ro ON ro_id = ro.id;
ELSE
CREATE TEMPORARY TABLE t AS
SELECT bytes, Action, ac_id FROM event;
END IF;
-- If storage_engine=MyISAM, groups only by a.AppCategory and returns 1 row
-- If storage_engine=InnoDB, groups correctly and returns 2 rows
SELECT
SUM(bytes) AS bytes,
a.AppCategory,
t.Action
FROM t
JOIN ApplicationCategory a USING (ac_id)
GROUP BY
a.AppCategory, t.Action;
DROP TEMPORARY TABLE t;
END //
DELIMITER ;
-- Error only happens when the procedure creates the temporary table as MyISAM.
SET storage_engine=MyISAM;
-- First call does not return any rows, which is fine, but this call is required or effect doesn't happen.
CALL myProc(1);
-- Second call shows error.
CALL myProc(0);
-- If you add 'EXPLAIN' to the two SELECT statements, the MyISAM version marks t as type 'system', but the InnoDB uses type 'ALL'.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.052 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: A stored procedure creates a table via CREATE...SELECT, then joins that table to an existing table with a GROUP BY. If the newly created table is MyISAM, and the procedure is called more than once, the grouping is done incorrectly. How to repeat: CREATE TABLE `ApplicationCategory` ( `ac_id` int, `AppCategory` varchar(100) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO `ApplicationCategory` VALUES (1,'GroupName'), (2,'GroupName'); CREATE TABLE `event` ( `Action` char(1), `ro_id` int, `bytes` bigint, `ac_id` int ) ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO `event` VALUES ('A',50,5,3); INSERT INTO `event` VALUES ('A',40,5,2); INSERT INTO `event` VALUES ('B',40,5,2); INSERT INTO `event` VALUES ('B',20,5,3); CREATE TABLE `ro` ( `id` int ) ENGINE=MyISAM; INSERT INTO `ro` VALUES (20); DELIMITER // CREATE PROCEDURE `myProc`(extraTable tinyint) BEGIN IF (extraTable) THEN CREATE TEMPORARY TABLE t AS SELECT bytes, Action, ac_id FROM event JOIN ro ON ro_id = ro.id; ELSE CREATE TEMPORARY TABLE t AS SELECT bytes, Action, ac_id FROM event; END IF; -- If storage_engine=MyISAM, groups only by a.AppCategory and returns 1 row -- If storage_engine=InnoDB, groups correctly and returns 2 rows SELECT SUM(bytes) AS bytes, a.AppCategory, t.Action FROM t JOIN ApplicationCategory a USING (ac_id) GROUP BY a.AppCategory, t.Action; DROP TEMPORARY TABLE t; END // DELIMITER ; -- Error only happens when the procedure creates the temporary table as MyISAM. SET storage_engine=MyISAM; -- First call does not return any rows, which is fine, but this call is required or effect doesn't happen. CALL myProc(1); -- Second call shows error. CALL myProc(0); -- If you add 'EXPLAIN' to the two SELECT statements, the MyISAM version marks t as type 'system', but the InnoDB uses type 'ALL'.