[I] `SanityCheckPlan` Error during planning: ... does not satisfy parent order requirements: ... [datafusion]

[via GitHub]

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

   ### Describe the bug
   
   When run this query I get an error
   
   ```sql
   > 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
   
   ```shell
   # Make 1 row csv file:
   echo "cpu" > /tmp/table.csv
   echo "1" >> /tmp/table.csv
   # run datafusion-cli
   datafusion-cli
   ```
   
   ```sql
   -- 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
   
   ```sql
   +-----+
   | cpu |
   +-----+
   | 1 |
   | foo |
   +-----+
   ```
   
   ### Additional context
   
   @appletreeisyellow  found this while upgrading DataFusion in InfluxDB 3.0 
with the change from https://github.com/apache/datafusion/pull/11196
   
   We also tried applying the patch from @suremarc  in  
https://github.com/apache/datafusion/pull/11363 but the query still fails
   
   Interestingly, a very similar query that uses 2 constants works fine:
   ```sql
   > 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.
   ```


-- 
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: github-unsubscr...@datafusion.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
us...@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org
For additional commands, e-mail: github-h...@datafusion.apache.org