Fix: Preserve sorting for the COPY TO plan

[bert-beyondloops]

Which issue does this PR close?

• Closes CopyTo plan loses ordering requirement during physical plan optimization #16784

Rationale for this change

The INSERT INTO already takes account of the ordering requirements during planning
The COPY TO not yet.
It seems it was not (yet) implemented?

This sometimes results in order requirements (sorting) being eliminated during physical plan optimizations.

What changes are included in this PR?

Are these changes tested?

A new test via dataframe/logical plan builder has been added.

Are there any user-facing changes?

Not via SQL, only when building your plans via the dataframe and/or LogicalPlanBuilder API.

[alamb]

Thanks @bert-beyondloops

I also double checked that COPY in SQL preserves the order

> explain COPY ( SELECT * FROM values(1),(2) ORDER BY column1) TO '/tmp/foo.csv';
+---------------+-------------------------------+
| plan_type     | plan                          |
+---------------+-------------------------------+
| physical_plan | ┌───────────────────────────┐ |
|               | │        DataSinkExec       │ |
|               | │    --------------------   │ |
|               | │     file: /tmp/foo.csv    │ |
|               | │                           │ |
|               | │        format: csv        │ |
|               | └─────────────┬─────────────┘ |
|               | ┌─────────────┴─────────────┐ |
|               | │          SortExec         │ |
|               | │    --------------------   │ |
|               | │  column1@0 ASC NULLS LAST │ |
|               | └─────────────┬─────────────┘ |
|               | ┌─────────────┴─────────────┐ |
|               | │       DataSourceExec      │ |
|               | │    --------------------   │ |
|               | │         bytes: 128        │ |
|               | │       format: memory      │ |
|               | │          rows: 1          │ |
|               | └───────────────────────────┘ |
|               |                               |
+---------------+-------------------------------+
1 row(s) fetched.
Elapsed 0.000 seconds.

[alamb]

I verified that this test fails without the code change in the PR:

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ Snapshot Summary ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Snapshot: copy_to_preserves_order
Source: datafusion/core/tests/dataframe/mod.rs:6241
────────────────────────────────────────────────────────────────────────────────
Expression: physical_plan_format
────────────────────────────────────────────────────────────────────────────────
-old snapshot
+new results
────────────┬───────────────────────────────────────────────────────────────────
    0     0 │ UnionExec
    1     1 │   DataSinkExec: sink=CsvSink(file_groups=[])
    2       │-    SortExec: expr=[column1@0 DESC], preserve_partitioning=[false]
    3       │-      DataSourceExec: partitions=1, partition_sizes=[1]
          2 │+    DataSourceExec: partitions=1, partition_sizes=[1]
    4     3 │   DataSourceExec: partitions=1, partition_sizes=[1]
────────────┴───────────────────────────────────────────────────────────────────

[bert-beyondloops]

Thanks @alamb for the quick review 🙏

[zhuqi-lucas]

LGTM Thank you @bert-beyondloops !

[xudong963]

The failing ci seems not to be related the PR.

[alamb]

close/reopen to retrigger CI

[alamb]

Thanks again @bert-beyondloops