JoeryH opened a new issue, #26430: URL: https://github.com/apache/datafusion/issues/26430
### Describe the bug A query that sorts by a scalar function of a product (for example `abs(a * b)`) fails planning with a `SanityCheckPlan` error. This happens when the sort's input is a projection with a computed column, above a round-robin repartition. The sort-preserving merge rejects its input, although the input's ordering is the same as the requirement: ```text SanityCheckPlan caused by Error during planning: Plan: ["SortPreservingMergeExec: [abs(a@0 * b@1) ASC NULLS LAST]", " SortExec: expr=[abs(a@0 * b@1) ASC NULLS LAST], preserve_partitioning=[true]", " ProjectionExec: expr=[a@0 as a, b@1 as b, c@2 + 1 as m]", " RepartitionExec: partitioning=RoundRobinBatch(2), input_partitions=1", " FilterExec: a@0 > 0", " DataSourceExec: partitions=1, partition_sizes=[1]"] does not satisfy order requirements: [abs(a@0 * b@1) ASC NULLS LAST]. Child-0 order: [[abs(a@0 * b@1) ASC NULLS LAST]] ``` ### To Reproduce In `datafusion-cli` at `main` (`63a2801c1`). The error is the same on 54.1.0: ```sql set datafusion.execution.target_partitions = 2; create table t(a double, b double, c double) as values (1, 2, 3), (4, -5, 6); select a, b, c + 1 as m from t where a > 0 order by abs(a * b); ``` The same table and settings, with one change to the query each time: | Query | Result | |---|---| | `select a, b, c + 1 as m from t where a > 0 order by abs(a * b)` | error | | `... order by abs(a / b)` | error | | `... order by abs(a * 2)` | error | | `... order by sqrt(a * b)` | error | | `... order by abs(a * b) limit 1` (TopK) | error | | `select a, b, -c as m ... order by abs(a * b)` | error | | `... order by abs(a + b)` | ok | | `... order by abs(a - b)` | ok | | `... order by a * b` | ok | | `... order by -(a * b)` | ok | | `... order by (a * b) + 1` | ok | | `select a, b ... order by abs(a * b)` (no computed column) | ok | | `select a, b, c as m ... order by abs(a * b)` (plain column) | ok | | `select a, b, 1 as m ... order by abs(a * b)` (literal) | ok | | `... from t order by abs(a * b)` (no filter, no repartition) | ok | So the failure needs all of these: - a scalar function over `*` or `/` - a computed column in the projection below the sort - a repartition below that projection ### Expected behavior The query plans and returns the two rows ordered by `abs(a * b)`. ### Additional context This is different from #26391 and #26283: no sort key here is a constant. #25921 (fixed by #25922) is in the same area, `EquivalenceGroup` projection, but `main` includes that fix and still fails. A workaround is to project the sort key and order by its alias: ```sql select a, b, c + 1 as m, abs(a * b) as k from t where a > 0 order by k; ``` -- 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]
