[jira] [Commented] (HIVE-18291) An exception should be raised if the result is outside the range of decimal

["Zoltan Haindrich (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/HIVE-18291?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16296589#comment-16296589 ] 

Zoltan Haindrich commented on HIVE-18291:
-----------------------------------------

I was curious...so I've looked into how this is handled in other dbs:

* mysql:
** "auto-tunes" the smallest decimal which can withold the resulting value
** throw some exception after reaching the limit of decimal
** interestingly: {{select cast(11 as decimal(1))}} is {{9}} ; no exception (there might be knob for this)
* psql:
** seem to know a wide limit at which a number can be represent(131k digits)
** knows the special type "decimal" which is most probably autoscaled at the cell level to fit the value
** even thru it only permits {{decimal(1000)}} to be defined
** defining a column or casting to a smaller decimal results in exceptions...

I feel that returning {{NULL}} in Hive; when special cases occur is somehow the usually expected behavior by most users, so I think that this improvement should be guarded with an option to retain the old behavior.

note: the solution should also work in vectorized mode; I'm not sure if that's a different code path...but it should be definetly at least checked once.

> An exception should be raised if the result is outside the range of decimal
> ---------------------------------------------------------------------------
>
>                 Key: HIVE-18291
>                 URL: https://issues.apache.org/jira/browse/HIVE-18291
>             Project: Hive
>          Issue Type: Sub-task
>          Components: SQL
>    Affects Versions: 2.3.0
>            Reporter: Marco Gaido
>            Assignee: Daniel Voros
>
> Citing SQL:2011 on page 27 available at http://standards.iso.org/ittf/PubliclyAvailableStandards/c053681_ISO_IEC_9075-1_2011.zip:
> {noformat}
> If the result cannot be represented exactly in the result type, then whether it is rounded
> or truncated is implementation-defined. An exception condition is raised if the result is
> outside the range of numeric values of the result type, or if the arithmetic operation
> is not defined for the operands.
> {noformat}
> Currently Hive is returning NULL instead of throwing an exception if the result is out of range, eg.:
> {code}
> > select 1000000000000000000.000001*1000000000000000000.000001;
> +-------+
> |  _c0  |
> +-------+
> | NULL  |
> +-------+
> {code}



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)