Issue converting postgres real data type to float64

[yaronius]

Hi! Just came across a nasty problem while migrating to pgx from pq. The issue is that when I have a table with a column of type REAL (which is a 32-bit type) and then scan the row into my struct with the corresponding field of type float64, the result has precision issues and cannot be compared to the original values. E.g. when I store in the DB value 0.2, I then get 0.20000000298023224 in my scanned struct. And the issues are due to float32 to float64 conversion on this line. This case is also not covered in tests when I change 42 to 4.2 on this line, it fails with the same issue:

=== RUN   TestFloat4AssignTo
    float4_test.go:106: 2: expected {4.2 2} to assign 4.2, but result was 4.199999809265137
--- FAIL: TestFloat4AssignTo (0.00s)

I found a way to fix this problem by changing the AssignTo() method of Float4:

func (src *Float4) AssignTo(dst interface{}) error {
    // extra conversion to mitigate precision issues when converting float32 to float64
    f64, err := strconv.ParseFloat(fmt.Sprintf("%g", src.Float), 64)
    if err != nil {
        return err
    }
    return float64AssignTo(f64, src.Status, dst)
}

Not sure if it's the correct way to properly convert float32 to float64, but at least it works. Any thoughts/suggestions on this issue? BTW this may also be related to https://github.com/jackc/pgtype/issues/27.

[jackc]

I think you are running into this: https://stackoverflow.com/questions/39642810/why-am-i-losing-precision-while-converting-float32-to-float64

[yaronius]

@jackc thanks for the response. Yes, I have already read this question and the answers. AFAIU the accepted answer claims that there's no loss of precision and the numbers are the same. But I'm not sure about the implications for the real-world apps (and tests) that rely on comparing those values with a certain constant value. Here's an illustration of what is happening in my case https://play.golang.org/p/Izf0kuwG1uj.

[jackc]

I believe the failure in your example is due to the untyped constant 0.2 being assigned to and compared with different types of floats. If I understand correctly, that decimal cannot be perfectly represented as a binary floating point value. So the conversion from float64 is lossy and when it is converted back to a float64 it no longer matches.

Use a float32 as the initial constant value and it works.

https://play.golang.org/p/ks1XPV5lzMo

[yaronius]

@jackc thank you for the response and clarification , I guess you are right, it turned out to be a tricky pitfall. I'm closing this issue.

[yaronius]

BTW the issue is still there if you use float value (instead of integers) in your tests, e.g.:

{src: pgtype.Float4{Float: 4.2, Status: pgtype.Present}, dst: &f64, expected: float64(4.2)},

I guess it's because this simple float conversion from float32 to float64 is tricky and should be avoided whenever possible. But I don't want to insist on this if it's only my problem.