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: | |
Category: | MySQL Server: General | Severity: | S3 (Non-critical) |
Version: | 5.0.41, 5.1 | OS: | Linux |
Assigned to: | Evgeny Potemkin | CPU Architecture: | Any |
Tags: | GROUP BY, regression, user variables |
[17 May 2007 8:48]
Piotr Czachur
[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.