在 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
78.06486321137547
是我期望的结果,但是在 PostgreSQL 下通过 ActiveRecord 获得的却是78.0648632113755
.
请问有没有办法让 ActiveRecord 在 PostgreSQL 下返回78.06486321137547
?
=====================================================================================
终于有时间做第二次调查,不过这次怎么也没办法复现上次 Rails Console 与 DB Console 不统一的情况,以下是我做的测试结果,一共获得了三个结果:
78.0648632113755
78.06486321137545
78.06486321137547
且与 MySQL 的结果保持一致78.0648632113755
78.0648632113755
78.0648632113755
78.0648632113755
78.0648632113755
78.0648632113755
78.0648632113755
78.0648632113755
78.0648632113755
78.0648632113755
78.0648632113755
78.0648632113755
78.06486321137547
78.06486321137547
78.06486321137545
78.06486321137545
其实由于第一次的问题没法复现,所以这次就没有啥问题了
发现 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 有了一样的结果