andygrove commented on issue #14272: URL: https://github.com/apache/datafusion/issues/14272#issuecomment-6932802046
Some more detail on this, from #27335 (now closed as a duplicate). For comparisons, `IN`, `CASE`, `COALESCE` and unions, `Float64` is coerced to `Decimal128(30, 15)` ([binary.rs#L1395](https://github.com/apache/datafusion/blob/30073406585de59d3884c087d91e919262e602ec/datafusion/expr-common/src/type_coercion/binary.rs#L1395)). Besides the overflow / NaN errors above, values below 1e-15 round to zero, so some queries silently return wrong results: ```sql SELECT CAST('1e20' AS DOUBLE) > CAST(1.5 AS DECIMAL(2, 1)); -- error: too large for Decimal128(30, 15) SELECT CAST('NaN' AS DOUBLE) > CAST(1.5 AS DECIMAL(2, 1)); -- error: Overflowing on NaN SELECT CAST('1e-20' AS DOUBLE) > CAST(0 AS DECIMAL(2, 1)); -- false (expected true) ``` Arithmetic (`double + decimal`) already gives `Float64`. Spark also casts the decimal to DOUBLE for comparisons ([DecimalPrecisionTypeCoercion.scala#L182-L185](https://github.com/apache/spark/blob/v4.2.0/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/DecimalPrecisionTypeCoercion.scala#L182-L185)) and for `CASE` / `IF` / `COALESCE` / `IN` ([TypeCoercionHelper.scala#L194-L195](https://github.com/apache/spark/blob/v4.2.0/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercionHelper.scala#L194-L195)). One thing a fix has to keep working is TPC-H q6 (#4024). `l_discount between 0.06 - 0.01 and 0.06 + 0.01` compares a `DECIMAL(15, 2)` column with float literals. `0.06 + 0.01` is `0.06999999999999999`, which rounds back to `0.07` as `Decimal128(30, 15)` but not as a double. Some ways to handle that: - only use DOUBLE when the float side is not a constant; - use DOUBLE when float literals are already parsed as decimals (`parse_float_as_decimal`, Spark dialect); - make the rule configurable. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
