Bug #42009 SELECT into variable gives different results to direct SELECT

Description:
This is a test database I have been working on:
http://download.robinclarke.net/mysql_meta.sql.gz

I have been working on a procedure on that database, and found some very strange behaviour.

SELECT GROUP_CONCAT( `privilege_type` SEPARATOR ', ' ),`schema` INTO @user_privileges_table, @user_schema FROM `mysql_meta`.`user_group_privileges` m WHERE m.`user_group`='newgroup' AND m.`isa`='TABLE' GROUP BY m.`schema`,m.`user_group`;
SELECT @user_privileges_table, @user_schema;

returns a different (and IMO incorrect) result to:
SELECT GROUP_CONCAT( `privilege_type` SEPARATOR ', ' ),`schema` FROM `mysql_meta`.`user_group_privileges` m WHERE m.`user_group`='newgroup' AND m.`isa`='TABLE' GROUP BY m.`schema`,m.`user_group`;

How to repeat:
Load the database:
http://download.robinclarke.net/mysql_meta.sql.gz

SELECT GROUP_CONCAT( `privilege_type` SEPARATOR ', ' ),`schema` INTO @user_privileges_table, @user_schema FROM `mysql_meta`.`user_group_privileges` m WHERE m.`user_group`='newgroup' AND m.`isa`='TABLE' GROUP BY m.`schema`,m.`user_group`;
SELECT @user_privileges_table, @user_schema;
SELECT GROUP_CONCAT( `privilege_type` SEPARATOR ', ' ),`schema` FROM `mysql_meta`.`user_group_privileges` m WHERE m.`user_group`='newgroup' AND m.`isa`='TABLE' GROUP BY m.`schema`,m.`user_group`;

Thank you for the report.

Verified as described.

Workaround: remove UNIQUE key from table `user_group_privileges`

test case for the testsuite

Attachment: bug42009.test (application/octet-stream, text), 1.46 KiB.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/67391

2755 Ramil Kalimullin	2009-02-24
      Fix for bug#42009: SELECT into variable gives different results to direct SELECT
      
      Problem: storing "SELECT ... INTO @var ..." results in variables we used val_xxx()
      methods which returned results of the current row. 
      So, in some cases (e.g. SELECT DISTINCT, GROUP BY or HAVING) we got data
      from the first row of a new group (where we evaluate a clause) instead of
      data from the last row of the previous group.
      
      Fix: use val_xxx_result() counterparts to get proper results.
      modified:
        mysql-test/r/distinct.result
        mysql-test/r/user_var.result
        mysql-test/t/user_var.test
        sql/item_func.cc
        sql/item_func.h
        sql/sql_class.cc

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/73711

2736 Ramil Kalimullin	2009-05-10 [merge]
      Fix for bug#42009: SELECT into variable gives different results to direct SELECT
      
      Problem: storing "SELECT ... INTO @var ..." results in variables we used val_xxx()
      methods which returned results of the current row. 
      So, in some cases (e.g. SELECT DISTINCT, GROUP BY or HAVING) we got data
      from the first row of a new group (where we evaluate a clause) instead of
      data from the last row of the previous group.
      
      Fix: use val_xxx_result() counterparts to get proper results.
     @ mysql-test/r/distinct.result
        Fix for bug#42009: SELECT into variable gives different results to direct SELECT
          - results adjusted.
     @ mysql-test/r/user_var.result
        Fix for bug#42009: SELECT into variable gives different results to direct SELECT
          - test result.
     @ mysql-test/t/user_var.test
        Fix for bug#42009: SELECT into variable gives different results to direct SELECT
          - test case.
     @ sql/item_func.cc
        Fix for bug#42009: SELECT into variable gives different results to direct SELECT
          - Item_func_set_user_var::save_item_result() added to evaluate and store 
            an item's result into a user variable.
     @ sql/item_func.h
        Fix for bug#42009: SELECT into variable gives different results to direct SELECT
          - Item_func_set_user_var::save_item_result() added to evaluate and store 
            an item's result into a user variable.
     @ sql/sql_class.cc
        Fix for bug#42009: SELECT into variable gives different results to direct SELECT
          - use Item_func_set_user_var::save_item_result() to store results into user 
            variables.

Pushed into 5.0.83 (revid:joro@sun.com-20090528073529-q9b8s60vlpu28fny) (version source revid:jimw@mysql.com-20090512212033-5f2l893u3rhrsw53) (merge vers: 5.0.82) (pib:6)

Pushed into 5.1.36 (revid:joro@sun.com-20090528073639-yohsb4q1jzg7ycws) (version source revid:mats@sun.com-20090511132802-nnkiyb2huih1tklz) (merge vers: 5.1.35) (pib:6)

Noted in 5.0.83, 5.1.36 changelogs.

SELECT ... INTO @var could produce values different from SELECT ...
without the INTO clause. 

Setting report to NDI pending push into 6.0.x.

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090616183122-chjzbaa30qopdra9) (version source revid:ramil@mysql.com-20090510163506-k1tne6467bmhv941) (merge vers: 6.0.12-alpha) (pib:11)

Noted in 5.4.4 changelog.

Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.

Ignore previous comment about 5.4.2.

Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)

Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)

Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

The 5.4 fix has been pushed to 5.4.2.

Just came accross this bug on mysql 5.1.54.

The problem is still not solved when using the LIMIT clause on a query.