Summary
Users encounter issues when querying FLOAT columns using the equality (=) operator in Dremio, potentially leading to no results despite existing matching data. This article explains the underlying problems caused by FLOAT's precision and provides best practices to resolve such issues.
Overview
In Dremio, when using the equality (=) operator on FLOAT columns, the floating-point number literal within your query is promoted to a DOUBLE type. This results in precision differences as FLOAT and DOUBLE have different precision levels.
Relevant versions
This article is relevant to Dremio 25.x
Steps to Resolve
Solution Using Range Comparisons:
Rewrite the Query: Instead of using the equality operator (=), use a range comparison to retrieve the expected result.
For example:
SELECT ItemNameType,
SerialNumber,
tDateTime,
autoVersion
FROM Manufacturing."Test Items & Results".Preparation.testing_data
WHERE SerialNumber = '1' AND
tDateTime = '2025-03-02 07:39:51.000' AND
ItemNameType = 18018 AND
autoVersion >= 15.1 AND
autoVersion <= 15.1
Best Practices for Comparison:
Explicit Casting: Ensure consistency by explicitly casting literals to FLOAT when performing comparisons. This can be done by modifying your query condition:
WHERE autoVersion = CAST(15.1 AS FLOAT)
Consistency in Joins: When performing join operations involving FLOAT fields, ensure that both comparison sides are of the FLOAT data type to prevent mismatches.