Numeric precision in reflections

1

Hi everyone,

In postgres I have a table T like this:

create table T (
dimension varchar,
measure numeric)

Measure only contains values with a maximum of two decimal places (e.g. 0.25, 0.1, 0.85)

In postgres the query:

select sum(measure) from T where dimension='test' group by dimension

will show 114 for sum(measure) (which is exact).

The same query without relections in dremio will also give 114.

If I create a reflection in Dremio for table T like this:

ALTER DATASET test.t
CREATE AGGREGATE REFLECTION test_t 
USING
DIMENSIONS (
dimension
)
MEASURES (
measure
)

and then rerun the same query dremio will show 114.00000000000003 as the result for sum(measure).

Obviously casting sum(measure) to a single precision type (float) will give me 114 again but I still wonder about the deviation. In the query Dremio uses to create the reflection the datatype of measure is not beeing messed with.
Is this a problem with Dremio internal datatypes?

2

We can try to reproduce this on our side.
Could you please get the profile and upload it here?

You can do this by going to the Jobs tab, select the job which was accelerated using reflection, click download profile.

Screenshot attached.

3

Sorry for answering so late. Here is a query profile for a query that is accelerated by an aggregate reflection and produces wrong results. (e.g sum of postgres numeric values with a maximum of 2 decimal places will deliver 1888.9999999999998).

c9935f74-5f0a-482e-b9e0-61bc228cfd15.zip (9,9 KB)

Running the same query with the reflection turned off will deliver the right value(1889)

4

Hi,
i got the same issue.