Bug #21720 SELECT <group func> INTO @var .. GROUP BY gives wrong result
Submitted: 18 Aug 2006 12:33 Modified: 14 Sep 2006 20:07
Reporter: [ name withheld ] (Basic Quality Contributor) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.24 OS:Microsoft Windows (WinXP sp2)
Assigned to: Assigned Account CPU Architecture:Any

[18 Aug 2006 12:33] [ name withheld ]
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
[18 Aug 2006 14:08] Tonci Grgin
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
[7 Sep 2006 16:55] Tomash Brechko
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;
[14 Sep 2006 20:07] Iggy Galarza
This is a duplicate of bug#20836 which has a Patch submitted for approval.