You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "alpaylan (via GitHub)" <gi...@apache.org> on 2026/10/23 00:59:24 UTC
[I] SanityCheckPlan error with sort-merge join when a filter makes a sort or join key constant (constant lost in `EquivalenceGroup` projection) [datafusion]
alpaylan opened a new issue, #26624:
URL: https://github.com/apache/datafusion/issues/26624
### Describe the bug
With `datafusion.optimizer.prefer_hash_join = false`, queries where a `WHERE` clause makes an expression constant, and that expression is then needed as a sort or join key above a `SortMergeJoinExec`, fail to plan with `SanityCheckPlan`. `EnforceSorting` drops the sort on the constant key. Then `ProjectionPushdown` embeds a projection into the `SortMergeJoinExec`, and when the equivalence properties are projected through it the constant is lost, so the requirement above is no longer met.
The same queries work with the default hash join, and in 55.2.0, `prefer_hash_join` is a planner preference, so it should not decide whether a valid query plans.
### To Reproduce
`datafusion-cli`, first query (constant `t1.b + 1` used as a sort key):
```sql
SET datafusion.optimizer.prefer_hash_join = false;
CREATE TABLE t (a INT, b INT) AS VALUES (1, 1);
SELECT t1.b + 1 AS x, t2.a FROM t t1 JOIN t t2 ON t1.a = t2.a WHERE t1.b + 1 = 2 ORDER BY x, t2.a;
```
```
SanityCheckPlan
caused by
Error during planning: Plan: ["SortPreservingMergeExec: [x@0 ASC NULLS LAST, a@1 ASC NULLS LAST]", " SortExec: expr=[a@1 ASC NULLS LAST], preserve_partitioning=[true]", " ProjectionExec: expr=[CAST(b@0 AS Int64) + 1 as x, a@1 as a]", " SortMergeJoinExec: join_type=Inner, on=[(a@0, a@0)], projection=[b@1, a@2]", " SortExec: expr=[a@0 ASC], preserve_partitioning=[true]", " RepartitionExec: partitioning=Hash([a@0], 16), input_partitions=1", " FilterExec: CAST(b@1 AS Int64) + 1 = 2", " DataSourceExec: partitions=1, partition_sizes=[1]", " SortExec: expr=[a@0 ASC], preserve_partitioning=[true]", " RepartitionExec: partitioning=Hash([a@0], 16), input_partitions=1", " DataSourceExec: partitions=1, partition_sizes=[1]"] does not satisfy order requirements: [x@0 ASC NULLS LAST, a@1 ASC NULLS LAST]. Child-0 order: [[a@1 ASC NULLS LAST]]
```
Second query (constant `t1.b + (scalar subquery)` used as a join key of a second sort-merge join):
```sql
SET datafusion.optimizer.prefer_hash_join = false;
CREATE TABLE t (a INT, b INT) AS VALUES (1, 1), (2, 0);
SELECT * FROM (SELECT t1.b + (SELECT max(a) FROM t) AS x FROM t t1 JOIN t t2 ON t1.a = t2.a) s JOIN t t3 ON s.x = t3.a WHERE s.x = 2;
```
```
SanityCheckPlan
caused by
Error during planning: Plan: ["SortMergeJoinExec: join_type=Inner, on=[(x@0, a@0)]", " RepartitionExec: partitioning=Hash([x@0], 16), input_partitions=16", " ProjectionExec: expr=[b@0 + scalar_subquery(<pending>) as x]", " SortMergeJoinExec: join_type=Inner, on=[(a@0, a@0)], projection=[b@1]", ...] does not satisfy order requirements: [x@0 ASC]. Child-0 order: []
```
### Expected behavior
First query: one row `x = 2, a = 1`. Second query: one row `x = 2, a = 2, b = 0`. DataFusion 55.2.0, and `main` without the `SET` all return these rows.
### Additional context
- `main` at c0ec5127ac99 (2026-10-22): both fail. Release 55.2.0 returns the right rows, because it cannot embed a projection into `SortMergeJoinExec` (added in #24517), but the code below is the same there.
- Non-default setting needed: `datafusion.optimizer.prefer_hash_join = false`. The first query also fails with `LEFT JOIN`, and with `upper(t1.s) = 'X'` / `ORDER BY upper(t1.s)`; a plain column (`WHERE t1.b = 1`) works.
- There are two gaps, both in `datafusion/physical-expr/src/equivalence/class.rs`: `EquivalenceGroup::project` rebuilds each class with `EquivalenceClass::new(...)` and drops the class's `constant` (first query), and `EquivalenceGroup::project_expr_indirect` treats `Literal` leaves as valid under any projection but not `ScalarSubqueryExpr` leaves (second query, which also needs the first fix).
Found with an automated differential/metamorphic testing tool; reproduced on current `main`.
### Willingness to contribute
I would like to contribute a fix for this bug, but request guidance from the DataFusion community
--
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-unsubscribe@datafusion.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] REGRESSION: SanityCheckPlan error with sort-merge join when a filter makes a sort or join key constant (constant lost in `EquivalenceGroup` projection) [datafusion]
Posted by "kumarUjjawal (via GitHub) via github" <gi...@datafusion.apache.org>.
kumarUjjawal commented on issue #26624:
URL: https://github.com/apache/datafusion/issues/26624#issuecomment-6650967393
I'm taking a look at this
--
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-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] REGRESSION: SanityCheckPlan error with sort-merge join when a filter makes a sort or join key constant (constant lost in `EquivalenceGroup` projection) [datafusion]
Posted by "alamb (via GitHub) via github" <gi...@datafusion.apache.org>.
alamb commented on issue #26624:
URL: https://github.com/apache/datafusion/issues/26624#issuecomment-6616072850
I reproduced this with the two queries from the description using `datafusion-cli`. It is a regression, so I am confirming the `REGRESSION` prefix and `regression` label.
| Version | Query 1 (`ORDER BY x`) | Query 2 (join on `s.x`) |
|---|---|---|
| 53.0.0 | `x=2, a=1` | `x=2, a=2, b=0` |
| 54.0.0 | `x=2, a=1` | `x=2, a=2, b=0` |
| 55.0.0 | `x=2, a=1` | `x=2, a=2, b=0` |
| 55.1.0 | `x=2, a=1` | `x=2, a=2, b=0` |
| main (57.0.0-dev) | `SanityCheckPlan` error | `SanityCheckPlan` error |
On current main the error message also names the rule that broke the requirements:
```
SanityCheckPlan: order and distribution requirements stopped holding after physical optimizer rule 'ProjectionPushdown'
```
#24517 (projection support in `SortMergeJoinExec`) is not in `55.2.0` but is in `branch-56` and `main`, which matches the analysis in the description. So this is a regression that will ship in 56.0.0 unless fixed there.
--
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-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] REGRESSION: SanityCheckPlan error with sort-merge join when a filter makes a sort or join key constant (constant lost in `EquivalenceGroup` projection) [datafusion]
Posted by "alamb (via GitHub) via github" <gi...@datafusion.apache.org>.
alamb commented on issue #26624:
URL: https://github.com/apache/datafusion/issues/26624#issuecomment-6616307845
I reproduced both queries with `datafusion-cli` and confirmed this is a regression that is not yet in any release. I marked the issue as a regression.
Results on the release binaries (both queries, with `prefer_hash_join = false`):
| Version | Query 1 | Query 2 |
|---|---|---|
| 50.3.0 through 55.1.0 | `x=2, a=1` | `x=2, a=2, b=0` |
| 55.2.0 (reporter) | ok | ok |
| `main` (57.0.0-dev) | `SanityCheckPlan` | `SanityCheckPlan` |
`git bisect` on `main` points at a different first-bad commit for each query:
- **Query 2** (scalar subquery as join key) first fails at 872df4d8f0, "fix: keep join filter columns when pushing a projection below SortMergeJoinExec" (#24593, merged 2026-08-26). Its parent returns the right row. That PR made expression projections get embedded into `SortMergeJoinExec` (the `projection=[...]` on the join node), which is what sends the equivalence properties through `EquivalenceGroup::project`. #24517 alone does not trigger either query.
- **Query 1** (`t1.b + 1` as sort key) first fails at b9cea816dd, "fix: prevent unsafe integer interval propagation" (#25234, merged 2026-09-18). Its parent returns the right row. Before that PR the filter `CAST(b) + 1 = 2` narrowed column `b` itself to a constant, so the plan did not depend on the constant of the whole expression. After it, only the expression `CAST(b) + 1` is known constant, and that is the constant `EquivalenceGroup::project` drops when it rebuilds classes with `EquivalenceClass::new`.
So the two gaps in `class.rs` described above look like latent bugs that #24593 and #25234 exposed. Since neither commit is in 55.x, this only affects `main` and the upcoming 56.0.0.
--
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-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] REGRESSION: SanityCheckPlan error with sort-merge join when a filter makes a sort or join key constant (constant lost in `EquivalenceGroup` projection) [datafusion]
Posted by "alamb (via GitHub) via github" <gi...@datafusion.apache.org>.
alamb closed issue #26624: REGRESSION: SanityCheckPlan error with sort-merge join when a filter makes a sort or join key constant (constant lost in `EquivalenceGroup` projection)
URL: https://github.com/apache/datafusion/issues/26624
--
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-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org