SQLite SUM oddity

In a test SQLite database I find the following and don’t understand it. Let’s say I have 3 REAL columns with the following 2 rows:

column1 79.8
column2 744.8
column3 824.6

column1 5.8
column2 6.8
column3 12.6

If I execute the following statement

select * from test where column1 + column2 <> column3

I get the first row and not the second row. Why is the first row showing up as not totaling properly?

If I execute

select sum(column1 + column2) from test where rowid = 1

I get the correct answer (824.6).

Testing with SQLiteStudio I get 79.8 + 744.8 = 824.59999999, so I guess SQLite find that different than 824.6

Maybe Xojo is rounding back the 824.5999999 to 824.6

Edit: using SQLite CLI I get:

sqlite> select *, (column1 + column2) from sum where (column1 + column2) <> column3 ; 79.8|744.8|824.6|824.6
and

sqlite> select *, (column1 + column2) from sum where (column1 + column2) = column3 ; 5.8|6.8|12.6|12.6
the CLI is reporting 824.6 visually and it say is different than 824.6, then row2 is saying that 12.6 is equal to 12.6

After some tests, I think you can “fix” the problem with:

select * from test where round((column1 + column2), 1) <> column3 ;

this return 0 records, and:

select * from test where round((column1 + column2), 1) = column3 ;

return both records.

Thanks, Alberto. That works.

Good to see you have a workaround.
As a general rule, don’t write code that compares two floats for equality.
They can vary by very small amounts.

[quote=411893:@Jeff Tullin]Good to see you have a workaround.
As a general rule, don’t write code that compares two floats for equality.
They can vary by very small amounts.[/quote]
Thanks Jeff, I learned that with this lesson.

I looked at printf(), that could be another option

select *, printf("%.1f", (column1 + column2)) as sum from test where sum <> column3 ;

[quote=411938:@Alberto De Poo]I looked at printf(), that could be another option

select *, printf("%.1f", (column1 + column2)) as sum from test where sum <> column3 ;

not saying that won’t work… just be careful of the SQLite Affinity conversion rules… since this compares a “string” to a “double”

Thank you Dave, you are correct the code was tested with the simple database (3 columns and 2 records) and it worked. But as you said if printf is going to be used is a better practice to compare “string” with “string”.

This also works:

select *, printf("%.1f", (column1 + column2)) as sum from test where sum <> printf("%.1f", column3) ;

[quote=411976:@Alberto De Poo]
This also works:

select *, printf("%.1f", (column1 + column2)) as sum from test where sum <> printf("%.1f", column3) ;

Only for equality.
The string ‘25.3’ is less than (<) ‘3.5’.

If you really need to get picky… mult x 100, and cast to an Int64