caofangkun created HIVE-4522:
--------------------------------
Summary: Confusing result generated when use mulit aggregate
functions with star columns
Key: HIVE-4522
URL: https://issues.apache.org/jira/browse/HIVE-4522
Project: Hive
Issue Type: Bug
Components: Query Processor
Affects Versions: 0.12.0
Reporter: caofangkun
Priority: Minor
hive (default)> set hive.cli.print.header=true;
hive (default)> select * from src;
OK
key value
35
48
100 100
Table src has two columns: key and value
But guess how many columns the following query will generate ?
Three ? No, it's two .
hive (default)> select * , count(key) as cnt from src;
OK
(tok_function count (tok_table_or_col key)) cnt
3 3
And what about this query ？
hive (default)> select * , count(key)， sum(value) as cnt from src group by key,
value;
Four columns ? No, it's six!
hive (default)> select * , count(key) as cnt , sum(value) as sum_value from src
group by key, value ;
OK
(tok_table_or_col key) (tok_table_or_col value) (tok_function count
(tok_table_or_col key)) (tok_function sum (tok_table_or_col value)) cnt
sum_value
35 1 35.0 1 35.0
100 100 1 100.0 1 100.0
48 1 0.0 1 0.0
The column names do not match and the result is Confusing。
Have a look at how such kind of queries work in MySQL :
mysql> select *, sum(id),count(data) from example ;
+------+------+---------+-------------+
| id | data | sum(id) | count(data) |
+------+------+---------+-------------+
| 1 | 2 | 6 | 3 |
+------+------+---------+-------------+
1 row in set (0.03 sec)
mysql> select *, sum(id) from example ;
+------+------+---------+
| id | data | sum(id) |
+------+------+---------+
| 1 | 2 | 6 |
+------+------+---------+
1 row in set (0.09 sec)
mysql> select *, sum(id),count(data) from example group by id, data ;
+------+------+---------+-------------+
| id | data | sum(id) | count(data) |
+------+------+---------+-------------+
| 1 | 2 | 1 | 1 |
| 2 | 2 | 2 | 1 |
| 3 | 3 | 3 | 1 |
+------+------+---------+-------------+
3 rows in set (0.00 sec)
--
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
