阿里云-RDS-MySQL-5.5计算问题

Question

第一部分：问题描述
 
1.问题简述：
有一SQL语句，在 [阿里云-RDS-MYSQL] 和 [非阿里云-MYSQL] 环境里执行，得出的结果不一样
 
2.运行环境：
A: [阿里云-RDS-MySQL- 5.5.18.1-Alibaba-rds-201406-log]

mysql>SHOW VARIABLES LIKE "%version%"
+-------------------------+---------------------------------+
| Variable_name           | Value                           |
+-------------------------+---------------------------------+
| innodb_version          | 1.1.8-20.1                      |
| protocol_version        | 10                              |
| slave_type_conversions  |                                 |
| version                 | 5.5.18.1-Alibaba-rds-201406-log |
| version_comment         | Source distribution             |
| version_compile_machine | x86_64                          |
| version_compile_os      | Linux                           |
+-------------------------+---------------------------------+ 

 
B: [非阿里云-MySQL]

 mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.6.24                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.6.24                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)

 
3.SQL语句：

 select (((((237803.3300+1659482.4400)/2.00)/1182208.5700)*30.00*6)+(0.50*(((((((1159248.2000+687984.6000)/2.00)/1527268.4000)*30.00*6)+((((457300.0000+1707654.0300)/2.00)/1182208.5700)*30.00*6))-(((((360.00*((1527268.4000-1182208.5700)-((2587.0000-((5354427.3100*0.0400*6)/12.00))+99354.4800+10550.5600)))/((((((1159248.2000+687984.6000)/2.00)/1527268.4000)*30.00*6)+((((457300.0000+1707654.0300)/2.00)/1182208.5700)*30.00*6))*0.5))-(0.50*((2587.0000-((5354427.3100*0.0400*6)/12.00))+99354.4800+10550.5600)))*(((((1159248.2000+687984.6000)/2.00)/1527268.4000)*30.00*6)+((((457300.0000+1707654.0300)/2.00)/1182208.5700)*30.00*6)))/1182208.5700))-((((237803.3300+1659482.4400)/2.00)/1182208.5700)*30.00*6)))) AS YF

 
4.运行结果：
A: [阿里云-RDS-MySQL- 5.5.18.1-Alibaba-rds-201406-log]
计算结果：144.43789669025999363208811782
 
B: [非阿里云-MySQL-5.5~5.6]
计算结果：105.93639914104807268662689486
 
这是为什么呢？


第二部分：测试与分析
 
自我测试：
 
第一步：SQL语句格式化

SELECT (
((((237803.3300+1659482.4400)/2.00)/1182208.5700)*30.00*6)
+
(
0.50
*
(
(
(((((1159248.2000+687984.6000)/2.00)/1527268.4000)*30.00*6)+((((457300.0000+1707654.0300)/2.00)/1182208.5700)*30.00*6))   
-
(
(
(
(
(360.00*((1527268.4000-1182208.5700)-((2587.0000-((5354427.3100*0.0400*6)/12.00))+99354.4800+10550.5600)))
/
((((((1159248.2000+687984.6000)/2.00)/1527268.4000)*30.00*6)+((((457300.0000+1707654.0300)/2.00)/1182208.5700)*30.00*6))*0.5)
)
-
(0.50*((2587.0000-((5354427.3100*0.0400*6)/12.00))+99354.4800+10550.5600))
)
*
(((((1159248.2000+687984.6000)/2.00)/1527268.4000)*30.00*6)+((((457300.0000+1707654.0300)/2.00)/1182208.5700)*30.00*6))
)
/
1182208.5700
)
)
-
((((237803.3300+1659482.4400)/2.00)/1182208.5700)*30.00*6)
)
)
) AS YF 

 
第二步：把几个大的语句单独出来执行，在 [阿里云-RDS-MySQL- 5.5.18.1-Alibaba-rds-201406-log] 和 [非阿里云-MySQL-5.5~5.6] 环境里执行，结果都一样

SELECT ((((237803.3300+1659482.4400)/2.00)/1182208.5700)*30.00*6) AS A
, (((((1159248.2000+687984.6000)/2.00)/1527268.4000)*30.00*6)+((((457300.0000+1707654.0300)/2.00)/1182208.5700)*30.00*6)) AS B
, (360.00*((1527268.4000-1182208.5700)-((2587.0000-((5354427.3100*0.0400*6)/12.00))+99354.4800+10550.5600))) AS C
, ((((((1159248.2000+687984.6000)/2.00)/1527268.4000)*30.00*6)+((((457300.0000+1707654.0300)/2.00)/1182208.5700)*30.00*6))*0.5) AS D
, (0.50*((2587.0000-((5354427.3100*0.0400*6)/12.00))+99354.4800+10550.5600)) AS E
, (((((1159248.2000+687984.6000)/2.00)/1527268.4000)*30.00*6)+((((457300.0000+1707654.0300)/2.00)/1182208.5700)*30.00*6)) AS F
, ((((237803.3300+1659482.4400)/2.00)/1182208.5700)*30.00*6) AS G

 
第三步：把第二步执行的结果带入到第一步的语句中，在 [阿里云-RDS-MySQL- 5.5.18.1-Alibaba-rds-201406-log] 和 [非阿里云-MySQL-5.5~5.6] 环境里执行，结果还是一样

SELECT (
144.43789669025999
+
(
0.50
*
(
(
273.67023142422547
-
(
(
(
(
122276281.03200000000000
/
136.835115712112735
)
-
2701.74690000000000
)
*
273.67023142422547
)
/
1182208.5700
)
)
-
144.43789669025999
)
)
) AS YF 

 
分析：
通过Java, Excel, 和在其它数据库平台计算此公式得出的结果大约是：105.93，所以推测在[阿里云-RDS-MySQL-5.5]数据中执行的结果有问题，以下是本人猜测的几种可能性：
1.应该不是四舍五入和精度的问题。
2.是不是语句解析后优化出的问题？
3.会不会与 [括号导致] 的哪步先算哪步后算有关？
4.会不会与 [乘法结合律规则导致] 的哪步先算哪步后算有关？
5.阿里云-RDS-MySQL-5.5数据库bug，但是MySQL-5.5本身是没有这个问题的?
 
有哪位高手帮忙看看？

Answer 1

我分别测试了阿里云RDS的5.1，5.5，5.6三个版本以及社区版MySQL的5.5和5.6，阿里云RDS的值与社区版的保持一致；
5.1和5.5的值都为144.437896690259993632088117820000，5.6版本的值都为105.936399141048072686626894862271；
我使用百度计算器计算出来的值为：105.9363991409，估计是MySQL版本的bug，在5.6进行了修复，测试如下：
阿里云RDS 5.6：
Server version: 5.6.16-log Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select (((((237803.3300+1659482.4400)/2.00)/1182208.5700)*30.00*6)+(0.50*(((((((1159248.2000+687984.6000)/2.00)/1527268.4000)*30.00*6)+((((457300.0000+1707654.0300)/2.00)/1182208.5700)*30.00*6))-(((((360.00*((1527268.4000-1182208.5700)-((2587.0000-((5354427.3100*0.0400*6)/12.00))+99354.4800+10550.5600)))/((((((1159248.2000+687984.6000)/2.00)/1527268.4000)*30.00*6)+((((457300.0000+1707654.0300)/2.00)/1182208.5700)*30.00*6))*0.5))-(0.50*((2587.0000-((5354427.3100*0.0400*6)/12.00))+99354.4800+10550.5600)))*(((((1159248.2000+687984.6000)/2.00)/1527268.4000)*30.00*6)+((((457300.0000+1707654.0300)/2.00)/1182208.5700)*30.00*6)))/1182208.5700))-((((237803.3300+1659482.4400)/2.00)/1182208.5700)*30.00*6)))) AS YF
-> ;
+------------------------------------+
| YF |
+------------------------------------+
| 105.936399141048072686626894862271 |

阿里云RDS 5.5：
Server version: 5.5.18.1-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select (((((237803.3300+1659482.4400)/2.00)/1182208.5700)*30.00*6)+(0.50*(((((((1159248.2000+687984.6000)/2.00)/1527268.4000)*30.00*6)+((((457300.0000+1707654.0300)/2.00)/1182208.5700)*30.00*6))-(((((360.00*((1527268.4000-1182208.5700)-((2587.0000-((5354427.3100*0.0400*6)/12.00))+99354.4800+10550.5600)))/((((((1159248.2000+687984.6000)/2.00)/1527268.4000)*30.00*6)+((((457300.0000+1707654.0300)/2.00)/1182208.5700)*30.00*6))*0.5))-(0.50*((2587.0000-((5354427.3100*0.0400*6)/12.00))+99354.4800+10550.5600)))*(((((1159248.2000+687984.6000)/2.00)/1527268.4000)*30.00*6)+((((457300.0000+1707654.0300)/2.00)/1182208.5700)*30.00*6)))/1182208.5700))-((((237803.3300+1659482.4400)/2.00)/1182208.5700)*30.00*6)))) AS YF
-> ;
+------------------------------------+
| YF |
+------------------------------------+
| 144.437896690259993632088117820000 |
+------------------------------------+

阿里云RDS 5.1：
Server version: 5.1.61-Alibaba-4446-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select (((((237803.3300+1659482.4400)/2.00)/1182208.5700)*30.00*6)+(0.50*(((((((1159248.2000+687984.6000)/2.00)/1527268.4000)*30.00*6)+((((457300.0000+1707654.0300)/2.00)/1182208.5700)*30.00*6))-(((((360.00*((1527268.4000-1182208.5700)-((2587.0000-((5354427.3100*0.0400*6)/12.00))+99354.4800+10550.5600)))/((((((1159248.2000+687984.6000)/2.00)/1527268.4000)*30.00*6)+((((457300.0000+1707654.0300)/2.00)/1182208.5700)*30.00*6))*0.5))-(0.50*((2587.0000-((5354427.3100*0.0400*6)/12.00))+99354.4800+10550.5600)))*(((((1159248.2000+687984.6000)/2.00)/1527268.4000)*30.00*6)+((((457300.0000+1707654.0300)/2.00)/1182208.5700)*30.00*6)))/1182208.5700))-((((237803.3300+1659482.4400)/2.00)/1182208.5700)*30.00*6)))) AS YF
-> ;
+------------------------------------+
| YF |
+------------------------------------+
| 144.437896690259993632088117820000 |
+------------------------------------+

社区版RDS 5.5：
Server version: 5.5.18.6-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select (((((237803.3300+1659482.4400)/2.00)/1182208.5700)*30.00*6)+(0.50*(((((((1159248.2000+687984.6000)/2.00)/1527268.4000)*30.00*6)+((((457300.0000+1707654.0300)/2.00)/1182208.5700)*30.00*6))-(((((360.00*((1527268.4000-1182208.5700)-((2587.0000-((5354427.3100*0.0400*6)/12.00))+99354.4800+10550.5600)))/((((((1159248.2000+687984.6000)/2.00)/1527268.4000)*30.00*6)+((((457300.0000+1707654.0300)/2.00)/1182208.5700)*30.00*6))*0.5))-(0.50*((2587.0000-((5354427.3100*0.0400*6)/12.00))+99354.4800+10550.5600)))*(((((1159248.2000+687984.6000)/2.00)/1527268.4000)*30.00*6)+((((457300.0000+1707654.0300)/2.00)/1182208.5700)*30.00*6)))/1182208.5700))-((((237803.3300+1659482.4400)/2.00)/1182208.5700)*30.00*6)))) AS YF
-> ;
+------------------------------------+
| YF |
+------------------------------------+
| 144.437896690259993632088117820000 |

社区版RDS 5.6：
Server version: 5.6.16.4-20140826-log Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@(none) 07:27:38> select (((((237803.3300+1659482.4400)/2.00)/1182208.5700)*30.00*6)+(0.50*(((((((1159248.2000+687984.6000)/2.00)/1527268.4000)*30.00*6)+((((457300.0000+1707654.0300)/2.00)/1182208.5700)*30.00*6))-(((((360.00*((1527268.4000-1182208.5700)-((2587.0000-((5354427.3100*0.0400*6)/12.00))+99354.4800+10550.5600)))/((((((1159248.2000+687984.6000)/2.00)/1527268.4000)*30.00*6)+((((457300.0000+1707654.0300)/2.00)/1182208.5700)*30.00*6))*0.5))-(0.50*((2587.0000-((5354427.3100*0.0400*6)/12.00))+99354.4800+10550.5600)))*(((((1159248.2000+687984.6000)/2.00)/1527268.4000)*30.00*6)+((((457300.0000+1707654.0300)/2.00)/1182208.5700)*30.00*6)))/1182208.5700))-((((237803.3300+1659482.4400)/2.00)/1182208.5700)*30.00*6)))) AS YF
-> ;
+------------------------------------+
| YF |
+------------------------------------+
| 105.936399141048072686626894862271 |
+------------------------------------+