tschwarzinger opened a new issue, #27472:
URL: https://github.com/apache/datafusion/issues/27472

   ### Describe the bug
   
   This is a follow-up to https://github.com/apache/datafusion/pull/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.
   
   ```sql
   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`:
   
   ```txt
   
   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=parquet
   ```
   
   In this version, no explicit sorting is necessary and output ordering is set.
   
   ### Additional context
   
   See https://github.com/apache/datafusion/pull/27393
   
   ### Willingness to contribute
   
   None


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: [email protected]

For queries about this service, please contact Infrastructure at:
[email protected]


---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to