[
https://issues.apache.org/jira/browse/IMPALA-3436?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16844063#comment-16844063
]
Tim Armstrong commented on IMPALA-3436:
---------------------------------------
[~boristyukin]. The SQL standard is silent on a lot of details like this.
Regardless, if you see [~grahn]'s comment other databases do have exactly the
same behaviour with DOUBLE/FLOAT input arguments. Sometimes it might appear
that a system is rounding to a precise decimal numeric value even though the
output is floating point but that's actually an artefact of how the inexact
floating point numbers are displayed.
> Round(double, int) should return decimal
> ----------------------------------------
>
> Key: IMPALA-3436
> URL: https://issues.apache.org/jira/browse/IMPALA-3436
> Project: IMPALA
> Issue Type: Improvement
> Components: Backend
> Affects Versions: Impala 2.5.0
> Reporter: Tim Armstrong
> Assignee: Taras Bobrovytsky
> Priority: Minor
> Labels: compatibility, usability
>
> Impala has several versions of round: round(double a), round(double a, int
> d), round(decimal a, int_type d)
> round(double a) returns a BIGINT, which makes sense because it rounds to the
> nearest int.
> round(decimal a, int_type d) returns a DECIMAL, which makes sense because it
> rounds to a decimal digit.
> round(double a, int d) predates DECIMAL support, so it returns a DOUBLE. It
> is specified to return the nearest double value.
> E.g. round(cast(1 as DOUBLE) / 10, 1) returns the binary floating point value
> closest to 0.1. This number has no exact decimal representation. Both
> 0.100000000000000000000000000 and 0.10000000000000000555 are valid decimal
> representations of this floating point number. I.e. if you convert them back
> to float, you will get the same number.
> This is correct according to floating point conversion rules and the Impala
> documentation, but it is confusing for two reasons:
> * round() returning a double is a little surprising, because it can't
> precisely represent the result
> * Impala clients can display the floating-point result in multiple *valid*
> ways. Different clients have different algorithms for converting
> floating-point to decimal for display, so even if Impala returns the same
> result it may appear as 0.1 in one client and 0.10000000000000000555. We
> don't specify that clients have to use a particular algorithm, so it's valid
> as long as it converts back to the same float as part of a round-trip.
> We should consider changing the spec of round() in Impala to always return a
> decimal to avoid this confusion.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
