Floating point numbers lose precision #78
Description
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)
}