This is an automated email from the ASF dual-hosted git repository.
wenchen pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/master by this push:
new 4fd2d6807bf [SPARK-45009][SQL] Decorrelate predicate subqueries in
join condition
4fd2d6807bf is described below
commit 4fd2d6807bffdfe015b95398aa2206b3145ce598
Author: Andy Lam <[email protected]>
AuthorDate: Mon Oct 16 11:40:16 2023 +0800
[SPARK-45009][SQL] Decorrelate predicate subqueries in join condition
### What changes were proposed in this pull request?
Pulling up correlated subquery predicates in Joins, and re-writing them
into ExistenceJoins if they are not pushed down into the join inputs.
### Why are the changes needed?
This change allows correlated IN and EXISTS subqueries in join condition.
This is valid SQL that is not yet supported by Spark SQL.
### Does this PR introduce _any_ user-facing change?
Yes, previously unsupported queries become supported.
### How was this patch tested?
Added SQL tests for IN and EXISTS in join conditions, and crossed-check
correctness with postgres (except for ANTI joins, which are not supported in
postgres).
Permutations of the tests:
1. Exists / Not exists / in / not in
2. Subquery references left child / right child
3. Join type: inner / left outer
4. Transitive predicates to try invoking filter inference
Closes #42725 from andylam-db/correlated-subquery-in-join-cond.
Authored-by: Andy Lam <[email protected]>
Signed-off-by: Wenchen Fan <[email protected]>
---
.../spark/sql/catalyst/optimizer/Optimizer.scala | 3 +-
.../spark/sql/catalyst/optimizer/subquery.scala | 68 ++
.../spark/sql/errors/QueryCompilationErrors.scala | 9 +
.../org/apache/spark/sql/internal/SQLConf.scala | 19 +
.../exists-in-join-condition.sql.out | 1006 ++++++++++++++++++++
.../subquery/in-subquery/in-null-semantics.sql.out | 46 +
.../in-subquery-in-join-condition.sql.out | 918 ++++++++++++++++++
.../exists-subquery/exists-in-join-condition.sql | 90 ++
.../subquery/in-subquery/in-null-semantics.sql | 9 +-
.../in-subquery/in-subquery-in-join-condition.sql | 85 ++
.../exists-in-join-condition.sql.out | 474 +++++++++
.../subquery/in-subquery/in-null-semantics.sql.out | 42 +
.../in-subquery-in-join-condition.sql.out | 436 +++++++++
13 files changed, 3203 insertions(+), 2 deletions(-)
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/Optimizer.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/Optimizer.scala
index f83cd36f0a8..6f65afada17 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/Optimizer.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/Optimizer.scala
@@ -55,7 +55,8 @@ abstract class Optimizer(catalogManager: CatalogManager)
Set(
"PartitionPruning",
"RewriteSubquery",
- "Extract Python UDFs")
+ "Extract Python UDFs",
+ "Infer Filters")
protected def fixedPoint =
FixedPoint(
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/subquery.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/subquery.scala
index 08cbd29b20f..5b95ee1df1b 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/subquery.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/subquery.scala
@@ -33,6 +33,7 @@ import org.apache.spark.sql.catalyst.rules._
import org.apache.spark.sql.catalyst.trees.TreePattern.{EXISTS_SUBQUERY,
IN_SUBQUERY, LATERAL_JOIN, LIST_SUBQUERY, PLAN_EXPRESSION, SCALAR_SUBQUERY}
import org.apache.spark.sql.errors.{QueryCompilationErrors,
QueryExecutionErrors}
import org.apache.spark.sql.internal.SQLConf
+import
org.apache.spark.sql.internal.SQLConf.{DECORRELATE_PREDICATE_SUBQUERIES_IN_JOIN_CONDITION,
OPTIMIZE_UNCORRELATED_IN_SUBQUERIES_IN_JOIN_CONDITION}
import org.apache.spark.sql.types._
import org.apache.spark.util.Utils
@@ -176,6 +177,71 @@ object RewritePredicateSubquery extends Rule[LogicalPlan]
with PredicateHelper {
Project(p.output, Filter(newCond.get, inputPlan))
}
+ // This case takes care of predicate subqueries in join conditions that
are not pushed down
+ // to the children nodes by [[PushDownPredicates]].
+ case j: Join if j.condition.exists(cond =>
+ SubqueryExpression.hasInOrCorrelatedExistsSubquery(cond)) &&
+ conf.getConf(DECORRELATE_PREDICATE_SUBQUERIES_IN_JOIN_CONDITION) =>
+
+ val optimizeUncorrelatedInSubqueries =
+ conf.getConf(OPTIMIZE_UNCORRELATED_IN_SUBQUERIES_IN_JOIN_CONDITION)
+ val relevantSubqueries = j.condition.get.collect {
+ case i: InSubquery if i.query.isCorrelated => i
+ case i: InSubquery if !i.query.isCorrelated &&
optimizeUncorrelatedInSubqueries => i
+ case e: Exists if e.isCorrelated => e
+ }
+ if (relevantSubqueries.isEmpty) {
+ j
+ } else {
+ // `subqueriesWithJoinInputReferenceInfo`is of type Seq[(Expression,
Boolean, Boolean)]
+ // (1): Expression, the join predicate containing some predicate
subquery we are interested
+ // in re-writing
+ // (2): Boolean, whether (1) references the left join input
+ // (3): Boolean, whether (1) references the right join input
+ val subqueriesWithJoinInputReferenceInfo = relevantSubqueries.map { e
=>
+ val referenceLeft = e.references.intersect(j.left.outputSet).nonEmpty
+ val referenceRight =
e.references.intersect(j.right.outputSet).nonEmpty
+ (e, referenceLeft, referenceRight)
+ }
+ val subqueriesReferencingBothJoinInputs =
subqueriesWithJoinInputReferenceInfo
+ .filter(i => i._2 && i._3)
+
+ // Currently do not support correlated subqueries in the join
predicate that reference both
+ // join inputs
+ if (subqueriesReferencingBothJoinInputs.nonEmpty) {
+ throw
QueryCompilationErrors.unsupportedCorrelatedSubqueryInJoinConditionError(
+ subqueriesReferencingBothJoinInputs.map(_._1))
+ }
+ val subqueriesReferencingLeft =
subqueriesWithJoinInputReferenceInfo.filter(_._2).map(_._1)
+ val subqueriesReferencingRight =
subqueriesWithJoinInputReferenceInfo.filter(_._3).map(_._1)
+ if (subqueriesReferencingLeft.isEmpty &&
subqueriesReferencingRight.isEmpty) {
+ j
+ } else {
+ var newCondition = j.condition.get
+ val newLeft = subqueriesReferencingLeft.foldLeft(j.left) {
+ case (p, e) =>
+ val (newCond, newInputPlan) = rewriteExistentialExpr(Seq(e), p)
+ // Update the join condition to rewrite the subquery expression
+ newCondition = newCondition.transform {
+ case expr if expr.fastEquals(e) => newCond.get
+ }
+ newInputPlan
+ }
+ val newRight = subqueriesReferencingRight.foldLeft(j.right) {
+ case (p, e) =>
+ val (newCond, newInputPlan) = rewriteExistentialExpr(Seq(e), p)
+ // Update the join condition to rewrite the subquery expression
+ newCondition = newCondition.transform {
+ case expr if expr.fastEquals(e) => newCond.get
+ }
+ newInputPlan
+ }
+ // Remove unwanted exists columns from new existence joins with new
Project
+ Project(j.output, j.copy(left = newLeft, right = newRight,
+ condition = Some(newCondition)))
+ }
+ }
+
case u: UnaryNode if u.expressions.exists(
SubqueryExpression.hasInOrCorrelatedExistsSubquery) =>
var newChild = u.child
@@ -410,6 +476,8 @@ object PullupCorrelatedPredicates extends Rule[LogicalPlan]
with PredicateHelper
} else {
newPlan
}
+ case j: Join if
conf.getConf(DECORRELATE_PREDICATE_SUBQUERIES_IN_JOIN_CONDITION) =>
+ rewriteSubQueries(j)
// Only a few unary nodes (Project/Filter/Aggregate) can contain
subqueries.
case q: UnaryNode =>
rewriteSubQueries(q)
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryCompilationErrors.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryCompilationErrors.scala
index 2f2341ab47f..5dfdd7757ad 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryCompilationErrors.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryCompilationErrors.scala
@@ -2099,6 +2099,15 @@ private[sql] object QueryCompilationErrors extends
QueryErrorsBase with Compilat
messageParameters = Map("expr" -> expr.sql, "dataType" ->
dataType.typeName))
}
+ def unsupportedCorrelatedSubqueryInJoinConditionError(
+ unsupportedSubqueryExpressions: Seq[Expression]): Throwable = {
+ new AnalysisException(
+ errorClass = "UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY." +
+ "UNSUPPORTED_CORRELATED_EXPRESSION_IN_JOIN_CONDITION",
+ messageParameters = Map("subqueryExpression" ->
+ unsupportedSubqueryExpressions.map(_.sql).mkString(", ")))
+ }
+
def functionCannotProcessInputError(
unbound: UnboundFunction,
arguments: Seq[Expression],
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala
index 000694f6f1b..9fd70692c06 100644
--- a/sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala
+++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala
@@ -4455,6 +4455,25 @@ object SQLConf {
.booleanConf
.createWithDefault(true)
+ val DECORRELATE_PREDICATE_SUBQUERIES_IN_JOIN_CONDITION =
+
buildConf("spark.sql.optimizer.decorrelatePredicateSubqueriesInJoinPredicate.enabled")
+ .internal()
+ .doc("Decorrelate predicate (in and exists) subqueries with correlated
references in join " +
+ "predicates.")
+ .version("4.0.0")
+ .booleanConf
+ .createWithDefault(true)
+
+ val OPTIMIZE_UNCORRELATED_IN_SUBQUERIES_IN_JOIN_CONDITION =
+
buildConf("spark.sql.optimizer.optimizeUncorrelatedInSubqueriesInJoinCondition.enabled")
+ .internal()
+ .doc("When true, optimize uncorrelated IN subqueries in join predicates
by rewriting them " +
+ s"to joins. This interacts with
${LEGACY_NULL_IN_EMPTY_LIST_BEHAVIOR.key} because it " +
+ "can rewrite IN predicates.")
+ .version("4.0.0")
+ .booleanConf
+ .createWithDefault(true)
+
val LEGACY_PERCENTILE_DISC_CALCULATION =
buildConf("spark.sql.legacy.percentileDiscCalculation")
.internal()
.doc("If true, the old bogus percentile_disc calculation is used. The old
calculation " +
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/subquery/exists-subquery/exists-in-join-condition.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/subquery/exists-subquery/exists-in-join-condition.sql.out
new file mode 100644
index 00000000000..035bbf9ec60
--- /dev/null
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/subquery/exists-subquery/exists-in-join-condition.sql.out
@@ -0,0 +1,1006 @@
+-- Automatically generated by SQLQueryTestSuite
+-- !query
+CREATE TEMP VIEW x(x1, x2) AS VALUES
+ (2, 1),
+ (1, 1),
+ (3, 4)
+-- !query analysis
+CreateViewCommand `x`, [(x1,None), (x2,None)], VALUES
+ (2, 1),
+ (1, 1),
+ (3, 4), false, false, LocalTempView, true
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+CREATE TEMP VIEW y(y1, y2) AS VALUES
+ (0, 2),
+ (1, 4),
+ (4, 11)
+-- !query analysis
+CreateViewCommand `y`, [(y1,None), (y2,None)], VALUES
+ (0, 2),
+ (1, 4),
+ (4, 11), false, false, LocalTempView, true
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+CREATE TEMP VIEW z(z1, z2) AS VALUES
+ (4, 2),
+ (3, 3),
+ (8, 1)
+-- !query analysis
+CreateViewCommand `z`, [(z1,None), (z2,None)], VALUES
+ (4, 2),
+ (3, 3),
+ (8, 1), false, false, LocalTempView, true
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x inner join y on x1 = y1 and exists (select * from z where z2 =
x2) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join Inner, ((x1#x = y1#x) AND exists#x [x2#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z2#x = outer(x2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x inner join y on x1 = y1 and not exists (select * from z where
z2 = x2) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join Inner, ((x1#x = y1#x) AND NOT exists#x [x2#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z2#x = outer(x2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x inner join y on x1 = y1 and exists (select * from z where z2 =
y2) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join Inner, ((x1#x = y1#x) AND exists#x [y2#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z2#x = outer(y2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x inner join y on x1 = y1 and not exists (select * from z where
z2 = y2) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join Inner, ((x1#x = y1#x) AND NOT exists#x [y2#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z2#x = outer(y2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left join y on x1 = y1 and exists (select * from z where z2 =
x2) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join LeftOuter, ((x1#x = y1#x) AND exists#x [x2#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z2#x = outer(x2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left join y on x1 = y1 and not exists (select * from z where
z2 = x2) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join LeftOuter, ((x1#x = y1#x) AND NOT exists#x [x2#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z2#x = outer(x2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left join y on x1 = y1 and exists (select * from z where z2 =
y2) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join LeftOuter, ((x1#x = y1#x) AND exists#x [y2#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z2#x = outer(y2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left join y on x1 = y1 and not exists (select * from z where
z2 = y2) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join LeftOuter, ((x1#x = y1#x) AND NOT exists#x [y2#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z2#x = outer(y2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x right join y on x1 = y1 and exists (select * from z where z2 =
x2) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join RightOuter, ((x1#x = y1#x) AND exists#x [x2#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z2#x = outer(x2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x right join y on x1 = y1 and not exists (select * from z where
z2 = x2) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join RightOuter, ((x1#x = y1#x) AND NOT exists#x [x2#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z2#x = outer(x2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x right join y on x1 = y1 and exists (select * from z where z2 =
y2) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join RightOuter, ((x1#x = y1#x) AND exists#x [y2#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z2#x = outer(y2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x right join y on x1 = y1 and not exists (select * from z where
z2 = y2) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join RightOuter, ((x1#x = y1#x) AND NOT exists#x [y2#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z2#x = outer(y2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x right join y on x1 = y1 and exists (select * from z where z2 =
x2) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join RightOuter, ((x1#x = y1#x) AND exists#x [x2#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z2#x = outer(x2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x right join y on x1 = y1 and not exists (select * from z where
z2 = x2) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join RightOuter, ((x1#x = y1#x) AND NOT exists#x [x2#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z2#x = outer(x2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x right join y on x1 = y1 and exists (select * from z where z2 =
y2) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join RightOuter, ((x1#x = y1#x) AND exists#x [y2#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z2#x = outer(y2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x right join y on x1 = y1 and not exists (select * from z where
z2 = y2) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join RightOuter, ((x1#x = y1#x) AND NOT exists#x [y2#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z2#x = outer(y2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left semi join y on x1 = y1 and exists (select * from z where
z2 = x2) order by x1, x2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST], true
++- Project [x1#x, x2#x]
+ +- Join LeftSemi, ((x1#x = y1#x) AND exists#x [x2#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z2#x = outer(x2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left semi join y on x1 = y1 and not exists (select * from z
where z2 = x2) order by x1, x2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST], true
++- Project [x1#x, x2#x]
+ +- Join LeftSemi, ((x1#x = y1#x) AND NOT exists#x [x2#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z2#x = outer(x2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left semi join y on x1 = y1 and exists (select * from z where
z2 = y2) order by x1, x2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST], true
++- Project [x1#x, x2#x]
+ +- Join LeftSemi, ((x1#x = y1#x) AND exists#x [y2#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z2#x = outer(y2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left semi join y on x1 = y1 and not exists (select * from z
where z2 = y2) order by x1, x2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST], true
++- Project [x1#x, x2#x]
+ +- Join LeftSemi, ((x1#x = y1#x) AND NOT exists#x [y2#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z2#x = outer(y2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left anti join y on x1 = y1 and exists (select * from z where
z2 = x2) order by x1, x2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST], true
++- Project [x1#x, x2#x]
+ +- Join LeftAnti, ((x1#x = y1#x) AND exists#x [x2#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z2#x = outer(x2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left anti join y on x1 = y1 and not exists (select * from z
where z2 = x2) order by x1, x2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST], true
++- Project [x1#x, x2#x]
+ +- Join LeftAnti, ((x1#x = y1#x) AND NOT exists#x [x2#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z2#x = outer(x2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left anti join y on x1 = y1 and exists (select * from z where
z2 = y2) order by x1, x2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST], true
++- Project [x1#x, x2#x]
+ +- Join LeftAnti, ((x1#x = y1#x) AND exists#x [y2#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z2#x = outer(y2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left anti join y on x1 = y1 and not exists (select * from z
where z2 = y2) order by x1, x2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST], true
++- Project [x1#x, x2#x]
+ +- Join LeftAnti, ((x1#x = y1#x) AND NOT exists#x [y2#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z2#x = outer(y2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x full outer join y on x1 = y1 and exists (select * from z where
z2 = x2) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join FullOuter, ((x1#x = y1#x) AND exists#x [x2#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z2#x = outer(x2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x full outer join y on x1 = y1 and not exists (select * from z
where z2 = x2) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join FullOuter, ((x1#x = y1#x) AND NOT exists#x [x2#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z2#x = outer(x2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x full outer join y on x1 = y1 and exists (select * from z where
z2 = y2) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join FullOuter, ((x1#x = y1#x) AND exists#x [y2#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z2#x = outer(y2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x full outer join y on x1 = y1 and not exists (select * from z
where z2 = y2) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join FullOuter, ((x1#x = y1#x) AND NOT exists#x [y2#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z2#x = outer(y2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x inner join y on x1 = y1 or exists (select * from z where z1 =
x1) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join Inner, ((x1#x = y1#x) OR exists#x [x1#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z1#x = outer(x1#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x inner join y on x1 = y1 or not exists (select * from z where
z1 = x1) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join Inner, ((x1#x = y1#x) OR NOT exists#x [x1#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z1#x = outer(x1#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left join y on x1 = y1 or exists (select * from z where z1 =
x1) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join LeftOuter, ((x1#x = y1#x) OR exists#x [x1#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z1#x = outer(x1#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left join y on x1 = y1 or not exists (select * from z where z1
= x1) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join LeftOuter, ((x1#x = y1#x) OR NOT exists#x [x1#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z1#x = outer(x1#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x inner join y on x1 = y1 or exists (select * from z where z1 =
y1) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join Inner, ((x1#x = y1#x) OR exists#x [y1#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z1#x = outer(y1#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x inner join y on x1 = y1 or not exists (select * from z where
z1 = y1) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join Inner, ((x1#x = y1#x) OR NOT exists#x [y1#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z1#x = outer(y1#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left join y on x1 = y1 or exists (select * from z where z1 =
y1) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join LeftOuter, ((x1#x = y1#x) OR exists#x [y1#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z1#x = outer(y1#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left join y on x1 = y1 or not exists (select * from z where z1
= y1) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join LeftOuter, ((x1#x = y1#x) OR NOT exists#x [y1#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z1#x = outer(y1#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x inner join y on x1 = y1 and exists (select * from z where z1 =
x1) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join Inner, ((x1#x = y1#x) AND exists#x [x1#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z1#x = outer(x1#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x inner join y on x1 = y1 and not exists (select * from z where
z1 = x1) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join Inner, ((x1#x = y1#x) AND NOT exists#x [x1#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z1#x = outer(x1#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left join y on x1 = y1 and exists (select * from z where z1 =
x1) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join LeftOuter, ((x1#x = y1#x) AND exists#x [x1#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z1#x = outer(x1#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left join y on x1 = y1 and not exists (select * from z where
z1 = x1) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join LeftOuter, ((x1#x = y1#x) AND NOT exists#x [x1#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z1#x = outer(x1#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x inner join y on x1 = y1 and exists (select * from z where z1 =
y1) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join Inner, ((x1#x = y1#x) AND exists#x [y1#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z1#x = outer(y1#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x inner join y on x1 = y1 and not exists (select * from z where
z1 = y1) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join Inner, ((x1#x = y1#x) AND NOT exists#x [y1#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z1#x = outer(y1#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left join y on x1 = y1 and exists (select * from z where z1 =
y1) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join LeftOuter, ((x1#x = y1#x) AND exists#x [y1#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z1#x = outer(y1#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left join y on x1 = y1 and not exists (select * from z where
z1 = y1) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join LeftOuter, ((x1#x = y1#x) AND NOT exists#x [y1#x])
+ : +- Project [z1#x, z2#x]
+ : +- Filter (z1#x = outer(y1#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/subquery/in-subquery/in-null-semantics.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/subquery/in-subquery/in-null-semantics.sql.out
index ac5c41dd307..7816958795d 100644
---
a/sql/core/src/test/resources/sql-tests/analyzer-results/subquery/in-subquery/in-null-semantics.sql.out
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/subquery/in-subquery/in-null-semantics.sql.out
@@ -163,6 +163,40 @@ Project [NOT cast(null as int) IN (list#x []) AS (NOT
(NULL IN (listquery())))#x
+- OneRowRelation
+-- !query
+select * from t left join t2 on (t.c in (select e from t_empty)) is null
+-- !query analysis
+Project [c#x, d#x]
++- Join LeftOuter, isnull(c#x IN (list#x []))
+ : +- Project [e#x]
+ : +- SubqueryAlias spark_catalog.default.t_empty
+ : +- Relation spark_catalog.default.t_empty[e#x] json
+ :- SubqueryAlias spark_catalog.default.t
+ : +- Relation spark_catalog.default.t[c#x] json
+ +- SubqueryAlias spark_catalog.default.t2
+ +- Relation spark_catalog.default.t2[d#x] json
+
+
+-- !query
+select * from t left join t2 on (t.c not in (select e from t_empty)) is null
+-- !query analysis
+Project [c#x, d#x]
++- Join LeftOuter, isnull(NOT c#x IN (list#x []))
+ : +- Project [e#x]
+ : +- SubqueryAlias spark_catalog.default.t_empty
+ : +- Relation spark_catalog.default.t_empty[e#x] json
+ :- SubqueryAlias spark_catalog.default.t
+ : +- Relation spark_catalog.default.t[c#x] json
+ +- SubqueryAlias spark_catalog.default.t2
+ +- Relation spark_catalog.default.t2[d#x] json
+
+
+-- !query
+set
spark.sql.optimizer.optimizeUncorrelatedInSubqueriesInJoinCondition.enabled=true
+-- !query analysis
+SetCommand
(spark.sql.optimizer.optimizeUncorrelatedInSubqueriesInJoinCondition.enabled,Some(true))
+
+
-- !query
select * from t left join t2 on (t.c in (select e from t_empty)) is null
-- !query analysis
@@ -197,6 +231,12 @@ set spark.sql.legacy.nullInEmptyListBehavior = true
SetCommand (spark.sql.legacy.nullInEmptyListBehavior,Some(true))
+-- !query
+set
spark.sql.optimizer.optimizeUncorrelatedInSubqueriesInJoinCondition.enabled=false
+-- !query analysis
+SetCommand
(spark.sql.optimizer.optimizeUncorrelatedInSubqueriesInJoinCondition.enabled,Some(false))
+
+
-- !query
select null in (select e from t_empty)
-- !query analysis
@@ -283,6 +323,12 @@ reset spark.sql.legacy.nullInEmptyListBehavior
ResetCommand spark.sql.legacy.nullInEmptyListBehavior
+-- !query
+reset
spark.sql.optimizer.optimizeUncorrelatedInSubqueriesInJoinCondition.enabled
+-- !query analysis
+ResetCommand
spark.sql.optimizer.optimizeUncorrelatedInSubqueriesInJoinCondition.enabled
+
+
-- !query
drop table t
-- !query analysis
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/subquery/in-subquery/in-subquery-in-join-condition.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/subquery/in-subquery/in-subquery-in-join-condition.sql.out
new file mode 100644
index 00000000000..c69988358fc
--- /dev/null
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/subquery/in-subquery/in-subquery-in-join-condition.sql.out
@@ -0,0 +1,918 @@
+-- Automatically generated by SQLQueryTestSuite
+-- !query
+CREATE TEMP VIEW x(x1, x2) AS VALUES
+ (2, 1),
+ (1, 1),
+ (3, 4)
+-- !query analysis
+CreateViewCommand `x`, [(x1,None), (x2,None)], VALUES
+ (2, 1),
+ (1, 1),
+ (3, 4), false, false, LocalTempView, true
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+CREATE TEMP VIEW y(y1, y2) AS VALUES
+ (0, 2),
+ (1, 4),
+ (4, 11)
+-- !query analysis
+CreateViewCommand `y`, [(y1,None), (y2,None)], VALUES
+ (0, 2),
+ (1, 4),
+ (4, 11), false, false, LocalTempView, true
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+CREATE TEMP VIEW z(z1, z2) AS VALUES
+ (4, 2),
+ (3, 3),
+ (8, 1)
+-- !query analysis
+CreateViewCommand `z`, [(z1,None), (z2,None)], VALUES
+ (4, 2),
+ (3, 3),
+ (8, 1), false, false, LocalTempView, true
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x inner join y on x1 = y1 and x2 IN (select z1 from z where z2 =
x2) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join Inner, ((x1#x = y1#x) AND x2#x IN (list#x [x2#x]))
+ : +- Project [z1#x]
+ : +- Filter (z2#x = outer(x2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x inner join y on x1 = y1 and x2 not IN (select z1 from z where
z2 = x2) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join Inner, ((x1#x = y1#x) AND NOT x2#x IN (list#x [x2#x]))
+ : +- Project [z1#x]
+ : +- Filter (z2#x = outer(x2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x inner join y on x1 = y1 and y2 IN (select z1 from z where z2 =
y2) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join Inner, ((x1#x = y1#x) AND y2#x IN (list#x [y2#x]))
+ : +- Project [z1#x]
+ : +- Filter (z2#x = outer(y2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x inner join y on x1 = y1 and y2 not IN (select z1 from z where
z2 = y2) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join Inner, ((x1#x = y1#x) AND NOT y2#x IN (list#x [y2#x]))
+ : +- Project [z1#x]
+ : +- Filter (z2#x = outer(y2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left join y on x1 = y1 and x2 IN (select z1 from z where z2 =
x2) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join LeftOuter, ((x1#x = y1#x) AND x2#x IN (list#x [x2#x]))
+ : +- Project [z1#x]
+ : +- Filter (z2#x = outer(x2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left join y on x1 = y1 and x2 not IN (select z1 from z where
z2 = x2) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join LeftOuter, ((x1#x = y1#x) AND NOT x2#x IN (list#x [x2#x]))
+ : +- Project [z1#x]
+ : +- Filter (z2#x = outer(x2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left join y on x1 = y1 and y2 IN (select z1 from z where z2 =
y2) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join LeftOuter, ((x1#x = y1#x) AND y2#x IN (list#x [y2#x]))
+ : +- Project [z1#x]
+ : +- Filter (z2#x = outer(y2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left join y on x1 = y1 and y2 not IN (select z1 from z where
z2 = y2) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join LeftOuter, ((x1#x = y1#x) AND NOT y2#x IN (list#x [y2#x]))
+ : +- Project [z1#x]
+ : +- Filter (z2#x = outer(y2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x right join y on x1 = y1 and x2 IN (select z1 from z where z2 =
x2) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join RightOuter, ((x1#x = y1#x) AND x2#x IN (list#x [x2#x]))
+ : +- Project [z1#x]
+ : +- Filter (z2#x = outer(x2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x right join y on x1 = y1 and x2 not IN (select z1 from z where
z2 = x2) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join RightOuter, ((x1#x = y1#x) AND NOT x2#x IN (list#x [x2#x]))
+ : +- Project [z1#x]
+ : +- Filter (z2#x = outer(x2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x right join y on x1 = y1 and y2 IN (select z1 from z where z2 =
y2) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join RightOuter, ((x1#x = y1#x) AND y2#x IN (list#x [y2#x]))
+ : +- Project [z1#x]
+ : +- Filter (z2#x = outer(y2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x right join y on x1 = y1 and y2 not IN (select z1 from z where
z2 = y2) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join RightOuter, ((x1#x = y1#x) AND NOT y2#x IN (list#x [y2#x]))
+ : +- Project [z1#x]
+ : +- Filter (z2#x = outer(y2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left semi join y on x1 = y1 and x2 IN (select z1 from z where
z2 = x2) order by x1, x2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST], true
++- Project [x1#x, x2#x]
+ +- Join LeftSemi, ((x1#x = y1#x) AND x2#x IN (list#x [x2#x]))
+ : +- Project [z1#x]
+ : +- Filter (z2#x = outer(x2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left semi join y on x1 = y1 and x2 not IN (select z1 from z
where z2 = x2) order by x1, x2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST], true
++- Project [x1#x, x2#x]
+ +- Join LeftSemi, ((x1#x = y1#x) AND NOT x2#x IN (list#x [x2#x]))
+ : +- Project [z1#x]
+ : +- Filter (z2#x = outer(x2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left semi join y on x1 = y1 and y2 IN (select z1 from z where
z2 = y2) order by x1, x2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST], true
++- Project [x1#x, x2#x]
+ +- Join LeftSemi, ((x1#x = y1#x) AND y2#x IN (list#x [y2#x]))
+ : +- Project [z1#x]
+ : +- Filter (z2#x = outer(y2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left semi join y on x1 = y1 and y2 not IN (select z1 from z
where z2 = y2) order by x1, x2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST], true
++- Project [x1#x, x2#x]
+ +- Join LeftSemi, ((x1#x = y1#x) AND NOT y2#x IN (list#x [y2#x]))
+ : +- Project [z1#x]
+ : +- Filter (z2#x = outer(y2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left anti join y on x1 = y1 and x2 IN (select z1 from z where
z2 = x2) order by x1, x2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST], true
++- Project [x1#x, x2#x]
+ +- Join LeftAnti, ((x1#x = y1#x) AND x2#x IN (list#x [x2#x]))
+ : +- Project [z1#x]
+ : +- Filter (z2#x = outer(x2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left anti join y on x1 = y1 and x2 not IN (select z1 from z
where z2 = x2) order by x1, x2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST], true
++- Project [x1#x, x2#x]
+ +- Join LeftAnti, ((x1#x = y1#x) AND NOT x2#x IN (list#x [x2#x]))
+ : +- Project [z1#x]
+ : +- Filter (z2#x = outer(x2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left anti join y on x1 = y1 and y2 IN (select z1 from z where
z2 = y2) order by x1, x2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST], true
++- Project [x1#x, x2#x]
+ +- Join LeftAnti, ((x1#x = y1#x) AND y2#x IN (list#x [y2#x]))
+ : +- Project [z1#x]
+ : +- Filter (z2#x = outer(y2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left anti join y on x1 = y1 and y2 not IN (select z1 from z
where z2 = y2) order by x1, x2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST], true
++- Project [x1#x, x2#x]
+ +- Join LeftAnti, ((x1#x = y1#x) AND NOT y2#x IN (list#x [y2#x]))
+ : +- Project [z1#x]
+ : +- Filter (z2#x = outer(y2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x full outer join y on x1 = y1 and x2 IN (select z1 from z where
z2 = x2) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join FullOuter, ((x1#x = y1#x) AND x2#x IN (list#x [x2#x]))
+ : +- Project [z1#x]
+ : +- Filter (z2#x = outer(x2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x full outer join y on x1 = y1 and x2 not IN (select z1 from z
where z2 = x2) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join FullOuter, ((x1#x = y1#x) AND NOT x2#x IN (list#x [x2#x]))
+ : +- Project [z1#x]
+ : +- Filter (z2#x = outer(x2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x full outer join y on x1 = y1 and y2 IN (select z1 from z where
z2 = y2) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join FullOuter, ((x1#x = y1#x) AND y2#x IN (list#x [y2#x]))
+ : +- Project [z1#x]
+ : +- Filter (z2#x = outer(y2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x full outer join y on x1 = y1 and y2 not IN (select z1 from z
where z2 = y2) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join FullOuter, ((x1#x = y1#x) AND NOT y2#x IN (list#x [y2#x]))
+ : +- Project [z1#x]
+ : +- Filter (z2#x = outer(y2#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x inner join y on x1 = y1 or x2 IN (select z1 from z where z1 =
x1) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join Inner, ((x1#x = y1#x) OR x2#x IN (list#x [x1#x]))
+ : +- Project [z1#x]
+ : +- Filter (z1#x = outer(x1#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x inner join y on x1 = y1 or x2 not IN (select z1 from z where
z1 = x1) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join Inner, ((x1#x = y1#x) OR NOT x2#x IN (list#x [x1#x]))
+ : +- Project [z1#x]
+ : +- Filter (z1#x = outer(x1#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left join y on x1 = y1 or x2 IN (select z1 from z where z1 =
x1) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join LeftOuter, ((x1#x = y1#x) OR x2#x IN (list#x [x1#x]))
+ : +- Project [z1#x]
+ : +- Filter (z1#x = outer(x1#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left join y on x1 = y1 or x2 not IN (select z1 from z where z1
= x1) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join LeftOuter, ((x1#x = y1#x) OR NOT x2#x IN (list#x [x1#x]))
+ : +- Project [z1#x]
+ : +- Filter (z1#x = outer(x1#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x inner join y on x1 = y1 or y2 IN (select z1 from z where z1 =
y1) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join Inner, ((x1#x = y1#x) OR y2#x IN (list#x [y1#x]))
+ : +- Project [z1#x]
+ : +- Filter (z1#x = outer(y1#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x inner join y on x1 = y1 or y2 not IN (select z1 from z where
z1 = y1) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join Inner, ((x1#x = y1#x) OR NOT y2#x IN (list#x [y1#x]))
+ : +- Project [z1#x]
+ : +- Filter (z1#x = outer(y1#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left join y on x1 = y1 or y2 IN (select z1 from z where z1 =
y1) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join LeftOuter, ((x1#x = y1#x) OR y2#x IN (list#x [y1#x]))
+ : +- Project [z1#x]
+ : +- Filter (z1#x = outer(y1#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left join y on x1 = y1 or y2 not IN (select z1 from z where z1
= y1) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join LeftOuter, ((x1#x = y1#x) OR NOT y2#x IN (list#x [y1#x]))
+ : +- Project [z1#x]
+ : +- Filter (z1#x = outer(y1#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x inner join y on x1 = y1 and x2 IN (select z1 from z where z1 =
x1) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join Inner, ((x1#x = y1#x) AND x2#x IN (list#x [x1#x]))
+ : +- Project [z1#x]
+ : +- Filter (z1#x = outer(x1#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x inner join y on x1 = y1 and x2 not IN (select z1 from z where
z1 = x1) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join Inner, ((x1#x = y1#x) AND NOT x2#x IN (list#x [x1#x]))
+ : +- Project [z1#x]
+ : +- Filter (z1#x = outer(x1#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left join y on x1 = y1 and x2 IN (select z1 from z where z1 =
x1) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join LeftOuter, ((x1#x = y1#x) AND x2#x IN (list#x [x1#x]))
+ : +- Project [z1#x]
+ : +- Filter (z1#x = outer(x1#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left join y on x1 = y1 and x2 not IN (select z1 from z where
z1 = x1) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join LeftOuter, ((x1#x = y1#x) AND NOT x2#x IN (list#x [x1#x]))
+ : +- Project [z1#x]
+ : +- Filter (z1#x = outer(x1#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x inner join y on x1 = y1 and y2 IN (select z1 from z where z1 =
y1) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join Inner, ((x1#x = y1#x) AND y2#x IN (list#x [y1#x]))
+ : +- Project [z1#x]
+ : +- Filter (z1#x = outer(y1#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x inner join y on x1 = y1 and y2 not IN (select z1 from z where
z1 = y1) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join Inner, ((x1#x = y1#x) AND NOT y2#x IN (list#x [y1#x]))
+ : +- Project [z1#x]
+ : +- Filter (z1#x = outer(y1#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left join y on x1 = y1 and y2 IN (select z1 from z where z1 =
y1) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join LeftOuter, ((x1#x = y1#x) AND y2#x IN (list#x [y1#x]))
+ : +- Project [z1#x]
+ : +- Filter (z1#x = outer(y1#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+select * from x left join y on x1 = y1 and y2 not IN (select z1 from z where
z1 = y1) order by x1, x2, y1, y2
+-- !query analysis
+Sort [x1#x ASC NULLS FIRST, x2#x ASC NULLS FIRST, y1#x ASC NULLS FIRST, y2#x
ASC NULLS FIRST], true
++- Project [x1#x, x2#x, y1#x, y2#x]
+ +- Join LeftOuter, ((x1#x = y1#x) AND NOT y2#x IN (list#x [y1#x]))
+ : +- Project [z1#x]
+ : +- Filter (z1#x = outer(y1#x))
+ : +- SubqueryAlias z
+ : +- View (`z`, [z1#x,z2#x])
+ : +- Project [cast(col1#x as int) AS z1#x, cast(col2#x as
int) AS z2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ :- SubqueryAlias x
+ : +- View (`x`, [x1#x,x2#x])
+ : +- Project [cast(col1#x as int) AS x1#x, cast(col2#x as int) AS
x2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias y
+ +- View (`y`, [y1#x,y2#x])
+ +- Project [cast(col1#x as int) AS y1#x, cast(col2#x as int) AS
y2#x]
+ +- LocalRelation [col1#x, col2#x]
diff --git
a/sql/core/src/test/resources/sql-tests/inputs/subquery/exists-subquery/exists-in-join-condition.sql
b/sql/core/src/test/resources/sql-tests/inputs/subquery/exists-subquery/exists-in-join-condition.sql
new file mode 100644
index 00000000000..5cff53d3381
--- /dev/null
+++
b/sql/core/src/test/resources/sql-tests/inputs/subquery/exists-subquery/exists-in-join-condition.sql
@@ -0,0 +1,90 @@
+-- Test that correlated EXISTS subqueries in join conditions are supported.
+
+-- Permutations of the test:
+-- 1. Exists / Not Exists
+-- 2. Reference left / right child
+-- 3. Join type: inner / left outer / right outer / full outer / left semi /
left anti
+-- 4. AND or OR for the join condition
+
+CREATE TEMP VIEW x(x1, x2) AS VALUES
+ (2, 1),
+ (1, 1),
+ (3, 4);
+
+CREATE TEMP VIEW y(y1, y2) AS VALUES
+ (0, 2),
+ (1, 4),
+ (4, 11);
+
+CREATE TEMP VIEW z(z1, z2) AS VALUES
+ (4, 2),
+ (3, 3),
+ (8, 1);
+
+-- Correlated EXISTS, REFERENCE LEFT, INNER JOIN
+select * from x inner join y on x1 = y1 and exists (select * from z where z2 =
x2) order by x1, x2, y1, y2;
+
+-- Correlated NOT EXISTS, REFERENCE LEFT, INNER JOIN
+select * from x inner join y on x1 = y1 and not exists (select * from z where
z2 = x2) order by x1, x2, y1, y2;
+
+-- Correlated EXISTS, REFERENCE RIGHT, INNER JOIN
+select * from x inner join y on x1 = y1 and exists (select * from z where z2 =
y2) order by x1, x2, y1, y2;
+
+-- Correlated NOT EXISTS, REFERENCE RIGHT, INNER JOIN
+select * from x inner join y on x1 = y1 and not exists (select * from z where
z2 = y2) order by x1, x2, y1, y2;
+
+-- Same as above, but for left outer join
+select * from x left join y on x1 = y1 and exists (select * from z where z2 =
x2) order by x1, x2, y1, y2;
+select * from x left join y on x1 = y1 and not exists (select * from z where
z2 = x2) order by x1, x2, y1, y2;
+select * from x left join y on x1 = y1 and exists (select * from z where z2 =
y2) order by x1, x2, y1, y2;
+select * from x left join y on x1 = y1 and not exists (select * from z where
z2 = y2) order by x1, x2, y1, y2;
+
+-- Same as above, but for right outer join
+select * from x right join y on x1 = y1 and exists (select * from z where z2 =
x2) order by x1, x2, y1, y2;
+select * from x right join y on x1 = y1 and not exists (select * from z where
z2 = x2) order by x1, x2, y1, y2;
+select * from x right join y on x1 = y1 and exists (select * from z where z2 =
y2) order by x1, x2, y1, y2;
+select * from x right join y on x1 = y1 and not exists (select * from z where
z2 = y2) order by x1, x2, y1, y2;
+
+-- Same as above, but for full outer join
+select * from x right join y on x1 = y1 and exists (select * from z where z2 =
x2) order by x1, x2, y1, y2;
+select * from x right join y on x1 = y1 and not exists (select * from z where
z2 = x2) order by x1, x2, y1, y2;
+select * from x right join y on x1 = y1 and exists (select * from z where z2 =
y2) order by x1, x2, y1, y2;
+select * from x right join y on x1 = y1 and not exists (select * from z where
z2 = y2) order by x1, x2, y1, y2;
+
+-- Same as above, but for left semi join
+select * from x left semi join y on x1 = y1 and exists (select * from z where
z2 = x2) order by x1, x2;
+select * from x left semi join y on x1 = y1 and not exists (select * from z
where z2 = x2) order by x1, x2;
+select * from x left semi join y on x1 = y1 and exists (select * from z where
z2 = y2) order by x1, x2;
+select * from x left semi join y on x1 = y1 and not exists (select * from z
where z2 = y2) order by x1, x2;
+
+-- Same as above, but for left anti join
+select * from x left anti join y on x1 = y1 and exists (select * from z where
z2 = x2) order by x1, x2;
+select * from x left anti join y on x1 = y1 and not exists (select * from z
where z2 = x2) order by x1, x2;
+select * from x left anti join y on x1 = y1 and exists (select * from z where
z2 = y2) order by x1, x2;
+select * from x left anti join y on x1 = y1 and not exists (select * from z
where z2 = y2) order by x1, x2;
+
+-- Same as above, but for full outer join
+select * from x full outer join y on x1 = y1 and exists (select * from z where
z2 = x2) order by x1, x2, y1, y2;
+select * from x full outer join y on x1 = y1 and not exists (select * from z
where z2 = x2) order by x1, x2, y1, y2;
+select * from x full outer join y on x1 = y1 and exists (select * from z where
z2 = y2) order by x1, x2, y1, y2;
+select * from x full outer join y on x1 = y1 and not exists (select * from z
where z2 = y2) order by x1, x2, y1, y2;
+
+-- OR instead of AND in the join condition
+select * from x inner join y on x1 = y1 or exists (select * from z where z1 =
x1) order by x1, x2, y1, y2;
+select * from x inner join y on x1 = y1 or not exists (select * from z where
z1 = x1) order by x1, x2, y1, y2;
+select * from x left join y on x1 = y1 or exists (select * from z where z1 =
x1) order by x1, x2, y1, y2;
+select * from x left join y on x1 = y1 or not exists (select * from z where z1
= x1) order by x1, x2, y1, y2;
+select * from x inner join y on x1 = y1 or exists (select * from z where z1 =
y1) order by x1, x2, y1, y2;
+select * from x inner join y on x1 = y1 or not exists (select * from z where
z1 = y1) order by x1, x2, y1, y2;
+select * from x left join y on x1 = y1 or exists (select * from z where z1 =
y1) order by x1, x2, y1, y2;
+select * from x left join y on x1 = y1 or not exists (select * from z where z1
= y1) order by x1, x2, y1, y2;
+
+-- Transitive predicates to test if inferring filters can cause issues.
+select * from x inner join y on x1 = y1 and exists (select * from z where z1 =
x1) order by x1, x2, y1, y2;
+select * from x inner join y on x1 = y1 and not exists (select * from z where
z1 = x1) order by x1, x2, y1, y2;
+select * from x left join y on x1 = y1 and exists (select * from z where z1 =
x1) order by x1, x2, y1, y2;
+select * from x left join y on x1 = y1 and not exists (select * from z where
z1 = x1) order by x1, x2, y1, y2;
+select * from x inner join y on x1 = y1 and exists (select * from z where z1 =
y1) order by x1, x2, y1, y2;
+select * from x inner join y on x1 = y1 and not exists (select * from z where
z1 = y1) order by x1, x2, y1, y2;
+select * from x left join y on x1 = y1 and exists (select * from z where z1 =
y1) order by x1, x2, y1, y2;
+select * from x left join y on x1 = y1 and not exists (select * from z where
z1 = y1) order by x1, x2, y1, y2;
diff --git
a/sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-null-semantics.sql
b/sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-null-semantics.sql
index cc01887a4e2..aad655f0509 100644
---
a/sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-null-semantics.sql
+++
b/sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-null-semantics.sql
@@ -32,10 +32,16 @@ select null not in (select e from v_empty);
select * from t left join t2 on (t.c in (select e from t_empty)) is null;
select * from t left join t2 on (t.c not in (select e from t_empty)) is null;
+-- Should have the same results as above with optimize IN subqueries enabled
+set
spark.sql.optimizer.optimizeUncorrelatedInSubqueriesInJoinCondition.enabled=true;
+-- IN subquery which IS rewritten to join
+select * from t left join t2 on (t.c in (select e from t_empty)) is null;
+select * from t left join t2 on (t.c not in (select e from t_empty)) is null;
--- Test legacy behavior flag
set spark.sql.legacy.nullInEmptyListBehavior = true;
+-- Disable optimize IN subqueries to joins because it affects null semantics
+set
spark.sql.optimizer.optimizeUncorrelatedInSubqueriesInJoinCondition.enabled=false;
-- constant null IN (empty subquery) - rewritten by NullPropagation rule
@@ -50,6 +56,7 @@ select * from t left join t2 on (t.c in (select e from
t_empty)) is null;
select * from t left join t2 on (t.c not in (select e from t_empty)) is null;
reset spark.sql.legacy.nullInEmptyListBehavior;
+reset
spark.sql.optimizer.optimizeUncorrelatedInSubqueriesInJoinCondition.enabled;
drop table t;
drop table t2;
diff --git
a/sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-subquery-in-join-condition.sql
b/sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-subquery-in-join-condition.sql
new file mode 100644
index 00000000000..d4fa2f6b0e8
--- /dev/null
+++
b/sql/core/src/test/resources/sql-tests/inputs/subquery/in-subquery/in-subquery-in-join-condition.sql
@@ -0,0 +1,85 @@
+-- Test that correlated EXISTS subqueries in join conditions are supported.
+
+-- Permutations of the test:
+-- 1. In / Not In
+-- 2. Reference left / right child
+-- 3. Join type: inner / left outer / right outer / full outer / left semi /
left anti
+-- 4. AND or OR for the join condition
+
+CREATE TEMP VIEW x(x1, x2) AS VALUES
+ (2, 1),
+ (1, 1),
+ (3, 4);
+
+CREATE TEMP VIEW y(y1, y2) AS VALUES
+ (0, 2),
+ (1, 4),
+ (4, 11);
+
+CREATE TEMP VIEW z(z1, z2) AS VALUES
+ (4, 2),
+ (3, 3),
+ (8, 1);
+
+
+--Correlated IN, REFERENCE LEFT, INNER JOIN
+select * from x inner join y on x1 = y1 and x2 IN (select z1 from z where z2 =
x2) order by x1, x2, y1, y2;
+
+--Correlated NOT IN, REFERENCE LEFT, INNER JOIN
+select * from x inner join y on x1 = y1 and x2 not IN (select z1 from z where
z2 = x2) order by x1, x2, y1, y2;
+
+--Correlated IN, REFERENCE RIGHT, INNER JOIN
+select * from x inner join y on x1 = y1 and y2 IN (select z1 from z where z2 =
y2) order by x1, x2, y1, y2;
+
+--Correlated NOT IN, REFERENCE RIGHT, INNER JOIN
+select * from x inner join y on x1 = y1 and y2 not IN (select z1 from z where
z2 = y2) order by x1, x2, y1, y2;
+
+-- Same as above, but for left join
+select * from x left join y on x1 = y1 and x2 IN (select z1 from z where z2 =
x2) order by x1, x2, y1, y2;
+select * from x left join y on x1 = y1 and x2 not IN (select z1 from z where
z2 = x2) order by x1, x2, y1, y2;
+select * from x left join y on x1 = y1 and y2 IN (select z1 from z where z2 =
y2) order by x1, x2, y1, y2;
+select * from x left join y on x1 = y1 and y2 not IN (select z1 from z where
z2 = y2) order by x1, x2, y1, y2;
+
+-- Same as above, but for right join
+select * from x right join y on x1 = y1 and x2 IN (select z1 from z where z2 =
x2) order by x1, x2, y1, y2;
+select * from x right join y on x1 = y1 and x2 not IN (select z1 from z where
z2 = x2) order by x1, x2, y1, y2;
+select * from x right join y on x1 = y1 and y2 IN (select z1 from z where z2 =
y2) order by x1, x2, y1, y2;
+select * from x right join y on x1 = y1 and y2 not IN (select z1 from z where
z2 = y2) order by x1, x2, y1, y2;
+
+-- Same as above, but for left semi join
+select * from x left semi join y on x1 = y1 and x2 IN (select z1 from z where
z2 = x2) order by x1, x2;
+select * from x left semi join y on x1 = y1 and x2 not IN (select z1 from z
where z2 = x2) order by x1, x2;
+select * from x left semi join y on x1 = y1 and y2 IN (select z1 from z where
z2 = y2) order by x1, x2;
+select * from x left semi join y on x1 = y1 and y2 not IN (select z1 from z
where z2 = y2) order by x1, x2;
+
+-- Same as above, but for left anti join
+select * from x left anti join y on x1 = y1 and x2 IN (select z1 from z where
z2 = x2) order by x1, x2;
+select * from x left anti join y on x1 = y1 and x2 not IN (select z1 from z
where z2 = x2) order by x1, x2;
+select * from x left anti join y on x1 = y1 and y2 IN (select z1 from z where
z2 = y2) order by x1, x2;
+select * from x left anti join y on x1 = y1 and y2 not IN (select z1 from z
where z2 = y2) order by x1, x2;
+
+-- Same as above, but for full outer join
+select * from x full outer join y on x1 = y1 and x2 IN (select z1 from z where
z2 = x2) order by x1, x2, y1, y2;
+select * from x full outer join y on x1 = y1 and x2 not IN (select z1 from z
where z2 = x2) order by x1, x2, y1, y2;
+select * from x full outer join y on x1 = y1 and y2 IN (select z1 from z where
z2 = y2) order by x1, x2, y1, y2;
+select * from x full outer join y on x1 = y1 and y2 not IN (select z1 from z
where z2 = y2) order by x1, x2, y1, y2;
+
+-- OR instead of AND in the join condition
+select * from x inner join y on x1 = y1 or x2 IN (select z1 from z where z1 =
x1) order by x1, x2, y1, y2;
+select * from x inner join y on x1 = y1 or x2 not IN (select z1 from z where
z1 = x1) order by x1, x2, y1, y2;
+select * from x left join y on x1 = y1 or x2 IN (select z1 from z where z1 =
x1) order by x1, x2, y1, y2;
+select * from x left join y on x1 = y1 or x2 not IN (select z1 from z where z1
= x1) order by x1, x2, y1, y2;
+select * from x inner join y on x1 = y1 or y2 IN (select z1 from z where z1 =
y1) order by x1, x2, y1, y2;
+select * from x inner join y on x1 = y1 or y2 not IN (select z1 from z where
z1 = y1) order by x1, x2, y1, y2;
+select * from x left join y on x1 = y1 or y2 IN (select z1 from z where z1 =
y1) order by x1, x2, y1, y2;
+select * from x left join y on x1 = y1 or y2 not IN (select z1 from z where z1
= y1) order by x1, x2, y1, y2;
+
+-- Transitive predicates to test if inferring filters can cause issues.
+select * from x inner join y on x1 = y1 and x2 IN (select z1 from z where z1 =
x1) order by x1, x2, y1, y2;
+select * from x inner join y on x1 = y1 and x2 not IN (select z1 from z where
z1 = x1) order by x1, x2, y1, y2;
+select * from x left join y on x1 = y1 and x2 IN (select z1 from z where z1 =
x1) order by x1, x2, y1, y2;
+select * from x left join y on x1 = y1 and x2 not IN (select z1 from z where
z1 = x1) order by x1, x2, y1, y2;
+select * from x inner join y on x1 = y1 and y2 IN (select z1 from z where z1 =
y1) order by x1, x2, y1, y2;
+select * from x inner join y on x1 = y1 and y2 not IN (select z1 from z where
z1 = y1) order by x1, x2, y1, y2;
+select * from x left join y on x1 = y1 and y2 IN (select z1 from z where z1 =
y1) order by x1, x2, y1, y2;
+select * from x left join y on x1 = y1 and y2 not IN (select z1 from z where
z1 = y1) order by x1, x2, y1, y2;
diff --git
a/sql/core/src/test/resources/sql-tests/results/subquery/exists-subquery/exists-in-join-condition.sql.out
b/sql/core/src/test/resources/sql-tests/results/subquery/exists-subquery/exists-in-join-condition.sql.out
new file mode 100644
index 00000000000..b490704bebc
--- /dev/null
+++
b/sql/core/src/test/resources/sql-tests/results/subquery/exists-subquery/exists-in-join-condition.sql.out
@@ -0,0 +1,474 @@
+-- Automatically generated by SQLQueryTestSuite
+-- !query
+CREATE TEMP VIEW x(x1, x2) AS VALUES
+ (2, 1),
+ (1, 1),
+ (3, 4)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE TEMP VIEW y(y1, y2) AS VALUES
+ (0, 2),
+ (1, 4),
+ (4, 11)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE TEMP VIEW z(z1, z2) AS VALUES
+ (4, 2),
+ (3, 3),
+ (8, 1)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+select * from x inner join y on x1 = y1 and exists (select * from z where z2 =
x2) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 1 4
+
+
+-- !query
+select * from x inner join y on x1 = y1 and not exists (select * from z where
z2 = x2) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+
+
+
+-- !query
+select * from x inner join y on x1 = y1 and exists (select * from z where z2 =
y2) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+
+
+
+-- !query
+select * from x inner join y on x1 = y1 and not exists (select * from z where
z2 = y2) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 1 4
+
+
+-- !query
+select * from x left join y on x1 = y1 and exists (select * from z where z2 =
x2) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 1 4
+2 1 NULL NULL
+3 4 NULL NULL
+
+
+-- !query
+select * from x left join y on x1 = y1 and not exists (select * from z where
z2 = x2) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 NULL NULL
+2 1 NULL NULL
+3 4 NULL NULL
+
+
+-- !query
+select * from x left join y on x1 = y1 and exists (select * from z where z2 =
y2) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 NULL NULL
+2 1 NULL NULL
+3 4 NULL NULL
+
+
+-- !query
+select * from x left join y on x1 = y1 and not exists (select * from z where
z2 = y2) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 1 4
+2 1 NULL NULL
+3 4 NULL NULL
+
+
+-- !query
+select * from x right join y on x1 = y1 and exists (select * from z where z2 =
x2) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+NULL NULL 0 2
+NULL NULL 4 11
+1 1 1 4
+
+
+-- !query
+select * from x right join y on x1 = y1 and not exists (select * from z where
z2 = x2) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+NULL NULL 0 2
+NULL NULL 1 4
+NULL NULL 4 11
+
+
+-- !query
+select * from x right join y on x1 = y1 and exists (select * from z where z2 =
y2) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+NULL NULL 0 2
+NULL NULL 1 4
+NULL NULL 4 11
+
+
+-- !query
+select * from x right join y on x1 = y1 and not exists (select * from z where
z2 = y2) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+NULL NULL 0 2
+NULL NULL 4 11
+1 1 1 4
+
+
+-- !query
+select * from x right join y on x1 = y1 and exists (select * from z where z2 =
x2) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+NULL NULL 0 2
+NULL NULL 4 11
+1 1 1 4
+
+
+-- !query
+select * from x right join y on x1 = y1 and not exists (select * from z where
z2 = x2) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+NULL NULL 0 2
+NULL NULL 1 4
+NULL NULL 4 11
+
+
+-- !query
+select * from x right join y on x1 = y1 and exists (select * from z where z2 =
y2) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+NULL NULL 0 2
+NULL NULL 1 4
+NULL NULL 4 11
+
+
+-- !query
+select * from x right join y on x1 = y1 and not exists (select * from z where
z2 = y2) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+NULL NULL 0 2
+NULL NULL 4 11
+1 1 1 4
+
+
+-- !query
+select * from x left semi join y on x1 = y1 and exists (select * from z where
z2 = x2) order by x1, x2
+-- !query schema
+struct<x1:int,x2:int>
+-- !query output
+1 1
+
+
+-- !query
+select * from x left semi join y on x1 = y1 and not exists (select * from z
where z2 = x2) order by x1, x2
+-- !query schema
+struct<x1:int,x2:int>
+-- !query output
+
+
+
+-- !query
+select * from x left semi join y on x1 = y1 and exists (select * from z where
z2 = y2) order by x1, x2
+-- !query schema
+struct<x1:int,x2:int>
+-- !query output
+
+
+
+-- !query
+select * from x left semi join y on x1 = y1 and not exists (select * from z
where z2 = y2) order by x1, x2
+-- !query schema
+struct<x1:int,x2:int>
+-- !query output
+1 1
+
+
+-- !query
+select * from x left anti join y on x1 = y1 and exists (select * from z where
z2 = x2) order by x1, x2
+-- !query schema
+struct<x1:int,x2:int>
+-- !query output
+2 1
+3 4
+
+
+-- !query
+select * from x left anti join y on x1 = y1 and not exists (select * from z
where z2 = x2) order by x1, x2
+-- !query schema
+struct<x1:int,x2:int>
+-- !query output
+1 1
+2 1
+3 4
+
+
+-- !query
+select * from x left anti join y on x1 = y1 and exists (select * from z where
z2 = y2) order by x1, x2
+-- !query schema
+struct<x1:int,x2:int>
+-- !query output
+1 1
+2 1
+3 4
+
+
+-- !query
+select * from x left anti join y on x1 = y1 and not exists (select * from z
where z2 = y2) order by x1, x2
+-- !query schema
+struct<x1:int,x2:int>
+-- !query output
+2 1
+3 4
+
+
+-- !query
+select * from x full outer join y on x1 = y1 and exists (select * from z where
z2 = x2) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+NULL NULL 0 2
+NULL NULL 4 11
+1 1 1 4
+2 1 NULL NULL
+3 4 NULL NULL
+
+
+-- !query
+select * from x full outer join y on x1 = y1 and not exists (select * from z
where z2 = x2) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+NULL NULL 0 2
+NULL NULL 1 4
+NULL NULL 4 11
+1 1 NULL NULL
+2 1 NULL NULL
+3 4 NULL NULL
+
+
+-- !query
+select * from x full outer join y on x1 = y1 and exists (select * from z where
z2 = y2) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+NULL NULL 0 2
+NULL NULL 1 4
+NULL NULL 4 11
+1 1 NULL NULL
+2 1 NULL NULL
+3 4 NULL NULL
+
+
+-- !query
+select * from x full outer join y on x1 = y1 and not exists (select * from z
where z2 = y2) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+NULL NULL 0 2
+NULL NULL 4 11
+1 1 1 4
+2 1 NULL NULL
+3 4 NULL NULL
+
+
+-- !query
+select * from x inner join y on x1 = y1 or exists (select * from z where z1 =
x1) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 1 4
+3 4 0 2
+3 4 1 4
+3 4 4 11
+
+
+-- !query
+select * from x inner join y on x1 = y1 or not exists (select * from z where
z1 = x1) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 0 2
+1 1 1 4
+1 1 4 11
+2 1 0 2
+2 1 1 4
+2 1 4 11
+
+
+-- !query
+select * from x left join y on x1 = y1 or exists (select * from z where z1 =
x1) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 1 4
+2 1 NULL NULL
+3 4 0 2
+3 4 1 4
+3 4 4 11
+
+
+-- !query
+select * from x left join y on x1 = y1 or not exists (select * from z where z1
= x1) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 0 2
+1 1 1 4
+1 1 4 11
+2 1 0 2
+2 1 1 4
+2 1 4 11
+3 4 NULL NULL
+
+
+-- !query
+select * from x inner join y on x1 = y1 or exists (select * from z where z1 =
y1) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 1 4
+1 1 4 11
+2 1 4 11
+3 4 4 11
+
+
+-- !query
+select * from x inner join y on x1 = y1 or not exists (select * from z where
z1 = y1) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 0 2
+1 1 1 4
+2 1 0 2
+2 1 1 4
+3 4 0 2
+3 4 1 4
+
+
+-- !query
+select * from x left join y on x1 = y1 or exists (select * from z where z1 =
y1) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 1 4
+1 1 4 11
+2 1 4 11
+3 4 4 11
+
+
+-- !query
+select * from x left join y on x1 = y1 or not exists (select * from z where z1
= y1) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 0 2
+1 1 1 4
+2 1 0 2
+2 1 1 4
+3 4 0 2
+3 4 1 4
+
+
+-- !query
+select * from x inner join y on x1 = y1 and exists (select * from z where z1 =
x1) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+
+
+
+-- !query
+select * from x inner join y on x1 = y1 and not exists (select * from z where
z1 = x1) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 1 4
+
+
+-- !query
+select * from x left join y on x1 = y1 and exists (select * from z where z1 =
x1) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 NULL NULL
+2 1 NULL NULL
+3 4 NULL NULL
+
+
+-- !query
+select * from x left join y on x1 = y1 and not exists (select * from z where
z1 = x1) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 1 4
+2 1 NULL NULL
+3 4 NULL NULL
+
+
+-- !query
+select * from x inner join y on x1 = y1 and exists (select * from z where z1 =
y1) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+
+
+
+-- !query
+select * from x inner join y on x1 = y1 and not exists (select * from z where
z1 = y1) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 1 4
+
+
+-- !query
+select * from x left join y on x1 = y1 and exists (select * from z where z1 =
y1) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 NULL NULL
+2 1 NULL NULL
+3 4 NULL NULL
+
+
+-- !query
+select * from x left join y on x1 = y1 and not exists (select * from z where
z1 = y1) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 1 4
+2 1 NULL NULL
+3 4 NULL NULL
diff --git
a/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-null-semantics.sql.out
b/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-null-semantics.sql.out
index 169b49fda84..68211e3ff1c 100644
---
a/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-null-semantics.sql.out
+++
b/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-null-semantics.sql.out
@@ -149,6 +149,32 @@ struct<c:int,d:int>
NULL NULL
+-- !query
+set
spark.sql.optimizer.optimizeUncorrelatedInSubqueriesInJoinCondition.enabled=true
+-- !query schema
+struct<key:string,value:string>
+-- !query output
+spark.sql.optimizer.optimizeUncorrelatedInSubqueriesInJoinCondition.enabled
true
+
+
+-- !query
+select * from t left join t2 on (t.c in (select e from t_empty)) is null
+-- !query schema
+struct<c:int,d:int>
+-- !query output
+1 NULL
+NULL NULL
+
+
+-- !query
+select * from t left join t2 on (t.c not in (select e from t_empty)) is null
+-- !query schema
+struct<c:int,d:int>
+-- !query output
+1 NULL
+NULL NULL
+
+
-- !query
set spark.sql.legacy.nullInEmptyListBehavior = true
-- !query schema
@@ -157,6 +183,14 @@ struct<key:string,value:string>
spark.sql.legacy.nullInEmptyListBehavior true
+-- !query
+set
spark.sql.optimizer.optimizeUncorrelatedInSubqueriesInJoinCondition.enabled=false
+-- !query schema
+struct<key:string,value:string>
+-- !query output
+spark.sql.optimizer.optimizeUncorrelatedInSubqueriesInJoinCondition.enabled
false
+
+
-- !query
select null in (select e from t_empty)
-- !query schema
@@ -215,6 +249,14 @@ struct<>
+-- !query
+reset
spark.sql.optimizer.optimizeUncorrelatedInSubqueriesInJoinCondition.enabled
+-- !query schema
+struct<>
+-- !query output
+
+
+
-- !query
drop table t
-- !query schema
diff --git
a/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-subquery-in-join-condition.sql.out
b/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-subquery-in-join-condition.sql.out
new file mode 100644
index 00000000000..9f829d522ad
--- /dev/null
+++
b/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-subquery-in-join-condition.sql.out
@@ -0,0 +1,436 @@
+-- Automatically generated by SQLQueryTestSuite
+-- !query
+CREATE TEMP VIEW x(x1, x2) AS VALUES
+ (2, 1),
+ (1, 1),
+ (3, 4)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE TEMP VIEW y(y1, y2) AS VALUES
+ (0, 2),
+ (1, 4),
+ (4, 11)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE TEMP VIEW z(z1, z2) AS VALUES
+ (4, 2),
+ (3, 3),
+ (8, 1)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+select * from x inner join y on x1 = y1 and x2 IN (select z1 from z where z2 =
x2) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+
+
+
+-- !query
+select * from x inner join y on x1 = y1 and x2 not IN (select z1 from z where
z2 = x2) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 1 4
+
+
+-- !query
+select * from x inner join y on x1 = y1 and y2 IN (select z1 from z where z2 =
y2) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+
+
+
+-- !query
+select * from x inner join y on x1 = y1 and y2 not IN (select z1 from z where
z2 = y2) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 1 4
+
+
+-- !query
+select * from x left join y on x1 = y1 and x2 IN (select z1 from z where z2 =
x2) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 NULL NULL
+2 1 NULL NULL
+3 4 NULL NULL
+
+
+-- !query
+select * from x left join y on x1 = y1 and x2 not IN (select z1 from z where
z2 = x2) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 1 4
+2 1 NULL NULL
+3 4 NULL NULL
+
+
+-- !query
+select * from x left join y on x1 = y1 and y2 IN (select z1 from z where z2 =
y2) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 NULL NULL
+2 1 NULL NULL
+3 4 NULL NULL
+
+
+-- !query
+select * from x left join y on x1 = y1 and y2 not IN (select z1 from z where
z2 = y2) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 1 4
+2 1 NULL NULL
+3 4 NULL NULL
+
+
+-- !query
+select * from x right join y on x1 = y1 and x2 IN (select z1 from z where z2 =
x2) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+NULL NULL 0 2
+NULL NULL 1 4
+NULL NULL 4 11
+
+
+-- !query
+select * from x right join y on x1 = y1 and x2 not IN (select z1 from z where
z2 = x2) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+NULL NULL 0 2
+NULL NULL 4 11
+1 1 1 4
+
+
+-- !query
+select * from x right join y on x1 = y1 and y2 IN (select z1 from z where z2 =
y2) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+NULL NULL 0 2
+NULL NULL 1 4
+NULL NULL 4 11
+
+
+-- !query
+select * from x right join y on x1 = y1 and y2 not IN (select z1 from z where
z2 = y2) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+NULL NULL 0 2
+NULL NULL 4 11
+1 1 1 4
+
+
+-- !query
+select * from x left semi join y on x1 = y1 and x2 IN (select z1 from z where
z2 = x2) order by x1, x2
+-- !query schema
+struct<x1:int,x2:int>
+-- !query output
+
+
+
+-- !query
+select * from x left semi join y on x1 = y1 and x2 not IN (select z1 from z
where z2 = x2) order by x1, x2
+-- !query schema
+struct<x1:int,x2:int>
+-- !query output
+1 1
+
+
+-- !query
+select * from x left semi join y on x1 = y1 and y2 IN (select z1 from z where
z2 = y2) order by x1, x2
+-- !query schema
+struct<x1:int,x2:int>
+-- !query output
+
+
+
+-- !query
+select * from x left semi join y on x1 = y1 and y2 not IN (select z1 from z
where z2 = y2) order by x1, x2
+-- !query schema
+struct<x1:int,x2:int>
+-- !query output
+1 1
+
+
+-- !query
+select * from x left anti join y on x1 = y1 and x2 IN (select z1 from z where
z2 = x2) order by x1, x2
+-- !query schema
+struct<x1:int,x2:int>
+-- !query output
+1 1
+2 1
+3 4
+
+
+-- !query
+select * from x left anti join y on x1 = y1 and x2 not IN (select z1 from z
where z2 = x2) order by x1, x2
+-- !query schema
+struct<x1:int,x2:int>
+-- !query output
+2 1
+3 4
+
+
+-- !query
+select * from x left anti join y on x1 = y1 and y2 IN (select z1 from z where
z2 = y2) order by x1, x2
+-- !query schema
+struct<x1:int,x2:int>
+-- !query output
+1 1
+2 1
+3 4
+
+
+-- !query
+select * from x left anti join y on x1 = y1 and y2 not IN (select z1 from z
where z2 = y2) order by x1, x2
+-- !query schema
+struct<x1:int,x2:int>
+-- !query output
+2 1
+3 4
+
+
+-- !query
+select * from x full outer join y on x1 = y1 and x2 IN (select z1 from z where
z2 = x2) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+NULL NULL 0 2
+NULL NULL 1 4
+NULL NULL 4 11
+1 1 NULL NULL
+2 1 NULL NULL
+3 4 NULL NULL
+
+
+-- !query
+select * from x full outer join y on x1 = y1 and x2 not IN (select z1 from z
where z2 = x2) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+NULL NULL 0 2
+NULL NULL 4 11
+1 1 1 4
+2 1 NULL NULL
+3 4 NULL NULL
+
+
+-- !query
+select * from x full outer join y on x1 = y1 and y2 IN (select z1 from z where
z2 = y2) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+NULL NULL 0 2
+NULL NULL 1 4
+NULL NULL 4 11
+1 1 NULL NULL
+2 1 NULL NULL
+3 4 NULL NULL
+
+
+-- !query
+select * from x full outer join y on x1 = y1 and y2 not IN (select z1 from z
where z2 = y2) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+NULL NULL 0 2
+NULL NULL 4 11
+1 1 1 4
+2 1 NULL NULL
+3 4 NULL NULL
+
+
+-- !query
+select * from x inner join y on x1 = y1 or x2 IN (select z1 from z where z1 =
x1) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 1 4
+
+
+-- !query
+select * from x inner join y on x1 = y1 or x2 not IN (select z1 from z where
z1 = x1) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 0 2
+1 1 1 4
+1 1 4 11
+2 1 0 2
+2 1 1 4
+2 1 4 11
+3 4 0 2
+3 4 1 4
+3 4 4 11
+
+
+-- !query
+select * from x left join y on x1 = y1 or x2 IN (select z1 from z where z1 =
x1) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 1 4
+2 1 NULL NULL
+3 4 NULL NULL
+
+
+-- !query
+select * from x left join y on x1 = y1 or x2 not IN (select z1 from z where z1
= x1) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 0 2
+1 1 1 4
+1 1 4 11
+2 1 0 2
+2 1 1 4
+2 1 4 11
+3 4 0 2
+3 4 1 4
+3 4 4 11
+
+
+-- !query
+select * from x inner join y on x1 = y1 or y2 IN (select z1 from z where z1 =
y1) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 1 4
+
+
+-- !query
+select * from x inner join y on x1 = y1 or y2 not IN (select z1 from z where
z1 = y1) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 0 2
+1 1 1 4
+1 1 4 11
+2 1 0 2
+2 1 1 4
+2 1 4 11
+3 4 0 2
+3 4 1 4
+3 4 4 11
+
+
+-- !query
+select * from x left join y on x1 = y1 or y2 IN (select z1 from z where z1 =
y1) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 1 4
+2 1 NULL NULL
+3 4 NULL NULL
+
+
+-- !query
+select * from x left join y on x1 = y1 or y2 not IN (select z1 from z where z1
= y1) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 0 2
+1 1 1 4
+1 1 4 11
+2 1 0 2
+2 1 1 4
+2 1 4 11
+3 4 0 2
+3 4 1 4
+3 4 4 11
+
+
+-- !query
+select * from x inner join y on x1 = y1 and x2 IN (select z1 from z where z1 =
x1) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+
+
+
+-- !query
+select * from x inner join y on x1 = y1 and x2 not IN (select z1 from z where
z1 = x1) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 1 4
+
+
+-- !query
+select * from x left join y on x1 = y1 and x2 IN (select z1 from z where z1 =
x1) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 NULL NULL
+2 1 NULL NULL
+3 4 NULL NULL
+
+
+-- !query
+select * from x left join y on x1 = y1 and x2 not IN (select z1 from z where
z1 = x1) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 1 4
+2 1 NULL NULL
+3 4 NULL NULL
+
+
+-- !query
+select * from x inner join y on x1 = y1 and y2 IN (select z1 from z where z1 =
y1) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+
+
+
+-- !query
+select * from x inner join y on x1 = y1 and y2 not IN (select z1 from z where
z1 = y1) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 1 4
+
+
+-- !query
+select * from x left join y on x1 = y1 and y2 IN (select z1 from z where z1 =
y1) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 NULL NULL
+2 1 NULL NULL
+3 4 NULL NULL
+
+
+-- !query
+select * from x left join y on x1 = y1 and y2 not IN (select z1 from z where
z1 = y1) order by x1, x2, y1, y2
+-- !query schema
+struct<x1:int,x2:int,y1:int,y2:int>
+-- !query output
+1 1 1 4
+2 1 NULL NULL
+3 4 NULL NULL
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
