Minor numeric rounding inconsistency

[fabiospampinato]

There seems to be a minor numeric rounding inconsistency:

1. Download the northwind demo database.
2. Run this query: sqlite3 -json tasks/northwind.sqlite "SELECT * FROM 'Order' WHERE Id=10308".
3. See this output: [{"Id":10308,"CustomerId":"ANATR","EmployeeId":7,"OrderDate":"2012-09-18","RequiredDate":"2012-10-16","ShippedDate":"2012-09-24","ShipVia":3,"Freight":11.499999999999999111,"ShipName":"Ana Trujillo Emparedados y helados","ShipAddress":"Avda. de la Constitución 2222","ShipCity":"México D.F.","ShipRegion":"Central America","ShipPostalCode":"05021","ShipCountry":"Mexico"}].
4. Notice that pretty long floating point number: 11.499999999999999111.
5. Notice how Number('11.499999999999999111') gives you 11.499999999999998.
6. Notice how better-sqlite3 instead gives you 11.5.

Now arguably better-sqlite3 is the one that is rounding the number correctly, that number is closer to what better-sqlite3 gives us than what Number gives us. Nonetheless the rounding is different from the one performed by JS, which may be something worth addressing.

[Prinzhorn]

The link for the database is 404, but I'm sure you can repro this with a self-contained demo? Could you also provide code for step 6?

const Database = require('better-sqlite3');
const db = new Database(':memory:');

const stmt = db.prepare('SELECT 11.499999999999999111');

console.log(stmt.get());

// { '11.499999999999999111': 11.499999999999998 }

[Prinzhorn]

Also better-sqlite3 doesn't work with numbers as strings, so the result of Number('11.499999999999999111') is irrelevant. It gets the actual value from SQLite:

https://github.com/WiseLibs/better-sqlite3/blob/38554b54756338d8a331f7202724e8a46afa1dba/src/util/data.lzz#L42-L46

Which also means my code above might be irrelevant, because 11.499999999999999111 cannot be represented and is implicitly converted by SQLite before it even gets to better-sqlite3?

[Prinzhorn]

Sorry for the spam, but the issue could also be in SQLite's JSON serializer. You assume that the output of sqlite3 -json is correct, but 11.499999999999999111 cannot be represented in IEEE 754. So the question is what does the database actually contain?

console.log(11.499999999999999111);
// 11.499999999999998

Edit: :smile:

$ sqlite3
SQLite version 3.40.1 2022-12-28 14:03:47
sqlite> SELECT 11.499999999999999111;
11.5
sqlite> SELECT 11.499999999999998;
11.5

(tbf there might be a setting in the CLI for serialization precision of numbers)

[JoshuaWise]

better-sqlite3 doesn't round numbers or even convert them between different formats. It just passes them to and from the core SQLite library.