Bug #1874 select max(col) into localvar ... doesn't work in SP
Submitted: 18 Nov 2003 8:16 Modified: 20 Nov 2003 9:40
Reporter: Per-Erik Martin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0 OS:Any (any)
Assigned to: Per-Erik Martin CPU Architecture:Any

[18 Nov 2003 8:16] Per-Erik Martin 
Description:
SELECT max(col) into localvar FROM table
doesn't work in an SP (while other aggregates, like SUM, does work).

The result is NULL (or 0 when converted to an int), instead of the
actual value.

How to repeat:
create table t1 (data int);
insert into t1 values (3, 1, 5, 9, 4);
create table t2 (max int);
delimiter |;
create procedure foo()
begin
  declare x int;
  select max(data) into x from t1;
  insert into t2 values (x);
  insert into t2 select max(data) from t1;
end|
delimiter ;|
call foo();
select * from t2;
[20 Nov 2003 9:12] Per-Erik Martin 
Corrections:

The aggregate that works is AVG, while MIN, MAX and SUM fail (possibly others).

In the How to repeat, the insertion should be

insert into t1 values (3), (1), (5), (9), (4);

of course.
[20 Nov 2003 9:40] Per-Erik Martin 
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html