[HIVE-4522] Confusing result generated when use mulit aggregate functions with star columns

• Type: Bug
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 0.12.0
• Component/s: Query Processor
• Assignee: Unassigned
• Reporter: caofangkun
• Created: 08/May/13 05:42
• Updated: 08/May/13 05:44

Description

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)

Issue Links

• relates to: HIVE-4392 Illogical InvalidObjectException throwed when use mulit aggregate functions with star columns