[Bug] format_string prints the unscaled value for %s of a DECIMAL and strips trailing zeros for %g #39

Describe the bug

Spark's format_string hands its arguments to Java's java.util.Formatter (stringExpressions.scala#L2045). Two behaviors of datafusion-spark differ from that:

1. %s with a DECIMAL argument prints the unscaled integer. Spark prints the decimal with its scale.
2. %g and %G drop trailing zeros like C does. Java keeps the full precision, which is six significant digits by default.

To Reproduce

Spark 4.0.0:

SELECT format_string('String: %s', CAST(123.456 AS DECIMAL(10,3)))
-- String: 123.456
SELECT format_string('%s', CAST(0.5 AS DECIMAL(10,2)))
-- 0.50
SELECT format_string('Compact: %g', 1234.5D)
-- Compact: 1234.50
SELECT format_string('%g', 0.0001D)
-- 0.000100000

A literal such as 1234.5 is a DECIMAL in Spark and makes %g throw (see below), so the examples use 1234.5D.

DataFusion, existing tests:

• format_string.slt#L1623 expects String: 123456 for %s of arrow_cast(123.456, 'Decimal128(10, 3)'). The %S test at #L1628 and the Decimal256 test at #L1649 show the same.
• format_string.slt#L155 expects Compact: 1234.5 for %g of 1234.5. The %G test at #L166 and the Float32 test at #L1565 are the same.

The 0.0001 example was not run on DataFusion.

Expected behavior

Print a DECIMAL argument of %s with its scale, keep trailing zeros for %g and %G like Formatter, and update the expectations above.

Related

• %t conversions on TIMESTAMP values are already tracked in bug: datafusion-spark format_string %t timestamp specifiers do not match Spark behavior #14.
• %f, %e and %g with a DECIMAL argument throw in Spark (IllegalFormatConversionException: f != org.apache.spark.sql.types.Decimal) but DataFusion formats them, for example format_string.slt#L609 expects Decimal: 123.456000. DataFusion is more permissive there, so this is informational.

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 #7. 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.