[
https://issues.apache.org/jira/browse/DRILL-7721?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Oleg Zinoviev updated DRILL-7721:
---------------------------------
Description:
The TopN operator with the same sort field value can change the order of the
rows relative to the original (unstable sort). The External_sort operator
preserves the original row order (at least in the example). This inconsistency
breaks the implementation of data pagination.
Example:
1) Create table
{code:sql}
create table dfs.test_sort as (
select 1 as index, 0 as value
union all
select 1 as index, 1 as value
union all
select 1 as index, -1 as value
union all
select 2 as index, 0 as value
union all
select 2 as index, 1 as value
union all
select 2 as index, -1 as value
union all
select -1 as index, 0 as value
union all
select -1 as index, 1 as value
union all
select -1 as index, -1 as value
)
{code}
2) Execute query:
{code:sql}
select * from dfs.test_sort s order by s.index limit 100 // Should be TopN sort
{code}
3) Execute query:
{code:sql}
select * from dfs.test_sort s order by s.index limit 1000 // Should be
External_sort
{code}
4) Compare results:)
!topn.png! !sort.png!
was:
The TopN operator with the same sort field value can change the order of the
lines relative to the original (unstable sort). The External_sort operator
preserves the original row order (at least in the example). This inconsistency
breaks the implementation of data pagination.
Example:
1) Create table
{code:sql}
create table dfs.test_sort as (
select 1 as index, 0 as value
union all
select 1 as index, 1 as value
union all
select 1 as index, -1 as value
union all
select 2 as index, 0 as value
union all
select 2 as index, 1 as value
union all
select 2 as index, -1 as value
union all
select -1 as index, 0 as value
union all
select -1 as index, 1 as value
union all
select -1 as index, -1 as value
)
{code}
2) Execute query:
{code:sql}
select * from dfs.test_sort s order by s.index limit 100 // Should be TopN sort
{code}
3) Execute query:
{code:sql}
select * from dfs.test_sort s order by s.index limit 1000 // Should be
External_sort
{code}
4) Compare results:)
!topn.png! !sort.png!
> Inconsistent behavior of external_sort and topN
> -----------------------------------------------
>
> Key: DRILL-7721
> URL: https://issues.apache.org/jira/browse/DRILL-7721
> Project: Apache Drill
> Issue Type: Bug
> Affects Versions: 1.17.0
> Reporter: Oleg Zinoviev
> Priority: Major
> Attachments: sort.png, topn.png
>
>
> The TopN operator with the same sort field value can change the order of the
> rows relative to the original (unstable sort). The External_sort operator
> preserves the original row order (at least in the example). This
> inconsistency breaks the implementation of data pagination.
> Example:
> 1) Create table
> {code:sql}
> create table dfs.test_sort as (
> select 1 as index, 0 as value
> union all
> select 1 as index, 1 as value
> union all
> select 1 as index, -1 as value
> union all
> select 2 as index, 0 as value
> union all
> select 2 as index, 1 as value
> union all
> select 2 as index, -1 as value
> union all
> select -1 as index, 0 as value
> union all
> select -1 as index, 1 as value
> union all
> select -1 as index, -1 as value
> )
> {code}
> 2) Execute query:
> {code:sql}
> select * from dfs.test_sort s order by s.index limit 100 // Should be TopN
> sort
> {code}
> 3) Execute query:
> {code:sql}
> select * from dfs.test_sort s order by s.index limit 1000 // Should be
> External_sort
> {code}
> 4) Compare results:)
> !topn.png! !sort.png!
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
