[jira] [Created] (DRILL-7721) Inconsistent behavior of external_sort and topN

["Oleg Zinoviev (Jira)" <ji...@apache.org>]

Oleg Zinoviev created DRILL-7721:
------------------------------------

             Summary: 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
         Attachments: sort.png, topn.png

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! 



--
This message was sent by Atlassian Jira
(v8.3.4#803005)