Weird rounding issue

Question

Something very weird is happening with decimals and floating numbers and I can't understand why or where (ruby/rails/postgresql).

Given a purchases table with a decimal column - total:

p1 = Purchase.where(total: 5.99).first_or_create
p2 = Purchase.where(total: 5.99).first_or_create

[p1.id, p2.id] # => [1, 2]

p3 = Purchase.where(total: 5.99.to_d).first_or_create
p4 = Purchase.where(total: 5.99.to_d).first_or_create

[p3.id, p4.id] # => [1, 1]

Both Ruby and postgresql have no problem representing 5.99 exactly, no matter if decimals or floats:

5.99.to_s         # => "5.99"
5.99.to_d.to_s    # => "5.99"
5.99 == 5.99.to_d # => true

SELECT CAST(5.99 AS DECIMAL) AS decimal, CAST(5.99 AS FLOAT) AS float;
  #  decimal | float 
  # ---------+-------
  #     5.99 |  5.99
  # (1 row)

SELECT CAST(5.99 AS DECIMAL) = CAST(5.99 AS FLOAT) AS equal;
  #  equal 
  # -------
  #  t
  # (1 row)

To top it all off, this doesn't happen with some other values:

p5 = Purchase.where(total: 5.75).first_or_create
p6 = Purchase.where(total: 5.75).first_or_create
p7 = Purchase.where(total: 5.75.to_d).first_or_create

[p5.id, p6.id, p7.id] # => [3, 3, 3]

Answer 1

This turned out to be a regression in rails. It's reproducible with 5.0.0.1??? and is gone by 5.1.0.0???.

---

I bisected it and found this commit to be the one that fixes the issue. This is the related issue.

The fix seems to be to stop using the pg gem's float encoder.