[
https://issues.apache.org/jira/browse/HIVE-5022?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13754950#comment-13754950
]
Jason Dere commented on HIVE-5022:
----------------------------------
There's the actual SQL reference rules regarding exact precision arithmetic
(6.12 if you're looking at SQL92, and it looks like later references look the
same):
1) If the data type of both operands of a dyadic arithmetic opera-
tor is exact numeric, then the data type of the result is exact
numeric, with precision and scale determined as follows:
a) Let S1 and S2 be the scale of the first and second operands
respectively.
b) The precision of the result of addition and subtraction is
implementation-defined, and the scale is the maximum of S1
and S2.
c) The precision of the result of multiplication is implementation-
defined, and the scale is S1 + S2.
d) The precision and scale of the result of division is
implementation-defined.
I'd agree with what hagleitn said about the resulting precision/scale of
division operations, Hive is allowed to define what precision/scale it returns
on division, and it probably should not be allowed to take up the entire
precision. Tinkering with MySQL a bit, it looks like it follows the
multiplication scale rules until it hits the max scale of 30, and then any
further multiplications continue to have scale 30. Not quite sure what rules
it's using for division scale, but it also does not exceed their max scale of
30.
> Decimal Arithmetic generates NULL value
> ---------------------------------------
>
> Key: HIVE-5022
> URL: https://issues.apache.org/jira/browse/HIVE-5022
> Project: Hive
> Issue Type: Bug
> Components: Types
> Affects Versions: 0.11.0
> Environment: Hortonworks 1.3 running Hive 0.11.0.1.3.0.0-107
> Reporter: Kevin Soo Hoo
> Assignee: Teddy Choi
> Attachments: HIVE-5022.1.patch.txt, HIVE-5022.2.patch.txt,
> HIVE-5022.3.patch.txt
>
>
> When a decimal division is the first operation, the quotient cannot be
> multiplied in a subsequent calculation. Instead, a NULL is returned.
> The following yield NULL results:
> select (cast (4.53 as decimal) / cast(25.86 as decimal)) * cast(0.087 as
> decimal) from <tablename> limit 1;
> select cast (4.53 as decimal) / cast(25.86 as decimal) * cast(0.087 as
> decimal) from <tablename> limit 1;
> If we move the multiplication operation to be first, then it will
> successfully calculate the result.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
