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.

 

[aeric]

Never use REAL data type. It seems it is a FLOAT type and I don't see it is suitable for currency. Maybe you can try use CAST to Decimal(10, 2) or ROUND.

 

[Cleidson]

Aeric,
What data type would you recommend for the field?
If it is of type string would the sum function work?

 

[klaus]

The REAL data type is an 8 byte floating point number the same as Double.
To get correct values you need to use GetDouble.
If you use GetString, the number is converted in a Float type by the underlying Java code.

 

[Cleidson]

Hi Klaus,
I don't plan on iterating row by row as it could take a long time when the database scales. For this I use the following structure to perform the sum:

Dim valueField as double
Dim sql as string
sql = “select Coalesce(sum(value), 0) From Table”
valueField = db. ExecSingleResult(sql)

See that there is no need to use getDouble, as I do not use Resultset in the query.

 

[aeric]

Aeric,
What data type would you recommend for the field?
If it is of type string would the sum function work?

I usually use NUMERIC and as pointed out by klaus, I use GetDouble to get the value.

 

[klaus]

Dim valueField as double
Dim sql as string
sql = “select Coalesce(sum(value), 0) From Table”
valueField = db. ExecSingleResult(sql)

In that case i do not know what happens.

Do you have a small program showing the problem, so we could look at it ?

 

[ilan]

i tried to reproduce your error but i also don't see a problem in your code

    Dim sqlstr As String = "select Coalesce(sum(value), 0) From table1"
    Dim valueField As Double = sql.ExecQuerySingleResult(sqlstr)
    Log(valueField &  " : " & sum)

i guess there is a rounding issue because you use float but convert it to double and you are talking about differences in cents.

 

[Cleidson]

Klaus,
Unfortunately, I don't have a small program, because what I'm developing is an application for financial purposes that is all interconnected.

ilan,
I don't know if it would be a rounding issue. I have taken all possible care to handle and enter the values into the database. No values have been entered with more than two digits. A query to the same demonstrates this.

As testing is in its infancy, there is time to test other data types for value fields. I'll do new tests and if the behavior continues I'll create a small demo.
Thank you all for your attention;

 

[Brian Dean]

What data type would you recommend for the field?

There is only one safe data type for currency, in my opinion, and that is integer, where one unit matches the smallest currency unit be it penny, centime, baht or fils. I know that doubles will usually work, but every time that the sort of problem described in this thread arises it is when integers are not being used. And I know that if you want to divide ten dollars equally between three accounts then there is a penny left over, but that is exactly what happens in the real world; you have to deal with it.

Given all that, I must admit that a discrepancy of 10 cents does not sound like a rounding error and maybe there is a coding error somewhere. But if this program is being aimed at a wide distribution then it needs watertight accounting: use integers!

 

[Cleidson]

Brian,
I've been doing a fine-tooth comb through the code looking for any coding errors, like you said. But, the integer idea is an interesting idea and wouldn't need much modification to the code I developed as a whole. As I said, the entire app is in the testing phase, so there's still time for testing.
Thank you for your attention.

 

[Mahares]

Unfortunately, I don't have a small program

Sine you cannot post a small project, I highly recommend you post a copy of the SQLite database (small) or large sample) with numbers representing your issue. You do not have to include confidential data. Let us know what column you are summing and I can assure you that you will get some very constructive help.
The Sum() unction has a way of returning floating point values which sometimes approximate and does not give a perfect summation.

 

[Cleidson]

Mahares,
I understood. The strangest thing is that there are several queries with different parameters and this problem occurs in two queries. In others, the query returns correct values. But your position, and that of other colleagues who responded to the topic, brings more sense to the problem itself. I work on this app in my spare time, so I'm going to test all the possibilities posted here and as soon as I have an answer I'll be in touch. I believe that this weekend I already have a position.
Thank you for your attention.

 

[aeric]

ExecSingleResult2

My second tip is avoid using ExecSingleResult2. There may be a possibility your data contains NULL values, which could cause weird result.

[EDIT: NO BUGS HERE] ExecQuerySingleResult (SQLite)

I assumed that a record with those values certainly existed. Yes, unless you are very sure about your data. I modified @aeric's code to make sure I choose a parameter that does not exist, but it still did not crash. It still returns a null value. I think your problem is somewhere else. I do...

www.b4x.com

 

[Mahares]

To calculate the sum of a given numeric columns I have created a complete B4A and B4J project to demonstrate the findings. Here I am including the full B4A code: In both projects, I have 2 buttons. One button uses ExecQuerySingleResult and the other button uses a resultset method.
1. In the B4A project the button that uses ExecQuerySingle approximates the sum ( 41591.70), whereas the button that uses the resultset accurately calculates the sum of column in question (41591.66).
2. In the B4J project the SQlite engine shows the same exact numerical sum for both buttons (41591.66).
3. I also tested both platforms using over 1000 records. The behavior is the same as using a small database. I should note that in B4J both buttons yielded the same accurate sum, whereas in the B4A project the ExecQuerySingle approximates the sum, but the resutlset button gives accurate sum.
4. I also tested for a data type of TEXT or REAL for the column in question. Both data types yielded similar results and the data type was not a factor.
5. NULL values were not a factor. Includimg or removing them did not alter the results.

Sub Class_Globals
    Private Root As B4XView
    Private xui As XUI
    Private sql As SQL
    Private Const Filename As String = "test.db"
End Sub

Public Sub Initialize
End Sub

Private Sub B4XPage_Created (Root1 As B4XView)
    Root = Root1
    Root.LoadLayout("Layout1")  'has 2 buttons:  They are Button1,  Button2
    
    xui.SetDataFolder("sql example") 'required in B4J
    #if B4J
    sql.InitializeSQLite(xui.DefaultFolder, Filename, True)
    #else
    sql.Initialize(xui.DefaultFolder, Filename, True)
    #End If
    
    sql.ExecNonQuery("DROP TABLE IF EXISTS table1")
'    sql.ExecNonQuery("CREATE TABLE IF NOT EXISTS table1 (item TEXT , value INTEGER)")
    sql.ExecNonQuery("CREATE TABLE IF NOT EXISTS table1 (item TEXT TEXT PRIMARY KEY, value TEXT)")
'    sql.ExecNonQuery("CREATE TABLE IF NOT EXISTS table1 (item TEXT PRIMARY KEY, value REAL)")
    filldata
End Sub

Private Sub filldata
    Dim strQuery As String
    strQuery="INSERT OR IGNORE INTO table1 VALUES(?, ?)"
    sql.ExecNonQuery2(strQuery, Array As String(0,Null))
    sql.ExecNonQuery2(strQuery, Array As String(1000,Null))
    sql.ExecNonQuery2(strQuery, Array As String(500,Null))
    
    sql.ExecNonQuery2(strQuery, Array As String(2000,6.75))
    sql.ExecNonQuery2(strQuery, Array As String(44,2109.79))
    sql.ExecNonQuery2(strQuery, Array As String(10440,6798.37))
    sql.ExecNonQuery2(strQuery, Array As String(1234,6098.75))
    sql.ExecNonQuery2(strQuery, Array As String(37,26578))    
    
'    For i =0 To 1000 'it can also be tested with 1001 records
'        sql.ExecNonQuery2(strQuery, Array As String("item"&i,Rnd(30000, 130000)/7))
'    Next
    
End Sub

Sub Button1_Click   'Using ExecQuerySingleResult
    Dim strQuery As String = "select Coalesce(sum(value), 0) From table1"
    Dim valueField As Double = sql.ExecQuerySingleResult(strQuery)
    Log(valueField)
    Log(NumberFormat2(valueField,1,2,2,False))
End Sub

Private Sub Button2_Click   'Using resultset
    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
End Sub

Those were the details and here is the summary to my findings:

	B4A	B4J
ExecQuerySingleResult	Aproximate sum	Accurate sum
Resultset	Accurate sum	Accurate sum

 

[makis_best]

With a quick search on Google about Coalesce bug I found lot of articles of people complaining
that Coalesce not working correct and return wrong results.
Maybe need to check that case also

 

[Mahares]

that Coalesce not working correct and return wrong results.
Maybe need to check that case also

I already tested it also with IFNULL instead of COALESCE and with TOTAL which is in a way equivalent to SUM and the results were the same.

 

[makis_best]

I already tested it also with IFNULL instead of COALESCE and with TOTAL which is in a way equivalent to SUM and the results were the same.

Maybe I needed to say something more about the articles I find online....
All out there talking both about IFNULL and COALESCE.
I just mention COALESCE cuz it was the only one at your example.
Please... Google your self and you find out.

 

[Mahares]

Google your self and you find out.

Your name says it all. You are the best. I googled and did not find anything reporting problems with COALESCE or IFNULL in SQLite. Can you put down a couple of links of what you found yourself and exactly what problems the articles report about them.

 

[makis_best]

Your name says it all. You are the best. I googled and did not find anything reporting problems with COALESCE or IFNULL in SQLite. Can you put down a couple of links of what you found yourself and exactly what problems the articles report about them.

No need to be offensive. We all here to help.
Don't stack specific with sqlite. Github is common for developers.
Start with this one.
https://bugs.mysql.com/bug.php?id=106267

And something more important was that in my search i find
lot of people complaining not only about sqlite but also for
SQL Server, PostqreeSQL, MYSQl. So I think it is something
common from the specific command