andygrove opened a new issue, #95: URL: https://github.com/apache/datafusion-spark/issues/95
Spark's [`FormatString.eval`](https://github.com/apache/spark/blob/v4.1.1/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/stringExpressions.scala#L2079-L2087) passes each argument's *internal* Catalyst value to `java.util.Formatter`. It does not first convert the value to a Java type: ```scala val arglist = children.tail.map(_.eval(input).asInstanceOf[AnyRef]) formatter.format(pattern.asInstanceOf[UTF8String].toString, arglist: _*) ``` So DECIMAL, DATE and TIMESTAMP arguments behave very differently from what `crates/spark/src/function/string/format_string.rs` and its tests assume. I found this by running `format_string.slt` on PySpark 4.2.0 with the runner from #54. ### DECIMAL arguments A DECIMAL reaches the formatter as `org.apache.spark.sql.types.Decimal`, which is not a `java.lang.Number`. So every floating-point conversion raises an error: ```sql SELECT format_string('Decimal: %.2f', CAST(123.456 AS DECIMAL(10,3))); -- datafusion-spark: Decimal: 123.46 -- Spark 4.2.0: error: f != org.apache.spark.sql.types.Decimal ``` The same happens for `%e`, `%E`, `%g`, `%G`, `%a` and `%A`. `%s` prints `Decimal.toString` (`123.456`), which #39 already covers. ### TIMESTAMP arguments A TIMESTAMP reaches the formatter as a `Long` of **microseconds** since the epoch. The `%t` conversions read a `Long` as **milliseconds**, so every date and time field is wrong by a factor of 1000: ```sql SELECT format_string('Year: %tY', TIMESTAMP '2023-12-25 14:30:45'); -- datafusion-spark: Year: 2023 -- Spark 4.2.0: Year: 55952 SELECT format_string('Epoch seconds: %ts', TIMESTAMP '1970-01-01 00:00:01'); -- datafusion-spark: Epoch seconds: 1 -- Spark 4.2.0: Epoch seconds: 1000 SELECT format_string('String: %s', timestamp_seconds(1703512245)); -- datafusion-spark: String: 1703512245 -- Spark 4.2.0: String: 1703512245000000 ``` 35 records in `format_string.slt` apply `%t` or `%T` to a TIMESTAMP and differ from Spark, almost all of them for this reason. ### DATE arguments A DATE reaches the formatter as an `Integer` of days. `%t` does not accept an `Integer`, so it raises an error: ```sql SELECT format_string('Year: %tY', date_from_unix_date(19716)); -- datafusion-spark: Year: 2023 -- Spark 4.2.0: error: Y != java.lang.Integer ``` ### Decision needed Spark's results here are artifacts of how it hands internal values to Java, and they are hard to call intended. The options are: 1. Emulate Spark: raise an error for floating-point conversions of DECIMAL and for `%t` of DATE, and treat a TIMESTAMP as epoch microseconds read as milliseconds. 2. Keep the current, more useful behavior and document the divergence (#55). Comet would want option 1, or would fall back to Spark for these argument types. ### Tests that rely on DataFusion's literal types Many other differences in `format_string.slt` come from literal types rather than from `format_string` itself. Spark types an unsuffixed literal such as `3.14159` as DECIMAL, and DataFusion types it as DOUBLE (apache/datafusion#27185). So `format_string('%.2f', 3.14159)` tests a DOUBLE in DataFusion and fails with the DECIMAL error above in Spark. This affects about 40 records using `%f`, `%e`, `%g` and `%a`. Those tests should write the literal as `3.14159::DOUBLE` so that both engines test the same type. ### Related - #39: `%s` of a DECIMAL prints the unscaled value, and `%g` strips trailing zeros - #79: `%b` of a non-boolean, `%h`/`%H` and a non-string format - `format_string.slt:474` passes `'America/New_York'` as the *format* argument of `from_unixtime`, which Spark rejects as an invalid pattern. The test probably meant to pass a time zone. -- 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]
