SanityCheckPlan Error during planning: ... does not satisfy parent order requirements: ... #11492
Description
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.