comphead opened a new issue, #26335: URL: https://github.com/apache/datafusion/issues/26335
### Describe the bug `SparkRound` returns the type of its first argument ([round.rs#L119](https://github.com/apache/datafusion/blob/9dec77584ea39d93aa01bba7f880b318b37d2c24/datafusion/spark/src/function/math/round.rs#L119)) and `round_decimal` rescales the rounded value back to the input scale ([round.rs#L364](https://github.com/apache/datafusion/blob/9dec77584ea39d93aa01bba7f880b318b37d2c24/datafusion/spark/src/function/math/round.rs#L364)). Spark computes a different result type ([mathExpressions.scala#L1490-L1506](https://github.com/apache/spark/blob/v4.0.0/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/mathExpressions.scala#L1490-L1506)). For `DECIMAL(p, s)` and a target scale `d >= 0` it is `DECIMAL(min(p - s + 1 + min(s, d), 38), min(s, d))`. For `d < 0` the scale is `0` and the precision is `min(max(p - s + 1, 1 - d), 38)`. So the rounded value has fewer digits than the input in Spark (`3` and not `3.0`), and anything that relies on the declared result type matching Spark's analyzer sees a different schema. ### To Reproduce Spark 4.0.0: ```sql SELECT round(CAST(2.5 AS DECIMAL(2,1)), 0) -- 3, DECIMAL(2,0) SELECT round(CAST(2.345 AS DECIMAL(10,3)), 2) -- 2.35, DECIMAL(10,2) SELECT round(CAST(125.0 AS DECIMAL(10,1)), -1) -- 130, DECIMAL(10,0) SELECT round(CAST(123.456 AS DECIMAL(10,3)), 5) -- 123.456, DECIMAL(11,3) SELECT round(CAST(9.9 AS DECIMAL(2,1)), 0) -- 10, DECIMAL(2,0) ``` DataFusion, existing tests in `round.slt` (the result keeps the input scale): | query | expected in `round.slt` | |---|---| | `round(2.5::decimal(2,1), 0::int)` ([#L359](https://github.com/apache/datafusion/blob/9dec77584ea39d93aa01bba7f880b318b37d2c24/datafusion/sqllogictest/test_files/spark/math/round.slt#L359)) | `3.0` | | `round(2.345::decimal(10,3), 2::int)` ([#L381](https://github.com/apache/datafusion/blob/9dec77584ea39d93aa01bba7f880b318b37d2c24/datafusion/sqllogictest/test_files/spark/math/round.slt#L381)) | `2.350` | | `round(125.0::decimal(10,1), -1::int)` ([#L404](https://github.com/apache/datafusion/blob/9dec77584ea39d93aa01bba7f880b318b37d2c24/datafusion/sqllogictest/test_files/spark/math/round.slt#L404)) | `130.0` | Many more decimal tests in the same file show the input scale. The `DECIMAL(10,3)` row with scale `5` shows the precision growing by one when no rounding is needed. The `9.9` row shows why Spark leaves room for it: the rounded value `10` needs one more integral digit. By reading `round_decimal`, `round(9.9::decimal(2,1), 0)` in DataFusion computes an unscaled value of `100` in a `Decimal128(2, 1)` array, which does not fit the declared precision. This was not run. ### Expected behavior Compute Spark's result type in `return_type`, round to the scale `min(s, d)` and update the expectations in `round.slt`. Related but different: #22812 (`round()` on floating point types). ### Additional context Found by running the queries in `datafusion/sqllogictest/test_files/spark` on a local Spark and comparing the results with the expected values in the files, which is the kind of check proposed in #17045. Spark results come from Apple's build of Spark 4.0.0 (`4.0.0.65-apple`) with a UTC session and `spark.sql.ansi.enabled=false` unless stated. The Spark source links point at the `v4.0.0` tag. DataFusion results are the sqllogictest expectations at 9dec77584, which CI enforces. Anything marked as not run was derived from reading the code. -- 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]
