Repository navigation
[dbolympics] SanityCheckPlan error when sorting by a function of a product above a projection with a computed column #26430
Description
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:
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:
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:
select a, b, c + 1 as m, abs(a * b) as k from t where a > 0 order by k;
Activity
0xamlab commentedon Oct 17, 2026
I reproduced this at 484183d with datafusion-cli on the two-row table; the exact same SanityCheckPlan error fires. Instrumenting EquivalenceProperties::ordering_satisfy_requirement shows get_expr_properties(abs(ab)) failing with NotImplemented("Can't create a zero scalar from data_type "Null""). That error originates at datafusion/functions/src/math/abs.rs:179 (Interval::make_zero(&range.lower().data_type())?). Since BinaryExpr::get_properties returns ExprProperties::new_unknown() for Multiply/Divide, abs receives a Null interval and the error short-circuits before the existing [[abs(ab)]] ordering equivalence can satisfy the requirement.
jayzhan211 commentedon Oct 24, 2026
Another way to hit this, with the same cause:
abs,sqrt,ln,log2orcoshover aCASE, which is whatCOALESCEbecomes after simplification. It needs no projection or repartition, and fails with one partition:CaseExprdoesn't implementget_properties, so likea * bit reports the unknown range of typeNull, andabsfails to build its zero. Giving*and/real ranges would not fix this one.Fixing it where the error is dropped covers both. In
update_properties(equivalence/properties/mod.rs), a failingget_propertiesreturns early through?, so the ordering lookup right below it never runs, although that lookup already knowsabs(...)is sorted. I tried this change onmain:With it, the query above, every failing query in this issue's table, and the same queries with
ln,log2andcoshplan and return their rows in the right order. (sqrt(a * b)then stops at execution withcannot take square root of a negative numberon the row wherea * bis negative.) All sqllogictest files pass.This also breaks query q121 of the dbolympics benchmark (#26661).
[-]SanityCheckPlan error when sorting by a function of a product above a projection with a computed column[/-][+][dbolympics] SanityCheckPlan error when sorting by a function of a product above a projection with a computed column[/+]alamb commentedon Oct 24, 2026
Affects DB Olympics, see: #26661