MySQL Bugs: #67752: aggregate functions return NULL in GROUP BY within stored procedure cursor

Bug #67752	aggregate functions return NULL in GROUP BY within stored procedure cursor
Submitted:	29 Nov 2012 8:16	Modified:	29 Nov 2012 17:13
Reporter:	Paul Lucassen	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.6.8rc	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	aggregate functions, cursor, stored procedure

Description:
In MySQL 5.6 (at least in 5.6.8rc) a simple cursor (in a stored procedure) defined by a GROUP BY select statement yields NULL values for the aggregate functions, where the same select statement called from the mysql client (outside of the stored procedure) yields the expected (non-NULL) values.

How to repeat:
/* feed the following into the mysql client */

---------------  start of test-script  ------------------------
drop table if exists t_lines;
create table t_lines (id int, gid int, value int);
insert into t_lines (id,gid,value) values (1,1,1);
insert into t_lines (id,gid,value) values (2,1,2);
insert into t_lines (id,gid,value) values (3,1,3);
insert into t_lines (id,gid,value) values (4,2,1);
insert into t_lines (id,gid,value) values (5,2,2);
insert into t_lines (id,gid,value) values (6,2,3);
insert into t_lines (id,gid,value) values (7,3,1);
insert into t_lines (id,gid,value) values (8,3,2);
insert into t_lines (id,gid,value) values (9,3,3);

select l.gid,max(l.value) v_max,min(l.value) v_min,sum(l.value) v_sum from t_lines l group by l.gid
;
/* 3 and 1 returned for each max and min */

DELIMITER //

DROP PROCEDURE IF EXISTS test_group_by_in_cursor
//
CREATE PROCEDURE test_group_by_in_cursor (
)
BEGIN
  DECLARE xid INT DEFAULT 0;
  DECLARE xmax INT DEFAULT 0;
  DECLARE xmin INT DEFAULT 1;
  DECLARE xsum INT DEFAULT 1;

  DECLARE found     BOOL  DEFAULT TRUE;
  DECLARE CONTINUE HANDLER FOR 1050 SET @table_already_exists := 1;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET found := FALSE;

  BEGIN
    DECLARE t_curs CURSOR FOR
      select l.gid,max(l.value) v_max,min(l.value) v_min,sum(l.value) v_sum from t_lines l group by l.gid
    ;
    SET found := TRUE;
    CREATE TEMPORARY TABLE t_result (id int,max int,min int,sum int);
    OPEN t_curs;
    REPEAT
      FETCH t_curs INTO
        xid,xmax,xmin,xsum
      ;
      IF found THEN
        INSERT INTO t_result (id,max,min,sum) values (xid,xmax,xmin,xsum);
      END IF;
      UNTIL NOT FOUND
    END REPEAT;
    CLOSE t_curs;

    SELECT * from t_result;
    /* NULL returned for each max and min */
  END;
END;
//

DELIMITER ;

CALL test_group_by_in_cursor();

----------------------- end of test script -------------------------------

It will result in the following output:

pgl@hkapgl2:~/bxmdb/SQL> mysql test < show_mysql_bug_cursor_group_by.sqlp
gid     v_max   v_min   v_sum
1       3       1       6
2       3       1       6
3       3       1       6
id      max     min     sum
1       NULL    NULL    NULL
2       NULL    NULL    NULL
3       NULL    NULL    NULL

Works as expected in 5.5.28:

...

mysql> CALL test_group_by_in_cursor();
+------+------+------+------+
| id   | max  | min  | sum  |
+------+------+------+------+
|    1 |    3 |    1 |    6 |
|    2 |    3 |    1 |    6 |
|    3 |    3 |    1 |    6 |
+------+------+------+------+
3 rows in set (0.41 sec)

Query OK, 0 rows affected (0.46 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.28    |
+-----------+
1 row in set (0.04 sec)

So this looks like a regression bug :)

Indeed, this what I get on recent 5.5.8-rc built based on latest sources available on Launchpad:

mysql> CALL test_group_by_in_cursor();
+------+------+------+------+
| id   | max  | min  | sum  |
+------+------+------+------+
|    1 | NULL | NULL | NULL |
|    2 | NULL | NULL | NULL |
|    3 | NULL | NULL | NULL |
+------+------+------+------+
3 rows in set (1.31 sec)

Query OK, 0 rows affected (1.31 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.8-rc  |
+-----------+
1 row in set (0.00 sec)

So, regression bug? Anybody?

Thank you for the report.

This is duplicate of internal bug #14740889 which was fixed in version 5.6.10