Sum on join across datasets is not correct

Hi,

I am trying to run a query that joins a products table on one db server again a order_items table in schema B on another db server like so (both are mysql databases)

select a.sku, sum(b.price) from db1.catalog.products as a join db2.sales.order_items as b on b.product_id = a.id group by a.sku

The price column is a decimal (10,2) but the result set contains values like 12.899999999909 which the expected value is actually 12.9. I know this because the two servers are simply read replicas from a master and hence contain the same data.

When I run this query replacing db1 with db2 (or vice-versa) the results returned are exact and as expected.

Is there something I am doing wrong or missing?

Having the same problem, but with an even simpler context:
SELECT a, b, c, d, e, f, g, h, a+b+c+d+e+f+g+h AS total FROM “x”

columns a to h are floats. Sometimes the sum is correct, but sometimes there seems to be a conversion issue (so, instead of 15.95 I get 15.950000000000001).

More detail about the data: I took the NYC yellow cab trips open data set for June 2019 (https://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2019-06.csv), converted the columns with the amounts to floats and ran queries like the one above.

@5ubbu @knorpsiu

What versions of Dremio are you using? If it is < 3.3.1 then we do not have any decimal support and treat them on Dremio as double. Starting 3.3.1 we support DECIMAL for PARQUET and ORC

Thanks
@balaji.ramaswamy

I am using version 3.3.2 community edition.
One question, though: I converted the text columns in the GUI (using the “Convert Data Type…” option in the drop down menu by the column name while browsing the dataset) and kept the original column. I noticed the SQL it generated was something like "SELECT … fare_amount, CONVERT_TO_FLOAT(fare_amount, 1, 1, 0) AS “fare_amount (new)”, … "; it seems the function “CONVERT_TO_FLOAT” is not part of the available SQL functions list (on the right panel when I am creating a new query).
Can this have anything to do with the issue?

@balaji.ramaswamy : Thanks for the update. I was using the latest tag around mid-July (which I now checked is 3.2.4). Let me quickly try this again with the latest now.