Bug #42009 SELECT into variable gives different results to direct SELECT
Submitted: 10 Jan 2009 1:44 Modified: 25 Jun 2009 22:36
Reporter: Robin Clarke Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.1.30-log, 4.1, 5.0, 5.1, 6.0 bzr OS:Linux (Ubuntu 8.10 with 2.6.27-9-generic kernel)
Assigned to: Ramil Kalimullin CPU Architecture:Any
Tags: GROUP, group_concat, select into

[10 Jan 2009 1:44] Robin Clarke 
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`;
[11 Jan 2009 14:45] Sveta Smirnova 
Thank you for the report.

Verified as described.

Workaround: remove UNIQUE key from table `user_group_privileges`
[11 Jan 2009 14:45] Sveta Smirnova 
test case for the testsuite

Attachment: bug42009.test (application/octet-stream, text), 1.46 KiB.
[24 Feb 2009 14:47] Bugs System 
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
[10 May 2009 15:51] Bugs System 
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.
[28 May 2009 7:41] Bugs System 
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)
[28 May 2009 8:19] Bugs System 
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)
[30 May 2009 2:43] Paul DuBois 
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.
[17 Jun 2009 19:28] Bugs System 
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)
[25 Jun 2009 22:36] Paul DuBois 
Noted in 5.4.4 changelog.
[12 Aug 2009 22:46] Paul DuBois 
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[15 Aug 2009 2:01] Paul DuBois 
Ignore previous comment about 5.4.2.
[26 Aug 2009 13:46] Bugs System 
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)
[26 Aug 2009 13:46] Bugs System 
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)
[26 Aug 2009 13:48] Bugs System 
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)
[27 Aug 2009 16:33] Bugs System 
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)
[8 Oct 2009 19:34] Paul DuBois 
The 5.4 fix has been pushed to 5.4.2.
[11 Oct 2011 16:50] Carlos Silva 
Just came accross this bug on mysql 5.1.54.

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