Is it possible to have type system with scale that is precision agnostic?

[Ian Bertolacci <ia...@workday.com.INVALID>]

I’ve got a good one for y’all today.
In our engine, we don’t care about precision, but we *do* care about scale. This is because we have mechanisms for handling arbitrarily wide decimals, but we at least require that all decimals in a column have the same scale.
It is possible for us to express that in the Calcite type system?

This usually isn’t a problem but we’ve hit a pretty trivial case today, specifically: union over decimal producing inputs.
It’s possible that the same columnar position is typed as decimal, with at least one of those inputs having a different scale than the others.
We intended to handle this by inserting casts to the least restrictive type (which typically derives a decimal with the max scale, effectively padding out lower scale numbers), and this works for the simple cases.

The problem is when one of those columns has the type system’s max precision and a lower scale. In this case, the least restrictive type is actually the *lowest* scale, highest precision type.
This is easy to encounter with sum, since the default implementation of deriveSumType uses the type systems max precision.
For example:
select x from table union all select y from otherTable
select x from table union all select sum(y) from otherTable

(where x is scale 3 and y is scale 2)
In the first case, the least restrictive type is decimal with scale 3, and we would cast y to that type, losing no information in the process.
But in the second case, the sum(y) produces a type with the type systems max precision, and so there is no room for the scale to be padded out, and so the least restrictive type is a decimal with scale 2, and now x would be down-cast to the lower scale, which is undesirable for us.

I’ve played around with setting the precision of our decimal columns or the max precision to PRECISION_NOT_SPECIFIED, but that seems to have the effect that scale is no longer respected or propagated through the type system.

Our immediate plan is to override deriveSumType to simply propagate the operand type, but that’s an ugly hack (and one with limited effect to boot).
Any better ideas?

Thanks!
-Ian J. Bertolacci

Re: Is it possible to have type system with scale that is precision agnostic?

[Julian Hyde <jh...@gmail.com>]

Postgres’ numeric and decimal types store arbitrary numbers of digits (left and right of the point). We could implement something similar in Calcite (a reasonable implementation would convert the decimal values to strings like “123.45” and “-.0678”, and numeric values to strings in another base).

Then your requirement would be a check constraint: make sure there are no more than N digits after the point (where N is an integer constant for a particular column).


> On Mar 17, 2025, at 11:56 AM, Ian Bertolacci <ia...@workday.com.invalid> wrote:
> 
> Thinking about it some more, it may be more accurate to ask if it is possible to have an infinite max scale/precision.
> We may care about the type’s scale and/or precision, but we do not necessarily need or want to define an upper bound on what they can be.
> 
> -Ian

Re: Is it possible to have type system with scale that is precision agnostic?

[Ian Bertolacci <ia...@workday.com.INVALID>]

Thinking about it some more, it may be more accurate to ask if it is possible to have an infinite max scale/precision.
We may care about the type’s scale and/or precision, but we do not necessarily need or want to define an upper bound on what they can be.

-Ian