Rails SQL Float 计算 通过 ActiveRecord 获得的值在 MySQL 和 PostgreSQL 下不统一
第一次调查
在 Rails console 下跑的结果
# Under PostgreSQL
ActiveRecord::Base.connection.execute("SELECT (ACOS(0.9999250668634809) * 6376.77271) as distance;").first
# => 78.0648632113755
# Under MySQL
ActiveRecord::Base.connection.execute("SELECT (ACOS(0.9999250668634809) * 6376.77271) as distance;").first
# => 78.06486321137547
在 DB console 下跑的结果
# Under PostgreSQL
SELECT (ACOS(0.9999250668634809) * 6376.77271) as distance;
#=> 78.06486321137547
# Under MySQL
SELECT (ACOS(0.9999250668634809) * 6376.77271) as distance;
#=> 78.06486321137547
System configuration:
- Rails version: 5.2.2.1
- Ruby version: 2.4.4
- PostgreSQL version: 11.2 / 9.6
- MySQL version: 5.5.49
我的问题
78.06486321137547 是我期望的结果，但是在 PostgreSQL 下通过 ActiveRecord 获得的却是78.0648632113755.
请问有没有办法让 ActiveRecord 在 PostgreSQL 下返回78.06486321137547?
=====================================================================================
第二次调查
终于有时间做第二次调查，不过这次怎么也没办法复现上次 Rails Console 与 DB Console 不统一的情况，以下是我做的测试结果，一共获得了三个结果：
- PostgreSQL 9.5, 10, 11 在 Linux/Mac 是统一的为
78.0648632113755 - PostgreSQL 12 在 Mac 下是
78.06486321137545 - PostgreSQL 12 在 Linux 下是
78.06486321137547且与 MySQL 的结果保持一致
具体测试数据：
Postgres 9.5
PG Host on Docker
- Rails console(host on Mac):
78.0648632113755 - DB console:
78.0648632113755
PG Host on Mac
- Rails console(host on Mac):
78.0648632113755 - DB console:
78.0648632113755
Postgres 10
PG Host on Docker
- Rails console(host on Mac):
78.0648632113755 - DB console:
78.0648632113755
PG Host on Mac
- Rails console(host on Mac):
78.0648632113755 - DB console:
78.0648632113755
Postgres 11
PG Host on Docker
- Rails console(host on Mac):
78.0648632113755 - DB console:
78.0648632113755
PG Host on Mac
- Rails console(host on Mac):
78.0648632113755 - DB console:
78.0648632113755
Postgres 12
PG Host on Docker
- Rails console(host on Mac):
78.06486321137547 - DB console:
78.06486321137547
PG Host on Mac
- Rails console(host on Mac):
78.06486321137545 - DB console:
78.06486321137545
第二次调查的问题
其实由于第一次的问题没法复现，所以这次就没有啥问题了
进一步调查为什么 PostgreSQL 12 的值不一样
发现 PotsgreSQL 12 的结果的 precision 多一位，就查了一下文档：
By default, floating point values are output in text form in their shortest precise decimal representation; the decimal value produced is closer to the true stored binary value than to any other value representable in the same binary precision. (However, the output value is currently never exactly midway between two representable values, in order to avoid a widespread bug where input routines do not properly respect the round-to-even rule.) This value will use at most 17 significant decimal digits for float8 values, and at most 9 digits for float4 values.
For compatibility with output generated by older versions of PostgreSQL, and to allow the output precision to be reduced, the extra_float_digits parameter can be used to select rounded decimal output instead. Setting a value of 0 restores the previous default of rounding the value to 6 (for float4) or 15 (for float8) significant decimal digits. Setting a negative value reduces the number of digits further; for example -2 would round output to 4 or 13 digits respectively.
-- PostgreSQL 12
而 PostgreSQL 11 下没有这些描述。 所以这可以算是 precision 的变化带来的不同，不过确正好在 LInux 下与 MySQL 有了一样的结果
psql (12.2, server 12.1)
Type "help" for help.
jasl=# SELECT (ACOS(0.9999250668634809) * 6376.77271) as distance;
distance
-------------------
78.06486321137545
(1 row)
2.6.5 :001 > ActiveRecord::Base.connection.execute("SELECT (ACOS(0.9999250668634809) * 6376.77271) as distance;").first
(0.5ms) SELECT (ACOS(0.9999250668634809) * 6376.77271) as distance;
=> {"distance"=>78.06486321137545}
再确认一下 PG 版本和结果？
终于有时间做进一步调查。（已更新原帖） 最想查的 Rails console 与 DB console 结果不统一的问题没法再次复现了... 不过还是学到了一个 PostgreSQL float precision 的变化。