Rails精度误差 [英] Rails precision error

问题描述

当我在Rails应用程序中运行它时

When I run this in my Rails application:

my_envelope.transactions.sum(:amount)

此SQL显示在日志文件中:

This SQL is shown in the log files:

SQL (0.3ms)  SELECT SUM("transactions"."amount") AS sum_id FROM "transactions" WHERE (envelope_id = 834498537)

并返回此值:

<BigDecimal:1011be570,'0.2515999999 9999997E2',27(27)>

如您所见，该值为25.159999.应该是25.16.当我自己在数据库上运行相同的SQL时，将返回正确的值.

As you can see, the value is 25.159999. It should be 25.16. When I run the same SQL on the database myself, the correct value is returned.

我有点困惑，因为我知道Floats存在精度问题，但是它返回的是BigDecimal. SQL列类型为十进制.我正在使用sqlite3(3.6.17)和sqlite3-ruby(1.3.2).有什么想法吗?

I'm a little confused because I know that there are precision problems with Floats, but it is returning a BigDecimal. The SQL column type is decimal. I'm using using sqlite3 (3.6.17) and sqlite3-ruby (1.3.2). Any ideas?

更新1

当我直接使用SQLite3-ruby界面运行此命令时，结果如下.

Here are the results when I run this directly using the SQLite3-ruby interface.

$ rails c test
Loading test environment (Rails 3.0.3)
irb(main):001:0> db = SQLite3::Database.new("db/test.sqlite3")
=> #<SQLite3::Database:0x5242020>
irb(main):002:0> db.execute("SELECT SUM(amount) FROM transactions WHERE envelope_id = 834498537")
=> [[25.159999999999997]]

该数字的类别为Float.顺便说一句，它求和的三个数字是-40.25、100和-34.59.

The class of that number is Float. btw, the three numbers it sums are -40.25, 100, and -34.59.

更新2

经过更多研究，事实证明，这只是sqlite3的工作方式.它会向sqlite3-ruby返回一个双精度值(与Ruby Float相同)，而sqlite3-ruby会将其作为Float传递给Rails.然后，Rails将其转换为BigDecimal，因为列类型为十进制.在Ruby 1.9之前，Ruby会为我们四舍五入这个数字，而我们看不到问题所在.

After more research, it turns out that this is just the way the sqlite3 works. It returns a double (same as Ruby Float) to sqlite3-ruby and sqlite3-ruby just passes it on to Rails as a Float. Then, Rails converts it to BigDecimal because the column type is decimal. Before Ruby 1.9, Ruby would round this number for us and we wouldn't see the problem.

推荐答案

这不是一个很好的解决方案，但是您可以通过将聚合计算的值转换为查询中的TEXT来绕过Float对象的创建.这样可以修复"舍入错误.如果您找到更好的解决方案(例如通过修补sqlite3-ruby驱动程序)，请更新此问题.

It's not an elegant solution but you can bypass the creation of the Float object by casting the value of your aggregate calculation to TEXT in the query. This "fixes" the rounding error. Please update this question if you find a better solution (such as by patching the sqlite3-ruby driver).

SELECT CAST(SUM(amount) AS TEXT) FROM transactions WHERE envelope_id = 834498537

通过强制转换为字符串，您可以允许Active Record调用BigDecimal的构造函数，该构造函数需要一个字符串，并绕过Float的错误ISO浮点问题.

By casting to a string you're allowing Active Record to call BigDecimal's constructor which requires a string and bypassing Float with its inaccurate ISO floating point issues.

顺便说一句，我怀疑将表命名为transactions是个好主意.在某些时候，这势必会与其他一些类名或特定于数据库的关键字发生冲突.

By the way, I doubt it's a good idea to name your table transactions. That's bound to conflict with some other class name or database-specific keyword at some point.

这篇关于Rails精度误差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！