Bug #54309 Incorrect grouping on MyISAM table created inside stored procedure

Submitted: 7 Jun 2010 20:45 Modified: 13 Oct 2010 12:28
Reporter: Scott Noyes Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.1.47 OS:Any
Assigned to: Assigned Account CPU Architecture:Any

[7 Jun 2010 20:45] Scott Noyes
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'.
[13 Oct 2010 12:28] Davi Arnaut
Closed as a duplicate of Bug#32868.

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.