MySQL Bugs: #21720: SELECT <group func> INTO @var .. GROUP BY gives wrong result

Bug #21720	SELECT <group func> INTO @var .. GROUP BY gives wrong result
Submitted:	18 Aug 2006 14:33	Modified:	14 Sep 2006 22:07
Reporter:	[ name withheld ] (Basic Quality Contributor)
Status:	Duplicate
Category:	Server	Severity:	S2 (Serious)
Version:	5.0.24	OS:	Microsoft Windows (WinXP sp2)
Assigned to:	Iggy Galarza	Target Version:

Description:
Hi, I've a select statement and a stored procedure which should return the same results
but do not.
See the "how to repeat" section for details.

How to repeat:
First of all the version:
mysql> select version();
+---------------------+
| version()           |
+---------------------+
| 5.0.24-community-nt |
+---------------------+
1 row in set (0.00 sec)

The table structure:
DROP TABLE IF EXISTS `test`.`bunch_o_data`;
CREATE TABLE  `test`.`bunch_o_data` (
  `sample` float default '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Data for the table is 1, 2, 2, 3, 3, 3, 3, 3, 3, 5 (sorry, don't have the inserts at
hand)

I'm running this select:
mysql> SELECT COUNT(*), sample
    -> FROM test.bunch_o_data GROUP BY sample ORDER BY COUNT(*) DESC LIMIT 1;
+----------+--------+
| COUNT(*) | sample |
+----------+--------+
|        6 |      3 |
+----------+--------+
1 row in set (0.00 sec)
to find out the mode, result is correct.
Now I run this stored proc:
DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`mode` $$
CREATE PROCEDURE `test`.`mode` (col_name varchar(255), tab_name varchar(255), schema_name
varchar(255))

BEGIN

set @xquery = concat('SELECT COUNT(*), ', col_name,' into @mode, @field FROM ',
schema_name, '.', tab_name,' GROUP BY ', col_name,' ORDER BY COUNT(*) DESC LIMIT 1;');

PREPARE `x_query` FROM @xquery;
EXECUTE `x_query`;
DEALLOCATE PREPARE `x_query`;

select @mode;

END $$

DELIMITER ;

And the result is:
mysql> call mode('sample', 'bunch_o_data', 'test');
+-------+
| @mode |
+-------+
| 0     |
+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Which is not what I'd expect, now the strangest part, if I run:
mysql> SELECT COUNT(*), sample
    -> into @mode, @field
    -> FROM test.bunch_o_data GROUP BY sample ORDER BY COUNT(*) DESC LIMIT 1;
Query OK, 1 row affected (0.00 sec)

mysql> select @mode;
+-------+
| @mode |
+-------+
| 0     |
+-------+
1 row in set (0.00 sec)

I get the same (wrong) result!!!
Please help

Suggested fix:
Don't know

Hello and thanks for your bug report.
Verified as described by reporter on Win XP SP2
>mysqld-max-nt.exe --standalone --console --log
060818 15:21:50  InnoDB: Started; log sequence number 0 45775
060818 15:21:50 [Note] mysqld-max-nt.exe: ready for connections.
Version: '5.0.24-log'  socket: ''  port: 3306  Source distribution

Changing bug title, as the bug has nothing to do with stored procedures, as "the strangest
part" of "how to repeat" section above suggests.

The minimal example would be (and the same with AVG(), SUM(), ...):

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (i INT);
INSERT INTO t1 VALUES (1), (1);

SELECT COUNT(i)
FROM t1 GROUP BY i;
#+----------+
#| COUNT(i) |
#+----------+
#|        2 | <= Correct
#+----------+

SELECT COUNT(i)
INTO @count
FROM t1 GROUP BY i;
SELECT @count;
#+--------+
#| @count |
#+--------+
#| 0      | <= Wrong
#+--------+

DROP TABLE t1;

This is a duplicate of bug#20836 which has a Patch submitted for approval.