[jira] [Commented] (HIVE-5878) Hive standard avg UDAF returns double as the return type for some exact input types
[
https://issues.apache.org/jira/browse/HIVE-5878?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13830776#comment-13830776
]
Hive QA commented on HIVE-5878:
---
{color:red}Overall{color}: -1 at least one tests failed
Here are the results of testing the latest attachment:
https://issues.apache.org/jira/secure/attachment/12615467/HIVE-5878.patch
{color:red}ERROR:{color} -1 due to 16 failed/errored test(s), 4684 tests
executed
*Failed tests:*
{noformat}
org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_create_genericudaf
org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_decimal_precision
org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_decimal_udf
org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_subquery_in
org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_subquery_in_having
org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_subquery_notin
org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_udf3
org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_vectorization_0
org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_vectorization_2
org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_vectorization_not
org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_vectorization_pushdown
org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_vectorization_short_regress
org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_vectorized_mapjoin
org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_vectorized_shufflejoin
org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_windowing
org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_windowing_udaf
{noformat}
Test results:
http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/419/testReport
Console output:
http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/419/console
Messages:
{noformat}
Executing org.apache.hive.ptest.execution.PrepPhase
Executing org.apache.hive.ptest.execution.ExecutionPhase
Executing org.apache.hive.ptest.execution.ReportingPhase
Tests failed with: TestsFailedException: 16 tests failed
{noformat}
This message is automatically generated.
ATTACHMENT ID: 12615467
> Hive standard avg UDAF returns double as the return type for some exact input
> types
> ---
>
> Key: HIVE-5878
> URL: https://issues.apache.org/jira/browse/HIVE-5878
> Project: Hive
> Issue Type: Bug
> Components: Types, UDF
>Affects Versions: 0.12.0
>Reporter: Xuefu Zhang
>Assignee: Xuefu Zhang
> Attachments: HIVE-5878.patch
>
>
> For standard, no-partial avg result, hive currently returns double as the
> result type.
> {code}
> hive> desc test;
> OK
> d int None
> Time taken: 0.051 seconds, Fetched: 1 row(s)
> hive> explain select avg(`d`) from test;
> ...
> Reduce Operator Tree:
> Group By Operator
> aggregations:
> expr: avg(VALUE._col0)
> bucketGroup: false
> mode: mergepartial
> outputColumnNames: _col0
> Select Operator
> expressions:
> expr: _col0
> type: double
> {code}
> However, exact types including integers and decimal should yield exact type.
> Here is what MySQL does:
> {code}
> mysql> desc test;
> +---+--+--+-+-+---+
> | Field | Type | Null | Key | Default | Extra |
> +---+--+--+-+-+---+
> | i | int(11) | YES | | NULL| |
> | b | tinyint(1) | YES | | NULL| |
> | d | double | YES | | NULL| |
> | s | varchar(5) | YES | | NULL| |
> | dd| decimal(5,2) | YES | | NULL| |
> +---+--+--+-+-+---+
> mysql> desc test62;
> +---+---+--+-+-+---+
> | Field | Type | Null | Key | Default | Extra |
> +---+---+--+-+-+---+
> | sum_t | decimal(14,4) | YES | | NULL| |
> +---+---+--+-+-+---+
> 1 row in set (0.00 sec)
> {code}
--
This message was sent by Atlassian JIRA
(v6.1#6144)
[jira] [Commented] (HIVE-5878) Hive standard avg UDAF returns double as the return type for some exact input types
[
https://issues.apache.org/jira/browse/HIVE-5878?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13841158#comment-13841158
]
Hive QA commented on HIVE-5878:
---
{color:red}Overall{color}: -1 at least one tests failed
Here are the results of testing the latest attachment:
https://issues.apache.org/jira/secure/attachment/12617339/HIVE-5878.1.patch
{color:red}ERROR:{color} -1 due to 6 failed/errored test(s), 4458 tests executed
*Failed tests:*
{noformat}
org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_vectorization_0
org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_vectorization_2
org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_vectorization_not
org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_vectorization_pushdown
org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_vectorization_short_regress
org.apache.hadoop.hive.cli.TestCliDriver.testCliDriver_vectorized_mapjoin
{noformat}
Test results:
http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/549/testReport
Console output:
http://bigtop01.cloudera.org:8080/job/PreCommit-HIVE-Build/549/console
Messages:
{noformat}
Executing org.apache.hive.ptest.execution.PrepPhase
Executing org.apache.hive.ptest.execution.ExecutionPhase
Executing org.apache.hive.ptest.execution.ReportingPhase
Tests exited with: TestsFailedException: 6 tests failed
{noformat}
This message is automatically generated.
ATTACHMENT ID: 12617339
> Hive standard avg UDAF returns double as the return type for some exact input
> types
> ---
>
> Key: HIVE-5878
> URL: https://issues.apache.org/jira/browse/HIVE-5878
> Project: Hive
> Issue Type: Bug
> Components: Types, UDF
>Affects Versions: 0.12.0
>Reporter: Xuefu Zhang
>Assignee: Xuefu Zhang
> Attachments: HIVE-5878.1.patch, HIVE-5878.patch
>
>
> For standard, no-partial avg result, hive currently returns double as the
> result type.
> {code}
> hive> desc test;
> OK
> d int None
> Time taken: 0.051 seconds, Fetched: 1 row(s)
> hive> explain select avg(`d`) from test;
> ...
> Reduce Operator Tree:
> Group By Operator
> aggregations:
> expr: avg(VALUE._col0)
> bucketGroup: false
> mode: mergepartial
> outputColumnNames: _col0
> Select Operator
> expressions:
> expr: _col0
> type: double
> {code}
> However, exact types including integers and decimal should yield exact type.
> Here is what MySQL does:
> {code}
> mysql> desc test;
> +---+--+--+-+-+---+
> | Field | Type | Null | Key | Default | Extra |
> +---+--+--+-+-+---+
> | i | int(11) | YES | | NULL| |
> | b | tinyint(1) | YES | | NULL| |
> | d | double | YES | | NULL| |
> | s | varchar(5) | YES | | NULL| |
> | dd| decimal(5,2) | YES | | NULL| |
> +---+--+--+-+-+---+
> mysql> create table test62 as select avg(i) from test;
> mysql> desc test62;
> +---+---+--+-+-+---+
> | Field | Type | Null | Key | Default | Extra |
> +---+---+--+-+-+---+
> | avg(i) | decimal(14,4) | YES | | NULL| |
> +---+---+--+-+-+---+
> 1 row in set (0.00 sec)
> {code}
--
This message was sent by Atlassian JIRA
(v6.1#6144)
[jira] [Commented] (HIVE-5878) Hive standard avg UDAF returns double as the return type for some exact input types
[
https://issues.apache.org/jira/browse/HIVE-5878?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13841442#comment-13841442
]
Eric Hanson commented on HIVE-5878:
---
I'm not comfortable with this change. If the data type of expression and
aggregate results in Hive was reasonable before, I think it would be best to
leave the type the same as it was, for backward compatibility, so we don't
break people's applications.
Also, the vectorized implementation of aggregates was built to return the same
data types as the row-at-a-time implementation, for compatibility. It is
important that any changes in semantics or types be implemented in both the
row-at-a-time, and vectorized execution paths.
Different database systems make different choices about expression and
aggregate result types. For example, in SQL Server, avg applied to an int is an
int:
{code}
create table test(i int);
select avg(i) avg_i into res2 from test;
{code}
gives table res2 with this schema:
{code}
CREATE TABLE [dbo].[res2](
[avg_i] [int] NULL
)
{code}
> Hive standard avg UDAF returns double as the return type for some exact input
> types
> ---
>
> Key: HIVE-5878
> URL: https://issues.apache.org/jira/browse/HIVE-5878
> Project: Hive
> Issue Type: Bug
> Components: Types, UDF
>Affects Versions: 0.12.0
>Reporter: Xuefu Zhang
>Assignee: Xuefu Zhang
> Attachments: HIVE-5878.1.patch, HIVE-5878.patch
>
>
> For standard, no-partial avg result, hive currently returns double as the
> result type.
> {code}
> hive> desc test;
> OK
> d int None
> Time taken: 0.051 seconds, Fetched: 1 row(s)
> hive> explain select avg(`d`) from test;
> ...
> Reduce Operator Tree:
> Group By Operator
> aggregations:
> expr: avg(VALUE._col0)
> bucketGroup: false
> mode: mergepartial
> outputColumnNames: _col0
> Select Operator
> expressions:
> expr: _col0
> type: double
> {code}
> However, exact types including integers and decimal should yield exact type.
> Here is what MySQL does:
> {code}
> mysql> desc test;
> +---+--+--+-+-+---+
> | Field | Type | Null | Key | Default | Extra |
> +---+--+--+-+-+---+
> | i | int(11) | YES | | NULL| |
> | b | tinyint(1) | YES | | NULL| |
> | d | double | YES | | NULL| |
> | s | varchar(5) | YES | | NULL| |
> | dd| decimal(5,2) | YES | | NULL| |
> +---+--+--+-+-+---+
> mysql> create table test62 as select avg(i) from test;
> mysql> desc test62;
> +---+---+--+-+-+---+
> | Field | Type | Null | Key | Default | Extra |
> +---+---+--+-+-+---+
> | avg(i) | decimal(14,4) | YES | | NULL| |
> +---+---+--+-+-+---+
> 1 row in set (0.00 sec)
> {code}
--
This message was sent by Atlassian JIRA
(v6.1#6144)
[jira] [Commented] (HIVE-5878) Hive standard avg UDAF returns double as the return type for some exact input types
[
https://issues.apache.org/jira/browse/HIVE-5878?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13841449#comment-13841449
]
Eric Hanson commented on HIVE-5878:
---
To be clear, I think the existing Hive behavior, where the result of
avg() is a double, is reasonable behavior and should not be
changed.
> Hive standard avg UDAF returns double as the return type for some exact input
> types
> ---
>
> Key: HIVE-5878
> URL: https://issues.apache.org/jira/browse/HIVE-5878
> Project: Hive
> Issue Type: Bug
> Components: Types, UDF
>Affects Versions: 0.12.0
>Reporter: Xuefu Zhang
>Assignee: Xuefu Zhang
> Attachments: HIVE-5878.1.patch, HIVE-5878.patch
>
>
> For standard, no-partial avg result, hive currently returns double as the
> result type.
> {code}
> hive> desc test;
> OK
> d int None
> Time taken: 0.051 seconds, Fetched: 1 row(s)
> hive> explain select avg(`d`) from test;
> ...
> Reduce Operator Tree:
> Group By Operator
> aggregations:
> expr: avg(VALUE._col0)
> bucketGroup: false
> mode: mergepartial
> outputColumnNames: _col0
> Select Operator
> expressions:
> expr: _col0
> type: double
> {code}
> However, exact types including integers and decimal should yield exact type.
> Here is what MySQL does:
> {code}
> mysql> desc test;
> +---+--+--+-+-+---+
> | Field | Type | Null | Key | Default | Extra |
> +---+--+--+-+-+---+
> | i | int(11) | YES | | NULL| |
> | b | tinyint(1) | YES | | NULL| |
> | d | double | YES | | NULL| |
> | s | varchar(5) | YES | | NULL| |
> | dd| decimal(5,2) | YES | | NULL| |
> +---+--+--+-+-+---+
> mysql> create table test62 as select avg(i) from test;
> mysql> desc test62;
> +---+---+--+-+-+---+
> | Field | Type | Null | Key | Default | Extra |
> +---+---+--+-+-+---+
> | avg(i) | decimal(14,4) | YES | | NULL| |
> +---+---+--+-+-+---+
> 1 row in set (0.00 sec)
> {code}
--
This message was sent by Atlassian JIRA
(v6.1#6144)
[jira] [Commented] (HIVE-5878) Hive standard avg UDAF returns double as the return type for some exact input types
[
https://issues.apache.org/jira/browse/HIVE-5878?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13841516#comment-13841516
]
Xuefu Zhang commented on HIVE-5878:
---
[~ehans] Thank you for your concern. However, I respectfully disagree that the
behavior WAS and IS reasonable for several reasons. First, AVG was probably
introduced before decimal, so there was no better choice than double. Hive has
the concept of exact types (int, long, decimal, etc.) vs approximate types
(double, float, etc), and Arithmetic operations (plus, divide, etc) on exact
types generates exact type for accuracy. If average is defined mathematically
as sum/count, then sum(int)/count should result an exact type. Otherwise, avg()
and sum()/count give different result. Another inconsistency exists when
avg(decimal) results a decimal. All those cause inconsistency in Hive's
mathematical concept and function behavior, and can create confusions among
users as well.
I understand vectorized current implementation chooses double for sum and uses
sum/count to get another double for average. While this extends the scope of
the changes, to me, however, vectorization is just implementation, which should
not dictate high-level concept and consistency.
> Hive standard avg UDAF returns double as the return type for some exact input
> types
> ---
>
> Key: HIVE-5878
> URL: https://issues.apache.org/jira/browse/HIVE-5878
> Project: Hive
> Issue Type: Bug
> Components: Types, UDF
>Affects Versions: 0.12.0
>Reporter: Xuefu Zhang
>Assignee: Xuefu Zhang
> Attachments: HIVE-5878.1.patch, HIVE-5878.patch
>
>
> For standard, no-partial avg result, hive currently returns double as the
> result type.
> {code}
> hive> desc test;
> OK
> d int None
> Time taken: 0.051 seconds, Fetched: 1 row(s)
> hive> explain select avg(`d`) from test;
> ...
> Reduce Operator Tree:
> Group By Operator
> aggregations:
> expr: avg(VALUE._col0)
> bucketGroup: false
> mode: mergepartial
> outputColumnNames: _col0
> Select Operator
> expressions:
> expr: _col0
> type: double
> {code}
> However, exact types including integers and decimal should yield exact type.
> Here is what MySQL does:
> {code}
> mysql> desc test;
> +---+--+--+-+-+---+
> | Field | Type | Null | Key | Default | Extra |
> +---+--+--+-+-+---+
> | i | int(11) | YES | | NULL| |
> | b | tinyint(1) | YES | | NULL| |
> | d | double | YES | | NULL| |
> | s | varchar(5) | YES | | NULL| |
> | dd| decimal(5,2) | YES | | NULL| |
> +---+--+--+-+-+---+
> mysql> create table test62 as select avg(i) from test;
> mysql> desc test62;
> +---+---+--+-+-+---+
> | Field | Type | Null | Key | Default | Extra |
> +---+---+--+-+-+---+
> | avg(i) | decimal(14,4) | YES | | NULL| |
> +---+---+--+-+-+---+
> 1 row in set (0.00 sec)
> {code}
--
This message was sent by Atlassian JIRA
(v6.1#6144)
[jira] [Commented] (HIVE-5878) Hive standard avg UDAF returns double as the return type for some exact input types
[
https://issues.apache.org/jira/browse/HIVE-5878?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13841534#comment-13841534
]
Eric Hanson commented on HIVE-5878:
---
To me, the issue is really about the importance of obeying the SQL standard
(which would argue for using exact types for results in the situations the
standard describes) vs. maintaining backward compatibility.
I don't think that accuracy is the issue. E.g. avg(int) yields int in SQL
Server which is less accurate than yielding double, but it does meet the SQL
standard's requirements of being an exact type. I would not favor making Hive
yield int for avg(int) to meet the SQL standard because that loses information
compared to the previous Hive behavior (yielding double) and that would be
perceived even more as a breaking change in existing applications than
producing decimal.
I agree that vectorization is an implementation detail. Vectorization can be
extended to handle decimal.
> Hive standard avg UDAF returns double as the return type for some exact input
> types
> ---
>
> Key: HIVE-5878
> URL: https://issues.apache.org/jira/browse/HIVE-5878
> Project: Hive
> Issue Type: Bug
> Components: Types, UDF
>Affects Versions: 0.12.0
>Reporter: Xuefu Zhang
>Assignee: Xuefu Zhang
> Attachments: HIVE-5878.1.patch, HIVE-5878.patch
>
>
> For standard, no-partial avg result, hive currently returns double as the
> result type.
> {code}
> hive> desc test;
> OK
> d int None
> Time taken: 0.051 seconds, Fetched: 1 row(s)
> hive> explain select avg(`d`) from test;
> ...
> Reduce Operator Tree:
> Group By Operator
> aggregations:
> expr: avg(VALUE._col0)
> bucketGroup: false
> mode: mergepartial
> outputColumnNames: _col0
> Select Operator
> expressions:
> expr: _col0
> type: double
> {code}
> However, exact types including integers and decimal should yield exact type.
> Here is what MySQL does:
> {code}
> mysql> desc test;
> +---+--+--+-+-+---+
> | Field | Type | Null | Key | Default | Extra |
> +---+--+--+-+-+---+
> | i | int(11) | YES | | NULL| |
> | b | tinyint(1) | YES | | NULL| |
> | d | double | YES | | NULL| |
> | s | varchar(5) | YES | | NULL| |
> | dd| decimal(5,2) | YES | | NULL| |
> +---+--+--+-+-+---+
> mysql> create table test62 as select avg(i) from test;
> mysql> desc test62;
> +---+---+--+-+-+---+
> | Field | Type | Null | Key | Default | Extra |
> +---+---+--+-+-+---+
> | avg(i) | decimal(14,4) | YES | | NULL| |
> +---+---+--+-+-+---+
> 1 row in set (0.00 sec)
> {code}
--
This message was sent by Atlassian JIRA
(v6.1#6144)
[jira] [Commented] (HIVE-5878) Hive standard avg UDAF returns double as the return type for some exact input types
[
https://issues.apache.org/jira/browse/HIVE-5878?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13841961#comment-13841961
]
Eric Hanson commented on HIVE-5878:
---
Does anybody else want to voice an opinion here?
> Hive standard avg UDAF returns double as the return type for some exact input
> types
> ---
>
> Key: HIVE-5878
> URL: https://issues.apache.org/jira/browse/HIVE-5878
> Project: Hive
> Issue Type: Bug
> Components: Types, UDF
>Affects Versions: 0.12.0
>Reporter: Xuefu Zhang
>Assignee: Xuefu Zhang
> Attachments: HIVE-5878.1.patch, HIVE-5878.patch
>
>
> For standard, no-partial avg result, hive currently returns double as the
> result type.
> {code}
> hive> desc test;
> OK
> d int None
> Time taken: 0.051 seconds, Fetched: 1 row(s)
> hive> explain select avg(`d`) from test;
> ...
> Reduce Operator Tree:
> Group By Operator
> aggregations:
> expr: avg(VALUE._col0)
> bucketGroup: false
> mode: mergepartial
> outputColumnNames: _col0
> Select Operator
> expressions:
> expr: _col0
> type: double
> {code}
> However, exact types including integers and decimal should yield exact type.
> Here is what MySQL does:
> {code}
> mysql> desc test;
> +---+--+--+-+-+---+
> | Field | Type | Null | Key | Default | Extra |
> +---+--+--+-+-+---+
> | i | int(11) | YES | | NULL| |
> | b | tinyint(1) | YES | | NULL| |
> | d | double | YES | | NULL| |
> | s | varchar(5) | YES | | NULL| |
> | dd| decimal(5,2) | YES | | NULL| |
> +---+--+--+-+-+---+
> mysql> create table test62 as select avg(i) from test;
> mysql> desc test62;
> +---+---+--+-+-+---+
> | Field | Type | Null | Key | Default | Extra |
> +---+---+--+-+-+---+
> | avg(i) | decimal(14,4) | YES | | NULL| |
> +---+---+--+-+-+---+
> 1 row in set (0.00 sec)
> {code}
--
This message was sent by Atlassian JIRA
(v6.1#6144)
[jira] [Commented] (HIVE-5878) Hive standard avg UDAF returns double as the return type for some exact input types
[
https://issues.apache.org/jira/browse/HIVE-5878?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13843348#comment-13843348
]
Xuefu Zhang commented on HIVE-5878:
---
Backward compatibility issue is a valid concern. However, before we decide to
enforce or break that, we need to ask ourselves whether the new behavior is the
right thing to do and how bad the old behavior is. We had questionings about
why we need to keep backward compatible to a bug.
As an example, Hive returned double for int/int previously, but now returns
decimal. This is a bigger backward compatibility problem, but obviously it's a
right thing to do. The issue in the discussion seems following the same line.
Also, I think hive is still in its early days, and it doesn't even have 1.0
release yet. Thus, personally, BC issue is less a concern than doing right
thing.
> Hive standard avg UDAF returns double as the return type for some exact input
> types
> ---
>
> Key: HIVE-5878
> URL: https://issues.apache.org/jira/browse/HIVE-5878
> Project: Hive
> Issue Type: Bug
> Components: Types, UDF
>Affects Versions: 0.12.0
>Reporter: Xuefu Zhang
>Assignee: Xuefu Zhang
> Attachments: HIVE-5878.1.patch, HIVE-5878.patch
>
>
> For standard, no-partial avg result, hive currently returns double as the
> result type.
> {code}
> hive> desc test;
> OK
> d int None
> Time taken: 0.051 seconds, Fetched: 1 row(s)
> hive> explain select avg(`d`) from test;
> ...
> Reduce Operator Tree:
> Group By Operator
> aggregations:
> expr: avg(VALUE._col0)
> bucketGroup: false
> mode: mergepartial
> outputColumnNames: _col0
> Select Operator
> expressions:
> expr: _col0
> type: double
> {code}
> However, exact types including integers and decimal should yield exact type.
> Here is what MySQL does:
> {code}
> mysql> desc test;
> +---+--+--+-+-+---+
> | Field | Type | Null | Key | Default | Extra |
> +---+--+--+-+-+---+
> | i | int(11) | YES | | NULL| |
> | b | tinyint(1) | YES | | NULL| |
> | d | double | YES | | NULL| |
> | s | varchar(5) | YES | | NULL| |
> | dd| decimal(5,2) | YES | | NULL| |
> +---+--+--+-+-+---+
> mysql> create table test62 as select avg(i) from test;
> mysql> desc test62;
> +---+---+--+-+-+---+
> | Field | Type | Null | Key | Default | Extra |
> +---+---+--+-+-+---+
> | avg(i) | decimal(14,4) | YES | | NULL| |
> +---+---+--+-+-+---+
> 1 row in set (0.00 sec)
> {code}
--
This message was sent by Atlassian JIRA
(v6.1.4#6159)
[jira] [Commented] (HIVE-5878) Hive standard avg UDAF returns double as the return type for some exact input types
[
https://issues.apache.org/jira/browse/HIVE-5878?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13843441#comment-13843441
]
Jason Dere commented on HIVE-5878:
--
Just adding a couple of bits of info to this discussion. There are some
significant effects for users if we decide to go with the change to returning
decimal type (both for avg and for int / int).
1) As I mentioned in HIVE-5356, there is a significant performance hit to
switching to decimal arithmetic. I suppose for avg() there could be some ways
to improve this for int type, such as keeping the sum/count portions as long,
and only doing decimal math when computing sum / count.
2) Decimal arithmetic can return NULL for some non-null inputs, if Hive is not
able to represent the entire result exactly. While most common cases (such as
the example in HIVE-5022) should not longer occur, in the general case it is
still possible to get a NULL result if the expression contains enough
operations. I know, there has been some discussion on this already, and some
of the reasoning behind why those semantics were ok for decimal arithmetic was
that if the user had made the choice to use decimal columns/types, then they
knew what they were getting into with the decimal arithmetic. But in this case
users are going to start seeing the effects of these decimal semantics all over
the place because they will start getting applied on expressions involving
integer columns. I don't think that users will find this NULL behavior
desirable with their integer expressions, so I think something will need to be
done one way or another here - either don't return decimal type in these
situations, or possibly address this null behavior in some way.
> Hive standard avg UDAF returns double as the return type for some exact input
> types
> ---
>
> Key: HIVE-5878
> URL: https://issues.apache.org/jira/browse/HIVE-5878
> Project: Hive
> Issue Type: Bug
> Components: Types, UDF
>Affects Versions: 0.12.0
>Reporter: Xuefu Zhang
>Assignee: Xuefu Zhang
> Attachments: HIVE-5878.1.patch, HIVE-5878.patch
>
>
> For standard, no-partial avg result, hive currently returns double as the
> result type.
> {code}
> hive> desc test;
> OK
> d int None
> Time taken: 0.051 seconds, Fetched: 1 row(s)
> hive> explain select avg(`d`) from test;
> ...
> Reduce Operator Tree:
> Group By Operator
> aggregations:
> expr: avg(VALUE._col0)
> bucketGroup: false
> mode: mergepartial
> outputColumnNames: _col0
> Select Operator
> expressions:
> expr: _col0
> type: double
> {code}
> However, exact types including integers and decimal should yield exact type.
> Here is what MySQL does:
> {code}
> mysql> desc test;
> +---+--+--+-+-+---+
> | Field | Type | Null | Key | Default | Extra |
> +---+--+--+-+-+---+
> | i | int(11) | YES | | NULL| |
> | b | tinyint(1) | YES | | NULL| |
> | d | double | YES | | NULL| |
> | s | varchar(5) | YES | | NULL| |
> | dd| decimal(5,2) | YES | | NULL| |
> +---+--+--+-+-+---+
> mysql> create table test62 as select avg(i) from test;
> mysql> desc test62;
> +---+---+--+-+-+---+
> | Field | Type | Null | Key | Default | Extra |
> +---+---+--+-+-+---+
> | avg(i) | decimal(14,4) | YES | | NULL| |
> +---+---+--+-+-+---+
> 1 row in set (0.00 sec)
> {code}
--
This message was sent by Atlassian JIRA
(v6.1.4#6159)
[jira] [Commented] (HIVE-5878) Hive standard avg UDAF returns double as the return type for some exact input types
[
https://issues.apache.org/jira/browse/HIVE-5878?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13843882#comment-13843882
]
Xuefu Zhang commented on HIVE-5878:
---
For information, the follow is the text from SQL-92[1] standard w.r.t AVG
function:
{quote}
c) If AVG is specified and DT is exact numeric, then the data
type of the result is exact numeric with implementation-
defined precision not less than the precision of DT and
implementation-defined scale not less than the scale of DT.
{quote}
Clearly, currently Hive deviates from this. Both MySQL and SQL server are in
line with this.
[1] http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
> Hive standard avg UDAF returns double as the return type for some exact input
> types
> ---
>
> Key: HIVE-5878
> URL: https://issues.apache.org/jira/browse/HIVE-5878
> Project: Hive
> Issue Type: Bug
> Components: Types, UDF
>Affects Versions: 0.12.0
>Reporter: Xuefu Zhang
>Assignee: Xuefu Zhang
> Attachments: HIVE-5878.1.patch, HIVE-5878.patch
>
>
> For standard, no-partial avg result, hive currently returns double as the
> result type.
> {code}
> hive> desc test;
> OK
> d int None
> Time taken: 0.051 seconds, Fetched: 1 row(s)
> hive> explain select avg(`d`) from test;
> ...
> Reduce Operator Tree:
> Group By Operator
> aggregations:
> expr: avg(VALUE._col0)
> bucketGroup: false
> mode: mergepartial
> outputColumnNames: _col0
> Select Operator
> expressions:
> expr: _col0
> type: double
> {code}
> However, exact types including integers and decimal should yield exact type.
> Here is what MySQL does:
> {code}
> mysql> desc test;
> +---+--+--+-+-+---+
> | Field | Type | Null | Key | Default | Extra |
> +---+--+--+-+-+---+
> | i | int(11) | YES | | NULL| |
> | b | tinyint(1) | YES | | NULL| |
> | d | double | YES | | NULL| |
> | s | varchar(5) | YES | | NULL| |
> | dd| decimal(5,2) | YES | | NULL| |
> +---+--+--+-+-+---+
> mysql> create table test62 as select avg(i) from test;
> mysql> desc test62;
> +---+---+--+-+-+---+
> | Field | Type | Null | Key | Default | Extra |
> +---+---+--+-+-+---+
> | avg(i) | decimal(14,4) | YES | | NULL| |
> +---+---+--+-+-+---+
> 1 row in set (0.00 sec)
> {code}
--
This message was sent by Atlassian JIRA
(v6.1.4#6159)
[jira] [Commented] (HIVE-5878) Hive standard avg UDAF returns double as the return type for some exact input types
[
https://issues.apache.org/jira/browse/HIVE-5878?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13847818#comment-13847818
]
Eric Hanson commented on HIVE-5878:
---
HIVE-5996 has a pretty useful, in-depth, related discussion about ANSI
compatibility vs. backward compatibility etc. that is useful context.
> Hive standard avg UDAF returns double as the return type for some exact input
> types
> ---
>
> Key: HIVE-5878
> URL: https://issues.apache.org/jira/browse/HIVE-5878
> Project: Hive
> Issue Type: Bug
> Components: Types, UDF
>Affects Versions: 0.12.0
>Reporter: Xuefu Zhang
>Assignee: Xuefu Zhang
> Attachments: HIVE-5878.1.patch, HIVE-5878.patch
>
>
> For standard, no-partial avg result, hive currently returns double as the
> result type.
> {code}
> hive> desc test;
> OK
> d int None
> Time taken: 0.051 seconds, Fetched: 1 row(s)
> hive> explain select avg(`d`) from test;
> ...
> Reduce Operator Tree:
> Group By Operator
> aggregations:
> expr: avg(VALUE._col0)
> bucketGroup: false
> mode: mergepartial
> outputColumnNames: _col0
> Select Operator
> expressions:
> expr: _col0
> type: double
> {code}
> However, exact types including integers and decimal should yield exact type.
> Here is what MySQL does:
> {code}
> mysql> desc test;
> +---+--+--+-+-+---+
> | Field | Type | Null | Key | Default | Extra |
> +---+--+--+-+-+---+
> | i | int(11) | YES | | NULL| |
> | b | tinyint(1) | YES | | NULL| |
> | d | double | YES | | NULL| |
> | s | varchar(5) | YES | | NULL| |
> | dd| decimal(5,2) | YES | | NULL| |
> +---+--+--+-+-+---+
> mysql> create table test62 as select avg(i) from test;
> mysql> desc test62;
> +---+---+--+-+-+---+
> | Field | Type | Null | Key | Default | Extra |
> +---+---+--+-+-+---+
> | avg(i) | decimal(14,4) | YES | | NULL| |
> +---+---+--+-+-+---+
> 1 row in set (0.00 sec)
> {code}
--
This message was sent by Atlassian JIRA
(v6.1.4#6159)
