You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@datafusion.apache.org by gi...@apache.org on 2026/10/02 02:28:01 UTC

(datafusion-comet) 01/02: feat: wire native existence join support (#4587)

This is an automated email from the ASF dual-hosted git repository.

github-merge-queue[bot] pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/datafusion-comet.git

commit 48ec1c002f788595a8086594d177e0a0b3d5b914
Author: Bhargava Vadlamani <11...@users.noreply.github.com>
AuthorDate: Fri Oct 2 00:27:43 2026 +0000

    feat: wire native existence join support (#4587)
    
    * implement_native_existence_joins
    
    * implement_native_existence_joins
    
    * implement_native_existence_joins_fix_plans
    
    * implement_native_existence_joins_fix_plans
    
    * implement_native_existence_joins_fix_plans
    
    * init_commit
    
    * make_doc_changes
    
    * make_doc_default_changes
    
    * address_review_comments_update_benches
    
    * make native join stricter and address review comments
    
    * make stricter fallbacks and add further tests
    
    * make stricter fallbacks and add further tests lint
    
    * make stricter fallbacks and add further tests
    
    * make stricter fallbacks and add further tests
    
    * fix: use Long literals in CometExistenceJoinBenchmark to satisfy strict Scala warnings
    
    * address review: distinct fallback reasons, IN/direct-marker/string-key SQL coverage, plain conf, docs
    
    - CometConf: switch existenceJoin flag to a plain conf(...) with a doc listing
      what still falls back (matches sortMergeJoinWithJoinFilter precedent)
    - operators.scala: give the flag-off ExistenceJoin case its own fallback reason
    - operators.md: note native existence support on BHJ/SHJ and what falls back
    - existence_join.sql: add direct-marker projection, IN-with-OR, and string-key
      cases; fix the stale 'disabled by default' header
    - CometJoinSuite: add flag-off fallback test and existence-SMJ-under-forceSHJ
      fallback test
    - CometExistenceJoinBenchmark: set spark.comet.exec.onHeap.enabled so both arms
      measure the native join
    
    * remove dead ExistenceJoin arm from CometSortMergeJoinExec.producedAttributes
    
    Existence sort-merge joins always fall back to Spark (the SMJ serde has no
    ExistenceJoin case), so a CometSortMergeJoinExec is never built with that join
    type. The override only ever returned AttributeSet.empty, which is already the
    inherited default for a join, so drop it entirely. The reachable hash-join and
    broadcast-hash-join overrides are unchanged.
    
    * make stricter fallbacks and add further tests
---
 docs/source/user-guide/latest/operators.md         |   4 +
 native/core/src/execution/planner.rs               |   1 +
 native/proto/src/proto/operator.proto              |   1 +
 .../main/scala/org/apache/comet/CometConf.scala    |   9 +
 .../scala/org/apache/comet/rules/RewriteJoin.scala |   6 +-
 .../org/apache/spark/sql/comet/operators.scala     |  41 ++-
 .../resources/sql-tests/join/existence_join.sql    | 281 +++++++++++++++++++++
 .../approved-plans-v1_4/q10/extended.txt           | 110 ++++----
 .../approved-plans-v1_4/q35/extended.txt           | 110 ++++----
 .../approved-plans-v1_4/q45/extended.txt           |  82 +++---
 .../approved-plans-v2_7/q35/extended.txt           | 110 ++++----
 .../org/apache/comet/exec/CometJoinSuite.scala     | 180 +++++++++++++
 .../benchmark/CometExistenceJoinBenchmark.scala    | 123 +++++++++
 .../comet/exec/CometEmptyRelationExecSuite.scala   |   9 +-
 14 files changed, 847 insertions(+), 220 deletions(-)

diff --git a/docs/source/user-guide/latest/operators.md b/docs/source/user-guide/latest/operators.md
index c9ba5e491e..a47055df7e 100644
--- a/docs/source/user-guide/latest/operators.md
+++ b/docs/source/user-guide/latest/operators.md
@@ -92,6 +92,10 @@ omitted from the tables below and may be reconsidered based on demand:
 | `SortMergeJoinExec`           | ✅     | Supports `BINARY` join keys. Nested-type (struct, array, map) and collated-string join keys fall back to Spark.                                                               |
 | `BroadcastNestedLoopJoinExec` | ✅     | Falls back to Spark when the preserved side is broadcast (for example LEFT OUTER with BROADCAST on the left) ([#4429](https://github.com/apache/datafusion-comet/pull/4429)). |
 
+`ExistenceJoin` (produced when `EXISTS` / `IN` is combined with another predicate via `OR`) runs
+natively on `BroadcastHashJoinExec` and `ShuffledHashJoinExec`. Existence sort-merge joins, residual
+(non-equi) join conditions, computed (non-column) join keys, and `NOT IN` fall back to Spark.
+
 ## Exchanges
 
 | Operator                | Status | Notes |
diff --git a/native/core/src/execution/planner.rs b/native/core/src/execution/planner.rs
index a15bd1fb62..42571d649a 100644
--- a/native/core/src/execution/planner.rs
+++ b/native/core/src/execution/planner.rs
@@ -2688,6 +2688,7 @@ impl PhysicalPlanner {
             Ok(JoinType::FullOuter) => DFJoinType::Full,
             Ok(JoinType::LeftSemi) => DFJoinType::LeftSemi,
             Ok(JoinType::LeftAnti) => DFJoinType::LeftAnti,
+            Ok(JoinType::Existence) => DFJoinType::LeftMark,
             Err(_) => {
                 return Err(GeneralError(format!(
                     "Unsupported join type: {join_type:?}"
diff --git a/native/proto/src/proto/operator.proto b/native/proto/src/proto/operator.proto
index d78ee8df39..d6676ac336 100644
--- a/native/proto/src/proto/operator.proto
+++ b/native/proto/src/proto/operator.proto
@@ -993,6 +993,7 @@ enum JoinType {
   FullOuter = 3;
   LeftSemi = 4;
   LeftAnti = 5;
+  Existence = 6;
 }
 
 enum BuildSide {
diff --git a/spark/src/main/scala/org/apache/comet/CometConf.scala b/spark/src/main/scala/org/apache/comet/CometConf.scala
index 92b7de23c5..6cfe5bc92b 100644
--- a/spark/src/main/scala/org/apache/comet/CometConf.scala
+++ b/spark/src/main/scala/org/apache/comet/CometConf.scala
@@ -235,6 +235,15 @@ 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] =
+    conf("spark.comet.exec.existenceJoin.enabled")
+      .category(CATEGORY_ENABLE_EXEC)
+      .doc(
+        "Whether to enable native ExistenceJoin, produced when EXISTS / NOT EXISTS / IN is " +
+          "combined with another predicate via OR. Sort-merge joins, residual (non-equi) join " +
+          "conditions, computed (non-column) join keys, and NOT IN fall back to Spark.")
+      .booleanConf
+      .createWithDefault(true)
   val COMET_EXEC_AGGREGATE_ENABLED: ConfigEntry[Boolean] =
     createExecEnabledConfig("aggregate", defaultValue = true)
   val COMET_EXEC_COLLECT_LIMIT_ENABLED: ConfigEntry[Boolean] =
diff --git a/spark/src/main/scala/org/apache/comet/rules/RewriteJoin.scala b/spark/src/main/scala/org/apache/comet/rules/RewriteJoin.scala
index 2864eea4ed..4ffd34451b 100644
--- a/spark/src/main/scala/org/apache/comet/rules/RewriteJoin.scala
+++ b/spark/src/main/scala/org/apache/comet/rules/RewriteJoin.scala
@@ -20,7 +20,7 @@
 package org.apache.comet.rules
 
 import org.apache.spark.sql.catalyst.optimizer.{BuildLeft, BuildRight, BuildSide, JoinSelectionHelper}
-import org.apache.spark.sql.catalyst.plans.LeftSemi
+import org.apache.spark.sql.catalyst.plans.{ExistenceJoin, LeftSemi}
 import org.apache.spark.sql.catalyst.plans.logical.Join
 import org.apache.spark.sql.execution.{SortExec, SparkPlan}
 import org.apache.spark.sql.execution.joins.{ShuffledHashJoinExec, SortMergeJoinExec}
@@ -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
           withFallbackReason(
             smj,
             "Cannot rewrite SortMergeJoin to HashJoin: " +
diff --git a/spark/src/main/scala/org/apache/spark/sql/comet/operators.scala b/spark/src/main/scala/org/apache/spark/sql/comet/operators.scala
index b6d16bfb22..2a70c7de8e 100644
--- a/spark/src/main/scala/org/apache/spark/sql/comet/operators.scala
+++ b/spark/src/main/scala/org/apache/spark/sql/comet/operators.scala
@@ -30,7 +30,7 @@ import org.apache.spark.broadcast.Broadcast
 import org.apache.spark.internal.Logging
 import org.apache.spark.rdd.RDD
 import org.apache.spark.sql.catalyst.InternalRow
-import org.apache.spark.sql.catalyst.expressions.{Ascending, Attribute, AttributeSeq, AttributeSet, CodegenObjectFactoryMode, Expression, ExpressionSet, Generator, LeafExpression, NamedExpression, SortOrder, XXH64}
+import org.apache.spark.sql.catalyst.expressions.{Ascending, Attribute, AttributeSeq, AttributeSet, CodegenObjectFactoryMode, Expression, ExpressionSet, Generator, LeafExpression, Literal, NamedExpression, SortOrder, XXH64}
 import org.apache.spark.sql.catalyst.expressions.aggregate.{AggregateExpression, AggregateMode, CollectList, CollectSet, Final, ImperativeAggregate, Mode, Partial, PartialMerge, Percentile, Sum}
 import org.apache.spark.sql.catalyst.expressions.codegen.CodegenFallback
 import org.apache.spark.sql.catalyst.optimizer.{BuildLeft, BuildRight, BuildSide}
@@ -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
         case _ =>
           // Spark doesn't support other join types
           withFallbackReason(join, s"Unsupported join type ${join.joinType}")
@@ -2844,6 +2870,11 @@ case class CometHashJoinExec(
   override def withNewChildrenInternal(newLeft: SparkPlan, newRight: SparkPlan): SparkPlan =
     this.copy(left = newLeft, right = newRight)
 
+  override def producedAttributes: AttributeSet = joinType match {
+    case ExistenceJoin(exists) => AttributeSet(exists)
+    case _ => AttributeSet.empty
+  }
+
   override def stringArgs: Iterator[Any] =
     Iterator(leftKeys, rightKeys, joinType, buildSide, condition, left, right)
 
@@ -2993,6 +3024,11 @@ case class CometBroadcastHashJoinExec(
   override def withNewChildrenInternal(newLeft: SparkPlan, newRight: SparkPlan): SparkPlan =
     this.copy(left = newLeft, right = newRight)
 
+  override def producedAttributes: AttributeSet = joinType match {
+    case ExistenceJoin(exists) => AttributeSet(exists)
+    case _ => AttributeSet.empty
+  }
+
   override def stringArgs: Iterator[Any] =
     Iterator(leftKeys, rightKeys, joinType, condition, buildSide, left, right)
 
@@ -3089,6 +3125,9 @@ 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
+        // before emitting, risking OOM on large equal-key groups. Tracked in
+        // https://github.com/apache/datafusion-comet/issues/6514.
         case _ =>
           // Spark doesn't support other join types
           withFallbackReason(join, s"Unsupported join type ${join.joinType}")
diff --git a/spark/src/test/resources/sql-tests/join/existence_join.sql b/spark/src/test/resources/sql-tests/join/existence_join.sql
new file mode 100644
index 0000000000..e408b2b4ef
--- /dev/null
+++ b/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)
+SELECT * FROM ex_left l
+WHERE l.region = 'US'
+   OR EXISTS (SELECT /*+ MERGE(r) */ 1 FROM ex_right_empty r WHERE r.k = l.k)
+ORDER BY l.id
+
+-- ============================================================
+-- Empty left: no rows in, no rows out (each strategy)
+-- ============================================================
+
+query
+SELECT * FROM ex_left_empty 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
+
+-- ============================================================
+-- All-NULL build keys: EXISTS is false for every left row
+-- (NULL = anything is NULL), so only the OR arm can qualify.
+-- ============================================================
+
+query
+SELECT * FROM ex_left l
+WHERE l.region = 'US'
+   OR EXISTS (SELECT /*+ SHUFFLE_HASH(r) */ 1 FROM ex_right_all_null r WHERE r.k = l.k)
+ORDER BY l.id
+
+-- ============================================================
+-- Right side has no NULL: NULL-keyed left rows (id 3, 6) reach the marker
+-- evaluation but cannot match, so their exists tag is false.
+-- ============================================================
+
+query
+SELECT * FROM ex_left l
+WHERE l.region = 'US'
+   OR EXISTS (SELECT /*+ BROADCAST(r) */ 1 FROM ex_right_no_nulls r WHERE r.k = l.k)
+ORDER BY l.id
+
+-- ============================================================
+-- NOT EXISTS combined with OR: also lowers to ExistenceJoin.
+-- Exercised across all three strategies.
+-- ============================================================
+
+query
+SELECT * FROM ex_left l
+WHERE l.region = 'US'
+   OR NOT 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 NOT 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 NOT EXISTS (SELECT /*+ MERGE(r) */ 1 FROM ex_right r WHERE r.k = l.k)
+ORDER BY l.id
+
+-- ============================================================
+-- Duplicate build keys: marker is "at least one match", so duplicates on the
+-- right must not multiply the output. Checked on SHJ and SMJ too.
+-- ============================================================
+
+query
+SELECT * FROM ex_left l
+WHERE l.region = 'US'
+   OR EXISTS (SELECT /*+ SHUFFLE_HASH(r) */ 1 FROM ex_right_dups 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_dups r WHERE r.k = l.k)
+ORDER BY l.id
+
+-- ============================================================
+-- Two EXISTS OR'd together: two Existence joins stacked in one plan.
+-- ============================================================
+
+query
+SELECT * FROM ex_left l
+WHERE EXISTS (SELECT /*+ BROADCAST(r) */ 1 FROM ex_right r WHERE r.k = l.k)
+   OR EXISTS (SELECT /*+ BROADCAST(r2) */ 1 FROM ex_right_no_nulls r2 WHERE r2.k = l.k)
+ORDER BY l.id
+
+-- ============================================================
+-- Marker used inside a more complex predicate (AND over NOT EXISTS OR ...).
+-- ============================================================
+
+query
+SELECT id, k, region FROM ex_left l
+WHERE l.id > 1
+  AND (l.region = 'US'
+       OR NOT EXISTS (SELECT /*+ BROADCAST(r) */ 1 FROM ex_right r WHERE r.k = l.k))
+ORDER BY l.id
+
+-- ============================================================
+-- Multi-column correlation
+-- ============================================================
+
+statement
+CREATE TABLE ex_left_multi(id int, k1 int, k2 int) USING parquet
+
+statement
+INSERT INTO ex_left_multi VALUES (1, 1, 100), (2, 2, 200), (3, 1, 300)
+
+statement
+CREATE TABLE ex_right_multi(k1 int, k2 int) USING parquet
+
+statement
+INSERT INTO ex_right_multi VALUES (1, 100), (2, 999)
+
+query
+SELECT * FROM ex_left_multi l
+WHERE l.id > 0
+  AND (l.k1 = 1
+       OR EXISTS (SELECT /*+ SHUFFLE_HASH(r) */ 1
+                  FROM ex_right_multi r WHERE r.k1 = l.k1 AND r.k2 = l.k2))
+ORDER BY l.id
+
+-- ============================================================
+-- Marker projected directly (not gated behind OR), so the exists tag itself
+-- is observed rather than masked by another true predicate.
+-- ============================================================
+
+query
+SELECT l.id, EXISTS (SELECT /*+ BROADCAST(r) */ 1 FROM ex_right r WHERE r.k = l.k) AS matched
+FROM ex_left l
+ORDER BY l.id
+
+query
+SELECT l.id, EXISTS (SELECT /*+ SHUFFLE_HASH(r) */ 1 FROM ex_right_dups r WHERE r.k = l.k) AS matched
+FROM ex_left l
+ORDER BY l.id
+
+-- ============================================================
+-- IN combined with OR also lowers to ExistenceJoin.
+-- ============================================================
+
+query
+SELECT * FROM ex_left l
+WHERE l.region = 'US'
+   OR l.k IN (SELECT /*+ BROADCAST(r) */ r.k FROM ex_right r)
+ORDER BY l.id
+
+query
+SELECT * FROM ex_left l
+WHERE l.region = 'US'
+   OR l.k IN (SELECT /*+ SHUFFLE_HASH(r) */ r.k FROM ex_right r)
+ORDER BY l.id
+
+-- ============================================================
+-- String join key: non-collated string keys run natively.
+-- ============================================================
+
+statement
+CREATE TABLE ex_right_str(s string) USING parquet
+
+statement
+INSERT INTO ex_right_str VALUES ('US'), ('APAC')
+
+query
+SELECT l.id, EXISTS (SELECT /*+ BROADCAST(r) */ 1 FROM ex_right_str r WHERE r.s = l.region) AS matched
+FROM ex_left l
+ORDER BY l.id
+
diff --git a/spark/src/test/resources/tpcds-plan-stability/approved-plans-v1_4/q10/extended.txt b/spark/src/test/resources/tpcds-plan-stability/approved-plans-v1_4/q10/extended.txt
index fc9dd6ca79..519c0d422b 100644
--- a/spark/src/test/resources/tpcds-plan-stability/approved-plans-v1_4/q10/extended.txt
+++ b/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.
\ No newline at end of file
diff --git a/spark/src/test/resources/tpcds-plan-stability/approved-plans-v1_4/q35/extended.txt b/spark/src/test/resources/tpcds-plan-stability/approved-plans-v1_4/q35/extended.txt
index 04c2562b8a..0704ae9a8b 100644
--- a/spark/src/test/resources/tpcds-plan-stability/approved-plans-v1_4/q35/extended.txt
+++ b/spark/src/test/resources/tpcds-plan-stability/approved-plans-v1_4/q35/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: 5 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: 11 native, 0 codegen dispatch.
\ No newline at end of file
diff --git a/spark/src/test/resources/tpcds-plan-stability/approved-plans-v1_4/q45/extended.txt b/spark/src/test/resources/tpcds-plan-stability/approved-plans-v1_4/q45/extended.txt
index f8c13ae483..a23e1af159 100644
--- a/spark/src/test/resources/tpcds-plan-stability/approved-plans-v1_4/q45/extended.txt
+++ b/spark/src/test/resources/tpcds-plan-stability/approved-plans-v1_4/q45/extended.txt
@@ -1,46 +1,44 @@
 CometColumnarToRow
 +- CometTakeOrderedAndProject
    +- CometHashAggregate
-      +- CometColumnarExchange
-         +- HashAggregate
-            +- Project
-               +- Filter
-                  +-  BroadcastHashJoin [COMET: Unsupported join type ExistenceJoin(exists#1)]
-                     :- CometColumnarToRow
-                     :  +- CometProject
-                     :     +- CometBroadcastHashJoin
-                     :        :- CometProject
-                     :        :  +- CometBroadcastHashJoin
-                     :        :     :- CometProject
-                     :        :     :  +- CometBroadcastHashJoin
-                     :        :     :     :- CometProject
-                     :        :     :     :  +- CometBroadcastHashJoin
-                     :        :     :     :     :- CometFilter
-                     :        :     :     :     :  +- CometNativeScan parquet spark_catalog.default.web_sales
-                     :        :     :     :     :        +- CometSubqueryBroadcast
-                     :        :     :     :     :           +- CometBroadcastExchange
-                     :        :     :     :     :              +- CometProject
-                     :        :     :     :     :                 +- CometFilter
-                     :        :     :     :     :                    +- CometNativeScan parquet spark_catalog.default.date_dim
-                     :        :     :     :     +- CometBroadcastExchange
-                     :        :     :     :        +- CometFilter
-                     :        :     :     :           +- CometNativeScan parquet spark_catalog.default.customer
-                     :        :     :     +- CometBroadcastExchange
-                     :        :     :        +- CometProject
-                     :        :     :           +- CometFilter
-                     :        :     :              +- CometNativeScan parquet spark_catalog.default.customer_address
-                     :        :     +- CometBroadcastExchange
-                     :        :        +- CometProject
-                     :        :           +- CometFilter
-                     :        :              +- CometNativeScan parquet spark_catalog.default.date_dim
-                     :        +- CometBroadcastExchange
-                     :           +- CometProject
-                     :              +- CometFilter
-                     :                 +- CometNativeScan parquet spark_catalog.default.item
-                     +- BroadcastExchange
-                        +- CometColumnarToRow
-                           +- CometProject
-                              +- CometFilter
-                                 +- CometNativeScan parquet spark_catalog.default.item
+      +- CometExchange
+         +- CometHashAggregate
+            +- CometProject
+               +- CometFilter
+                  +- CometBroadcastHashJoin
+                     :- CometProject
+                     :  +- CometBroadcastHashJoin
+                     :     :- CometProject
+                     :     :  +- CometBroadcastHashJoin
+                     :     :     :- CometProject
+                     :     :     :  +- CometBroadcastHashJoin
+                     :     :     :     :- CometProject
+                     :     :     :     :  +- CometBroadcastHashJoin
+                     :     :     :     :     :- CometFilter
+                     :     :     :     :     :  +- CometNativeScan parquet spark_catalog.default.web_sales
+                     :     :     :     :     :        +- CometSubqueryBroadcast
+                     :     :     :     :     :           +- CometBroadcastExchange
+                     :     :     :     :     :              +- CometProject
+                     :     :     :     :     :                 +- CometFilter
+                     :     :     :     :     :                    +- CometNativeScan parquet spark_catalog.default.date_dim
+                     :     :     :     :     +- CometBroadcastExchange
+                     :     :     :     :        +- CometFilter
+                     :     :     :     :           +- CometNativeScan parquet spark_catalog.default.customer
+                     :     :     :     +- CometBroadcastExchange
+                     :     :     :        +- CometProject
+                     :     :     :           +- CometFilter
+                     :     :     :              +- CometNativeScan parquet spark_catalog.default.customer_address
+                     :     :     +- CometBroadcastExchange
+                     :     :        +- CometProject
+                     :     :           +- CometFilter
+                     :     :              +- CometNativeScan parquet spark_catalog.default.date_dim
+                     :     +- CometBroadcastExchange
+                     :        +- CometProject
+                     :           +- CometFilter
+                     :              +- CometNativeScan parquet spark_catalog.default.item
+                     +- CometBroadcastExchange
+                        +- CometProject
+                           +- CometFilter
+                              +- CometNativeScan parquet spark_catalog.default.item
 
-Comet accelerated 32 out of 37 eligible operators (86%). Final plan contains 3 transitions between Spark and Comet. Accelerated expressions: 9 native, 0 codegen dispatch.
\ No newline at end of file
+Comet accelerated 37 out of 37 eligible operators (100%). Final plan contains 1 transitions between Spark and Comet. Accelerated expressions: 11 native, 0 codegen dispatch.
\ No newline at end of file
diff --git a/spark/src/test/resources/tpcds-plan-stability/approved-plans-v2_7/q35/extended.txt b/spark/src/test/resources/tpcds-plan-stability/approved-plans-v2_7/q35/extended.txt
index 04c2562b8a..0704ae9a8b 100644
--- a/spark/src/test/resources/tpcds-plan-stability/approved-plans-v2_7/q35/extended.txt
+++ b/spark/src/test/resources/tpcds-plan-stability/approved-plans-v2_7/q35/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: 5 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: 11 native, 0 codegen dispatch.
\ No newline at end of file
diff --git a/spark/src/test/scala/org/apache/comet/exec/CometJoinSuite.scala b/spark/src/test/scala/org/apache/comet/exec/CometJoinSuite.scala
index 06da27766d..eec16205a1 100644
--- a/spark/src/test/scala/org/apache/comet/exec/CometJoinSuite.scala
+++ b/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")
+        }
+      }
+    }
+  }
+
   test("scans of one bucketed table in a single native plan each read their own files") {
     withTable("bucketed_self", "bucketed_dim") {
       val rows = for {
diff --git a/spark/src/test/scala/org/apache/spark/sql/benchmark/CometExistenceJoinBenchmark.scala b/spark/src/test/scala/org/apache/spark/sql/benchmark/CometExistenceJoinBenchmark.scala
new file mode 100644
index 0000000000..c33eeb302b
--- /dev/null
+++ b/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 native hash-join
+ * paths (BroadcastHashJoin, ShuffledHashJoin). 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")
+      // Off-heap memory is disabled, so Comet runs in on-heap mode, which must be enabled
+      // explicitly or Comet stays disabled and both arms measure Spark.
+      .set("spark.comet.exec.onHeap.enabled", "true")
+
+    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 * 1024L
+    val buildRows = 10000L
+
+    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)"
+
+        // The existence-join feature flag is Comet-only, but the join-strategy settings are
+        // applied via withSQLConf so they affect BOTH the Spark baseline and the Comet arm --
+        // otherwise Spark broadcasts the small build and the two arms measure different
+        // strategies. Existence SMJ falls back to Spark, so only the native BHJ/SHJ paths are
+        // benchmarked here.
+        val existenceEnabled = Map(CometConf.COMET_EXEC_EXISTENCE_JOIN_ENABLED.key -> "true")
+
+        withSQLConf(
+          SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "10MB",
+          SQLConf.ADAPTIVE_AUTO_BROADCASTJOIN_THRESHOLD.key -> "10MB") {
+          runBenchmark("ExistenceJoin - BroadcastHashJoin") {
+            runExpressionBenchmark(
+              "exists OR predicate (BHJ)",
+              probeRows,
+              query,
+              existenceEnabled)
+          }
+        }
+
+        withSQLConf(
+          SQLConf.PREFER_SORTMERGEJOIN.key -> "false",
+          "spark.sql.join.forceApplyShuffledHashJoin" -> "true",
+          SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+          SQLConf.ADAPTIVE_AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1") {
+          runBenchmark("ExistenceJoin - ShuffledHashJoin") {
+            runExpressionBenchmark(
+              "exists OR predicate (SHJ)",
+              probeRows,
+              query,
+              existenceEnabled)
+          }
+        }
+      }
+    }
+  }
+}
diff --git a/spark/src/test/spark-4.x/org/apache/comet/exec/CometEmptyRelationExecSuite.scala b/spark/src/test/spark-4.x/org/apache/comet/exec/CometEmptyRelationExecSuite.scala
index e8978b1992..a855e4484a 100644
--- a/spark/src/test/spark-4.x/org/apache/comet/exec/CometEmptyRelationExecSuite.scala
+++ b/spark/src/test/spark-4.x/org/apache/comet/exec/CometEmptyRelationExecSuite.scala
@@ -81,7 +81,7 @@ class CometEmptyRelationExecSuite extends CometTestBase {
     }
   }
 
-  test("EmptyRelationExec discovered by AQE feeds native aggregates and Spark existence joins") {
+  test("EmptyRelationExec discovered by AQE feeds native aggregates and native existence joins") {
     withSQLConf(
       SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> "true",
       SQLConf.SHUFFLE_PARTITIONS.key -> "2",
@@ -102,7 +102,8 @@ class CometEmptyRelationExecSuite extends CometTestBase {
           collect(aggregatePlan) { case a: CometHashAggregateExec => a }.nonEmpty,
           aggregatePlan.toString)
 
-        // Existence joins retain Spark's fallback and preserve probe rows with false markers.
+        // Existence joins now run natively over the CometEmptyRelation, preserving probe rows with
+        // false markers.
         val existence = "SELECT l._1, EXISTS (SELECT /*+ BROADCAST(r) */ 1 FROM " +
           s"$empty r WHERE r.k = l._1) AS matched FROM aqe_empty_input l"
         val (_, joinPlan) = checkSparkAnswer(existence)
@@ -111,9 +112,9 @@ class CometEmptyRelationExecSuite extends CometTestBase {
           collect(joinPlan) { case e: CometEmptyRelationExec => e }.nonEmpty,
           joinPlan.toString)
         assert(
-          collect(joinPlan) { case j: BroadcastHashJoinExec => j }.nonEmpty,
+          collect(joinPlan) { case j: CometBroadcastHashJoinExec => j }.nonEmpty,
           joinPlan.toString)
-        assert(collect(joinPlan) { case j: CometBroadcastHashJoinExec => j }.isEmpty)
+        assert(collect(joinPlan) { case j: BroadcastHashJoinExec => j }.isEmpty)
       }
     }
   }


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For additional commands, e-mail: commits-help@datafusion.apache.org