Closed BlueStalker closed 2 years ago
I think the multiple of 10.0 * 10.0 for Decimal(38, 18) exceeds Decimal(38, 36).
select cast(10.0 as Decimal(38, 16)) * cast(10.0 as Decimal(38, 16)) should work by reducing the precision of decimal part.
@BlueStalker, as @hustnn observed, when x
and y
are of decimal(38, 18)
type, their multiplication is of decimal(38, 36)
type.
When multiplying, scales (numbers of digits after decimal point) add up: https://github.com/prestosql/presto/blob/c74b775e103a7a239aafef1b7ca15fe6370495fe/presto-main/src/main/java/io/prestosql/type/DecimalOperators.java#L252
decimal(38, 36)
cannot represent the resulting value of 100, hence the error.
We cannot dynamically adjust the scale, since it's statically know, it's part of the result's type.
(@martint, am I correct that the spec leaves no way out here?)
@BlueStalker, unfortunately you need to manually CAST
to decrease the scale (number of decimal digits) before multiplying, so that all digits before decimal point fit into the type.
Note that this may chop of (round) some non-zero digits from the end of your values, if some values actually have 18 digits after decimal point.
If this is not part of financial calculations and you do not need decimal
's exact semantics, you can cast the operands to double
.
This is what the spec says about this:
c) Otherwise, the declared type of both operands of a dyadic arithmetic operator is exact numeric and the
declared type of the result is an implementation-defined exact numeric type, with precision and scale determined as follows:
i) Let S1 and S2 be the scale of the first and second operands respectively.
ii) The precision of the result of addition and subtraction is implementation-defined, and the scale
is the maximum of S1 and S2.
iii) The precision of the result of multiplication is implementation-defined, and the scale is S1 + S2.
iv) The precision and scale of the result of division are implementation-defined.
[...]
5) If the most specific type of the result of an arithmetic operation is exact numeric, then
Case:
a) If the operator is not division and the mathematical result of the operation is not exactly
representable with the precision and scale of the declared type of the result, then an exception
condition is raised: data exception — numeric value out of range.
b) If the operator is division and the approximate mathematical result of the operation represented
with the precision and scale of the declared type of the result loses one or more leading significant
digits after rounding or truncating if necessary, then an exception condition is raised: data exception —
numeric value out of range. The choice of whether to round or truncate is implementation-defined.
a) If the operator is not division and the mathematical result of the operation is not exactly representable with the precision and scale of the declared type of the result, then an exception condition is raised: data exception — numeric value out of range.
Seems to suggest working as expected? Should this be closed then @martint?
Correct. This works as expected according to the SQL specification, so let's close it.
select cast(10.0 as Decimal(38, 18)) * cast(10.0 as Decimal(38, 18)); ==> com.facebook.presto.spi.PrestoException: Decimal overflow
whileselect cast(0.1 as Decimal(38, 18)) * cast(0.1 as Decimal(38, 18)); works