Bug #42009 SELECT into variable gives different results to direct SELECT
Submitted: 10 Jan 2:44 Modified: 26 Jun 0:36
Reporter: Robin Clarke
Status: Closed
Category: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 Target Version:5.0+
Tags: select into, GROUP, group_concat
Triage: Triaged: D2 (Serious)

[10 Jan 2: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 15:45] Sveta Smirnova 
Thank you for the report.

Verified as described.

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

Attachment: bug42009.test (application/octet-stream, text), 1.46 KiB.
[24 Feb 15: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 17: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 9: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 10: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 4: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 21: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)
[26 Jun 0:36] Paul DuBois 
Noted in 5.4.4 changelog.
[13 Aug 0: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 4:01] Paul DuBois 
Ignore previous comment about 5.4.2.
[26 Aug 15: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 15: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 15: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 18: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 21:34] Paul DuBois 
The 5.4 fix has been pushed to 5.4.2.