Bug #28494 Assigning to variable in SELECT spoils result of GROUP BY
Submitted: 17 May 2007 8:48 Modified: 19 Jun 2007 1:12
Reporter: Piotr Czachur Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.0.41, 5.1 OS:Linux
Assigned to: Evgeny Potemkin
Tags: GROUP BY, regression, user variables

[17 May 2007 8:48] Piotr Czachur
Description:
Following query with "@a :=" returns different result than without it.
I guess it's because priceIntervalStart is treated as user variable, 
but i think it's not correct behaviour.

With "@a :="
+--------------------+--------------+
| priceIntervalStart | productCount |
+--------------------+--------------+
|                  0 |            3 | 
+--------------------+--------------+

Without it:
+--------------------+--------------+
| priceIntervalStart | productCount |
+--------------------+--------------+
|                  0 |            2 | 
|                 10 |            1 | 
+--------------------+--------------+

BTW in MySQL 5.0.24 query with "@a :=" and without it workes the same and returns two rows as i would expect.

How to repeat:
CREATE TABLE `products` (
`name` VARCHAR( 10 ) NOT NULL ,
`price` INT NOT NULL ,
PRIMARY KEY ( `name` )
);

INSERT INTO products VALUES ('tea', 7), ('coffie', 14), ('sugar', 3);

SELECT
    @a := CASE
        WHEN price BETWEEN 0 AND  9 THEN  0
        WHEN price BETWEEN 10 AND  19 THEN  10
    END
        AS priceIntervalStart,
    COUNT(*) AS productCount
FROM
    products
GROUP BY
    priceIntervalStart
;

Suggested fix:
Assigning value to variable should not change the result in that case.
[17 May 2007 10:54] Sveta Smirnova
test case

Attachment: bug28494.test (application/octet-stream, text), 789 bytes.

[17 May 2007 10:54] Sveta Smirnova
Thank you for the report.

Verified as described. Occured since 5.0.36/5.0.37.
[31 May 2007 20:09] 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/27845

ChangeSet@1.2511, 2007-06-01 00:05:49+04:00, evgen@moonbone.local +4 -0
  Bug#28494: Grouping by Item_func_set_user_var produces incorrect result.
  
  The end_update() function uses the Item::save_org_in_field() function to
  save original values of items into the group buffer. But for the 
  Item_func_set_user_var it was mapped to the save_in_field function.
  This function wrongly decided to use the result_field. This leads to saving
  incorrect value in the grouping buffer and wrong result of the whole query.
  
  The can_use_result_field argument of the bool type is added to the
  Item_func_set_user_var::save_in_field() function. If it is set to FALSE
  then the item's result field won't be used. Otherwise it will be detected
  whether the result field will can be used (old behaviour).
  Two wrapping functions for the function above are added to the 
  Item_func_set_user_var class:
  the save_in_field(Field *field, bool no_conversions) - it calls the above
  function with the can_use_result_field set to TRUE.
  the save_org_in_field(Field *field) - same, but the can_use_result_field
  is set to FALSE.
[31 May 2007 21:10] 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/27850

ChangeSet@1.2511, 2007-06-01 01:07:14+04:00, evgen@moonbone.local +4 -0
  Bug#28494: Grouping by Item_func_set_user_var produces incorrect result.
  
  The end_update() function uses the Item::save_org_in_field() function to
  save original values of items into the group buffer. But for the 
  Item_func_set_user_var this method was mapped to the save_in_field method.
  This function wrongly decides to use the result_field. This leads to saving
  incorrect value in the grouping buffer and wrong result of the whole query.
  
  The can_use_result_field argument of the bool type is added to the
  Item_func_set_user_var::save_in_field() function. If it is set to FALSE
  then the item's result field won't be used. Otherwise it will be detected
  whether the result field will be used (old behaviour).
  Two wrapping functions for the function above are added to the 
  Item_func_set_user_var class:
  the save_in_field(Field *field, bool no_conversions) - it calls the above
  function with the can_use_result_field set to TRUE.
  the save_org_in_field(Field *field) - same, but the can_use_result_field
  is set to FALSE.
[31 May 2007 21:20] 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/27853

ChangeSet@1.2511, 2007-06-01 01:17:14+04:00, evgen@moonbone.local +4 -0
  Bug#28494: Grouping by Item_func_set_user_var produces incorrect result.
  
  The end_update() function uses the Item::save_org_in_field() function to
  save original values of items into the group buffer. But for the 
  Item_func_set_user_var this method was mapped to the save_in_field method.
  The latter function wrongly decides to use the result_field. This leads to
  saving incorrect value in the grouping buffer and wrong result of the whole
  query.
  
  The can_use_result_field argument of the bool type is added to the
  Item_func_set_user_var::save_in_field() function. If it is set to FALSE
  then the item's result field won't be used. Otherwise it will be detected
  whether the result field will be used (old behaviour).
  Two wrapping functions for the function above are added to the 
  Item_func_set_user_var class:
  the save_in_field(Field *field, bool no_conversions) - it calls the above
  function with the can_use_result_field set to TRUE.
  the save_org_in_field(Field *field) - same, but the can_use_result_field
  is set to FALSE.
[2 Jun 2007 18:45] 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/27994

ChangeSet@1.2506, 2007-06-02 22:23:05+04:00, evgen@moonbone.local +4 -0
  Bug#28494: Grouping by Item_func_set_user_var produces incorrect result.
  
  This is an additional fix.
  Item::val_xxx methods are supposed to use original data source and
  Item::val_xxx_result methods to use the item's result field. But for the
  Item_func_set_user_var class val_xxx_result maethods were mapped to val_xxx
  methods. This leads, in particular, to producing bad sort keys and thus
  wrong order of the result set of queries with group by/order by clauses.
  
  The additional use_result_field parameter is added to the
  Item_func_set_user_var::val_xxx methods. Depending on its value the class
  will or won't use the result_field.
  Two sets of wrapper functions are added to the Item_func_set_user_var class.
  val_xxx methods are call appropriate function from above set with the
  use_result_field set to FALSE.
  val_xxx_result methods - same as above but use_result_field is set to TRUE.
[2 Jun 2007 19:12] 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/27996

ChangeSet@1.2506, 2007-06-02 23:08:58+04:00, evgen@moonbone.local +4 -0
  Bug#28494: Grouping by Item_func_set_user_var produces incorrect result.
  
  This is an additional fix.
  Item::val_xxx methods are supposed to use original data source and
  Item::val_xxx_result methods to use the item's result field. But for the
  Item_func_set_user_var class val_xxx_result methods were mapped to val_xxx
  methods. This leads, in particular, to producing bad sort keys and thus
  wrong order of the result set of queries with group by/order by clauses.
  
  The set of val_xxx_result methods is added to the Item_func_set_user_var
  class. It's the same as the val_xxx set of method but uses the result_field
  to return a value.
[2 Jun 2007 19:20] 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/27997

ChangeSet@1.2506, 2007-06-02 23:17:46+04:00, evgen@moonbone.local +4 -0
  Bug#28494: Grouping by Item_func_set_user_var produces incorrect result.
  
  This is an additional fix.
  Item::val_xxx methods are supposed to use original data source and
  Item::val_xxx_result methods to use the item's result field. But for the
  Item_func_set_user_var class val_xxx_result methods were mapped to val_xxx
  methods. This leads, in particular, to producing bad sort keys and thus
  wrong order of the result set of queries with group by/order by clauses.
  
  The set of val_xxx_result methods is added to the Item_func_set_user_var
  class. It's the same as the val_xxx set of method but uses the result_field
  to return a value.
[4 Jun 2007 21:20] Bugs System
Pushed into 5.1.20-beta
[4 Jun 2007 21:22] Bugs System
Pushed into 5.0.44
[19 Jun 2007 1:12] Paul Dubois
Noted in 5.0.44, 5.1.20 changelogs.

A query that grouped by the result of an expression returned a
different result when the expression was assigned to a user variable.