Bug #75266 | Setting sum of decimal field on user defined variable is not working as expected | ||
---|---|---|---|
Submitted: | 18 Dec 2014 16:36 | Modified: | 18 Dec 2014 18:17 |
Reporter: | Mehmet Melih Karcı | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
Version: | 5.6.19 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[18 Dec 2014 16:36]
Mehmet Melih Karcı
[18 Dec 2014 16:47]
Peter Laursen
I can reproduce this, But you use "LIMIT 1;" and don't specify an "ORDER BY" in the query. Differerent optimizer code and optimizer settings could result in this. So I am no sure it is a bug! -- Peter -- not a MySQL/Oracle person.
[18 Dec 2014 16:50]
Mehmet Melih Karcı
It's ok to remove ORDER BY and LIMIT. Result set is different anyway.
[18 Dec 2014 18:16]
MySQL Verification Team
Thank you for the bug report. Only 5.6 affected not repeatable on 5.5 and 5.7: -> AND @total < 250 -> ORDER BY id_company_sales DESC -> LIMIT 1; +------------------+------------+--------+--------+ | id_company_sales | date | points | total | +------------------+------------+--------+--------+ | 49090 | 2014-11-28 | 8.35 | 256.03 | +------------------+------------+--------+--------+ 1 row in set (0.00 sec) mysql 5.5 > SHOW VARIABLES LIKE "%VERSION%"; +-------------------------+--------------------------------+ | Variable_name | Value | +-------------------------+--------------------------------+ | innodb_version | 5.5.42 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.5.42 | | version_comment | Source distribution 2014.12.02 | | version_compile_machine | AMD64 | | version_compile_os | Win64 | +-------------------------+--------------------------------+ 7 rows in set (0.00 sec) ************************************************************* -> AND @total < 250 -> ORDER BY id_company_sales DESC -> LIMIT 1; +------------------+------------+--------+--------+ | id_company_sales | date | points | total | +------------------+------------+--------+--------+ | 53415 | 2014-12-09 | 26.10 | 282.13 | +------------------+------------+--------+--------+ 1 row in set (0.00 sec) mysql 5.6 > SHOW VARIABLES LIKE "%VERSION%"; +-------------------------+--------------------------------+ | Variable_name | Value | +-------------------------+--------------------------------+ | innodb_version | 5.6.23 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.6.23 | | version_comment | Source distribution 2014.12.02 | | version_compile_machine | x86_64 | | version_compile_os | Win64 | +-------------------------+--------------------------------+ 7 rows in set (0.00 sec) **************************************************************** -> AND cs.order_id != 0 -> AND @total < 250 -> ORDER BY id_company_sales DESC -> LIMIT 1; +------------------+------------+--------+--------+ | id_company_sales | date | points | total | +------------------+------------+--------+--------+ | 49090 | 2014-11-28 | 8.35 | 256.03 | +------------------+------------+--------+--------+ 1 row in set (0.00 sec) mysql 5.7 > SHOW VARIABLES LIKE "%VERSION%"; +-------------------------+--------------------------------+ | Variable_name | Value | +-------------------------+--------------------------------+ | innodb_version | 5.7.6 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.7.6-m16 | | version_comment | Source distribution 2014.12.02 | | version_compile_machine | x86_64 | | version_compile_os | Win64 | +-------------------------+--------------------------------+ 7 rows in set (0.00 sec) mysql 5.7 >