[jira] [Commented] (HIVE-18291) An exception should be raised if the result is outside the range of decimal
[
https://issues.apache.org/jira/browse/HIVE-18291?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16810755#comment-16810755
]
Ivan Suller commented on HIVE-18291:
I'd vote against this change as well. At least the default should be the
current behaviour. When I used Hive daily we depended on this behaviour very
often: if an input is not what we expect we simply want it to be ignored. Still
better to have one row incomplete than have a billion rows missing.
> 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
>Priority: Major
>
> 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 100.01*100.01;
> +---+
> | _c0 |
> +---+
> | NULL |
> +---+
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (HIVE-18291) An exception should be raised if the result is outside the range of decimal
[
https://issues.apache.org/jira/browse/HIVE-18291?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16310116#comment-16310116
]
Daniel Voros commented on HIVE-18291:
-
[~sershe] I think that ticket is HIVE-13098. Based on the size of that WIP
patch, I think I'll put this on hold for now.
> 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 100.01*100.01;
> +---+
> | _c0 |
> +---+
> | NULL |
> +---+
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
[jira] [Commented] (HIVE-18291) An exception should be raised if the result is outside the range of decimal
[
https://issues.apache.org/jira/browse/HIVE-18291?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16301874#comment-16301874
]
Sergey Shelukhin commented on HIVE-18291:
-
There's a JIRA for this somewhere already, and IIRC I even had a partial patch.
The problem as mentioned above is the default behavior. In the perfect world, a
config flag needs to be added, but given a number of places where that would
need to be checked, it's a big pain so seems noone has picked it up.
> 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 100.01*100.01;
> +---+
> | _c0 |
> +---+
> | NULL |
> +---+
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
[jira] [Commented] (HIVE-18291) An exception should be raised if the result is outside the range of decimal
[
https://issues.apache.org/jira/browse/HIVE-18291?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16297106#comment-16297106
]
Xuefu Zhang commented on HIVE-18291:
I think Hive current behavior is well-established, understood, and accepted,
and I don't see the need to change just because of certain standard, especially
such a change alters the default behavior. Please note that the standard
changes too!
Returning NULL in the said case is by design as well. Hive's decimal in fact
follows more with mySQL, though the implementation borrowed a lot from SQL
server.
When we test a query on certain DB, we need to note that a DB server may offer
different mode such as a strict mode in MySQL
(https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html) that dictates error
handling. Data errors would throws an exception in strict mode, such as
divide-by-zero. Otherwise, NULL will be returned.
Since Hive doesn't have a server strict mode, returning NULL for the case here
is quite reasonable. If one likes to make the behavior configurable,
introducing different modes in HS2 would be a more appropriate approach.
Thus, I would be -0 on introducing SQL compliance on this, but certainly -1 on
changing the default behavior.
> 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 100.01*100.01;
> +---+
> | _c0 |
> +---+
> | NULL |
> +---+
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
[jira] [Commented] (HIVE-18291) An exception should be raised if the result is outside the range of decimal
[
https://issues.apache.org/jira/browse/HIVE-18291?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16297105#comment-16297105
]
Andrew Sherman commented on HIVE-18291:
---
Thanks [~mgaido] [~kgyrtkirk] for the comparisons. +1 for following sql
standard by default with a switch for backwards compatibility
> 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 100.01*100.01;
> +---+
> | _c0 |
> +---+
> | NULL |
> +---+
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
[jira] [Commented] (HIVE-18291) An exception should be raised if the result is outside the range of decimal
[
https://issues.apache.org/jira/browse/HIVE-18291?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16296963#comment-16296963
]
Marco Gaido commented on HIVE-18291:
Thanks for your analysis [~kgyrtkirk], I can add to your comparisons that:
- Oracle is like Postgres
- SQLServer, from which Hive draws his inspiration for the implementation of
Decimal operations, behaves like Hive in case of truncation needed (even though
in addition and subtractions it seems that Hive looses an extra digit compared
to SQLServer, but this is a minor issue at the moment IMHO), but it throws an
exception instead of returning {{NULL}}, ie. SQLServer respects the standard,
but Hive doesn't.
I agree on adding a configuration option to switch to previous Hive behavior,
but I'd set it by default to behave like SQL standard suggests.
> 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 100.01*100.01;
> +---+
> | _c0 |
> +---+
> | NULL |
> +---+
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
[jira] [Commented] (HIVE-18291) An exception should be raised if the result is outside the range of decimal
[
https://issues.apache.org/jira/browse/HIVE-18291?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=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 100.01*100.01;
> +---+
> | _c0 |
> +---+
> | NULL |
> +---+
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
