[
https://issues.apache.org/jira/browse/HIVE-14281?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15390247#comment-15390247
]
Chaoyu Tang commented on HIVE-14281:
------------------------------------
Here is the document about precision and scale in MS SqlServer (see
https://msdn.microsoft.com/en-us/library/ms190476.aspx)
Basically the multiplication of two decimals (p1, s1) and (p2, s2) results in a
decimal with precision p1 + p2 + 1 and scale s1 + s2. But precision or scale
maximum should be 38. When the result precision is greater than 38, the
corresponding scale is reduced to prevent the integral part of a result from
being truncated.
-----
I did some test in Oracle whose decimal (numeric) max precision is also 38. For
example:
dec(38,18) 92345678901234567890.123456789012345678 *
dec(38,18) 20000000000000000000.00000000000000001
==> 1846913578024691357802469135780246914483
The result JDBC SQL type is numeric (0,0).
It looks like that Oracle also truncates the result scale part in order to keep
its integral part.
----
PostgreSQL supports max precision 131072 and scale 16383, which are large
enough and should not have this issue.
> Issue in decimal multiplication
> -------------------------------
>
> Key: HIVE-14281
> URL: https://issues.apache.org/jira/browse/HIVE-14281
> Project: Hive
> Issue Type: Bug
> Components: Types
> Reporter: Chaoyu Tang
> Assignee: Chaoyu Tang
>
> {code}
> CREATE TABLE test (a DECIMAL(38,18), b DECIMAL(38,18));
> INSERT OVERWRITE TABLE test VALUES (20, 20);
> SELECT a*b from test
> {code}
> The returned result is NULL (instead of 400)
> It is because Hive adds the scales from operands and the type for a*b is set
> to decimal (38, 36). Hive could not handle this case properly (e.g. by
> rounding)
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
