导轨精度误差
当我在 Rails 应用程序中运行此命令时：
my_envelope.transactions.sum(:amount)
此 SQL 显示在日志文件中：
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 时，会返回正确的值。
我有点困惑，因为我知道 Floats 存在精度问题，但它返回 BigDecimal。 SQL 列类型是十进制。我正在使用 sqlite3 (3.6.17) 和 sqlite3-ruby (1.3.2)。有什么想法吗？
更新 1
以下是我使用 SQLite3-ruby 接口直接运行此命令时的结果。
$ 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。
更新2
经过更多研究，事实证明这就是sqlite3的工作方式。它返回一个双精度值（与 Ruby Float 相同）给 sqlite3-ruby，而 sqlite3-ruby 只是将其作为 Float 传递给 Rails。然后，Rails 将其转换为 BigDecimal，因为列类型是十进制。在 Ruby 1.9 之前，Ruby 会为我们四舍五入这个数字，我们不会看到这个问题。
When I run this in my Rails application:
my_envelope.transactions.sum(:amount)
This SQL is shown in the log files:
SQL (0.3ms) SELECT SUM("transactions"."amount") AS sum_id FROM "transactions" WHERE (envelope_id = 834498537)
And this value is returned:
<BigDecimal:1011be570,'0.2515999999 9999997E2',27(27)>
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.
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?
Update 1
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]]
The class of that number is Float. btw, the three numbers it sums are -40.25, 100, and -34.59.
Update 2
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这不是一个优雅的解决方案，但您可以通过将聚合计算的值转换为查询中的 TEXT 来绕过 Float 对象的创建。这“修复”了舍入误差。如果您找到更好的解决方案（例如通过修补 sqlite3-ruby 驱动程序），请更新此问题。
通过转换为字符串，您允许 Active Record 调用需要字符串的 BigDecimal 构造函数，并绕过 Float 及其不准确的 ISO 浮点问题。
顺便说一句，我怀疑将表命名为
transactions是个好主意。在某些时候，这肯定会与其他一些类名或特定于数据库的关键字发生冲突。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).
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.
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.