SanityCheckPlan Error during planning: ... does not satisfy parent order requirements: ...

[alamb]

Describe the bug

When run this query I get an error

> SELECT (SELECT cpu from t2 ORDER BY cpu) UNION ALL (SELECT 'bar' as cpu from t2) ORDER BY cpu;

SanityCheckPlan
caused by
Error during planning: Child: ["UnionExec", "  SortExec: expr=[cpu@0 ASC NULLS LAST], preserve_partitioning=[true]", "    ProjectionExec: expr=[CAST(cpu@0 AS Utf8) as cpu]", "      RepartitionExec: partitioning=RoundRobinBatch(16), input_partitions=1", "        CsvExec: file_groups={1 group: [[tmp/table.csv]]}, projection=[cpu], output_ordering=[cpu@0 ASC NULLS LAST], has_header=true", "  ProjectionExec: expr=[bar as cpu]", "    CsvExec: file_groups={1 group: [[tmp/table.csv]]}, has_header=true"] does not satisfy parent order requirements: [PhysicalSortRequirement { expr: Column { name: "cpu", index: 0 }, options: Some(SortOptions { descending: false, nulls_first: false }) }]
>

To Reproduce

# Make 1 row csv file:
echo "cpu" > /tmp/table.csv
echo "1" >> /tmp/table.csv
# run datafusion-cli
datafusion-cli

-- define table with existing order
create external table t1(cpu int)
stored as csv location '/tmp/table.csv'
with order (cpu)
OPTIONS (format.has_header true);

--- Union a query with the actual data and one with a constant
SELECT (SELECT cpu from t2 ORDER BY cpu) UNION ALL (SELECT 'bar' as cpu from t2) ORDER BY cpu;

Expected behavior

I expect the query to run successfully and produce a two column table

+-----+
| cpu |
+-----+
| 1 |
| foo |
+-----+

Additional context

@appletreeisyellow found this while upgrading DataFusion in InfluxDB 3.0 with the change from #11196

We also tried applying the patch from @suremarc in #11363 but the query still fails

Interestingly, a very similar query that uses 2 constants works fine:

> SELECT (SELECT 'foo' as cpu from t2 ORDER BY cpu) UNION ALL (SELECT 'bar' as cpu from t2) ORDER BY cpu;

+-----+
| cpu |
+-----+
| bar |
| foo |
+-----+
2 row(s) fetched.
Elapsed 0.003 seconds.

[alamb]

When I was debugging this in our environment, I think the bug seems to be that the UnionExec is not correctly computing its output sort order when only one of the inputs has a constant output but not all of them