andygrove opened a new issue, #27335: URL: https://github.com/apache/datafusion/issues/27335
### Describe the bug When a FLOAT or DOUBLE meets a DECIMAL in a comparison, `IN`, `CASE`, `COALESCE` or another type union, DataFusion casts the float to a decimal. [`decimal_coercion`](https://github.com/apache/datafusion/blob/e7c51c7fb5ebd301f5eb239afbea159e2cfbcfb6/datafusion/expr-common/src/type_coercion/binary.rs#L1177-L1209) turns `Float64` into `Decimal128(30, 15)` ([#L1395](https://github.com/apache/datafusion/blob/e7c51c7fb5ebd301f5eb239afbea159e2cfbcfb6/datafusion/expr-common/src/type_coercion/binary.rs#L1395), next to a TODO saying it may overflow) and widens from there. Most doubles don't fit that type: - values of 1e15 or more overflow, and the query fails; - NaN and the infinities can't be cast, and the query fails; - values smaller than 1e-15 round to zero, and the query returns wrong results. Arithmetic isn't affected: `double + decimal` is a `Float64`. ### To Reproduce On `main` (e7c51c7fb), with the default settings: ```sql SELECT CAST('1e20' AS DOUBLE) > CAST(1.5 AS DECIMAL(2, 1)); -- Arrow error: Invalid argument error: 99999999999999996863.366107917975552 is too large to store in a Decimal128 of precision 30. Max is 999999999999999.999999999999999 SELECT CAST('NaN' AS DOUBLE) > CAST(1.5 AS DECIMAL(2, 1)); -- Arrow error: Cast error: Cannot cast to Decimal128(30, 15). Overflowing on NaN SELECT CAST('1e-20' AS DOUBLE) > CAST(0 AS DECIMAL(2, 1)); -- false SELECT CASE WHEN true THEN CAST('NaN' AS DOUBLE) ELSE CAST(1.5 AS DECIMAL(2, 1)) END; -- Arrow error: Cast error: Cannot cast to Decimal128(30, 15). Overflowing on NaN CREATE TABLE m (d DOUBLE, n DECIMAL(10, 2)) AS VALUES (1e20, 1.5), (1e-20, 0); SELECT d, n FROM m WHERE d > n; -- Failed to cast field 'd' from Float64 to Decimal128(30, 15) ``` With `datafusion.sql_parser.parse_float_as_decimal = true` a plain literal is enough: `SELECT CAST('1e-20' AS DOUBLE) > 0.0` returns false. ### Expected behavior `true`, `true`, `true`, `NaN`, and both rows of `m`. Spark, which the `Decimal128(30, 15)` mapping comes from (`DecimalType.forType`), doesn't use it for comparisons: it casts the decimal to DOUBLE ([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)). DOUBLE is also its common type of a DECIMAL and a FLOAT or DOUBLE in `CASE`, `IF`, `COALESCE` and `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)). Spark does this in every version from 3.4 to 4.2. ### Additional context Preferring the decimal has a purpose, though (see #4024). In TPC-H q6, `l_discount between 0.06 - 0.01 and 0.06 + 0.01` compares a `DECIMAL(15, 2)` column with float literals, and `0.06 + 0.01` is `0.06999999999999999`. Cast to `Decimal128(30, 15)` it rounds back to `0.07`, so the rows where `l_discount` is 0.07 are kept. Compared as doubles, `0.07 <= 0.06 + 0.01` is false. A fix has to keep that case working. Some options: - cast to DOUBLE only when the float side isn't a constant; - use DOUBLE when float literals are already decimals, that is with `parse_float_as_decimal` or the Spark dialect; - make the rule configurable. This will matter more once #27185 is fixed: with the Spark dialect, `1.5` becomes a DECIMAL (#27213), so every `WHERE double_col > 0.5` hits it. -- 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]
