[
https://issues.apache.org/jira/browse/HIVE-27876?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17789050#comment-17789050
]
Krisztian Kasa commented on HIVE-27876:
---------------------------------------
I found another data correctness issue regarding this optimization:
{code}
create table test_bucket(age int, name string, dept string) clustered by (age)
sorted by (age asc) into 2 buckets stored as orc;
insert into test_bucket values (10, 'user1', 'dept1'), (10, 'user2' , 'dept2'),
( 2, 'user2' , 'dept2');
insert into test_bucket values (1, 'user1', 'dept1'), ( 2, 'user2' , 'dept2');
select * from test_bucket;
{code}
Order is not global:
{code}
2 user2 dept2
10 user1 dept1
10 user2 dept2
2 user2 dept2
1 user1 dept1
{code}
{code}
select age, count(*) from test_bucket group by age;
{code}
Records with key {{age = 2}} are bit aggregated:
{code}
2 1
10 2
2 1
1 1
{code}
First insert creates one file:
{code}
itests/qtest/target/localfs/warehouse/test_bucket/000000_0
{"age":2,"name":"user2","dept":"dept2"}
{"age":10,"name":"user1","dept":"dept1"}
{"age":10,"name":"user2","dept":"dept2"}
{code}
Second insert creates 2 files:
{code}
itests/qtest/target/localfs/warehouse/test_bucket/000000_0_copy_1
{"age":2,"name":"user2","dept":"dept2"}
itests/qtest/target/localfs/warehouse/test_bucket/000001_0
{"age":1,"name":"user1","dept":"dept1"}
{code}
> Incorrect query results on tables with ClusterBy & SortBy
> ---------------------------------------------------------
>
> Key: HIVE-27876
> URL: https://issues.apache.org/jira/browse/HIVE-27876
> Project: Hive
> Issue Type: Bug
> Reporter: Naresh P R
> Assignee: Ramesh Kumar Thangarajan
> Priority: Major
> Labels: pull-request-available
>
> Repro:
>
> {code:java}
> create external table test_bucket(age int, name string, dept string)
> clustered by (age, name) sorted by (age asc, name asc) into 2 buckets stored
> as orc;
> insert into test_bucket values (1, 'user1', 'dept1'), ( 2, 'user2' , 'dept2');
> insert into test_bucket values (1, 'user1', 'dept1'), ( 2, 'user2' , 'dept2');
> //empty wrong results
> select age, name, count(*) from test_bucket group by age, name having
> count(*) > 1;
> +------+-------+------+
> | age | name | _c2 |
> +------+-------+------+
> +------+-------+------+
> // Workaround
> set hive.map.aggr=false;
> select age, name, count(*) from test_bucket group by age, name having
> count(*) > 1;
> +------+--------+------+
> | age | name | _c2 |
> +------+--------+------+
> | 1 | user1 | 2 |
> | 2 | user2 | 2 |
> +------+--------+------+ {code}
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
