metabase
Filter by this value when selecting "equals" / "not equal to" doesn't work for the aggregated queries
Describe the bug
When you click on the value in the result of the aggregated query and select "=" or "≠" in Filter by this value, you get the query that produces no results.
To Reproduce
- Go to + New question
- Select Products table from the Sample database
- Add Sum of Price and group by Category
- Click on one of the bars in the viz or one of the Sum of Price values in the resulting table
- Select "=" or "≠" in Filter by this value
- See No results!
Expected behavior
It should filter the result by the specified value
Logs
No response
Information about your Metabase installation
0b7bf91
Severity
Produces incorrect results
Additional context
No response
Looks like floating point precision issue, the filter uses a very specific value:
Using a "between" filter with 1 less digit gives a correct result:
This is JSON serialization issue. Can be reproduced on 693126716b9a6ecad7345a8983ad4f10f1b019f2, master, pre-v56. Repro steps:
- New -> Question -> Orders -> Sum by Total -> Group by Category
- Visualize -> Switch to table -> Click on the Count cell for Gadget ->
=-> See no results
The problem is that there is precision loss when the value is serialized in the BE endpoint. Compare:
The BE should keep maximum possible precision for float/double when serializing to a string. Note - the TOTAL column has FLOAT type in H2.
@ranquild This is still the same as the last time I looked at it. The precision in the DB exceeds that of floating point numbers in both js and java.
Chrome console:
Clojure console:
Slack thread https://metaboat.slack.com/archives/C0645JP1W81/p1752077233481669
H2 in particular auto-promotes precision during aggregation (docs, some impl).
We explored a theory that there was some kind of truncation occurring first in H2's decimal representation and that this then got rounded to a different value on the JVM, but that would seem to violate the roundtrip idempotency guarantee of the IEEE 754 standard (float -> decimal -> float is idempotent; decimal -> float -> decimal is not).
In any case, I don't believe there is a Double that could be rounded or truncated to 406621.245803247473363:
user=> (Double/longBitsToDouble 0x4118D174FBB3D8B5)
406621.2458032475
user=> (Double/longBitsToDouble 0x4118D174FBB3D8B4)
406621.24580324744
My uninformed guess is that H2's promotion of decimal precision is to avoid issues with mathematically identical aggregations being unequal based on execution details (order of operations). I think our best bet is either still a between utilizing ULP when aggregations are involved, or else BigDecimal use throughout the pipeline.
From @galdre :
We could add a ~= drill that explicitly searches between an inced and deced ULP (i.e., ~= on 406621.2458032475 would search between > 406621.24580324744 and < 406621.24580324756. This way it's explicit and the user opts into the behavior.
