mysql - ACOS() 函数返回 NULL

Question

我有点困惑。这是我的问题

mysql> select acos(cos( radians(37.09024) ) * cos( radians(37.09024) ) * cos(radians(-95.712891) - radians(-95.712891)) + sin(radians(37.09024)) * sin(radians(37.09024)));
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| acos(cos( radians(37.09024) ) * cos( radians(37.09024) ) * cos(radians(-95.712891) - radians(-95.712891)) + sin(radians(37.09024)) * sin(radians(37.09024))) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                                                                         NULL |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> select cos( radians(37.09024) ) * cos( radians(37.09024) ) * cos(radians(-95.712891) - radians(-95.712891)) + sin(radians(37.09024)) * sin(radians(37.09024));
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| cos( radians(37.09024) ) * cos( radians(37.09024) ) * cos(radians(-95.712891) - radians(-95.712891)) + sin(radians(37.09024)) * sin(radians(37.09024)) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                                                                      1 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

我知道 ACOS 函数的范围是 [-1,1]。所以ACOS(1)必为0。

mysql> select acos(1);                                                                                                       +---------+
| acos(1) |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

mysql>

我的MySQL版本太旧，无法更新。MySQL 版本 14.12 Distrib 5.0.22

我做了改变

select acos(cos( radians(37.09024) ) * cos( radians(37.09024) ) * cos(radians(-95.712891) - radians(-95.712891)) + sin(radians(37.09024)) * sin(radians(37.09024))<strong>*1</strong>);

并且工作了。但我不明白为什么 ACOS (... **1) 会成功。当我执行 ACOS (1*...) 时没有用。如果是这种情况，则返回不是一个数字。两种情况都应该有效

Answer 1

Bug #2338 Trignometric arithmatic problems在 4.0.17 版本中被报告，但我无法在 5.0.22 版本中重现该错误。

测试:

$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.22-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT VERSION();
+-----------------+
| VERSION()       |
+-----------------+
| 5.0.22-standard | 
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode;
+-------------------+--------------------+
| @@GLOBAL.sql_mode | @@SESSION.sql_mode |
+-------------------+--------------------+
|                   |                    | 
+-------------------+--------------------+
1 row in set (0.00 sec)

mysql> SELECT ACOS(COS(RADIANS(37.09024)) * COS(RADIANS(37.09024) ) * COS(RADIANS(-95.712891) - RADIANS(-95.712891)) + SIN(RADIANS(37.09024)) * SIN(RADIANS(37.09024)));
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| ACOS(COS(RADIANS(37.09024)) * COS(RADIANS(37.09024) ) * COS(RADIANS(-95.712891) - RADIANS(-95.712891)) + SIN(RADIANS(37.09024)) * SIN(RADIANS(37.09024))) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                                                                         0 | 
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)