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.
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).
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
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.
@balaji.ramaswamy@5ubbu
I think I understand the problem better: the issue has to do with the datatypes. See this article.
Could Dremio implement something equivalent to NUMBER(X,Y)?