Bug #67821 GROUP BY not working in stored procedure
Submitted: 5 Dec 2012 22:37 Modified: 6 Jan 2013 17:58
Reporter: Tony Anderson Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.5.28 OS:Microsoft Windows (7 Ultimate 64bit)
Assigned to: CPU Architecture:Any
Tags: GROUP, stored procedure

[5 Dec 2012 22:37] Tony Anderson
Description:
The following query

SELECT Broker
     , year(MM_YY)
     , sum(Less35)
     , sum(GreaterThan35Less45)
     , sum(GreaterThan45)
FROM
  report_survey
GROUP BY
  Broker
, year(MM_YY);

does not work correctly in a stored procedure when preceded by the code shown in  how to repeat section to populate the table it uses. It appears to return the last couple of records.

If the stored procedure just contains some simple INSERT INTO statements to populate the table the GROUP BY statement works correctly.

E.g. 
  TRUNCATE TABLE report_survey;
  INSERT INTO report_survey VALUES (0, 'AA', '2010-11-01', 1, 1, 1);
  INSERT INTO report_survey VALUES (0, 'AA', '2010-12-01', 1, 0, 1);
  INSERT INTO report_survey VALUES (0, 'AA', '2011-08-01', 3, 1, 1);
  INSERT INTO report_survey VALUES (0, 'AA', '2011-10-01', 1, 3, 1);
  INSERT INTO report_survey VALUES (0, 'BB', '2010-11-01', 1, 1, 1);
  INSERT INTO report_survey VALUES (0, 'BB', '2010-12-01', 1, 0, 1);
  INSERT INTO report_survey VALUES (0, 'BB', '2011-08-01', 3, 1, 1);
  INSERT INTO report_survey VALUES (0, 'BB', '2011-10-01', 1, 3, 1);
  INSERT INTO report_survey VALUES (0, 'CC', '2010-11-01', 1, 1, 1);
  INSERT INTO report_survey VALUES (0, 'CC', '2010-12-01', 1, 0, 1);
  INSERT INTO report_survey VALUES (0, 'CC', '2011-08-01', 3, 1, 1);
  INSERT INTO report_survey VALUES (0, 'CC', '2011-10-01', 1, 3, 1);

  SELECT Broker
       , year(MM_YY)
       , sum(Less35)
       , sum(GreaterThan35Less45)
       , sum(GreaterThan45)
  FROM
    report_survey
  GROUP BY
    Broker
  , year(MM_YY);

I can only assume the more complex code to populate the table in the stored procedure is having an affect on the GROUP BY statement.

How to repeat:
Create a suitable table with following structure,

    CREATE TABLE report_survey
    (
      Id INT(10) PRIMARY KEY AUTO_INCREMENT NOT NULL,
      Broker VARCHAR(100),
      MM_YY DATE,
      Less35 INT,
      GreaterThan35Less45 INT,
      GreaterThan45 INT
    );

Use a version of the stored procedure below to populate the report_survey table.

BEGIN 
DECLARE Less35                     INT;
  DECLARE GreaterThan35Less45        INT;
  DECLARE GreaterThan45              INT;

  DECLARE src_reference              INT;
  DECLARE submit_date_time           DATETIME;
  DECLARE survey_conducted_date_time DATETIME;
  DECLARE date_diff                  INT;
  DECLARE db_broker                  TEXT;
  DECLARE db_mm                      TEXT;
  DECLARE db_yy                      TEXT;

  DECLARE broker                     TEXT;
  DECLARE mmyy                       TEXT;

  DECLARE eof                        INT;
  DECLARE cur1 CURSOR FOR
  SELECT c.company_name AS 'broker'     -- VARCHAR(100)
       , a.src_reference                -- INT(10)
       , b.submit_date_time             -- DATETIME
       , month(b.submit_date_time) AS 'mm'    -- INT
       , year(b.submit_date_time) AS 'yy'     -- INT
       , a.survey_conducted_date_time   -- DATETIME
  FROM
    survey a
  JOIN survey_request b
  ON b.src_reference = a.src_reference
  JOIN contacts_companies c
  ON c.reference = b.broker_company_reference
  WHERE
    a.survey_conducted_date_time IS NOT NULL
  ORDER BY
    c.company_name
  , b.submit_date_time;
  DECLARE CONTINUE HANDLER FOR NOT FOUND
  SET eof = 1;

  SET Less35 = 0;
  SET GreaterThan35Less45 = 0;
  SET GreaterThan45 = 0;

  SET broker = '';
  SET mmyy = '';

  TRUNCATE TABLE report_survey;

  OPEN cur1;
  SET eof = 0;

  WHILE eof = 0
  DO
    FETCH cur1 INTO db_broker, src_reference, submit_date_time, db_mm, db_yy, survey_conducted_date_time;
    IF broker <> db_broker THEN
      SET broker = db_broker;
      SET mmyy = '';
    END IF;
    IF mmyy <> concat(db_mm, '-', db_yy) THEN
      IF mmyy <> '' THEN
        INSERT INTO report_survey VALUES (0, broker, str_to_date(concat('1-', mmyy), '%d-%m-%Y'), Less35, GreaterThan35Less45, GreaterThan45);
      END IF;
      SET mmyy = concat(db_mm, '-', db_yy);
      SET Less35 = 0;
      SET GreaterThan35Less45 = 0;
      SET GreaterThan45 = 0;
    END IF;

    SET date_diff = datediff(survey_conducted_date_time, submit_date_time);
    IF date_diff <= 35 THEN
      SET Less35 = Less35 + 1;
    END IF;
    IF date_diff > 35 AND date_diff <= 45 THEN
      SET GreaterThan35Less45 = GreaterThan35Less45 + 1;
    END IF;
    IF date_diff > 45 THEN
      SET GreaterThan45 = GreaterThan45 + 1;
    END IF;
  END WHILE;
  CLOSE cur1;

  SELECT Broker
       , year(MM_YY)
       , sum(Less35)
       , sum(GreaterThan35Less45)
       , sum(GreaterThan45)
  FROM
    report_survey
  GROUP BY
    Broker
  , year(MM_YY);

END

Suggested fix:
Nothing to suggest.
[6 Dec 2012 17:58] Sveta Smirnova
Thank you for the report.

Please provide minimal dump, necessary to demonstrate the issue, of all involved tables.
[7 Jan 2013 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".