Recently switched to version 2022 r4.1 and discovered a rounding issue. I have a project created that demonstrates the issue but it won’t let me upload it. Send me a pm and I’ll be happy to share.
The original amount stored in a database is $ 10,000,000.25.
If you run this project and click on the “Add $ 10 Million” button, it will increment the amount shown by $ 10,000,000.10 and save it to the database, then extract the new amount from the database and display it on the screen.
The amounts should always end in 5, but note what happens when you get over $ 100 million. In XOJO, Version 2022 r4.1, they suddenly start ending in 0, which means it is rounding up to the nearest .10.
In XOJO, Version 2022 r3.2, the rounding continues to round properly and the amounts continue to round properly and always end in 5 even when going into the hundreds of million.
Shouldn’t we at least expect xojo to maintain some consistency in dealing with floating point numbers from version to version?
Kind of. But in case of a previous behavior being incorrect, they should fix it, announce the change, and people, if affected, I don’t know how, negatively by a fix, should handle it.
On the other side, if such change is due to a bug introduction, it needs investigation and fix.
You’ve mentioned a database multiple times here and I’m assuming because it’s shareable, you’re talking about SQLite. It looks from your description that this only manifests when the data is passed into the database and then retrieved. Is that correct? If so, does it also do this if you just add the values in a variable of type Double without passing through the database?
Var db As New SQLiteDatabase
Var testFile As FolderItem = SpecialFolder.Desktop.Child("UnitTestAmount.sqlite")
#Pragma BreakOnExceptions Off
Try
testFile.Remove // remove last test
Catch
End
db.DatabaseFile = testFile
db.CreateDatabase
Var amount As Currency = 10000000.25
Var sum As Currency = 10000000.10
db.ExecuteSQL("CREATE TABLE UnitTestAmount (amount REAL);")
db.ExecuteSQL("INSERT INTO UnitTestAmount (amount) VALUES (?);", amount)
For i As Integer = 1 to 100
amount = amount + sum
db.ExecuteSQL("INSERT INTO UnitTestAmount (amount) VALUES (?);", sum)
Next
Var rs As RowSet
rs = db.SelectSQL("SELECT SUM(amount) AS DBAmount FROM UnitTestAmount")
Var dbAmount As String = rs.Column("DBAmount").CurrencyValue.ToString
rs.Close
db.Close
MessageBox "amount: "+amount.ToString+ " DB amount: "+dbAmount
Quit
Remember, my international settings uses comma as decimal separator for the presentation layer.
Xojo had bugs related to mixing things in the past, not sure if this adds to this case too.
Thank you @Rick_Araujo for taking the time to properly test this. I have seen the previous bugs you mentioned, and I am a little nervous now as to the extent these bugs might pop up.
I am not using international settings, and instead using the US separator, which hopefully insulates me a bit from this issue.
Regardless, this is very concerning, and I really appreciate your efforts to clearly point out this issue.
Last Autumn I found a Xojo bug (subsequently fixed) with large integer values (larger than 32 bits):
rs.Column("somecol").IntegerValue
was only giving back 32 bits. To get all 64 bits required:
rs.Column("somecol").Int64Value
This was late last year and was fixed soon after. I wonder if something similar may be happening for these Reals - which will be 64 bit floating point in SQLite. Perhaps only 32 bits is making its way back to the Currency variable (I know nothing of these currency types, sorry).
Here is a link to my project. The amounts displayed should always end in .05 but once you get over $ 100 million the rounding is to the tenths instead of to the hundredths.
With variables being incremented there is no problem. It’s only when reading the data from a database to a variable and then displaying.
Var db As New SQLiteDatabase
Var testFile As FolderItem = SpecialFolder.Desktop.Child("UnitTestAmount.sqlite")
#Pragma BreakOnExceptions Off
Try
testFile.Remove // remove last test
Catch
End
db.DatabaseFile = testFile
db.CreateDatabase
Var xojoAmount As Currency = 10000000.25
Var sum As Currency = 10000000.10
db.ExecuteSQL("CREATE TABLE UnitTestAmount (amount REAL);")
db.ExecuteSQL("INSERT INTO UnitTestAmount (amount) VALUES (?);", xojoAmount)
For i As Integer = 1 to 100
xojoAmount = xojoAmount + sum
db.ExecuteSQL("INSERT INTO UnitTestAmount (amount) VALUES (?);", sum)
Next
Var rs As RowSet
rs = db.SelectSQL("SELECT SUM(amount) AS DBAmount FROM UnitTestAmount")
Var amountReference As Currency = 1010000010.25 // That's the correct sum
Var amountReferenceStr As String = "1010000010.25"
Var amountReferenceDoubleStr As String = "1010000010.2500"
Var xojoAmountStr As String = xojoAmount.ToString
Var dbAmountCur As Currency = rs.Column("DBAmount").CurrencyValue // total amount from DB
Var dbAmountStr As String = rs.Column("DBAmount").CurrencyValue.ToString // total string from DB
Var dbAmountDoubleCurrency As Currency = rs.Column("DBAmount").DoubleValue // total amount from DB as Double
Var dbAmountDoubleCurrencyStr As String = rs.Column("DBAmount").DoubleValue.ToString(Nil, "#0.0000") // total from DB as Double String
rs.Close
db.Close
Var numFails As Integer = 0
If xojoAmount <> amountReference Then
numFails = numFails + 1
// Fix it and resume
Break // Xojo sum failed
End
If xojoAmountStr <> amountReferenceStr Then
numFails = numFails + 1
// Fix it and resume
Break // Xojo sum as string seems invalid, inspect it
End
If dbAmountCur <> amountReference Then
numFails = numFails + 1
// Fix it and resume
Break // DB Currency sum returned by Xojo is invalid
End
If dbAmountStr <> amountReferenceStr Then
numFails = numFails + 1
// Fix it and resume
Break // DB Currency sum string seems invalid, inspect it
End
If dbAmountDoubleCurrency <> amountReference Then
numFails = numFails + 1
// Fix it and resume
Break // DB Double sum returned by Xojo is invalid
End
If dbAmountDoubleCurrencyStr <> amountReferenceDoubleStr Then
numFails = numFails + 1
// Fix it and resume
Break // DB Double sum string seems invalid, inspect it
End
If numFails > 0 Then
MessageBox "Tests failed. Total fails = "+numFails.ToString
Else
MessageBox "Tests passed OK"
End
Quit
