The functions_arithmetic_decimal return expressions cap precision at 38 and take the surplus out of the scale. This is add; the last four lines are the same in subtract, multiply, divide and modulus:
init_scale = max(S1,S2)
init_prec = init_scale + max(P1 - S1, P2 - S2) + 1
min_scale = min(init_scale, 6)
delta = init_prec - 38
prec = min(init_prec, 38)
scale_after_borrow = max(init_scale - delta, min_scale)
scale = init_prec > 38 ? scale_after_borrow : init_scale
DECIMAL<prec, scale>
A result that needs more digits than the returned type holds therefore loses fractional ones, and neither the file nor type_classes.md says what becomes of them: whether they are truncated or rounded, and if rounded, in which direction. These functions carry an overflow option and no rounding one, where functions_arithmetic.yaml gives rounding: [ TIE_TO_EVEN, TIE_AWAY_FROM_ZERO, TRUNCATE, CEILING, FLOOR ] to the fp32 and fp64 overloads of the same four operations.
This is not a corner case. Over every operand type pair type_classes.md allows, the precision an exact result would need reaches 77 for add, subtract and multiply and 115 for divide. It cannot arise for modulus, whose unbounded precision never exceeds 38.
Three engines answer alike. 1.2345685 * 1 at dec<38,10> returns dec<38,6>, and the value is 1.234569 in Spark 4.0.1, Hive 4.0.1 and Trino 483, -1.234569 for the negative — rounded away from zero, where rounding to even would keep 1.234568. Trino has to be asked through multiply, since its add keeps the scale above 38 and never reduces; MySQL and ClickHouse never reduce at all, so the question does not reach them. That the three agree is not the same as the specification saying so: an engine that truncated instead would be conformant today, and a consumer has nothing to check against.
Happy to send a rounding option for these, or one sentence saying the digits are rounded away from zero, whichever the project would rather have. The cases in #1213 do not reach this either: their scale-reduction operands lose only zeros.
The
functions_arithmetic_decimalreturn expressions cap precision at 38 and take the surplus out of the scale. This isadd; the last four lines are the same insubtract,multiply,divideandmodulus:A result that needs more digits than the returned type holds therefore loses fractional ones, and neither the file nor
type_classes.mdsays what becomes of them: whether they are truncated or rounded, and if rounded, in which direction. These functions carry anoverflowoption and noroundingone, wherefunctions_arithmetic.yamlgivesrounding: [ TIE_TO_EVEN, TIE_AWAY_FROM_ZERO, TRUNCATE, CEILING, FLOOR ]to the fp32 and fp64 overloads of the same four operations.This is not a corner case. Over every operand type pair
type_classes.mdallows, the precision an exact result would need reaches 77 foradd,subtractandmultiplyand 115 fordivide. It cannot arise formodulus, whose unbounded precision never exceeds 38.Three engines answer alike.
1.2345685 * 1atdec<38,10>returnsdec<38,6>, and the value is1.234569in Spark 4.0.1, Hive 4.0.1 and Trino 483,-1.234569for the negative — rounded away from zero, where rounding to even would keep1.234568. Trino has to be asked throughmultiply, since itsaddkeeps the scale above 38 and never reduces; MySQL and ClickHouse never reduce at all, so the question does not reach them. That the three agree is not the same as the specification saying so: an engine that truncated instead would be conformant today, and a consumer has nothing to check against.Happy to send a
roundingoption for these, or one sentence saying the digits are rounded away from zero, whichever the project would rather have. The cases in #1213 do not reach this either: their scale-reduction operands lose only zeros.