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'.
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'.