REAL values (floating-point) comparing issue in Sqlite

Question

I make a query base on sample database chinook.db (https://www.sqlitetutorial.net/sqlite-sample-database/) below, but the result is strange.

-- SQLite version 3.30.1 2019-10-10 20:19:45
select
    Total,
    TotalSaleValue
from invoices
natural join (
    select
        InvoiceId,
        sum(UnitPrice) as TotalSaleValue
    from invoice_items
    group by InvoiceId
)
where Total != TotalSaleValue;

-- Output
Total       TotalSaleValue
----------  --------------
13.86       13.86
13.86       13.86
13.86       13.86
13.86       13.86
13.86       13.86
13.86       13.86
13.86       13.86
13.86       13.86
13.86       13.86
13.86       13.86
...

I use where Total != TotalSaleValue to filter the unequal values, and I expect to have the empty row. But, the result shows Total and TotalSaleValue with the same value?!

Total is the value from invoices table, and the value of TotalSaleValue is calculated from invoice_items table which is the sum of the UnitPrice of each invoice.

-- invoice_items Table
InvoiceLineId  InvoiceId   TrackId     UnitPrice   Quantity
-------------  ----------  ----------  ----------  ----------
1              1           2           0.99        1
2              1           4           0.99        1
3              2           6           0.99        1
4              2           8           0.99        1
5              2           10          0.99        1
6              2           12          0.99        1
7              3           16          0.99        1
8              3           20          0.99        1
9              3           24          0.99        1
10             3           28          0.99        1

-- invoices Table
InvoiceId   ...         CustomerId  Total
----------  ----------  ----------  ----------
1           ...         2           1.98
2           ...         4           3.96
3           ...         8           5.94
4           ...         14          8.91
5           ...         23          13.86
6           ...         37          0.99
7           ...         38          1.98
8           ...         40          1.98
9           ...         42          3.96
10          ...         46          5.94

Does anyone know what's wrong with my query?

Update:

I found that the result would be correct if I cast the Total and TotalSaleValue to TEXT and give the alias name X and Y.

select
    cast(Total as text) as X,
    cast(TotalSaleValue as text) as Y
from invoices
natural join (
    select
        InvoiceId,
        sum(UnitPrice) as TotalSaleValue
    from invoice_items
    group by InvoiceId
)
where X != Y;

I bet if you use `printf()` to show more decimal digits, you'll see the numbers aren't exactly the same. Very common issue when comparing floating point numbers. See [Is floating-point math broken?](https://stackoverflow.com/questions/588004/is-floating-point-math-broken) — Shawn, Nov 07 '19 at 02:49
@Shawn I use `printf("%10.20f", Total)` to see the result. The values are exactly the same. — Leon Huang, Nov 07 '19 at 10:36

Leon Huang · Accepted Answer · 2019-11-07T11:38:01.133

Thanks to the reference from @Shwan

https://stackoverflow.com/a/588014/2802074

The result will be correct if I use abs(Total - TotalSaleValue) > 0.00001 to avoid the floating-point break issue.

select
    Total,
    TotalSaleValue
from invoices
natural join (
    select
        InvoiceId,
        sum(UnitPrice) as TotalSaleValue
    from invoice_items
    group by InvoiceId
)
where abs(Total - TotalSaleValue) > 0.00001;

Summary

Instead of using WHERE x == y, always use WHERE abs(x - y) < 0.00001 to compare the REAL (floating-point) values

REAL values (floating-point) comparing issue in Sqlite

Update:

1 Answers1

Summary