Floating point numbers lose precision

[florianreinhart]

The age-old floating point issues bit me once again...

Note: This most definitely also applies to Float, but I have just tested with Double.

When Swift formats a Double as a String it rounds the number, e.g. an internal representation of 7.7087009966199993 has a String value of 7.70870099662. When you create a Double from that String, you get back 7.7087009966200002. This is closer to 7.70870099662 than 7.7087009966199993, so it is the right thing to do here.

mysql-swift uses the String initializer to convert numbers to strings and hands it off to MySQL client. I believe the server then decodes that string in much the same way Swift does: The string 7.70870099662 is converted to the double value 7.7087009966200002.

JSONEncoder/JSONDecoder does not observe these issues and formats the double value as 7.7087009966199993. We should probably look into the Codable implementation of Double and figure out how they encode floating point values.

I have created a sample implementation to reproduce the issue:

CREATE TABLE `DoubleTable` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `doubleField` double NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

import Foundation
import MySQL

struct MySQLOptions: MySQL.ConnectionOption {
    init() { }
    let host = "127.0.0.1"
    let port = 3306
    let user = "doubleUser"
    let password = "doubleUser"
    let database = "DoubleDatabase"
    let timeZone = TimeZone(identifier: "UTC")!
    let encoding = Connection.Encoding.UTF8MB4
    let timeout = 10
    let reconnect = true
    let omitDetailsOnError = false
}

struct DoubleStruct: Codable {
    let id: UInt32
    let doubleField: Double
}

let pool = ConnectionPool(options: MySQLOptions())

try! pool.execute { connection in
    // Write a double value to the database
    print("Writing double value to database")
    let doubleStruct1 = DoubleStruct(id: 0, doubleField: 7.7087009966199993)
    var query = "INSERT INTO DoubleTable (doubleField) VALUES (?)"
    let result = try connection.query(query, [doubleStruct1.doubleField])
    print("Created row with id \(result.insertedID) and double field \(doubleStruct1.doubleField). Memory dump follows below.")
    dump(doubleStruct1.doubleField)

    // Read the same double value from the database
    query = "SELECT id, doubleField FROM DoubleTable WHERE id = ?"
    let doubleStructs: [DoubleStruct] = try connection.query(query, [UInt32(result.insertedID)])
    let doubleStruct2 = doubleStructs.first!
    print("Read row with id \(doubleStruct2.id) and double field \(doubleStruct2.doubleField). Their internal values are\(doubleStruct1.doubleField == doubleStruct2.doubleField ? "" : " not") equal. Dump of the read value follows.")
    dump(doubleStruct2.doubleField)

    // Encode and decode as JSON
    print("\nNow encoding as JSON")
    let jsonEncoder = JSONEncoder()
    let jsonDecoder = JSONDecoder()
    let json1 = try jsonEncoder.encode(doubleStruct1)
    let json2 = try jsonEncoder.encode(doubleStruct2)
    print(String(decoding: json1, as: UTF8.self))
    print(String(decoding: json2, as: UTF8.self))
    let doubleStruct1a = try jsonDecoder.decode(DoubleStruct.self, from: json1)
    let doubleStruct2a = try jsonDecoder.decode(DoubleStruct.self, from: json2)
    print("JSON double values for struct 1 are\(doubleStruct1.doubleField == doubleStruct1a.doubleField ? "" : " not") equal. Dump follows.")
    dump(doubleStruct1.doubleField)
    dump(doubleStruct1a.doubleField)
    print("JSON double values for struct 2 are\(doubleStruct2.doubleField == doubleStruct2a.doubleField ? "" : " not") equal. Dump follows.")
    dump(doubleStruct2.doubleField)
    dump(doubleStruct2a.doubleField)

    // Convert to string and back
    print("\nNow encoding as string")
    let string1 = String(doubleStruct1.doubleField)
    let doubleFromString = Double(string1)!
    print("Encoding and decoding from string. Values are\(doubleStruct1.doubleField == doubleFromString ? "" : " not") equal. Dump follows")
    dump(doubleStruct1.doubleField)
    dump(doubleFromString)
}

[florianreinhart]

I dug into this a little more. Here is what I’ve found.

There is an issue tracking the double/string conversion issue: SR-106: description returns a rounded value of Double.

I also looked into the open source implementation of JSONEncoder and JSONSerialization. They use a CFNumberFormatter to format floating point values and wrap them in an NSNumber first: https://github.com/apple/swift-corelibs-foundation/blob/6501fa20bf4c5f36627ce3dce73ffc190ba41c0b/Foundation/JSONSerialization.swift#L438

However, this produces different results as on Apple platforms. I filed SR-7195: JSONSerialization encodes Double differently on Darwin and Linux.

[florianreinhart]

A short term fix that we could implement: String(format: "%.16e", self)

[florianreinhart]

SR-106 has been fixed since Swift 4.2. And also SR-7195 was fixed in apple/swift-corelibs-foundation#1722 and will be part of Swift 5.