[EPIC] Support Decimal for User Defined Functions

[theirix]

Is your feature request related to a problem or challenge?

Math UDFs in the datafusion-functions crate support integer and floating types, but not Decimals. This epic is dedicated to adding and improving decimal support for UDFs. It is a follow-up epic to adding Decimal support to the DataFusion core #3523.

So far, it is implemented for log, power binary UDFs; round, ceil unary UDF. Turns out, more code should be moved into helper functions to make UDFs leaner and abstracted from details (e.g., scalar vs. array cases, casting, etc).

Latest state (as for Aug 25)

Legend:

• ✅ - all good, implemented
• ❌ - not supported or not applicable
• ⬜ - DataFusion implementation conforms to possible support, nothing to follow-up

UDF	Is implemented	Could Support Decimal	Comments
abs	✅	✅	Native support, #17808
any_value	✅	✅	Native support by design
approx_distinct	✅	✅	Native support, #23190
approx_median	⬜	❌	Coerce to floats as with majority of DBs, #21074
approx_percentile_cont	⬜	❌	See approx_median
approx_percentile_cont_with_weight	✅	✅	See approx_median
array_agg	✅	✅	Native support by design
avg	✅	✅	Native support, #22713
bit_and / bit_or / bit_xor	⬜	❌	N/A, bit domain
bool_and / bool_or	⬜	❌	N/A, boolean domain
ceil	✅	✅	Native support, #18979
corr	❌	✅	Coerced. Native support is hard. #3572 , #3481, #19463
count / count_distinct	⬜	❌	N/A, integer domain
covar_samp / covar_pop	⬜	✅	See corr
degrees,radians,cot	✅	❌	N/A, float domain, coerced to float
exp	✅	❌	N/A, float domain, coerced to float
factorial	⬜	❌	N/A, integer domain
first_value / last_value	✅	✅	Native support, #17501
floor	✅	✅	Native support, #18979
gcd	✅	✅	Native support, #22655
greatest	✅	✅	Native support by design
isnan	✅	✅	Native support, #20093
iszero	✅	✅	Native support, #20093
least	✅	✅	Native support by design
lcm	✅	✅	Native support, #22655
median	✅	✅	Native support, #24419
log	✅	✅	Partial support, #17023, #17555
ln,log2,log10	⬜	❌	N/A, float domain
min / max	✅	✅	Native support, #17501
nanvl	⬜	❌	N/A, decimals are not nans
nth_value	✅	✅	Native support by design
percentile_cont	✅	✅	Native support, #24419
pi, random	⬜	❌	N/A, float domain
power / pow	⬜	❌	Decimals removed due to complexity, now coerced to floats. #18032
regr_* (e.g. slope)	⬜	❌	See corr
round	✅	✅	Native support, #17054,
signum	⬜	✅	N/A, returns integer
sqrt,cbrt	✅	❌	N/A, float domain, coerced to float
stddev / stddev_pop	❌	✅	Coerced. Native support is hard. #3572 , #21926
sum	✅	✅	Native support, #17591
trunc	✅	✅	Native support, #23320
variance / var_pop	⬜	✅	See stddev
trigonometric functions	⬜	❌	N/A, float domain, coerced to float

Describe the solution you'd like

There are the following primary directions:

1. Adding support for well-known Decimal128 and Decimal256 to existing functions
2. Adding support for new Decimal32 and Decimal64, which are not yet fully supported
3. Refining coercion rules to work with mixtures of floats/decimals
4. Ensuring it would work properly with the new parse_float_as_decimal flag, forcing floats to be decimals after SQL parsing
5. Improving tests to validate correct behaviour for floats/decimals and corner cases
6. Moving some core support to the Arrow libraries

I welcome thoughts and discussions about these directions.

Describe alternatives you've considered

The approach of coercing decimals to floats could work, but it loses precision and data and doesn't match the behaviour of existing SQL engines (Postgres, Spark). Decimals should be first-class citizens.

Additional context

Related tickets:

Function support:

• ROUND support decimal #17054
• feat: support decimal for math functions: pow #18031
• Native decimal 32/64/256 bit support for log #17555
• Add Decimal128 support to Ceil and Floor #7689
• Decimal128 implementation of log loses precision #18524
• Allow log/pow on negative scale decimals #19250
• Allow log with non-integer base on decimals #19347
• Allow pow with negative & non-integer exponent on decimals #19348
• feat: Decimal support for gcd and lcm #19057
• trunc(decimal) produces unexpected/wrong results above 2^53 #22512
• power(decimal, float) should return float #22472
• round decimal incorrect result #19921
• Add Decimal support for floor preimage #20080
• bug: Median() truncates integers #19536
• decimal support for agg function #1545
• Decimal128 support for statistical aggregations #3572
• Align median and percentile_cont implementations (preserve Decimal in percentile_cont, alias median) #22042
• percentile_cont is wrong for Decimal32 with lower interpolation precision #24576
• avg(decimal) might lose precision and cause an overflow over return type #24369
• ceil, floor on decimal can produce spurious overflow #22511
• FLOOR decimal returns unexpected error #20640

Core support:

• Refactor away usage of NUMERICS/INTEGERS in datafusion/expr-common/src/type_coercion/aggregates.rs #18092
• Decimal32/64 aren't as well supported as as the 128 and 256 bit variants #17489
• Support smaller decimal types through SQL interface #17747
• Improve scale support for binary decimal operations #19621
• Decimal32/Decimal64 support in Substrait Producer #21779
• Decimal values in SLT tests lose display precision if they have trailing zeros #23835

Coercion and type issues:

• Coercion between floating point types and exact numeric types should result in floating point type as the common super type #14272
• Floor built in function return type  #8795
• Decimal & UInt Binary operation giving wrong output #16667
• Unsafe / incorrect unwrap_cast optimizations for narrowing casts (e.g. Decimal) #23095

Related but excluded from this epic:

• Set default value of parse_float_as_decimal to true #14612
• Replace TypeSignature::Numeric with TypeSignature::Coercible #14760
• TypeSignature::Coercible for math functions #14763
• Improve error messages when coercion fails #19004

[Jefffrey]

Thanks for creating this; I'll be keen to help review & merge PRs relating to this epic 👍

I feel some of the listed issues might be better off being omitted from this epic to tighten it's focus, for example:

• Refactor away usage of NUMERICS/INTEGERS in datafusion/expr-common/src/type_coercion/aggregates.rs #18092
• Replace TypeSignature::Numeric with TypeSignature::Coercible #14760
• TypeSignature::Coercible for math functions #14763

These are more generic issues regarding signature usage so perhaps can leave them off?

• They are assigned to me, I've been meaning to work on them 😅

[theirix]

Thank you! I agree, some of them are too vague. We can move them to "nice-to-have" section or just omit. I will add my bunch of follow-up placeholder issues soon.