Android Question Weird sqlite behavior

[Cleidson]

Hello everybody,
A strange situation has occurred to me when using the sqlite sum function. The application I've been developing uses sqlite as a database and many fields in the tables were declared as REAL. I have been inserting the values without any problem in the fields, but when I do a select with the sum function the query return is less than the real value that the query should return. In some cases the same select sum statement returns with a difference of 10 cents from one query to another. And the amount of lines to add is small, at the moment I have 60 launches. Which becomes a bigger problem when you start scaling releases.
I use a variable of type Double and ExecSingleResult2 to query the database.
The statement is usually Select coalesce (sum(value), 0) From Table
I made another function to validate the query by using execquery and iterating line by line. In this test the result is as expected.
Has anyone experienced this problem?
I thank you for your attention.

 

[Cleidson]

Hello everybody,
I didn't understand the behavior of Sqlite with the command. And apparently it wasn't just me that happened. The tests that Mahares ran, too, had their flaws. I had already done tests iterating line by line and the result was correct. Using Sum gave error. As for Coalesce, I have a desktop program that works perfectly with this procedure, however, the database is firebird. That's why I replicated it in Sqlite.
Therefore, I followed Brian Dean's tip and changed all Real fields to Integer and so far the tests worked as they should and using the same structure: Sum, Coalesce, ExecQuerySingleResult2. The only change I had to make was in the number formatting procedure, in cases of queries to the database, which in this case is a monetary formatting in the 0,000.00 style and in the conversion of this style into an integer for insertion in the database.
I will continue the tests and check the results, but I thank you all for your attention and contributions that only improve our knowledge.

 

[Mahares]

I had already done tests iterating line by line and the result was correc

What do you mean by line by line. Can you show your code. When you use the below code, you only get one record and there is no iterating. It is extremely fast, especially if you are using one column in the query. And above all, it calculates the correct SUM() without having to change your data to integers.

Dim strQuery As String = "select Coalesce(sum(value), 0) AS mysum From table1"
    Dim rs As ResultSet = sql.ExecQuery(strQuery)
    If rs.NextRow Then
        Log(rs.GetDouble("mysum"))
        Log(NumberFormat2(rs.GetDouble("mysum"),1,2,2,False))
    End If

 

[Cleidson]

Mahares,
I usually call it the test of nine. I used your example, with SUM and did another procedure iterating line by line, like:

Dim strQuery As String = "select value From table1"
Dim value as double = 0
Dim rs As ResultSet = sql.ExecQuery(strQuery)
If rs.NextRow Then
value = value + rs.GetDouble(“value”)
End If

And I compared the results. It was in this way that I verified that the operations did not match and that there were differences in cents. If it was a penny, you could still think about rounding, but 10 cents was a lot and, to make matters worse, it was less.

 

[Mahares]

did another procedure iterating line by line, like:

Your code is wrong. It returns 0 all the time. If you want help, you need to post the exact correct code and a database that represents your numbers so we can make the comparison. If you are serious about learning, B4X and particularly SQLite, you can get a lot of help from members here.
If you are happy with your results, that is what counts. It is your project after all. But, I will not touch it with a ten foot pole the way you are approaching it.

 

[Cleidson]

.
I'll arrange it as soon as I get home. For the record, the laugh is about keeping your distance.

 

[OliverA]

I'm pretty sure the answer/explanation to this issue lies within this thread: https://www.b4x.com/android/forum/threads/sqlite-cursor-getstring-versus-getdouble.52903/

Internally (looking at the SQL library source posted on GitHub), ExecQuerySingleResult uses getString to retrieve the value from the query. If (as discussed in the linked post) the REAL SQLite value returned happens to contain scientific notation, getString will truncate the result. The issue can be avoided by using getDouble instead of getString. This explains why @Mahares code produced an approximation with ExecQuerySingleResult (that internally uses getString) and the correct result when using getDouble. Also, please note that this only seems to affect SQLite on Android.

GitHub link: https://github.com/AnywhereSoftware...heresoftware/b4a/sql/SQL.java#L275C18-L275C27

 

[Cleidson]

Hello everybody,
Follow the example as I said in post #25. The example simulates 10,000 random releases of doubles and integers and then performs a query with the sum function and an iteration line by line.

In my tests there was only difference in the sum function when querying the column with the numbers in REAL (Double).

The same problem I had in the app I'm developing.

So the solution was to declare every value field as an integer, in the database, and when I have to show the user a query or the result of an operation, I use a procedure that formats the variable for the mask 0.000,00

At the moment it has met expectations, but it is necessary to scale the bank and carry out more tests.
Thanks in advance for everyone's attention.

 

[Mahares]

Thanks in advance for everyone's attention

Do not use: ExecQuerySingleResult.
If you replace your code:

sql = "Select Coalesce(Sum(vlr), 0) From Account"
    vlr1 = db.SQLApp.ExecQuerySingleResult (sql)
    Label3.Text = $"Function Sum with Double: ${NumberFormat2(vlr1, 1, 2, 2, False)}"$
    'Label3.Text = $"Function Sum with Double: ${vlr1}"$
    Label3.Top = 43%y
    Label3.Height = 8%y

With my code below , your numbers will work and match label4 results where you use line by line:

Dim sqlmahares As String = "Select Coalesce(Sum(vlr), 0) as mysum From Account"
    Dim rs As ResultSet = db.SQLApp.ExecQuery(sqlmahares)
    If rs.NextRow Then
        vlr1 = rs.getdouble("mysum")
    End If
    Label3.Text = $"Function Sum with Double: ${NumberFormat2(vlr1, 1, 2, 2, False)}"$
    Label3.Top = 43%y
    Label3.Height = 8%y
    rs.Close

By the way you should close the resultset every time you are done with a query and reopen for the other query, although this did not affect anything.
ADDED THE FOLLOWING TEST LATER: The method using If Rs.nextrow I suggested runs at least 5 times faster than the rs.nextrow line by line you are using. Tested in debug and release.