[I] Kept sort-merge join reads unsorted input after replaceSortMergeJoin rewrites the join below it [datafusion-comet]

["dwsmith1983 (via GitHub)" <gi...@apache.org>]

dwsmith1983 opened a new issue, #6673:
URL: https://github.com/apache/datafusion-comet/issues/6673

   ### Describe the bug
   
   With `spark.comet.exec.replaceSortMergeJoin=true`, a sort-merge join that `RewriteJoin` keeps can sit directly above a join on the same key that it rewrote to a shuffled hash join. `EnsureRequirements` ran before the rule and placed no sort between the two joins, because the inner sort-merge join's ordering already satisfied the outer one. The rewrite then removes the inner join's sorts, so the kept join reads unsorted input. Neither the native sort-merge join nor Spark's checks its input ordering, so matches are silently dropped.
   
   The rewrite already keeps `LeftSemi` joins with a right build side (in `branch-1.1`) and `ExistenceJoin` (on main), so both lose rows today.
   
   ### Steps to reproduce
   
   Parquet tables `big` = `(i % 100, i)` for 10000 rows, `small` = `(i * 10, i)` for 10 rows, `mid` = `(i % 100, i)` for 3000 rows, with `spark.comet.exec.replaceSortMergeJoin=true`, both broadcast thresholds at -1 and two shuffle partitions:
   
   ```sql
   SELECT big._1, big._2, small._2
   FROM big JOIN small ON big._1 = small._1
   WHERE small._2 < 0 OR EXISTS (SELECT 1 FROM mid WHERE mid._1 = big._1)
   ```
   
   Spark returns 1000 rows and Comet returns 10, with AQE on or off. The plan is a `SortMergeJoin` (ExistenceJoin) over the inner join rewritten to `CometHashJoin`, with no sort between them. A `LeftSemi` variant (`EXISTS` correlated on both `big._1` and `small._2`, with `PushLeftSemiLeftAntiThroughJoin` excluded) returns 10 or 5 rows against 1000.
   
   ### Expected behavior
   
   The same rows as Spark.
   
   ### Additional context
   
   The fix is in #6384, which needed it for the joins its build-size limit keeps: after the rewrite, a local sort is added wherever a child no longer satisfies its parent's required ordering, the same check `EnsureRequirements` makes. That PR adds these queries as tests. The `LeftSemi` part applies to `branch-1.1` as well.
   


-- 
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] Kept sort-merge join reads unsorted input after replaceSortMergeJoin rewrites the join below it [datafusion-comet]

["andygrove (via GitHub)" <gi...@apache.org>]

andygrove closed issue #6673: Kept sort-merge join reads unsorted input after replaceSortMergeJoin rewrites the join below it
URL: https://github.com/apache/datafusion-comet/issues/6673


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