You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "coderfender (via GitHub)" <gi...@apache.org> on 2026/06/03 23:34:14 UTC

[PR] feat: implement native existence join implementation [datafusion-comet]

coderfender opened a new pull request, #4587:
URL: https://github.com/apache/datafusion-comet/pull/4587

   ## Which issue does this PR close?
   
   <!--
   We generally require a GitHub issue to be filed for all bug fixes and enhancements and this helps us generate change logs for our releases. You can link an issue to this PR using the GitHub syntax. For example `Closes #123` indicates that this PR will close issue #123.
   -->
   
   Closes #.
   
   ## Rationale for this change
   
   <!--
    Why are you proposing this change? If this is already explained clearly in the issue then this section is not needed.
    Explaining clearly why changes are proposed helps reviewers understand your changes and offer better suggestions for fixes.
   -->
   
   ## What changes are included in this PR?
   
   <!--
   There is no need to duplicate the description in the issue here but it is sometimes worth providing a summary of the individual changes in this PR.
   -->
   
   ## How are these changes tested?
   
   <!--
   We typically require tests for all PRs in order to:
   1. Prevent the code from being accidentally broken by subsequent changes
   2. Serve as another way to document the expected behavior of the code
   
   If tests are not included in your PR, please explain why (for example, are they covered by existing tests)?
   -->
   


-- 
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: [PR] feat: wire native existence join support [datafusion-comet]

Posted by "comphead (via GitHub)" <gi...@apache.org>.
comphead commented on PR #4587:
URL: https://github.com/apache/datafusion-comet/pull/4587#issuecomment-5877082217

   Failing tests are the biggest issue so far
     - sql-file: join/existence_join.sql *** FAILED *** (1 second, 688 milliseconds)
   


-- 
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: [PR] feat: wire native existence join support [datafusion-comet]

Posted by "comphead (via GitHub)" <gi...@apache.org>.
comphead commented on code in PR #4587:
URL: https://github.com/apache/datafusion-comet/pull/4587#discussion_r4157863390


##########
spark/src/main/scala/org/apache/spark/sql/comet/operators.scala:
##########
@@ -2574,6 +2574,32 @@ trait CometHashJoin {
         case FullOuter => JoinType.FullOuter
         case LeftSemi => JoinType.LeftSemi
         case LeftAnti => JoinType.LeftAnti
+        case ExistenceJoin(_) if CometConf.COMET_EXEC_EXISTENCE_JOIN_ENABLED.get(join.conf) =>
+          // Native only for equi-key joins with bare column/literal keys and no residual.
+          if (join.condition.isDefined) {
+            withFallbackReason(
+              join,
+              "ExistenceJoin with a residual (non-equi) condition is not supported natively")
+            return None
+          } else if (!(join.leftKeys ++ join.rightKeys).forall {
+              case _: Attribute | _: Literal => true
+              case _ => false
+            }) {
+            withFallbackReason(
+              join,
+              "ExistenceJoin with a computed (non-column) join key is not supported natively")
+            return None
+          } else {
+            JoinType.Existence
+          }
+        case ExistenceJoin(_) =>
+          // The guard above matched only when the flag is enabled; reaching here means it is off.
+          // Report a toggle-specific reason so the plan does not read like a permanent limitation.
+          withFallbackReason(
+            join,
+            "Native ExistenceJoin is disabled; set " +
+              s"${CometConf.COMET_EXEC_EXISTENCE_JOIN_ENABLED.key}=true to enable it")
+          return None

Review Comment:
   These guards would read better as flat early exits in the pre-flight block above (after the null-aware check, before `val condition`), like the collation and null-aware guards:
   
   ```scala
   if (join.joinType.isInstanceOf[ExistenceJoin]) {
     if (!CometConf.COMET_EXEC_EXISTENCE_JOIN_ENABLED.get(join.conf)) {
       withFallbackReason(join, "<current flag-off reason>")
       return None
     }
     // same shape for join.condition.isDefined and !joinKeys.forall(_.isInstanceOf[Attribute])
   }
   ```
   
   The match then keeps a single `case ExistenceJoin(_) => JoinType.Existence`. Each guard keeps its own reason, so the per-guard tests should be unaffected (expectation, not run). This also removes:
   
   - the second `case ExistenceJoin(_)` arm, which only works because of arm order, and the comment that explains that order
   - the `join.leftKeys ++ join.rightKeys` rebuild, since `joinKeys` is already in scope
   - the `exprToProto` call on the residual that is then thrown away. If the residual holds an unsupported expression, the early `return None` fires first and the `residual (non-equi)` reason is never recorded. `CometSortMergeJoinExec.convert` already checks its join-filter flag before `val condition`.
   
   Two smaller points:
   
   - I expect `Literal` cannot reach this check. `ExtractEquiJoinKeys` skips equalities with a reference-free side (`l.references.isEmpty || r.references.isEmpty`, same in Spark 3.4.3, 3.5.5 and 4.0.2), so `_.isInstanceOf[Attribute]` would be enough and the new `Literal` import could go. This is from reading the Spark source, not run.
   - The reason for the guards (batch-level evaluation is eager while Spark stops at the first match) only lives in test comments. One sentence here would be more useful than `Native only for equi-key joins ...`.



##########
spark/src/test/scala/org/apache/comet/exec/CometJoinSuite.scala:
##########
@@ -1574,6 +1574,186 @@ class CometJoinSuite extends CometTestBase {
     }
   }
 
+  test("ExistenceJoin via BroadcastHashJoin (EXISTS combined with OR)") {
+    withSQLConf(
+      CometConf.COMET_EXEC_EXISTENCE_JOIN_ENABLED.key -> "true",
+      SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "10MB",
+      SQLConf.ADAPTIVE_AUTO_BROADCASTJOIN_THRESHOLD.key -> "10MB") {
+      withParquetTable((0 until 100).map(i => (i, if (i % 3 == 0) "US" else "EU")), "tbl_a") {
+        withParquetTable((0 until 30).map(i => (i, i + 1)), "tbl_b") {
+          val df = sql("SELECT * FROM tbl_a a " +
+            "WHERE a._2 = 'US' OR EXISTS (SELECT /*+ BROADCAST(b) */ 1 FROM tbl_b b WHERE b._1 = a._1)")
+          checkSparkAnswerAndOperator(
+            df,
+            Seq(classOf[CometBroadcastExchangeExec], classOf[CometBroadcastHashJoinExec]))
+        }
+      }
+    }
+  }
+
+  test("ExistenceJoin via ShuffledHashJoin (EXISTS combined with OR)") {
+    withSQLConf(
+      CometConf.COMET_EXEC_EXISTENCE_JOIN_ENABLED.key -> "true",
+      SQLConf.PREFER_SORTMERGEJOIN.key -> "false",
+      "spark.sql.join.forceApplyShuffledHashJoin" -> "true",
+      SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+      SQLConf.ADAPTIVE_AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1") {
+      withParquetTable((0 until 100).map(i => (i, if (i % 3 == 0) "US" else "EU")), "tbl_a") {
+        withParquetTable((0 until 30).map(i => (i, i + 1)), "tbl_b") {
+          val df = sql(
+            "SELECT * FROM tbl_a a " +
+              "WHERE a._2 = 'US' OR EXISTS (SELECT 1 FROM tbl_b b WHERE b._1 = a._1)")
+          checkSparkAnswerAndOperator(df, Seq(classOf[CometHashJoinExec]))
+        }
+      }
+    }
+  }
+
+  test("ExistenceJoin via SortMergeJoin falls back to Spark") {

Review Comment:
   Three of these tests re-run queries that `existence_join.sql` already has, with the same assertion: `ExistenceJoin via SortMergeJoin falls back to Spark`, `ExistenceJoin with duplicate build keys runs natively (markers not multiplied)` and `ExistenceJoin with NOT EXISTS combined with OR runs natively`.
   
   - The fixture covers duplicates. `ex_right` holds key 2 twice and `ex_left` id 2 has region `EU`, so a multiplied marker would add a row, and `ex_right_dups` repeats key 1 under `SHUFFLE_HASH`.
   - `NOT` is applied by a Filter above the same `ExistenceJoin`, and the duplicate-key and `NOT EXISTS` tests carry the same class pin as the first test.
   - The SMJ fallback is decided by the join type alone, and the fixture's `MERGE` queries assert the same `Unsupported join type` reason.
   
   These three can go. The first two tests are worth keeping because they pin the join class, which a `.sql` query cannot do.
   
   The residual, computed-key and flag-off tests share the same tables and AQE-off block and differ only in the correlation predicate (and the flag). They could be one test with a single #6442 comment, or `.sql` fixtures that set `-- Config: spark.sql.adaptive.enabled=false` and use `query expect_fallback(...)`. The flag-off case would need its own fixture because `-- Config:` is file-wide.



##########
spark/src/main/scala/org/apache/comet/rules/RewriteJoin.scala:
##########
@@ -67,8 +67,10 @@ object RewriteJoin extends JoinSelectionHelper {
   def rewrite(plan: SparkPlan): SparkPlan = plan match {
     case smj: SortMergeJoinExec =>
       getSmjBuildSide(smj) match {
-        case Some(BuildRight) if smj.joinType == LeftSemi =>
+        case Some(BuildRight)
+            if smj.joinType == LeftSemi || smj.joinType.isInstanceOf[ExistenceJoin] =>
           // LeftSemi https://github.com/apache/datafusion-comet/issues/2667
+          // ExistenceJoin https://github.com/apache/datafusion-comet/issues/2697

Review Comment:
   #2697 is titled "Investigate potential issues with HashJoin and LeftSemi" and neither it nor #2667 mentions `ExistenceJoin`, so this line reads as if the issue covered existence joins. Something like `// ExistenceJoin is excluded by analogy with LeftSemi until the root cause in #2697 is found` would be more accurate. The forced-SHJ test in `CometJoinSuite` has the same wording in its comment.



##########
spark/src/test/scala/org/apache/comet/exec/CometJoinSuite.scala:
##########
@@ -1574,6 +1574,186 @@ class CometJoinSuite extends CometTestBase {
     }
   }
 
+  test("ExistenceJoin via BroadcastHashJoin (EXISTS combined with OR)") {
+    withSQLConf(
+      CometConf.COMET_EXEC_EXISTENCE_JOIN_ENABLED.key -> "true",
+      SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "10MB",
+      SQLConf.ADAPTIVE_AUTO_BROADCASTJOIN_THRESHOLD.key -> "10MB") {
+      withParquetTable((0 until 100).map(i => (i, if (i % 3 == 0) "US" else "EU")), "tbl_a") {
+        withParquetTable((0 until 30).map(i => (i, i + 1)), "tbl_b") {
+          val df = sql("SELECT * FROM tbl_a a " +
+            "WHERE a._2 = 'US' OR EXISTS (SELECT /*+ BROADCAST(b) */ 1 FROM tbl_b b WHERE b._1 = a._1)")
+          checkSparkAnswerAndOperator(
+            df,
+            Seq(classOf[CometBroadcastExchangeExec], classOf[CometBroadcastHashJoinExec]))
+        }
+      }
+    }
+  }
+
+  test("ExistenceJoin via ShuffledHashJoin (EXISTS combined with OR)") {
+    withSQLConf(
+      CometConf.COMET_EXEC_EXISTENCE_JOIN_ENABLED.key -> "true",
+      SQLConf.PREFER_SORTMERGEJOIN.key -> "false",
+      "spark.sql.join.forceApplyShuffledHashJoin" -> "true",
+      SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+      SQLConf.ADAPTIVE_AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1") {
+      withParquetTable((0 until 100).map(i => (i, if (i % 3 == 0) "US" else "EU")), "tbl_a") {
+        withParquetTable((0 until 30).map(i => (i, i + 1)), "tbl_b") {
+          val df = sql(
+            "SELECT * FROM tbl_a a " +
+              "WHERE a._2 = 'US' OR EXISTS (SELECT 1 FROM tbl_b b WHERE b._1 = a._1)")
+          checkSparkAnswerAndOperator(df, Seq(classOf[CometHashJoinExec]))
+        }
+      }
+    }
+  }
+
+  test("ExistenceJoin via SortMergeJoin falls back to Spark") {
+    // Existence sort-merge joins are not executed natively; verify the fallback.
+    withSQLConf(
+      CometConf.COMET_EXEC_EXISTENCE_JOIN_ENABLED.key -> "true",
+      SQLConf.PREFER_SORTMERGEJOIN.key -> "true",
+      SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+      SQLConf.ADAPTIVE_AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1") {
+      withParquetTable((0 until 100).map(i => (i, if (i % 3 == 0) "US" else "EU")), "tbl_a") {
+        withParquetTable((0 until 30).map(i => (i, i + 1)), "tbl_b") {
+          val df = sql(
+            "SELECT * FROM tbl_a a " +
+              "WHERE a._2 = 'US' OR EXISTS (SELECT 1 FROM tbl_b b WHERE b._1 = a._1)")
+          checkSparkAnswerAndFallbackReason(df, "Unsupported join type")
+        }
+      }
+    }
+  }
+
+  test("ExistenceJoin with residual condition falls back to Spark") {
+    // A non-equi residual predicate is evaluated over the whole candidate batch by DataFusion's
+    // LeftMark join (no first-match short-circuit), so Comet keeps it on Spark; verify parity.
+    // AQE is disabled because a declined broadcast join loses its fallback reason from the
+    // AQE-final plan; see https://github.com/apache/datafusion-comet/issues/6442.
+    withSQLConf(
+      CometConf.COMET_EXEC_EXISTENCE_JOIN_ENABLED.key -> "true",
+      SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> "false",
+      SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "10MB",
+      SQLConf.ADAPTIVE_AUTO_BROADCASTJOIN_THRESHOLD.key -> "10MB") {
+      withParquetTable((0 until 100).map(i => (i, if (i % 3 == 0) "US" else "EU")), "tbl_a") {
+        withParquetTable((0 until 30).map(i => (i, i + 1)), "tbl_b") {
+          val df = sql(
+            "SELECT * FROM tbl_a a WHERE a._2 = 'US' OR EXISTS " +
+              "(SELECT /*+ BROADCAST(b) */ 1 FROM tbl_b b WHERE b._1 = a._1 AND b._2 > a._1)")
+          checkSparkAnswerAndFallbackReason(df, "residual (non-equi)")
+        }
+      }
+    }
+  }
+
+  test("ExistenceJoin with computed join key falls back to Spark") {
+    // A computed join key (not a bare column reference) is evaluated eagerly over the batch by
+    // the native join, so Comet keeps it on Spark; verify parity.
+    // AQE is disabled because a declined broadcast join loses its fallback reason from the
+    // AQE-final plan; see https://github.com/apache/datafusion-comet/issues/6442.
+    withSQLConf(
+      CometConf.COMET_EXEC_EXISTENCE_JOIN_ENABLED.key -> "true",
+      SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> "false",
+      SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "10MB",
+      SQLConf.ADAPTIVE_AUTO_BROADCASTJOIN_THRESHOLD.key -> "10MB") {
+      withParquetTable((0 until 100).map(i => (i, if (i % 3 == 0) "US" else "EU")), "tbl_a") {
+        withParquetTable((0 until 30).map(i => (i, i + 1)), "tbl_b") {
+          val df = sql(
+            "SELECT * FROM tbl_a a WHERE a._2 = 'US' OR EXISTS " +
+              "(SELECT /*+ BROADCAST(b) */ 1 FROM tbl_b b WHERE b._1 = a._1 + 1)")
+          checkSparkAnswerAndFallbackReason(df, "computed (non-column)")
+        }
+      }
+    }
+  }
+
+  test("ExistenceJoin with duplicate build keys runs natively (markers not multiplied)") {
+    withSQLConf(
+      CometConf.COMET_EXEC_EXISTENCE_JOIN_ENABLED.key -> "true",
+      SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "10MB",
+      SQLConf.ADAPTIVE_AUTO_BROADCASTJOIN_THRESHOLD.key -> "10MB") {
+      withParquetTable((0 until 100).map(i => (i, if (i % 3 == 0) "US" else "EU")), "tbl_a") {
+        // Build side repeats keys 0..4 many times; the existence marker must stay "at least one
+        // match" and must not multiply matching probe rows.
+        withParquetTable((0 until 30).map(i => (i % 5, i)), "tbl_b") {
+          val df = sql(
+            "SELECT * FROM tbl_a a WHERE a._2 = 'US' OR EXISTS " +
+              "(SELECT /*+ BROADCAST(b) */ 1 FROM tbl_b b WHERE b._1 = a._1)")
+          checkSparkAnswerAndOperator(
+            df,
+            Seq(classOf[CometBroadcastExchangeExec], classOf[CometBroadcastHashJoinExec]))
+        }
+      }
+    }
+  }
+
+  test("ExistenceJoin with NOT EXISTS combined with OR runs natively") {
+    withSQLConf(
+      CometConf.COMET_EXEC_EXISTENCE_JOIN_ENABLED.key -> "true",
+      SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "10MB",
+      SQLConf.ADAPTIVE_AUTO_BROADCASTJOIN_THRESHOLD.key -> "10MB") {
+      withParquetTable((0 until 100).map(i => (i, if (i % 3 == 0) "US" else "EU")), "tbl_a") {
+        withParquetTable((0 until 30).map(i => (i, i + 1)), "tbl_b") {
+          val df = sql(
+            "SELECT * FROM tbl_a a WHERE a._2 = 'US' OR NOT EXISTS " +
+              "(SELECT /*+ BROADCAST(b) */ 1 FROM tbl_b b WHERE b._1 = a._1)")
+          checkSparkAnswerAndOperator(
+            df,
+            Seq(classOf[CometBroadcastExchangeExec], classOf[CometBroadcastHashJoinExec]))
+        }
+      }
+    }
+  }
+
+  test("ExistenceJoin falls back to Spark when the feature flag is disabled") {
+    // With the flag off, the join is declined with a toggle-specific reason rather than a generic
+    // "Unsupported join type" message that reads like a permanent limitation. AQE is disabled
+    // because a declined broadcast join loses its fallback reason from the AQE-final plan; see
+    // https://github.com/apache/datafusion-comet/issues/6442.
+    withSQLConf(
+      CometConf.COMET_EXEC_EXISTENCE_JOIN_ENABLED.key -> "false",
+      SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> "false",
+      SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "10MB",
+      SQLConf.ADAPTIVE_AUTO_BROADCASTJOIN_THRESHOLD.key -> "10MB") {
+      withParquetTable((0 until 100).map(i => (i, if (i % 3 == 0) "US" else "EU")), "tbl_a") {
+        withParquetTable((0 until 30).map(i => (i, i + 1)), "tbl_b") {
+          val df = sql(
+            "SELECT * FROM tbl_a a WHERE a._2 = 'US' OR EXISTS " +
+              "(SELECT /*+ BROADCAST(b) */ 1 FROM tbl_b b WHERE b._1 = a._1)")
+          checkSparkAnswerAndFallbackReason(df, "Native ExistenceJoin is disabled")
+        }
+      }
+    }
+  }
+
+  test("ExistenceJoin via SortMergeJoin stays on Spark even with forceShuffledHashJoin") {
+    // RewriteJoin refuses to rewrite an existence SortMergeJoin into a BuildRight ShuffledHashJoin
+    // (https://github.com/apache/datafusion-comet/issues/2697), so even with forced SHJ the join
+    // must stay on Spark rather than run as a native hash join. Assert on the plan (no native join
+    // form) rather than the fallback reason, which is the robust signal for this rewrite guard.
+    withSQLConf(
+      CometConf.COMET_EXEC_EXISTENCE_JOIN_ENABLED.key -> "true",
+      CometConf.COMET_FORCE_SHJ.key -> "true",
+      SQLConf.PREFER_SORTMERGEJOIN.key -> "true",
+      SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+      SQLConf.ADAPTIVE_AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1") {
+      withParquetTable((0 until 100).map(i => (i, if (i % 3 == 0) "US" else "EU")), "tbl_a") {
+        withParquetTable((0 until 30).map(i => (i, i + 1)), "tbl_b") {
+          val df = sql(
+            "SELECT * FROM tbl_a a " +
+              "WHERE a._2 = 'US' OR EXISTS (SELECT 1 FROM tbl_b b WHERE b._1 = a._1)")
+          val (_, cometPlan) = checkSparkAnswer(df)
+          assert(
+            collect(cometPlan) { case h: CometHashJoinExec => h }.isEmpty &&
+              collect(cometPlan) { case s: CometSortMergeJoinExec => s }.isEmpty,
+            s"Existence SMJ must not be rewritten to a native hash join:\n$cometPlan")

Review Comment:
   This walks the plan twice for one emptiness check. A single `collectFirst` keeps the same assertion (`nativeHashJoins` earlier in this suite is another one-pass helper if only the hash join half is needed):
   
   ```suggestion
             assert(
               collectFirst(cometPlan) {
                 case j @ (_: CometHashJoinExec | _: CometSortMergeJoinExec) => j
               }.isEmpty,
               s"Existence SMJ must not be rewritten to a native hash join:\n$cometPlan")
   ```



##########
spark/src/test/resources/sql-tests/join/existence_join.sql:
##########
@@ -0,0 +1,281 @@
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements.  See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership.  The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License.  You may obtain a copy of the License at
+--
+--   http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied.  See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+
+-- Tests for ExistenceJoin: produced when EXISTS / NOT EXISTS is combined
+-- with another predicate via OR, preventing rewrite to LeftSemi / LeftAnti.
+--
+-- Strategy hints are placed INSIDE the EXISTS subquery (on the subquery's own
+-- relation alias), because an outer hint referencing the subquery table cannot
+-- resolve it and silently falls back to a broadcast hash join. Subquery-local
+-- hints (verified on Spark 4.1.3 with AQE off) select ShuffledHashJoin /
+-- SortMergeJoin.
+--
+-- Native existence support is currently hash-only: BROADCAST and SHUFFLE_HASH
+-- cases exercise CometBroadcastHashJoinExec / CometHashJoinExec, while MERGE
+-- cases fall back to Spark's SortMergeJoin (existence SMJ is not yet native) and
+-- verify result parity under the Comet-enabled config.
+
+-- Native ExistenceJoin support is enabled by default; this fixture pins the flag on explicitly.
+-- Config: spark.comet.exec.existenceJoin.enabled=true
+
+-- ============================================================
+-- Setup: NULLs (both sides), duplicates, empty build, all-NULL build
+-- ============================================================
+
+statement
+CREATE TABLE ex_left(id int, k int, region string) USING parquet
+
+statement
+INSERT INTO ex_left VALUES
+  (1, 1, 'US'),
+  (2, 2, 'EU'),
+  (3, NULL, 'US'),
+  (4, 4, 'EU'),
+  (5, 5, 'EU'),
+  (6, NULL, 'EU')
+
+statement
+CREATE TABLE ex_right(id int, k int) USING parquet
+
+statement
+INSERT INTO ex_right VALUES (10, 1), (11, 2), (12, 2), (13, NULL)
+
+statement
+CREATE TABLE ex_right_no_nulls(id int, k int) USING parquet
+
+statement
+INSERT INTO ex_right_no_nulls VALUES (10, 1), (11, 5)
+
+statement
+CREATE TABLE ex_right_empty(id int, k int) USING parquet
+
+statement
+CREATE TABLE ex_right_dups(id int, k int) USING parquet
+
+statement
+INSERT INTO ex_right_dups VALUES (10, 1), (11, 1), (12, 1), (13, 2)
+
+statement
+CREATE TABLE ex_right_all_null(id int, k int) USING parquet
+
+statement
+INSERT INTO ex_right_all_null VALUES (10, NULL), (11, NULL)
+
+statement
+CREATE TABLE ex_left_empty(id int, k int, region string) USING parquet
+
+-- ============================================================
+-- EXISTS with OR across all three strategies (hint in subquery)
+-- ============================================================
+
+query
+SELECT * FROM ex_left l
+WHERE l.region = 'US'
+   OR EXISTS (SELECT /*+ BROADCAST(r) */ 1 FROM ex_right r WHERE r.k = l.k)
+ORDER BY l.id
+
+query
+SELECT * FROM ex_left l
+WHERE l.region = 'US'
+   OR EXISTS (SELECT /*+ SHUFFLE_HASH(r) */ 1 FROM ex_right r WHERE r.k = l.k)
+ORDER BY l.id
+
+query expect_fallback(Unsupported join type)
+SELECT * FROM ex_left l
+WHERE l.region = 'US'
+   OR EXISTS (SELECT /*+ MERGE(r) */ 1 FROM ex_right r WHERE r.k = l.k)
+ORDER BY l.id
+
+-- ============================================================
+-- Empty build: every left row is unmatched, only OR-arm rows survive
+-- ============================================================
+
+query
+SELECT * FROM ex_left l
+WHERE l.region = 'US'
+   OR EXISTS (SELECT /*+ BROADCAST(r) */ 1 FROM ex_right_empty r WHERE r.k = l.k)
+ORDER BY l.id
+
+query expect_fallback(Unsupported join type)

Review Comment:
   There are four `MERGE` queries and all assert `Unsupported join type`, which `CometSortMergeJoinExec` reports from the join type alone. The data, `NOT` and duplicate keys cannot change that outcome, so the first one is enough and this one, the `NOT EXISTS` one and the duplicate-keys one can go.
   
   This one could instead become `SHUFFLE_HASH` over `ex_right_empty`. None of the new tests covers SHJ with an empty build side, while BHJ is covered a few lines up.
   
   Optionally the `NOT EXISTS` + `SHUFFLE_HASH` query as well, since it is the plain `EXISTS` + `SHUFFLE_HASH` join with a `NOT` Filter above it. The banner of the "Empty left" section says "each strategy" but only `SHUFFLE_HASH` runs.



-- 
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: [PR] feat: implement native existence join implementation [datafusion-comet]

Posted by "coderfender (via GitHub)" <gi...@apache.org>.
coderfender commented on PR #4587:
URL: https://github.com/apache/datafusion-comet/pull/4587#issuecomment-4618128818

   Absolutely agreed! Thank you for the review @comphead . 


-- 
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: [PR] feat: wire native existence join support [datafusion-comet]

Posted by "coderfender (via GitHub)" <gi...@apache.org>.
coderfender commented on code in PR #4587:
URL: https://github.com/apache/datafusion-comet/pull/4587#discussion_r4156541132


##########
spark/src/main/scala/org/apache/spark/sql/comet/operators.scala:
##########
@@ -2488,6 +2488,17 @@ trait CometHashJoin {
         case FullOuter => JoinType.FullOuter
         case LeftSemi => JoinType.LeftSemi
         case LeftAnti => JoinType.LeftAnti
+        // Native only for equi-join keys that are bare column refs / literals: Spark short-
+        // circuits on first match and can skip key evaluation, but DF evaluates all keys eagerly
+        // over the batch, so a computed key (e.g. a throwing cast) could error on skipped rows.
+        case ExistenceJoin(_)
+            if CometConf.COMET_EXEC_EXISTENCE_JOIN_ENABLED.get(join.conf) &&
+              join.condition.isEmpty &&
+              (join.leftKeys ++ join.rightKeys).forall {
+                case _: Attribute | _: Literal => true

Review Comment:
   fixed build / import error



-- 
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: [PR] feat: wire native existence join support [datafusion-comet]

Posted by "coderfender (via GitHub)" <gi...@apache.org>.
coderfender commented on code in PR #4587:
URL: https://github.com/apache/datafusion-comet/pull/4587#discussion_r4156453301


##########
spark/src/test/resources/sql-tests/join/existence_join.sql:
##########
@@ -0,0 +1,234 @@
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements.  See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership.  The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License.  You may obtain a copy of the License at
+--
+--   http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied.  See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+
+-- Tests for ExistenceJoin: produced when EXISTS / NOT EXISTS is combined
+-- with another predicate via OR, preventing rewrite to LeftSemi / LeftAnti.
+--
+-- Strategy hints are placed INSIDE the EXISTS subquery (on the subquery's own
+-- relation alias), because an outer hint referencing the subquery table cannot
+-- resolve it and silently falls back to a broadcast hash join. Subquery-local
+-- hints (verified on Spark 4.1.3 with AQE off) select ShuffledHashJoin /
+-- SortMergeJoin.
+--
+-- Native existence support is currently hash-only: BROADCAST and SHUFFLE_HASH
+-- cases exercise CometBroadcastHashJoinExec / CometHashJoinExec, while MERGE
+-- cases fall back to Spark's SortMergeJoin (existence SMJ is not yet native) and
+-- verify result parity under the Comet-enabled config.
+
+-- Native ExistenceJoin support is experimental and disabled by default.
+-- Config: spark.comet.exec.existenceJoin.enabled=true
+
+-- ============================================================
+-- Setup: NULLs (both sides), duplicates, empty build, all-NULL build
+-- ============================================================
+
+statement
+CREATE TABLE ex_left(id int, k int, region string) USING parquet
+
+statement
+INSERT INTO ex_left VALUES
+  (1, 1, 'US'),
+  (2, 2, 'EU'),
+  (3, NULL, 'US'),
+  (4, 4, 'EU'),
+  (5, 5, 'EU'),
+  (6, NULL, 'EU')
+
+statement
+CREATE TABLE ex_right(id int, k int) USING parquet
+
+statement
+INSERT INTO ex_right VALUES (10, 1), (11, 2), (12, 2), (13, NULL)
+
+statement
+CREATE TABLE ex_right_no_nulls(id int, k int) USING parquet
+
+statement
+INSERT INTO ex_right_no_nulls VALUES (10, 1), (11, 5)
+
+statement
+CREATE TABLE ex_right_empty(id int, k int) USING parquet
+
+statement
+CREATE TABLE ex_right_dups(id int, k int) USING parquet
+
+statement
+INSERT INTO ex_right_dups VALUES (10, 1), (11, 1), (12, 1), (13, 2)
+
+statement
+CREATE TABLE ex_right_all_null(id int, k int) USING parquet
+
+statement
+INSERT INTO ex_right_all_null VALUES (10, NULL), (11, NULL)
+
+statement
+CREATE TABLE ex_left_empty(id int, k int, region string) USING parquet
+
+-- ============================================================
+-- EXISTS with OR across all three strategies (hint in subquery)
+-- ============================================================
+
+query
+SELECT * FROM ex_left l
+WHERE l.region = 'US'
+   OR EXISTS (SELECT /*+ BROADCAST(r) */ 1 FROM ex_right r WHERE r.k = l.k)

Review Comment:
   Added all three: a direct marker projection (`SELECT l.id, EXISTS (…) FROM ex_left l`), the `l.k IN (SELECT …)` form combined with `OR`, and a string join key for both BHJ and SHJ.



-- 
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: [PR] feat: wire native existence join support [datafusion-comet]

Posted by "coderfender (via GitHub)" <gi...@apache.org>.
coderfender commented on PR #4587:
URL: https://github.com/apache/datafusion-comet/pull/4587#issuecomment-5934455473

   @andygrove filed #6514 for native existence sort-merge joins, with the blocker (output buffering in DataFusion's `BitwiseSortMergeJoinStream`, from sunchao's [finding](https://github.com/apache/datafusion-comet/pull/4587#discussion_r4042586365)) and a note that Spark picks SMJ whenever the subquery side is too big to broadcast. I'll link it from the comment in `CometSortMergeJoinExec.convert` in a follow-up so this doesn't trigger another full label run. Thanks for adding the `run-all-spark-profiles` / `run-spark-4.1-tests` labels — I'll push next so they fire.


-- 
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: [PR] feat: implement native native existence join support [datafusion-comet]

Posted by "andygrove (via GitHub)" <gi...@apache.org>.
andygrove commented on PR #4587:
URL: https://github.com/apache/datafusion-comet/pull/4587#issuecomment-5441787839

   > **Note on this review:** this was generated by an LLM (Claude Code) at my request while I worked through a review backlog. I have not verified the individual findings myself. Please treat everything below as suggestions to evaluate rather than as authoritative review feedback, and push back on anything that is wrong or already handled.
   
   Mapping Spark's `ExistenceJoin` onto DataFusion's `LeftMark` is a neat idea and the change is refreshingly small. Gating it behind a disabled-by-default config while it settles is the right call, and the `producedAttributes` overrides are the detail that would have been easy to miss.
   
   Before this can be reviewed properly, though, a few things.
   
   **The description is empty**
   
   Every section of the template is still a placeholder: no rationale, no summary of changes, no testing notes. For a change that introduces a new join type mapping across three join operators, the reviewer needs to know what you verified. In particular I would want the description to answer the semantic question below, because a reader cannot get that from the diff.
   
   **Is `LeftMark` semantically identical to `ExistenceJoin`?**
   
   Spark's `ExistenceJoin(exists)` emits every left row plus a boolean that is true when at least one right row matches the equi keys *and* the join condition. Three things I would want confirmed against DataFusion's `LeftMark`:
   
   - Does `LeftMark` evaluate the non-equi `condition` before setting the mark? Spark's `ExistenceJoin` is most often planned for `EXISTS ... OR ...` shapes, which is exactly where a non-trivial condition appears. If DataFusion marks on the key match alone and filters afterwards, the results diverge.
   - Can the mark column ever be NULL? Spark's `exists` attribute is `nullable = false`. If `LeftMark` can produce a null, downstream expressions will behave differently.
   - Where does the mark land in the output schema, and does that match `left.output :+ exists`?
   
   A short section in the description walking through those, ideally with a DataFusion source link, would make this reviewable.
   
   **`BroadcastNestedLoopJoinExec` is not covered**
   
   `ExistenceJoin` is added to `CometHashJoin` and `CometSortMergeJoinExec`, but Spark frequently plans an existence join as a broadcast nested loop join when there are no equi-join keys, which is the common shape for `WHERE EXISTS (...) OR x > 1`. Is that deliberately out of scope? If so, saying so would help; if not, those queries will silently keep falling back and the feature will look like it does not work.
   
   **Benchmark results**
   
   `CometExistenceJoinBenchmark` is added but the description has no numbers. Since the motivation for a native existence join is presumably speed, what does it buy over the fallback?
   
   **One CI note**
   
   There is a failing check on this PR. Worth looking at before the next round of review.
   


-- 
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: [PR] feat: wire native existence join support [datafusion-comet]

Posted by "sunchao (via GitHub)" <gi...@apache.org>.
sunchao commented on code in PR #4587:
URL: https://github.com/apache/datafusion-comet/pull/4587#discussion_r4104655922


##########
spark/src/main/scala/org/apache/spark/sql/comet/operators.scala:
##########
@@ -2488,6 +2488,17 @@ trait CometHashJoin {
         case FullOuter => JoinType.FullOuter
         case LeftSemi => JoinType.LeftSemi
         case LeftAnti => JoinType.LeftAnti
+        // Native only for equi-join keys that are bare column refs / literals: Spark short-
+        // circuits on first match and can skip key evaluation, but DF evaluates all keys eagerly
+        // over the batch, so a computed key (e.g. a throwing cast) could error on skipped rows.
+        case ExistenceJoin(_)
+            if CometConf.COMET_EXEC_EXISTENCE_JOIN_ENABLED.get(join.conf) &&
+              join.condition.isEmpty &&
+              (join.leftKeys ++ join.rightKeys).forall {
+                case _: Attribute | _: Literal => true

Review Comment:
   [P1] Import `Literal` before using it in the new guard. The explicit Catalyst expression imports omit `org.apache.spark.sql.catalyst.expressions.Literal`, so this pattern fails compilation with `not found: type Literal`. This blocks the JVM build and tests even when existence joins are disabled. Add the missing import or fully qualify the type.
   
   Evidence: Exact-head CI reproduced this with `./mvnw -B install -DskipTests -Dmaven.test.skip=true -Pspark-4.1`: operators.scala:2498 reports `not found: type Literal`. The Spark 3.4 compilation and Spark 4.1 execution jobs report the same error. Build log: https://github.com/apache/datafusion-comet/actions/runs/36071677324/job/107874995590



##########
spark/src/test/resources/sql-tests/join/existence_join.sql:
##########
@@ -0,0 +1,234 @@
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements.  See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership.  The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License.  You may obtain a copy of the License at
+--
+--   http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied.  See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+
+-- Tests for ExistenceJoin: produced when EXISTS / NOT EXISTS is combined
+-- with another predicate via OR, preventing rewrite to LeftSemi / LeftAnti.
+--
+-- Strategy hints are placed INSIDE the EXISTS subquery (on the subquery's own
+-- relation alias), because an outer hint referencing the subquery table cannot
+-- resolve it and silently falls back to a broadcast hash join. Subquery-local
+-- hints (verified on Spark 4.1.3 with AQE off) select ShuffledHashJoin /
+-- SortMergeJoin.
+--
+-- Native existence support is currently hash-only: BROADCAST and SHUFFLE_HASH
+-- cases exercise CometBroadcastHashJoinExec / CometHashJoinExec, while MERGE
+-- cases fall back to Spark's SortMergeJoin (existence SMJ is not yet native) and
+-- verify result parity under the Comet-enabled config.
+
+-- Native ExistenceJoin support is experimental and disabled by default.
+-- Config: spark.comet.exec.existenceJoin.enabled=true
+
+-- ============================================================
+-- Setup: NULLs (both sides), duplicates, empty build, all-NULL build
+-- ============================================================
+
+statement
+CREATE TABLE ex_left(id int, k int, region string) USING parquet
+
+statement
+INSERT INTO ex_left VALUES
+  (1, 1, 'US'),
+  (2, 2, 'EU'),
+  (3, NULL, 'US'),
+  (4, 4, 'EU'),
+  (5, 5, 'EU'),
+  (6, NULL, 'EU')
+
+statement
+CREATE TABLE ex_right(id int, k int) USING parquet
+
+statement
+INSERT INTO ex_right VALUES (10, 1), (11, 2), (12, 2), (13, NULL)
+
+statement
+CREATE TABLE ex_right_no_nulls(id int, k int) USING parquet
+
+statement
+INSERT INTO ex_right_no_nulls VALUES (10, 1), (11, 5)
+
+statement
+CREATE TABLE ex_right_empty(id int, k int) USING parquet
+
+statement
+CREATE TABLE ex_right_dups(id int, k int) USING parquet
+
+statement
+INSERT INTO ex_right_dups VALUES (10, 1), (11, 1), (12, 1), (13, 2)
+
+statement
+CREATE TABLE ex_right_all_null(id int, k int) USING parquet
+
+statement
+INSERT INTO ex_right_all_null VALUES (10, NULL), (11, NULL)
+
+statement
+CREATE TABLE ex_left_empty(id int, k int, region string) USING parquet
+
+-- ============================================================
+-- EXISTS with OR across all three strategies (hint in subquery)
+-- ============================================================
+
+query
+SELECT * FROM ex_left l
+WHERE l.region = 'US'
+   OR EXISTS (SELECT /*+ BROADCAST(r) */ 1 FROM ex_right r WHERE r.k = l.k)
+ORDER BY l.id
+
+query
+SELECT * FROM ex_left l
+WHERE l.region = 'US'
+   OR EXISTS (SELECT /*+ SHUFFLE_HASH(r) */ 1 FROM ex_right r WHERE r.k = l.k)
+ORDER BY l.id
+
+query

Review Comment:
   [P2] Use an expected-fallback directive for the `MERGE` cases. Plain `query` calls `checkSparkAnswerAndOperator`, which requires native execution, but these hints select an existence `SortMergeJoin` that this PR deliberately leaves in Spark. Once compilation is fixed, this case fails the native-operator assertion and stops the fixture despite correct results. Change the sort-merge cases to `query expect_fallback(Unsupported join type)` so they verify the intended behavior.
   
   Evidence: Fresh Spark 4.1.3 probes of this exact query returned ids [1, 2, 3] and selected `SortMergeJoin ... ExistenceJoin` with AQE both enabled and disabled. At this head, CometSortMergeJoinExec.convert rejects that join type at operators.scala:3029. CometSqlFileTestSuite.scala:155–156 routes plain queries to the native assertion, and CometTestBase.scala:619–626 rejects remaining Spark operators. This is a source-confirmed assertion conflict; the full Comet fixture could not run because of the compilation failure.



-- 
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: [PR] feat: wire native existence join support [datafusion-comet]

Posted by "coderfender (via GitHub)" <gi...@apache.org>.
coderfender commented on code in PR #4587:
URL: https://github.com/apache/datafusion-comet/pull/4587#discussion_r4156452671


##########
spark/src/test/scala/org/apache/comet/exec/CometJoinSuite.scala:
##########
@@ -1572,4 +1572,132 @@ class CometJoinSuite extends CometTestBase {
       }
     }
   }
+
+  test("ExistenceJoin via BroadcastHashJoin (EXISTS combined with OR)") {
+    withSQLConf(
+      CometConf.COMET_EXEC_EXISTENCE_JOIN_ENABLED.key -> "true",
+      SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "10MB",
+      SQLConf.ADAPTIVE_AUTO_BROADCASTJOIN_THRESHOLD.key -> "10MB") {
+      withParquetTable((0 until 100).map(i => (i, if (i % 3 == 0) "US" else "EU")), "tbl_a") {
+        withParquetTable((0 until 30).map(i => (i, i + 1)), "tbl_b") {
+          val df = sql("SELECT * FROM tbl_a a " +
+            "WHERE a._2 = 'US' OR EXISTS (SELECT /*+ BROADCAST(b) */ 1 FROM tbl_b b WHERE b._1 = a._1)")
+          checkSparkAnswerAndOperator(
+            df,
+            Seq(classOf[CometBroadcastExchangeExec], classOf[CometBroadcastHashJoinExec]))
+        }
+      }
+    }
+  }
+
+  test("ExistenceJoin via ShuffledHashJoin (EXISTS combined with OR)") {
+    withSQLConf(
+      CometConf.COMET_EXEC_EXISTENCE_JOIN_ENABLED.key -> "true",
+      SQLConf.PREFER_SORTMERGEJOIN.key -> "false",
+      "spark.sql.join.forceApplyShuffledHashJoin" -> "true",
+      SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+      SQLConf.ADAPTIVE_AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1") {
+      withParquetTable((0 until 100).map(i => (i, if (i % 3 == 0) "US" else "EU")), "tbl_a") {
+        withParquetTable((0 until 30).map(i => (i, i + 1)), "tbl_b") {
+          val df = sql(
+            "SELECT * FROM tbl_a a " +
+              "WHERE a._2 = 'US' OR EXISTS (SELECT 1 FROM tbl_b b WHERE b._1 = a._1)")
+          checkSparkAnswerAndOperator(df, Seq(classOf[CometHashJoinExec]))
+        }
+      }
+    }
+  }
+
+  test("ExistenceJoin via SortMergeJoin falls back to Spark") {

Review Comment:
   Done. The residual and computed-key tests now use `checkSparkAnswerAndFallbackReason` with distinct reasons (`residual (non-equi)` and `computed (non-column)`), and the flag-off case now reports its own `Native ExistenceJoin is disabled; set …=true` reason instead of the generic "Unsupported join type", so each guard is distinguishable. One note: AQE is disabled in the two broadcast fallback tests because a declined broadcast join loses its fallback reason from the AQE-final plan; I filed #6442 to fix that visibility gap properly.



##########
spark/src/main/scala/org/apache/spark/sql/comet/operators.scala:
##########
@@ -3003,6 +3024,8 @@ object CometSortMergeJoinExec extends CometOperatorSerde[SortMergeJoinExec] {
         case FullOuter => JoinType.FullOuter
         case LeftSemi => JoinType.LeftSemi
         case LeftAnti => JoinType.LeftAnti
+        // Existence SMJ falls back to Spark: DF 55.1.0's BitwiseSortMergeJoin buffers output

Review Comment:
   `RewriteJoin` excludes `ExistenceJoin` with `BuildRight` from the forced-SHJ rewrite (alongside `LeftSemi`, citing #2697), so existence SMJs stay on Spark. I kept that exclusion rather than enabling it, since #2697 is still open, and added a test (`ExistenceJoin via SortMergeJoin stays on Spark even with forceShuffledHashJoin`) asserting the join is not rewritten into a native hash join.



##########
spark/src/test/resources/sql-tests/join/existence_join.sql:
##########
@@ -0,0 +1,234 @@
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements.  See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership.  The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License.  You may obtain a copy of the License at
+--
+--   http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied.  See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+
+-- Tests for ExistenceJoin: produced when EXISTS / NOT EXISTS is combined
+-- with another predicate via OR, preventing rewrite to LeftSemi / LeftAnti.
+--
+-- Strategy hints are placed INSIDE the EXISTS subquery (on the subquery's own
+-- relation alias), because an outer hint referencing the subquery table cannot
+-- resolve it and silently falls back to a broadcast hash join. Subquery-local
+-- hints (verified on Spark 4.1.3 with AQE off) select ShuffledHashJoin /
+-- SortMergeJoin.
+--
+-- Native existence support is currently hash-only: BROADCAST and SHUFFLE_HASH
+-- cases exercise CometBroadcastHashJoinExec / CometHashJoinExec, while MERGE
+-- cases fall back to Spark's SortMergeJoin (existence SMJ is not yet native) and
+-- verify result parity under the Comet-enabled config.
+
+-- Native ExistenceJoin support is experimental and disabled by default.
+-- Config: spark.comet.exec.existenceJoin.enabled=true
+
+-- ============================================================
+-- Setup: NULLs (both sides), duplicates, empty build, all-NULL build
+-- ============================================================
+
+statement
+CREATE TABLE ex_left(id int, k int, region string) USING parquet
+
+statement
+INSERT INTO ex_left VALUES
+  (1, 1, 'US'),
+  (2, 2, 'EU'),
+  (3, NULL, 'US'),
+  (4, 4, 'EU'),
+  (5, 5, 'EU'),
+  (6, NULL, 'EU')
+
+statement
+CREATE TABLE ex_right(id int, k int) USING parquet
+
+statement
+INSERT INTO ex_right VALUES (10, 1), (11, 2), (12, 2), (13, NULL)
+
+statement
+CREATE TABLE ex_right_no_nulls(id int, k int) USING parquet
+
+statement
+INSERT INTO ex_right_no_nulls VALUES (10, 1), (11, 5)
+
+statement
+CREATE TABLE ex_right_empty(id int, k int) USING parquet
+
+statement
+CREATE TABLE ex_right_dups(id int, k int) USING parquet
+
+statement
+INSERT INTO ex_right_dups VALUES (10, 1), (11, 1), (12, 1), (13, 2)
+
+statement
+CREATE TABLE ex_right_all_null(id int, k int) USING parquet
+
+statement
+INSERT INTO ex_right_all_null VALUES (10, NULL), (11, NULL)
+
+statement
+CREATE TABLE ex_left_empty(id int, k int, region string) USING parquet
+
+-- ============================================================
+-- EXISTS with OR across all three strategies (hint in subquery)
+-- ============================================================
+
+query
+SELECT * FROM ex_left l
+WHERE l.region = 'US'
+   OR EXISTS (SELECT /*+ BROADCAST(r) */ 1 FROM ex_right r WHERE r.k = l.k)

Review Comment:
   Added all three: a direct marker projection (`SELECT l.id, EXISTS (…) FROM ex_left l`), the `l.k IN (SELECT …)` form combined with `OR`, and a string join key — across BHJ and SHJ.



##########
spark/src/main/scala/org/apache/comet/CometConf.scala:
##########
@@ -235,6 +235,11 @@ object CometConf extends ShimCometConf {
     createExecEnabledConfig("broadcastNestedLoopJoin", defaultValue = true)
   val COMET_EXEC_SORT_MERGE_JOIN_ENABLED: ConfigEntry[Boolean] =
     createExecEnabledConfig("sortMergeJoin", defaultValue = true)
+  val COMET_EXEC_EXISTENCE_JOIN_ENABLED: ConfigEntry[Boolean] =
+    createExecEnabledConfig(
+      "existenceJoin",
+      defaultValue = true,
+      notes = Some("Enables native ExistenceJoin (EXISTS/NOT EXISTS/IN combined with OR)."))

Review Comment:
   Switched `existenceJoin.enabled` to a plain `conf(...)` (matching the `sortMergeJoinWithJoinFilter` precedent) with a doc that lists what still falls back (sort-merge joins, residual conditions, computed keys, `NOT IN`); the double period is gone. Added the same note to the joins table in `operators.md`.



-- 
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: [PR] feat: wire native existence join support [datafusion-comet]

Posted by "andygrove (via GitHub)" <gi...@apache.org>.
andygrove commented on PR #4587:
URL: https://github.com/apache/datafusion-comet/pull/4587#issuecomment-5934436514

   > I created and tagged a new issue to support Existence join with SMJ use case
   
   Could you link it from the comment in CometSortMergeJoinExec.convert?


-- 
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: [PR] feat: wire native existence join support [datafusion-comet]

Posted by "coderfender (via GitHub)" <gi...@apache.org>.
coderfender commented on PR #4587:
URL: https://github.com/apache/datafusion-comet/pull/4587#issuecomment-5723733164

   Thank you for the review. Let me take a look and address the feedback soon


-- 
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: [PR] feat: wire native existence join support [datafusion-comet]

Posted by "coderfender (via GitHub)" <gi...@apache.org>.
coderfender commented on PR #4587:
URL: https://github.com/apache/datafusion-comet/pull/4587#issuecomment-5933361410

   @andygrove thanks for the review — addressed the top-level items:
   
   - **Golden files:** regenerated with `./dev/regenerate-golden-files.sh`; q10/q35/q45 (v1.4) and q35 (v2.7) now run the existence join natively (no more `[COMET: Unsupported join type ExistenceJoin(...)]`).
   - **`CometEmptyRelationExecSuite`:** updated the AQE test to expect the native `CometBroadcastHashJoinExec` over the `CometEmptyRelation` instead of asserting Spark fallback.
   - **PR description:** corrected — the flag now defaults to `true`, and `CometSortMergeJoinExec` is **not** wired (existence SMJ falls back to Spark). Updated the test/fallback summary and linked the two follow-ups (#6442, #6484).
   
   The per-comment replies above cover the inline threads (distinct fallback reasons, the `forceShuffledHashJoin` exclusion + test, the direct-marker/`IN`/string-key SQL coverage, and the `CometConf` cleanup).


-- 
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: [PR] feat: wire native existence join support [datafusion-comet]

Posted by "coderfender (via GitHub)" <gi...@apache.org>.
coderfender commented on PR #4587:
URL: https://github.com/apache/datafusion-comet/pull/4587#issuecomment-5934326709

   I created and tagged a new issue to support Existence join with SMJ use case


-- 
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: [PR] feat: wire native existence join support [datafusion-comet]

Posted by "coderfender (via GitHub)" <gi...@apache.org>.
coderfender commented on PR #4587:
URL: https://github.com/apache/datafusion-comet/pull/4587#issuecomment-5823899154

   @comphead  , the default config is to disable this join which explains why golden files aren't changed. Do you think we should enable it by default and let the code fallback or keep it experimental and OFF by default ?


-- 
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: [PR] feat: wire native existence join support [datafusion-comet]

Posted by "comphead (via GitHub)" <gi...@apache.org>.
comphead commented on PR #4587:
URL: https://github.com/apache/datafusion-comet/pull/4587#issuecomment-5835336516

   > @comphead , the default config is to disable this join which explains why golden files aren't changed. Do you think we should enable it by default and let the code fallback or keep it experimental and OFF by default ?
   
   I dont see reasons of not enabling them, @andygrove @viirya WDYT?


-- 
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: [PR] feat: wire native existence join support [datafusion-comet]

Posted by "coderfender (via GitHub)" <gi...@apache.org>.
coderfender commented on PR #4587:
URL: https://github.com/apache/datafusion-comet/pull/4587#issuecomment-5936069959

   Take a look in a moment and also investigating the CI failures


-- 
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: [PR] feat: wire native existence join support [datafusion-comet]

Posted by "sunchao (via GitHub)" <gi...@apache.org>.
sunchao commented on code in PR #4587:
URL: https://github.com/apache/datafusion-comet/pull/4587#discussion_r4042586357


##########
spark/src/main/scala/org/apache/spark/sql/comet/operators.scala:
##########
@@ -2428,6 +2428,8 @@ trait CometHashJoin {
         case FullOuter => JoinType.FullOuter
         case LeftSemi => JoinType.LeftSemi
         case LeftAnti => JoinType.LeftAnti
+        case ExistenceJoin(_) if CometConf.COMET_EXEC_EXISTENCE_JOIN_ENABLED.get(join.conf) =>

Review Comment:
   [P2] Preserve first-match evaluation for residual predicates
   
   Could we keep hash existence joins with residual predicates in Spark until this evaluation difference is handled? Spark stops after the first qualifying candidate, but DataFusion 55.1 evaluates the hash-join filter on the candidate batch before reducing it to a marker. With equal right-hand keys, a successful candidate can therefore be followed by a candidate that throws, although Spark never evaluates it.
   
   For example, I ran Spark 4.1.3 with a single-file left input `(id=1, vals=[1.0], threshold=-0.0)` and right rows `(id=1, idx=0), (id=1, idx=1)`. An `EXISTS` correlation on `r.id=l.id AND element_at(l.vals,r.idx)>l.threshold` returns `true`: Spark visits index 1 first and stops. The native candidate-batch evaluation reaches index 0, which Comet's `ListExtract` rejects even with ANSI disabled.
   
   There is also a wrong-marker case for the residual `r.value > l.value` with equal integer keys, `r.value=+0.0`, and `l.value=-0.0`. Spark returns false, while the pinned Arrow 59.3 comparison returns true. `normalizePlan` only rewrites project/filter expressions, so it does not normalize this join condition.
   
   These checks used fresh Spark/Arrow probes and the pinned DataFusion source, not an end-to-end run of this Comet head.



##########
spark/src/main/scala/org/apache/spark/sql/comet/operators.scala:
##########
@@ -2943,6 +2955,8 @@ object CometSortMergeJoinExec extends CometOperatorSerde[SortMergeJoinExec] {
         case FullOuter => JoinType.FullOuter
         case LeftSemi => JoinType.LeftSemi
         case LeftAnti => JoinType.LeftAnti
+        case ExistenceJoin(_) if CometConf.COMET_EXEC_EXISTENCE_JOIN_ENABLED.get(join.conf) =>

Review Comment:
   [P2] Keep existence SMJ behind fallback until output retention is bounded
   
   Could we leave existence sort-merge joins in Spark until the pinned DataFusion implementation yields completed output inside its spanning-key and exhausted-inner loops? In DataFusion 55.1, `BitwiseSortMergeJoinStream::process_unfiltered_match_loop` and `drain_outer` repeatedly push outer batches into `BatchCoalescer`, but `emit_completed_batches` only runs after those loops return. The reservation tracks the inner key buffer, not that queued output.
   
   Consequently, a large equal-key group or an empty right input can retain an entire left partition outside the configured memory budget before returning its first output batch. That can cause executor OOM even though the join reports little or no reserved memory.
   
   I checked this control flow in the exact 55.1 source matching `Cargo.lock`. A separate cached 54.1 probe corroborated the pattern: all 102,400 outer rows were consumed before the first 1,024-row result with a one-byte pool and zero reported reservation. That cached execution is not a run of the current dependency. A regression should verify incremental output and bounded retained memory for both empty-right and skewed-key inputs.



##########
spark/src/main/scala/org/apache/spark/sql/comet/operators.scala:
##########
@@ -2428,6 +2428,8 @@ trait CometHashJoin {
         case FullOuter => JoinType.FullOuter
         case LeftSemi => JoinType.LeftSemi
         case LeftAnti => JoinType.LeftAnti
+        case ExistenceJoin(_) if CometConf.COMET_EXEC_EXISTENCE_JOIN_ENABLED.get(join.conf) =>
+          JoinType.Existence

Review Comment:
   [P2] Check computed-key evaluation safety on both inputs
   
   Could we add a recursive safety check for existence join keys, beyond checking whether each expression can be serialized? Spark packs pairs of integer keys and can skip the second expression when the first is NULL, whereas the native join evaluates the key expressions independently over the batch.
   
   With ANSI enabled, left rows `(id=1, prefix=NULL, txt='bad'), (id=2, prefix=1, txt='1')`, and right row `(a=1,b=1)`, Spark 4.1.3 returns `(1,false),(2,true)` for `EXISTS (... WHERE r.a=l.prefix AND r.b=CAST(l.txt AS INT))`. Native key evaluation reaches the invalid cast on the first row. Similarly, `LIMIT 1` can stop Spark after a valid probe row while a native key batch still evaluates a later invalid row.
   
   A computed integer key can also conceal a floating comparison: `CASE WHEN x < 0.0 THEN 1 ELSE 2 END` yields 2 in Spark for `x=-0.0`, but the pinned Arrow comparison selects 1. Normalizing the final floating join key does not help because this key is an integer. The guard needs to inspect descendants on both inputs and retain native execution for expressions safe under batch evaluation.
   
   The Spark baseline and Arrow 59.3 comparison were executed locally. The current native key path was source-checked; full Comet integration was not run.



##########
spark/src/test/scala/org/apache/spark/sql/benchmark/CometExistenceJoinBenchmark.scala:
##########
@@ -0,0 +1,123 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+package org.apache.spark.sql.benchmark
+
+import org.apache.spark.SparkConf
+import org.apache.spark.sql.SparkSession
+import org.apache.spark.sql.internal.SQLConf
+
+import org.apache.comet.{CometConf, CometSparkSessionExtensions}
+
+/**
+ * Benchmark to measure performance of Comet's ExistenceJoin support across the three join
+ * physical operators (BHJ, SHJ, SMJ). To run this benchmark:
+ * {{{
+ *   SPARK_GENERATE_BENCHMARK_FILES=1 make benchmark-org.apache.spark.sql.benchmark.CometExistenceJoinBenchmark
+ * }}}
+ * Results will be written to "spark/benchmarks/CometExistenceJoinBenchmark-**results.txt".
+ */
+object CometExistenceJoinBenchmark extends CometBenchmarkBase {
+
+  override def getSparkSession: SparkSession = {
+    val conf = new SparkConf()
+      .setAppName("CometExistenceJoinBenchmark")
+      .set("spark.master", "local[5]")
+      .setIfMissing("spark.driver.memory", "3g")
+      .setIfMissing("spark.executor.memory", "3g")
+      .set(
+        "spark.shuffle.manager",
+        "org.apache.spark.sql.comet.execution.shuffle.CometShuffleManager")
+
+    val sparkSession = SparkSession.builder
+      .config(conf)
+      .withExtensions(new CometSparkSessionExtensions)
+      .getOrCreate()
+
+    sparkSession.conf.set(CometConf.COMET_ENABLED.key, "false")
+    sparkSession.conf.set(CometConf.COMET_EXEC_ENABLED.key, "false")
+    sparkSession.conf.set(SQLConf.ANSI_ENABLED.key, "false")
+    sparkSession.conf.set("spark.sql.shuffle.partitions", "2")
+
+    sparkSession
+  }
+
+  override def runCometBenchmark(mainArgs: Array[String]): Unit = {
+    val probeRows = 1024 * 1024
+    val buildRows = 10000
+
+    withTempPath { dir =>
+      withTempTable("probe", "build") {
+        spark
+          .range(probeRows)
+          .selectExpr("id AS k", "CASE WHEN id % 3 = 0 THEN 'US' ELSE 'EU' END AS region")
+          .write
+          .parquet(s"${dir.getAbsolutePath}/probe")
+        spark
+          .range(buildRows)
+          .selectExpr("id * 7 AS k")
+          .write
+          .parquet(s"${dir.getAbsolutePath}/build")
+
+        spark.read.parquet(s"${dir.getAbsolutePath}/probe").createOrReplaceTempView("probe")
+        spark.read.parquet(s"${dir.getAbsolutePath}/build").createOrReplaceTempView("build")
+
+        val query =
+          "SELECT count(*) FROM probe p " +
+            "WHERE p.region = 'US' OR EXISTS (SELECT 1 FROM build b WHERE b.k = p.k)"
+
+        runBenchmark("ExistenceJoin - BroadcastHashJoin") {
+          runExpressionBenchmark(
+            "exists OR predicate (BHJ)",
+            probeRows,
+            query,
+            Map(
+              CometConf.COMET_EXEC_EXISTENCE_JOIN_ENABLED.key -> "true",
+              SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "10MB",
+              SQLConf.ADAPTIVE_AUTO_BROADCASTJOIN_THRESHOLD.key -> "10MB"))
+        }
+
+        runBenchmark("ExistenceJoin - ShuffledHashJoin") {
+          runExpressionBenchmark(
+            "exists OR predicate (SHJ)",
+            probeRows,
+            query,
+            Map(
+              CometConf.COMET_EXEC_EXISTENCE_JOIN_ENABLED.key -> "true",
+              SQLConf.PREFER_SORTMERGEJOIN.key -> "false",

Review Comment:
   [P2] Apply benchmark strategy settings to the Spark baseline too
   
   This is the same benchmark mismatch noted in the [existing discussion](https://github.com/apache/datafusion-comet/pull/4587#issuecomment-5723010195), anchored at the config map that causes it. `runExpressionBenchmark` treats this map as `extraCometConfigs`, so the SHJ/SMJ planning settings apply only to Comet. The Spark arm retains normal broadcast planning for the small build table, and the reported SHJ/SMJ ratios compare different strategies between engines.
   
   Could we wrap each case in shared `withSQLConf` strategy settings, leaving the Comet feature flag in `extraCometConfigs`, and assert the actual join class for both arms? Subquery-local hints are also viable on Spark 4.1.3, as verified in the SQL-fixture comment. This would make the benchmark labels and performance comparisons reflect the strategy being measured.



##########
spark/src/test/resources/sql-tests/join/existence_join.sql:
##########
@@ -0,0 +1,167 @@
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements.  See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership.  The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License.  You may obtain a copy of the License at
+--
+--   http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied.  See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+
+-- Tests for ExistenceJoin: produced when EXISTS / NOT EXISTS is combined
+-- with another predicate via OR, preventing rewrite to LeftSemi / LeftAnti.
+-- Each query runs against the three physical join strategies (BHJ, SHJ,
+-- SMJ) via hints, so we exercise CometBroadcastHashJoinExec,
+-- CometHashJoinExec, and CometSortMergeJoinExec all carrying joinType =
+-- ExistenceJoin.
+
+-- Native ExistenceJoin support is experimental and disabled by default.
+-- Config: spark.comet.exec.existenceJoin.enabled=true
+
+-- ============================================================
+-- Setup: covers NULLs, duplicates, empty build side
+-- ============================================================
+
+statement
+CREATE TABLE ex_left(id int, k int, region string) USING parquet
+
+statement
+INSERT INTO ex_left VALUES
+  (1, 1, 'US'),
+  (2, 2, 'EU'),
+  (3, NULL, 'US'),
+  (4, 4, 'EU'),
+  (5, 5, 'EU')
+
+statement
+CREATE TABLE ex_right(id int, k int) USING parquet
+
+statement
+INSERT INTO ex_right VALUES (10, 1), (11, 2), (12, 2), (13, NULL)
+
+statement
+CREATE TABLE ex_right_no_nulls(id int, k int) USING parquet
+
+statement
+INSERT INTO ex_right_no_nulls VALUES (10, 1), (11, 5)
+
+statement
+CREATE TABLE ex_right_empty(id int, k int) USING parquet
+
+statement
+CREATE TABLE ex_right_dups(id int, k int) USING parquet
+
+statement
+INSERT INTO ex_right_dups VALUES (10, 1), (11, 1), (12, 1), (13, 2)
+
+-- ============================================================
+-- EXISTS with OR: BHJ build-right
+-- ============================================================
+
+query
+SELECT /*+ BROADCAST(ex_right) */ * FROM ex_left l
+WHERE l.region = 'US' OR EXISTS (SELECT 1 FROM ex_right r WHERE r.k = l.k)
+ORDER BY l.id
+
+-- ============================================================
+-- EXISTS with OR: SHJ build-right
+-- ============================================================
+
+query
+SELECT /*+ SHUFFLE_HASH(ex_right) */ * FROM ex_left l

Review Comment:
   [P2] Place strategy hints in the subquery that owns the relation
   
   Following up on the [fixture concern already raised](https://github.com/apache/datafusion-comet/pull/4587#issuecomment-5723010195): I confirmed on Spark 4.1.3 with AQE disabled that the outer `SHUFFLE_HASH(ex_right)` and `MERGE(ex_right)` hints cannot resolve `ex_right`, emit warnings, and both produce a broadcast hash join.
   
   One correction to that discussion: putting the hint inside `EXISTS` does work in this version. `EXISTS (SELECT /*+ SHUFFLE_HASH(r) */ 1 FROM ex_right r WHERE r.k=l.k)` selects `ShuffledHashJoin`, and the corresponding `MERGE(r)` selects `SortMergeJoin` in the fresh plan probes.
   
   Could we move the hints into their subqueries and assert the selected physical strategy, or use a configuration matrix that enforces the same coverage? The current fixture does not establish null/duplicate/empty-input coverage across the named strategies. The separate Scala tests do cover all three operators for their simple unique, non-null integer case.



##########
native/core/src/execution/planner.rs:
##########
@@ -2699,6 +2699,7 @@ impl PhysicalPlanner {
             Ok(JoinType::FullOuter) => DFJoinType::Full,
             Ok(JoinType::LeftSemi) => DFJoinType::LeftSemi,
             Ok(JoinType::LeftAnti) => DFJoinType::LeftAnti,
+            Ok(JoinType::Existence) => DFJoinType::LeftMark,

Review Comment:
   [P2] Avoid enumerating every duplicate build match
   
   For Spark's `BuildRight`, the later input swap turns this `LeftMark` into `RightMark`. The pinned DataFusion 55.1 hash lookup enumerates every equal-key candidate pair before the RightMark path reduces duplicate probe indices to Boolean markers. With N identical build keys and M matching probe rows, that means N*M candidate matches for M output rows, rather than Spark's existence lookup stopping after a match.
   
   Could we deduplicate the evaluated build-key tuples for residual-free existence joins, or use a mark-join implementation that stops after the first match? For example, 10,000 identical build keys and 1,000 matching probes imply 10 million candidates even though only 1,000 markers are needed. The new benchmark's distinct build keys do not exercise this case.
   
   I verified that Spark 4.1.3 preserves duplicate build rows in this plan without inserting a distinct aggregate, and checked the duplicate-chain traversal in the exact DataFusion 55.1 source. Any deduplication helper should account for its memory and support bounded spilling. It must not discard distinct right payloads while residual predicates can still use them. No current-version performance timing is claimed here.



-- 
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: [PR] feat: implement native native existence join support [datafusion-comet]

Posted by "andygrove (via GitHub)" <gi...@apache.org>.
andygrove commented on PR #4587:
URL: https://github.com/apache/datafusion-comet/pull/4587#issuecomment-5070902159

   Thanks @coderfender. The posted benchmarks show that this would be a performance regression compared to Spark? Did performance improve since those were posted?


-- 
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: [PR] feat: wire native existence join support [datafusion-comet]

Posted by "andygrove (via GitHub)" <gi...@apache.org>.
andygrove commented on code in PR #4587:
URL: https://github.com/apache/datafusion-comet/pull/4587#discussion_r4107051685


##########
spark/src/main/scala/org/apache/spark/sql/comet/operators.scala:
##########
@@ -3003,6 +3024,8 @@ object CometSortMergeJoinExec extends CometOperatorSerde[SortMergeJoinExec] {
         case FullOuter => JoinType.FullOuter
         case LeftSemi => JoinType.LeftSemi
         case LeftAnti => JoinType.LeftAnti
+        // Existence SMJ falls back to Spark: DF 55.1.0's BitwiseSortMergeJoin buffers output

Review Comment:
   This says existence SMJs fall back, but with `spark.comet.exec.forceShuffledHashJoin=true` they don't. `RewriteJoin` turns them into a `ShuffledHashJoinExec` with `BuildRight`, which now runs natively. I confirmed that with AQE on and off, and the answers matched on a small table. `RewriteJoin` skips that rewrite for `LeftSemi` with `BuildRight` because of the q69 wrong results in #2667, and #2697 is still open. Should `ExistenceJoin` get the same exclusion for now? If you think it's safe, could you add a test with that config on?



##########
spark/src/test/resources/sql-tests/join/existence_join.sql:
##########
@@ -0,0 +1,234 @@
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements.  See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership.  The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License.  You may obtain a copy of the License at
+--
+--   http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied.  See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+
+-- Tests for ExistenceJoin: produced when EXISTS / NOT EXISTS is combined
+-- with another predicate via OR, preventing rewrite to LeftSemi / LeftAnti.
+--
+-- Strategy hints are placed INSIDE the EXISTS subquery (on the subquery's own
+-- relation alias), because an outer hint referencing the subquery table cannot
+-- resolve it and silently falls back to a broadcast hash join. Subquery-local
+-- hints (verified on Spark 4.1.3 with AQE off) select ShuffledHashJoin /
+-- SortMergeJoin.
+--
+-- Native existence support is currently hash-only: BROADCAST and SHUFFLE_HASH
+-- cases exercise CometBroadcastHashJoinExec / CometHashJoinExec, while MERGE
+-- cases fall back to Spark's SortMergeJoin (existence SMJ is not yet native) and
+-- verify result parity under the Comet-enabled config.
+
+-- Native ExistenceJoin support is experimental and disabled by default.
+-- Config: spark.comet.exec.existenceJoin.enabled=true
+
+-- ============================================================
+-- Setup: NULLs (both sides), duplicates, empty build, all-NULL build
+-- ============================================================
+
+statement
+CREATE TABLE ex_left(id int, k int, region string) USING parquet
+
+statement
+INSERT INTO ex_left VALUES
+  (1, 1, 'US'),
+  (2, 2, 'EU'),
+  (3, NULL, 'US'),
+  (4, 4, 'EU'),
+  (5, 5, 'EU'),
+  (6, NULL, 'EU')
+
+statement
+CREATE TABLE ex_right(id int, k int) USING parquet
+
+statement
+INSERT INTO ex_right VALUES (10, 1), (11, 2), (12, 2), (13, NULL)
+
+statement
+CREATE TABLE ex_right_no_nulls(id int, k int) USING parquet
+
+statement
+INSERT INTO ex_right_no_nulls VALUES (10, 1), (11, 5)
+
+statement
+CREATE TABLE ex_right_empty(id int, k int) USING parquet
+
+statement
+CREATE TABLE ex_right_dups(id int, k int) USING parquet
+
+statement
+INSERT INTO ex_right_dups VALUES (10, 1), (11, 1), (12, 1), (13, 2)
+
+statement
+CREATE TABLE ex_right_all_null(id int, k int) USING parquet
+
+statement
+INSERT INTO ex_right_all_null VALUES (10, NULL), (11, NULL)
+
+statement
+CREATE TABLE ex_left_empty(id int, k int, region string) USING parquet
+
+-- ============================================================
+-- EXISTS with OR across all three strategies (hint in subquery)
+-- ============================================================
+
+query
+SELECT * FROM ex_left l
+WHERE l.region = 'US'
+   OR EXISTS (SELECT /*+ BROADCAST(r) */ 1 FROM ex_right r WHERE r.k = l.k)

Review Comment:
   Every query here reads the marker through `OR`, so rows where the other side is true pass whatever the marker says. Could we add cases that select it directly, such as `SELECT l.id, EXISTS (SELECT 1 FROM ex_right r WHERE r.k = l.k) FROM ex_left l` and the `l.k IN (SELECT r.k ...)` form, plus a string key? `IN` has no coverage yet even though the config doc lists it. I ran those shapes on BHJ and SHJ with nulls, duplicates and an empty build side, and they matched Spark, so this just locks that in.



##########
spark/src/main/scala/org/apache/comet/CometConf.scala:
##########
@@ -235,6 +235,11 @@ object CometConf extends ShimCometConf {
     createExecEnabledConfig("broadcastNestedLoopJoin", defaultValue = true)
   val COMET_EXEC_SORT_MERGE_JOIN_ENABLED: ConfigEntry[Boolean] =
     createExecEnabledConfig("sortMergeJoin", defaultValue = true)
+  val COMET_EXEC_EXISTENCE_JOIN_ENABLED: ConfigEntry[Boolean] =
+    createExecEnabledConfig(
+      "existenceJoin",
+      defaultValue = true,
+      notes = Some("Enables native ExistenceJoin (EXISTS/NOT EXISTS/IN combined with OR)."))

Review Comment:
   This renders as "Whether to enable existenceJoin by default. Enables native ExistenceJoin (EXISTS/NOT EXISTS/IN combined with OR).." with a double period, because `createExecEnabledConfig` adds its own. Now that the flag is on by default, could it be a plain `conf(...)` whose doc says what still falls back (sort-merge joins, extra join conditions, computed keys, `NOT IN`)? Could the joins table in `docs/source/user-guide/latest/operators.md` carry the same note?



##########
spark/src/test/scala/org/apache/comet/exec/CometJoinSuite.scala:
##########
@@ -1572,4 +1572,132 @@ class CometJoinSuite extends CometTestBase {
       }
     }
   }
+
+  test("ExistenceJoin via BroadcastHashJoin (EXISTS combined with OR)") {
+    withSQLConf(
+      CometConf.COMET_EXEC_EXISTENCE_JOIN_ENABLED.key -> "true",
+      SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "10MB",
+      SQLConf.ADAPTIVE_AUTO_BROADCASTJOIN_THRESHOLD.key -> "10MB") {
+      withParquetTable((0 until 100).map(i => (i, if (i % 3 == 0) "US" else "EU")), "tbl_a") {
+        withParquetTable((0 until 30).map(i => (i, i + 1)), "tbl_b") {
+          val df = sql("SELECT * FROM tbl_a a " +
+            "WHERE a._2 = 'US' OR EXISTS (SELECT /*+ BROADCAST(b) */ 1 FROM tbl_b b WHERE b._1 = a._1)")
+          checkSparkAnswerAndOperator(
+            df,
+            Seq(classOf[CometBroadcastExchangeExec], classOf[CometBroadcastHashJoinExec]))
+        }
+      }
+    }
+  }
+
+  test("ExistenceJoin via ShuffledHashJoin (EXISTS combined with OR)") {
+    withSQLConf(
+      CometConf.COMET_EXEC_EXISTENCE_JOIN_ENABLED.key -> "true",
+      SQLConf.PREFER_SORTMERGEJOIN.key -> "false",
+      "spark.sql.join.forceApplyShuffledHashJoin" -> "true",
+      SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+      SQLConf.ADAPTIVE_AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1") {
+      withParquetTable((0 until 100).map(i => (i, if (i % 3 == 0) "US" else "EU")), "tbl_a") {
+        withParquetTable((0 until 30).map(i => (i, i + 1)), "tbl_b") {
+          val df = sql(
+            "SELECT * FROM tbl_a a " +
+              "WHERE a._2 = 'US' OR EXISTS (SELECT 1 FROM tbl_b b WHERE b._1 = a._1)")
+          checkSparkAnswerAndOperator(df, Seq(classOf[CometHashJoinExec]))
+        }
+      }
+    }
+  }
+
+  test("ExistenceJoin via SortMergeJoin falls back to Spark") {

Review Comment:
   This test and the residual-condition and computed-key tests below only call `checkSparkAnswer`, so they pass whether or not Comet falls back. I deleted the `condition.isEmpty` and key checks and added an `ExistenceJoin` case to the SMJ serde. All three queries then ran natively and every ExistenceJoin test still passed. Could they use `checkSparkAnswerAndFallbackReason` instead? It would also help if each guard had its own reason. Today the flag being off, a residual condition and a computed key all report `Unsupported join type ExistenceJoin(exists#N)`, so the tests can't tell which guard fired, and the message reads like a permanent limitation to someone looking at their plan.



##########
spark/src/test/resources/sql-tests/join/existence_join.sql:
##########
@@ -0,0 +1,234 @@
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements.  See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership.  The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License.  You may obtain a copy of the License at
+--
+--   http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied.  See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+
+-- Tests for ExistenceJoin: produced when EXISTS / NOT EXISTS is combined
+-- with another predicate via OR, preventing rewrite to LeftSemi / LeftAnti.
+--
+-- Strategy hints are placed INSIDE the EXISTS subquery (on the subquery's own
+-- relation alias), because an outer hint referencing the subquery table cannot
+-- resolve it and silently falls back to a broadcast hash join. Subquery-local
+-- hints (verified on Spark 4.1.3 with AQE off) select ShuffledHashJoin /
+-- SortMergeJoin.
+--
+-- Native existence support is currently hash-only: BROADCAST and SHUFFLE_HASH
+-- cases exercise CometBroadcastHashJoinExec / CometHashJoinExec, while MERGE
+-- cases fall back to Spark's SortMergeJoin (existence SMJ is not yet native) and
+-- verify result parity under the Comet-enabled config.
+
+-- Native ExistenceJoin support is experimental and disabled by default.
+-- Config: spark.comet.exec.existenceJoin.enabled=true
+
+-- ============================================================
+-- Setup: NULLs (both sides), duplicates, empty build, all-NULL build
+-- ============================================================
+
+statement
+CREATE TABLE ex_left(id int, k int, region string) USING parquet
+
+statement
+INSERT INTO ex_left VALUES
+  (1, 1, 'US'),
+  (2, 2, 'EU'),
+  (3, NULL, 'US'),
+  (4, 4, 'EU'),
+  (5, 5, 'EU'),
+  (6, NULL, 'EU')
+
+statement
+CREATE TABLE ex_right(id int, k int) USING parquet
+
+statement
+INSERT INTO ex_right VALUES (10, 1), (11, 2), (12, 2), (13, NULL)
+
+statement
+CREATE TABLE ex_right_no_nulls(id int, k int) USING parquet
+
+statement
+INSERT INTO ex_right_no_nulls VALUES (10, 1), (11, 5)
+
+statement
+CREATE TABLE ex_right_empty(id int, k int) USING parquet
+
+statement
+CREATE TABLE ex_right_dups(id int, k int) USING parquet
+
+statement
+INSERT INTO ex_right_dups VALUES (10, 1), (11, 1), (12, 1), (13, 2)
+
+statement
+CREATE TABLE ex_right_all_null(id int, k int) USING parquet
+
+statement
+INSERT INTO ex_right_all_null VALUES (10, NULL), (11, NULL)
+
+statement
+CREATE TABLE ex_left_empty(id int, k int, region string) USING parquet
+
+-- ============================================================
+-- EXISTS with OR across all three strategies (hint in subquery)
+-- ============================================================
+
+query
+SELECT * FROM ex_left l
+WHERE l.region = 'US'
+   OR EXISTS (SELECT /*+ BROADCAST(r) */ 1 FROM ex_right r WHERE r.k = l.k)
+ORDER BY l.id
+
+query
+SELECT * FROM ex_left l
+WHERE l.region = 'US'
+   OR EXISTS (SELECT /*+ SHUFFLE_HASH(r) */ 1 FROM ex_right r WHERE r.k = l.k)
+ORDER BY l.id
+
+query

Review Comment:
   Confirmed locally. With the four `MERGE` cases switched to `query expect_fallback(Unsupported join type)`, the rest of the file passes.



-- 
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: [PR] feat: wire native existence join support [datafusion-comet]

Posted by "comphead (via GitHub)" <gi...@apache.org>.
comphead commented on code in PR #4587:
URL: https://github.com/apache/datafusion-comet/pull/4587#discussion_r4157324659


##########
spark/src/test/resources/tpcds-plan-stability/approved-plans-v1_4/q10/extended.txt:
##########
@@ -1,61 +1,57 @@
-TakeOrderedAndProject
-+-  HashAggregate [COMET: Comet aggregate that merges intermediate buffers requires a Comet child aggregate when the intermediate buffer formats are incompatible with Spark. Incompatible aggregate function(s): count]
-   +- Exchange
-      +- HashAggregate
-         +- Project
-            +- BroadcastHashJoin
-               :- Project
-               :  +- BroadcastHashJoin
-               :     :- Project
-               :     :  +- Filter
-               :     :     +- BroadcastHashJoin
-               :     :        :-  BroadcastHashJoin [COMET: Unsupported join type ExistenceJoin(exists#1)]
-               :     :        :  :- CometColumnarToRow
-               :     :        :  :  +- CometBroadcastHashJoin
-               :     :        :  :     :- CometFilter
-               :     :        :  :     :  +- CometNativeScan parquet spark_catalog.default.customer
-               :     :        :  :     +- CometBroadcastExchange
-               :     :        :  :        +- CometProject
-               :     :        :  :           +- CometBroadcastHashJoin
-               :     :        :  :              :- CometNativeScan parquet spark_catalog.default.store_sales
-               :     :        :  :              :     +- CometSubqueryBroadcast
-               :     :        :  :              :        +- CometBroadcastExchange
-               :     :        :  :              :           +- CometProject
-               :     :        :  :              :              +- CometFilter
-               :     :        :  :              :                 +- CometNativeScan parquet spark_catalog.default.date_dim
-               :     :        :  :              +- CometBroadcastExchange
-               :     :        :  :                 +- CometProject
-               :     :        :  :                    +- CometFilter
-               :     :        :  :                       +- CometNativeScan parquet spark_catalog.default.date_dim
-               :     :        :  +- BroadcastExchange
-               :     :        :     +- CometColumnarToRow
-               :     :        :        +- CometProject
-               :     :        :           +- CometBroadcastHashJoin
-               :     :        :              :- CometNativeScan parquet spark_catalog.default.web_sales
-               :     :        :              :     +- ReusedSubquery
-               :     :        :              +- CometBroadcastExchange
-               :     :        :                 +- CometProject
-               :     :        :                    +- CometFilter
-               :     :        :                       +- CometNativeScan parquet spark_catalog.default.date_dim
-               :     :        +- BroadcastExchange
-               :     :           +- CometColumnarToRow
-               :     :              +- CometProject
-               :     :                 +- CometBroadcastHashJoin
-               :     :                    :- CometNativeScan parquet spark_catalog.default.catalog_sales
-               :     :                    :     +- ReusedSubquery
-               :     :                    +- CometBroadcastExchange
-               :     :                       +- CometProject
-               :     :                          +- CometFilter
-               :     :                             +- CometNativeScan parquet spark_catalog.default.date_dim
-               :     +- BroadcastExchange
-               :        +- CometColumnarToRow
-               :           +- CometProject
-               :              +- CometFilter
-               :                 +- CometNativeScan parquet spark_catalog.default.customer_address
-               +- BroadcastExchange
-                  +- CometColumnarToRow
+CometColumnarToRow
++- CometTakeOrderedAndProject
+   +- CometHashAggregate
+      +- CometExchange
+         +- CometHashAggregate
+            +- CometProject
+               +- CometBroadcastHashJoin
+                  :- CometProject
+                  :  +- CometBroadcastHashJoin
+                  :     :- CometProject
+                  :     :  +- CometFilter
+                  :     :     +- CometBroadcastHashJoin
+                  :     :        :- CometBroadcastHashJoin
+                  :     :        :  :- CometBroadcastHashJoin
+                  :     :        :  :  :- CometFilter
+                  :     :        :  :  :  +- CometNativeScan parquet spark_catalog.default.customer
+                  :     :        :  :  +- CometBroadcastExchange
+                  :     :        :  :     +- CometProject
+                  :     :        :  :        +- CometBroadcastHashJoin
+                  :     :        :  :           :- CometNativeScan parquet spark_catalog.default.store_sales
+                  :     :        :  :           :     +- CometSubqueryBroadcast
+                  :     :        :  :           :        +- CometBroadcastExchange
+                  :     :        :  :           :           +- CometProject
+                  :     :        :  :           :              +- CometFilter
+                  :     :        :  :           :                 +- CometNativeScan parquet spark_catalog.default.date_dim
+                  :     :        :  :           +- CometBroadcastExchange
+                  :     :        :  :              +- CometProject
+                  :     :        :  :                 +- CometFilter
+                  :     :        :  :                    +- CometNativeScan parquet spark_catalog.default.date_dim
+                  :     :        :  +- CometBroadcastExchange
+                  :     :        :     +- CometProject
+                  :     :        :        +- CometBroadcastHashJoin
+                  :     :        :           :- CometNativeScan parquet spark_catalog.default.web_sales
+                  :     :        :           :     +- ReusedSubquery
+                  :     :        :           +- CometBroadcastExchange
+                  :     :        :              +- CometProject
+                  :     :        :                 +- CometFilter
+                  :     :        :                    +- CometNativeScan parquet spark_catalog.default.date_dim
+                  :     :        +- CometBroadcastExchange
+                  :     :           +- CometProject
+                  :     :              +- CometBroadcastHashJoin
+                  :     :                 :- CometNativeScan parquet spark_catalog.default.catalog_sales
+                  :     :                 :     +- ReusedSubquery
+                  :     :                 +- CometBroadcastExchange
+                  :     :                    +- CometProject
+                  :     :                       +- CometFilter
+                  :     :                          +- CometNativeScan parquet spark_catalog.default.date_dim
+                  :     +- CometBroadcastExchange
+                  :        +- CometProject
+                  :           +- CometFilter
+                  :              +- CometNativeScan parquet spark_catalog.default.customer_address
+                  +- CometBroadcastExchange
                      +- CometProject
                         +- CometFilter
                            +- CometNativeScan parquet spark_catalog.default.customer_demographics
 
-Comet accelerated 24 out of 40 eligible operators (60%). Final plan contains 5 transitions between Spark and Comet. Accelerated expressions: 7 native, 0 codegen dispatch.
\ No newline at end of file
+Comet accelerated 40 out of 40 eligible operators (100%). Final plan contains 1 transitions between Spark and Comet. Accelerated expressions: 10 native, 0 codegen dispatch.

Review Comment:
   Nice!!!



-- 
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: [PR] feat: wire native existence join support [datafusion-comet]

Posted by "coderfender (via GitHub)" <gi...@apache.org>.
coderfender commented on PR #4587:
URL: https://github.com/apache/datafusion-comet/pull/4587#issuecomment-5935206203

   Merge commit with main to land rust updates Andy made 


-- 
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: [PR] feat: implement native native existence join support [datafusion-comet]

Posted by "comphead (via GitHub)" <gi...@apache.org>.
comphead commented on PR #4587:
URL: https://github.com/apache/datafusion-comet/pull/4587#issuecomment-5148377477

   nice PR in DF in terms of performance https://github.com/apache/datafusion/pull/23870


-- 
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: [PR] feat: implement native native existence join support [datafusion-comet]

Posted by "comphead (via GitHub)" <gi...@apache.org>.
comphead commented on PR #4587:
URL: https://github.com/apache/datafusion-comet/pull/4587#issuecomment-5071482254

   Apparently sooner or later we would need to use DF mark joins, they can be disabled by default and we can improve them in background


-- 
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: [PR] feat: wire native existence join support [datafusion-comet]

Posted by "comphead (via GitHub)" <gi...@apache.org>.
comphead commented on PR #4587:
URL: https://github.com/apache/datafusion-comet/pull/4587#issuecomment-5934811692

   I'll run the small simplification pass recommendation 


-- 
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: [PR] feat: wire native existence join support [datafusion-comet]

Posted by "coderfender (via GitHub)" <gi...@apache.org>.
coderfender merged PR #4587:
URL: https://github.com/apache/datafusion-comet/pull/4587


-- 
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: [PR] feat: wire native existence join support [datafusion-comet]

Posted by "sunchao (via GitHub)" <gi...@apache.org>.
sunchao commented on code in PR #4587:
URL: https://github.com/apache/datafusion-comet/pull/4587#discussion_r4131690071


##########
spark/src/test/scala/org/apache/comet/exec/CometJoinSuite.scala:
##########
@@ -1572,4 +1572,131 @@ class CometJoinSuite extends CometTestBase {
       }
     }
   }
+
+  test("ExistenceJoin via BroadcastHashJoin (EXISTS combined with OR)") {
+    withSQLConf(
+      CometConf.COMET_EXEC_EXISTENCE_JOIN_ENABLED.key -> "true",
+      SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "10MB",
+      SQLConf.ADAPTIVE_AUTO_BROADCASTJOIN_THRESHOLD.key -> "10MB") {
+      withParquetTable((0 until 100).map(i => (i, if (i % 3 == 0) "US" else "EU")), "tbl_a") {
+        withParquetTable((0 until 30).map(i => (i, i + 1)), "tbl_b") {
+          val df = sql("SELECT * FROM tbl_a a " +
+            "WHERE a._2 = 'US' OR EXISTS (SELECT /*+ BROADCAST(b) */ 1 FROM tbl_b b WHERE b._1 = a._1)")
+          checkSparkAnswerAndOperator(
+            df,
+            Seq(classOf[CometBroadcastExchangeExec], classOf[CometBroadcastHashJoinExec]))
+        }
+      }
+    }
+  }
+
+  test("ExistenceJoin via ShuffledHashJoin (EXISTS combined with OR)") {
+    withSQLConf(
+      CometConf.COMET_EXEC_EXISTENCE_JOIN_ENABLED.key -> "true",
+      SQLConf.PREFER_SORTMERGEJOIN.key -> "false",
+      "spark.sql.join.forceApplyShuffledHashJoin" -> "true",
+      SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+      SQLConf.ADAPTIVE_AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1") {
+      withParquetTable((0 until 100).map(i => (i, if (i % 3 == 0) "US" else "EU")), "tbl_a") {
+        withParquetTable((0 until 30).map(i => (i, i + 1)), "tbl_b") {
+          val df = sql(
+            "SELECT * FROM tbl_a a " +
+              "WHERE a._2 = 'US' OR EXISTS (SELECT 1 FROM tbl_b b WHERE b._1 = a._1)")
+          checkSparkAnswerAndOperator(df, Seq(classOf[CometHashJoinExec]))
+        }
+      }
+    }
+  }
+
+  test("ExistenceJoin via SortMergeJoin falls back to Spark") {
+    // Existence sort-merge joins are not executed natively; verify the fallback.
+    withSQLConf(
+      CometConf.COMET_EXEC_EXISTENCE_JOIN_ENABLED.key -> "true",
+      SQLConf.PREFER_SORTMERGEJOIN.key -> "true",
+      SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+      SQLConf.ADAPTIVE_AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1") {
+      withParquetTable((0 until 100).map(i => (i, if (i % 3 == 0) "US" else "EU")), "tbl_a") {
+        withParquetTable((0 until 30).map(i => (i, i + 1)), "tbl_b") {
+          val df = sql(
+            "SELECT * FROM tbl_a a " +
+              "WHERE a._2 = 'US' OR EXISTS (SELECT 1 FROM tbl_b b WHERE b._1 = a._1)")
+          checkSparkAnswerAndFallbackReason(df, "Unsupported join type")
+        }
+      }
+    }
+  }
+
+  test("ExistenceJoin with residual condition falls back to Spark") {
+    // A non-equi residual predicate is evaluated over the whole candidate batch by DataFusion's
+    // LeftMark join (no first-match short-circuit), so Comet keeps it on Spark; verify parity.
+    withSQLConf(
+      CometConf.COMET_EXEC_EXISTENCE_JOIN_ENABLED.key -> "true",
+      SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "10MB",
+      SQLConf.ADAPTIVE_AUTO_BROADCASTJOIN_THRESHOLD.key -> "10MB") {
+      withParquetTable((0 until 100).map(i => (i, if (i % 3 == 0) "US" else "EU")), "tbl_a") {
+        withParquetTable((0 until 30).map(i => (i, i + 1)), "tbl_b") {
+          val df = sql(
+            "SELECT * FROM tbl_a a WHERE a._2 = 'US' OR EXISTS " +
+              "(SELECT /*+ BROADCAST(b) */ 1 FROM tbl_b b WHERE b._1 = a._1 AND b._2 > a._1)")
+          checkSparkAnswerAndFallbackReason(df, "residual (non-equi)")

Review Comment:
   [P2] Make the new fallback-reason assertions pass with the default planning configuration. This residual-condition test and the computed-key test at line 1659 both fail in Spark 4.1 CI: results match Spark and the final plans contain Spark `BroadcastHashJoin`, but `checkSparkAnswerAndFallbackReason` finds no fallback reasons. These assertions add two execution-suite failures. Preserve the guard-specific reasons on the final plan, or test the reasons at a stable planning phase while retaining an assertion that execution falls back to Spark.
   
   Evidence: Head-associated CI job https://github.com/apache/datafusion-comet/actions/runs/36515604951/job/109239141889 reports `Expected fallback reason 'residual (non-equi)' but no fallback reasons were found` and the equivalent failure for `computed (non-column)`. `CometTestBase.checkSparkAnswerAndFallbackReasons` checks the executed Comet-enabled plan after result comparison. The relevant guards and assertions are unchanged between the requested head and the CI merge revision. With native build prerequisites available, isolate these tests using `./mvnw test -Pspark-4.1 -Dtest=none -Dsuites="org.apache.comet.exec.CometJoinSuite ExistenceJoin"`.



-- 
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: [PR] feat: implement native native existence join support [datafusion-comet]

Posted by "coderfender (via GitHub)" <gi...@apache.org>.
coderfender commented on PR #4587:
URL: https://github.com/apache/datafusion-comet/pull/4587#issuecomment-5072551486

   @andygrove  thank you for the comment, Yes the bechmarks are indeed different (with atleast 2x speedup)
   ```
   Running benchmark: exists OR predicate (BHJ)
     Running case: Spark
     Stopped after 3 iterations, 2120 ms
     Running case: Comet
     Stopped after 7 iterations, 2043 ms
   
   OpenJDK 64-Bit Server VM 17.0.16+8-LTS on Mac OS X 26.3.2
   Apple M5 Pro
   exists OR predicate (BHJ):                Best Time(ms)   Avg Time(ms)   Stdev(ms)    Rate(M/s)   Per Row(ns)   Relative
   ------------------------------------------------------------------------------------------------------------------------
   Spark                                               703            707           7          1.5         670.1       1.0X
   Comet                                               286            292           6          3.7         273.2       2.5X
   
   Running benchmark: exists OR predicate (SHJ)
     Running case: Spark
     Stopped after 3 iterations, 2216 ms
     Running case: Comet
     Stopped after 7 iterations, 2205 ms
   
   OpenJDK 64-Bit Server VM 17.0.16+8-LTS on Mac OS X 26.3.2
   Apple M5 Pro
   exists OR predicate (SHJ):                Best Time(ms)   Avg Time(ms)   Stdev(ms)    Rate(M/s)   Per Row(ns)   Relative
   ------------------------------------------------------------------------------------------------------------------------
   Spark                                               738            739           1          1.4         703.5       1.0X
   Comet                                               309            315           7          3.4         294.4       2.4X
   
   Running benchmark: exists OR predicate (SMJ)
     Running case: Spark
     Stopped after 3 iterations, 2181 ms
     Running case: Comet
     Stopped after 6 iterations, 2017 ms
   
   OpenJDK 64-Bit Server VM 17.0.16+8-LTS on Mac OS X 26.3.2
   Apple M5 Pro
   exists OR predicate (SMJ):                Best Time(ms)   Avg Time(ms)   Stdev(ms)    Rate(M/s)   Per Row(ns)   Relative
   ------------------------------------------------------------------------------------------------------------------------
   Spark                                               714            727          15          1.5         680.9       1.0X
   Comet                                               331            336           4          3.2         315.8       2.2X
   ```


-- 
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: [PR] feat: implement native native existence join support [datafusion-comet]

Posted by "coderfender (via GitHub)" <gi...@apache.org>.
coderfender commented on PR #4587:
URL: https://github.com/apache/datafusion-comet/pull/4587#issuecomment-4625309517

   Mixed results with benches 
   
   
   ```
   Running benchmark: exists OR predicate (BHJ)
     Running case: Spark
     Stopped after 29 iterations, 2015 ms
     Running case: Comet
     Stopped after 61 iterations, 2011 ms
   
   OpenJDK 64-Bit Server VM 17.0.16+8-LTS on Mac OS X 26.3.2
   Apple M5 Pro
   exists OR predicate (BHJ):                Best Time(ms)   Avg Time(ms)   Stdev(ms)    Rate(M/s)   Per Row(ns)   Relative
   ------------------------------------------------------------------------------------------------------------------------
   Spark                                                43             70          15         24.4          41.1       1.0X
   Comet                                                29             33           3         35.7          28.0       1.5X
   
   Running benchmark: exists OR predicate (SHJ)
     Running case: Spark
     Stopped after 53 iterations, 2029 ms
     Running case: Comet
     Stopped after 44 iterations, 2043 ms
   
   OpenJDK 64-Bit Server VM 17.0.16+8-LTS on Mac OS X 26.3.2
   Apple M5 Pro
   exists OR predicate (SHJ):                Best Time(ms)   Avg Time(ms)   Stdev(ms)    Rate(M/s)   Per Row(ns)   Relative
   ------------------------------------------------------------------------------------------------------------------------
   Spark                                                35             38           2         29.7          33.7       1.0X
   Comet                                                42             46           5         24.8          40.3       0.8X
   
   Running benchmark: exists OR predicate (SMJ)
     Running case: Spark
     Stopped after 54 iterations, 2034 ms
     Running case: Comet
     Stopped after 35 iterations, 2019 ms
   
   OpenJDK 64-Bit Server VM 17.0.16+8-LTS on Mac OS X 26.3.2
   Apple M5 Pro
   exists OR predicate (SMJ):                Best Time(ms)   Avg Time(ms)   Stdev(ms)    Rate(M/s)   Per Row(ns)   Relative
   ------------------------------------------------------------------------------------------------------------------------
   Spark                                                35             38           2         29.8          33.6       1.0X
   Comet                                                54             58           4         19.4          51.6       0.7X
   ```


-- 
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: [PR] feat: implement native native existence join support [datafusion-comet]

Posted by "comphead (via GitHub)" <gi...@apache.org>.
comphead commented on PR #4587:
URL: https://github.com/apache/datafusion-comet/pull/4587#issuecomment-5723010195

   ## Review: native ExistenceJoin
   
   I traced the change end to end (Spark plan -> serde -> proto -> `planner.rs` -> DataFusion 55.1.0) and checked the Spark reference behaviour in `RewritePredicateSubquery` and `ResolveHints`.
   
   ### What checks out
   
   The core mapping is sound, so the findings below are about coverage and packaging rather than the join semantics:
   
   - `BuildRight` is the only build side Spark produces for `ExistenceJoin` (`canBuildBroadcastLeft` / `canBuildShuffledHashJoinLeft` both exclude it), so the hash-join path always takes the `swap_inputs` branch. `JoinType::swap()` turns `LeftMark` into `RightMark`, `swap_inputs` skips the reverting projection for mark joins, and `build_join_schema` lays `RightMark` out as right fields followed by `mark`. After the swap `right` is Spark's left, so the native schema is `[left.output..., mark]`, which matches Spark's `left.output :+ exists`.
   - `Field::new("mark", Boolean, false)` matches `AttributeReference("exists", BooleanType, nullable = false)`.
   - DF 55.1.0 `SortMergeJoinExec` routes `LeftMark` through `BitwiseSortMergeJoinStream`, streams the left side, and reports `maintains_input_order = [true, false]`, which matches Spark's `SortMergeJoinExec.outputOrdering` for `LeftExistence(_)`.
   - The join dynamic filter is gated on `join.join_type() == Inner`, so mark joins are unaffected.
   - `outputPartitioning` already handles the new type via the existing `LeftExistence(_)` case.
   
   ### Blocking: the SQL fixture only ever exercises BroadcastHashJoin
   
   `existence_join.sql` states that the hints run each query against BHJ, SHJ and SMJ. They do not. Spark's `ResolveHints.applyJoinStrategyHint` descends with `mapChildren`, which visits plan children only and never subquery expressions, and it stops at `SubqueryAlias`. For
   
   ```sql
   SELECT /*+ BROADCAST(ex_right) */ * FROM ex_left l
   WHERE l.region = 'US' OR EXISTS (SELECT 1 FROM ex_right r WHERE r.k = l.k)
   ```
   
   the traversal reaches `SubqueryAlias(l)` and stops, `ex_right` is never matched, and the hint is dropped with a `hintRelationsNotFound` warning. Putting the hint inside the subquery does not help either, since `EliminateResolvedHint` runs in the first optimizer batch (`FinishAnalysis`) while `RewritePredicateSubquery` runs in the much later `RewriteSubquery` batch, so there is no `Join` for the hint to attach to.
   
   Consequences:
   
   - `CometTestBase` sets `autoBroadcastJoinThreshold=1g`, so all ten queries plan as BHJ. `CometSortMergeJoinExec` and `CometHashJoinExec` get no SQL-level coverage at all.
   - Queries 1, 2 and 3 are the same query three times, and the two empty-build queries are the same query twice.
   
   Strategy selection has to come from config. The fixture format already supports this:
   
   ```
   -- Config: spark.sql.adaptive.autoBroadcastJoinThreshold=-1
   -- Config: spark.sql.join.forceApplyShuffledHashJoin=true
   -- ConfigMatrix: spark.sql.autoBroadcastJoinThreshold=10485760,-1
   -- ConfigMatrix: spark.sql.join.preferSortMergeJoin=true,false
   ```
   
   That yields four runs covering BHJ, SMJ and SHJ, and lets the three duplicated query groups collapse to one each.
   
   ### Missing coverage for the reachable non-equi paths
   
   `RewritePredicateSubquery` produces `ExistenceJoin` from three shapes, and the fixture covers only one of them.
   
   1. **Join filter.** `Exists(sub, _, _, conditions, _)` puts the whole correlated predicate in the join condition. A correlation such as `r.k = l.k AND r.v > l.v` splits into equi keys plus `condition = Some(r.v > l.v)`, which serialises and reaches `HashJoinExec` with a `JoinFilter` that `swap_inputs` then rewrites via `JoinFilter::swap`, and reaches `BitwiseSortMergeJoinStream` on the SMJ side. This is the least-travelled DataFusion path in the change and nothing in the PR exercises it. Note the SMJ variant is additionally gated by `spark.comet.exec.sortMergeJoinWithJoinFilter.enabled`, which defaults to true.
   
   2. **`IN` combined with `OR`.** `InSubquery` lowers to `ExistenceJoin` with plain equi keys, so it goes straight through the new code and is untested.
   
   3. **`NOT IN` combined with `OR`.** This builds the condition `(l.k = r.k) OR isnull(l.k = r.k)`, from which `ExtractEquiJoinKeys` extracts nothing, so it plans as a `BroadcastNestedLoopJoin` and falls back. That is a reasonable scope boundary, but the config name suggests otherwise. Worth an `expect_fallback` case plus a sentence in the config doc.
   
   Also missing: a fallback case proving the join falls back when the config is off (the default), and any non-integer key type. Join keys are the one place `NormalizeFloatingNumbers` and string/decimal/date comparison differences show up, so a double key with `NaN` and `-0.0` and a string key would be worth having.
   
   ### Benchmark compares different plans on the two arms
   
   `runExpressionBenchmark` applies `extraCometConfigs` to the Comet arm only, and the queries in `CometExistenceJoinBenchmark` carry no hints. The Spark baseline therefore runs with the session defaults and plans a BroadcastHashJoin in all three cases, so the "ShuffledHashJoin" and "SortMergeJoin" rows measure Comet SHJ and Comet SMJ against Spark BHJ. (`CometHashJoinBenchmark` gets away with the same config map because its queries carry `SHUFFLE_HASH` hints that apply to both arms, and as above, hints are not available here.)
   
   Fix is to wrap each `runBenchmark` in `withSQLConf(strategyConfigs: _*)` so both arms see the same strategy, leaving only `COMET_EXEC_EXISTENCE_JOIN_ENABLED` in `extraCometConfigs`.
   
   ### Reuse and duplication
   
   - **Benchmark file.** `CometExistenceJoinBenchmark` is a fourth copy of a `getSparkSession` that already exists verbatim in `CometHashJoinBenchmark`, `CometBroadcastHashJoinBenchmark` and `CometSortMergeJoinBenchmark`, and its SHJ config map is identical to `CometHashJoinBenchmark.cometConfigs`. An `exists OR predicate` case added to each of those three files would be a few lines each instead of 123, and each file already forces its own strategy.
   
   - **`joinType` match.** The same eight-line match now exists three times in `operators.scala` (`CometHashJoin.doConvert`, `CometSortMergeJoinExec.convert`, `CometBroadcastNestedLoopJoinExec.convert`), and the PR edits two of the three. A single `toProtoJoinType(join, allowExistence)` helper would leave one place to touch when the next join type lands.
   
   - **`producedAttributes`.** Three byte-identical overrides. Since `output` differs from `inputSet` only for `ExistenceJoin`, `AttributeSet(output) -- inputSet` is a one-liner that needs no join-type match, or put the existing version on a small shared trait alongside the already-duplicated `outputPartitioning`.
   
   - **`CometJoinSuite`.** The three new tests differ only in their config map and expected exec class. One table-driven test over `Seq((label, configs, expectedClass))` covers the same ground in about a third of the lines. The `/*+ BROADCAST(b) */` inside the first test's subquery is dead for the reason described above, and the test already forces broadcast by threshold, so it should go.
   
   ### Smaller points
   
   - When the config is off, the fallback reason is `Unsupported join type ExistenceJoin(exists#N)`, which reads like a permanent limitation rather than a flag being off. `CometSortMergeJoinExec` already models the better message for the `sortMergeJoinWithJoinFilter` gate.
   - `createExecEnabledConfig("existenceJoin", ...)` renders as "Whether to enable existenceJoin by default", but this is not an exec, it is a join-type gate spanning three execs. `spark.comet.exec.sortMergeJoinWithJoinFilter.enabled` is the established precedent for a join sub-feature and uses a plain `conf(...)` with a written doc string.
   - `planner.rs` has join-type coverage in its unit tests around the `build_side` cases. One case for `JoinType::Existence` asserting the swapped plan's schema ends in `mark` would pin the mapping without a Spark round trip.
   


-- 
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: [PR] feat: wire native existence join support [datafusion-comet]

Posted by "viirya (via GitHub)" <gi...@apache.org>.
viirya commented on PR #4587:
URL: https://github.com/apache/datafusion-comet/pull/4587#issuecomment-5836026232

   > I dont see reasons of not enabling them, @andygrove @viirya WDYT?
   
   Is there any concern on enabling this? Like corner cases with regression?


-- 
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: [PR] feat: wire native existence join support [datafusion-comet]

Posted by "andygrove (via GitHub)" <gi...@apache.org>.
andygrove commented on PR #4587:
URL: https://github.com/apache/datafusion-comet/pull/4587#issuecomment-5876300625

   This is a light fully automated review since there are so many PRs open.
   
   `CometExistenceJoinBenchmark.scala:38` overrides `getSparkSession` with a copy of the join benchmarks' session setup from before #6195, and that change is now in this branch through the merge from main. #6195 made `isCometLoaded` (`CometSparkSessionExtensions.scala:139`) return false when `spark.memory.offHeap.enabled` is off and `spark.comet.exec.onHeap.enabled` is unset. It also added `.set("spark.comet.exec.onHeap.enabled", "true")` to `CometBenchmarkBase.getSparkSession` and to the overrides in `CometHashJoinBenchmark`, `CometBroadcastHashJoinBenchmark` and `CometSortMergeJoinBenchmark`. This override sets neither, and nothing on the `make benchmark-%` path sets them either. So `CometExecRule` and `CometScanRule` return the plan unchanged, and both the BHJ and SHJ cases end up timing Spark against Spark. The only hint is the "NOT fully Comet native" warning from `runExpressionBenchmark`. Could you add the same `spark.comet.exec.onHeap.enabled` line to the conf here, so the res
 ults file measures the native existence join?
   


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