Repository navigation
Description
Describe the bug
This is a follow-up to #27393
In this PR, it was established that only explicit sort order should be used by COPY but this only works if SortExec is the direct child of DataSinkExec.
In order to fix that I think we need a discussion about how we can detect explicit sort orders reliably. Maybe we could also use the logical plan for detecting that.
To Reproduce
See copy.slt.
The following additional slt queries trigger the bug. The query wraps the sort inside a projection so SortExec is not the direct child.
query I
COPY (
SELECT id FROM (
SELECT 'b' AS id, ts, x FROM union_const_src
UNION ALL
SELECT 'a' AS id, ts, x FROM union_const_src
) ORDER BY id, ts
) TO 'test_files/scratch/copy/union_const_order/out_projected_2.parquet';
----
6
statement ok
CREATE EXTERNAL TABLE copy_with_order_2 STORED AS PARQUET
LOCATION 'test_files/scratch/copy/union_const_order/out_projected_2.parquet';
query TT
EXPLAIN SELECT * FROM copy_with_order_2 ORDER BY id;
----
logical_plan
01)Sort: copy_with_order_2.id ASC NULLS LAST
02)--TableScan: copy_with_order_2 projection=[id]
physical_plan
01)SortExec: expr=[id@0 ASC NULLS LAST], preserve_partitioning=[false]
02)--DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/copy/union_const_order/out_projected_2.parquet]]}, projection=[id], file_type=parquet, sort_order_for_reorder=[id@0 ASC NULLS LAST]The explicit sort at the end should not be necessary. But because DataSinkExec does not use the explicit ordering, the Parquet file does not get the ordering metadata (I assume).
Expected behavior
Here is the same tests but with SELECT * causing the SortExec to be a direct child of DataSinkExec:
query I
COPY (
SELECT * FROM (
SELECT 'b' AS id, ts, x FROM union_const_src
UNION ALL
SELECT 'a' AS id, ts, x FROM union_const_src
) ORDER BY id, ts
) TO 'test_files/scratch/copy/union_const_order/out_projected_2.parquet';
----
6
statement ok
CREATE EXTERNAL TABLE copy_with_order_2 STORED AS PARQUET
LOCATION 'test_files/scratch/copy/union_const_order/out_projected_2.parquet';
query TT
EXPLAIN SELECT * FROM copy_with_order_2 ORDER BY id;
----
logical_plan
01)Sort: copy_with_order_2.id ASC NULLS LAST
02)--TableScan: copy_with_order_2 projection=[id, ts, x]
physical_plan DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/copy/union_const_order/out_projected_2.parquet]]}, projection=[id, ts, x], output_ordering=[id@0 ASC NULLS LAST, ts@1 ASC NULLS LAST], file_type=parquetIn this version, no explicit sorting is necessary and output ordering is set.
Additional context
See #27393
Willingness to contribute
None
Activity
[-]Wrong input ordering for `DataSinkExec` with complex inner plans[/-][+]`DataSinkExec` does not detect input ordering with complex inner plans (`SortExec` not the direct child)[/+]