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:55:19 UTC

[I] SanityCheckPlan error with sort-merge join when a filter says `a + b = a` [datafusion]

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

   ### Describe the bug
   
   If a filter contains an equality between an expression and one of its own sub-expressions, such as
   `x.a + x.b = x.a`, then a sort-merge join on `(a, b)` above that filter fails physical planning with a
   `SanityCheckPlan` error. The same query works with the default `prefer_hash_join = true`.
   
   ### To Reproduce
   
   
   Run in `datafusion-cli`:
   
   ```sql
   CREATE TABLE t (a INT, b INT) AS VALUES (1, 0), (2, 3);
   SET datafusion.optimizer.prefer_hash_join = false;
   SELECT * FROM t x JOIN t y ON x.a = y.a AND x.b = y.b WHERE x.a + x.b = x.a;
   ```
   
   Actual output on `main`:
   
   ```
   SanityCheckPlan
   caused by
   Error during planning: Plan: ["SortMergeJoinExec: join_type=Inner, on=[(a@0, a@0), (b@1, b@1)]", "  SortExec: expr=[a@0 + b@1 ASC, b@1 ASC], preserve_partitioning=[true]", "    RepartitionExec: partitioning=Hash([a@0, b@1], 16), input_partitions=1", "      FilterExec: a@0 + b@1 = a@0", "        DataSourceExec: partitions=1, partition_sizes=[1]", "  SortExec: expr=[a@0 + b@1 ASC, b@1 ASC], preserve_partitioning=[true]", "    RepartitionExec: partitioning=Hash([a@0, b@1], 16), input_partitions=1", "      FilterExec: a@0 + b@1 = a@0", "        DataSourceExec: partitions=1, partition_sizes=[1]"] does not satisfy order requirements: [a@0 ASC, b@1 ASC]. Child-0 order: [[a@0 + b@1 ASC, b@1 ASC]]
   ```
   
   ### Expected behavior
   
   
   
   The query should return the one matching row, as it does with the default settings:
   
   ```
   SELECT * FROM t x JOIN t y ON x.a = y.a AND x.b = y.b WHERE x.a + x.b = x.a;
   # SQLite 3 / DataFusion with prefer_hash_join = true: 1 | 0 | 1 | 0
   # DataFusion with prefer_hash_join = false: SanityCheckPlan error (above)
   ```
   
   `prefer_hash_join` is documented as a preference: "When set to true, the physical plan optimizer will prefer
   HashJoin over SortMergeJoin." It should not decide whether a valid query plans at all.
   
   
   ### Additional context
   
   
   
   - Tested on `main` at c0ec5127ac99 (2026-10-22): fails. Latest release 55.2.0: fails the same way.
   - With a single join key (`ON x.a = y.a`) the query works.
   - Where in the code: `FilterExec` turns `a + b = a` into the equivalence class `{a + b, a}` with `a + b` as the
     canonical member. `EquivalenceGroup::normalize_expr` (`datafusion/physical-expr/src/equivalence/class.rs`)
     rewrites bottom-up, so it also rewrites the `a` inside `a + b` and gets `(a + b) + b`, which is not in the class.
     The sort that `EnforceSorting` inserts for the join keys is then not recognized as satisfying `[a, b]`.
   - Related: #20194, fixed by #20231, was the same normalization problem with a literal (`expr = literal`). That fix
     skips equalities where neither side is a column. `a + b = a` has a column on one side, so it is still affected.
   - 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] SanityCheckPlan error with sort-merge join when a filter says `a + b = a` [datafusion]

Posted by "abp002 (via GitHub)" <gi...@apache.org>.
abp002 commented on issue #26623:
URL: https://github.com/apache/datafusion/issues/26623#issuecomment-6531454313

   take


-- 
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