This is an automated email from the ASF dual-hosted git repository.
mihaibudiu pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 73b05e37c7 [CALCITE-7873] Correlated subqueries in ON JOIN conditions
crash the compiler with expand=true
73b05e37c7 is described below
commit 73b05e37c75bc38e663be7dcd87315324b44a9e5
Author: Mihai Budiu <[email protected]>
AuthorDate: Wed Oct 21 18:38:19 2026 -0700
[CALCITE-7873] Correlated subqueries in ON JOIN conditions crash the
compiler with expand=true
Signed-off-by: Mihai Budiu <[email protected]>
---
.../apache/calcite/sql2rel/SqlToRelConverter.java | 122 +++-
.../apache/calcite/test/SqlToRelConverterTest.xml | 22 +-
core/src/test/resources/sql/sub-query.iq | 689 +++++++++++++++++++++
3 files changed, 807 insertions(+), 26 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
index a910f15e01..f380de8d82 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -1342,23 +1342,24 @@ private void convertWhere(
return;
}
+ bb.setRoot(createFilterWithVariables(bb, convertedWhere2), false);
+ }
+
+ /** Creates a Filter on the root of a blackboard, with the correlation
+ * variables that its condition uses. */
+ private RelNode createFilterWithVariables(Blackboard bb, RexNode condition) {
final RelFactories.FilterFactory filterFactory =
RelFactories.DEFAULT_FILTER_FACTORY;
final RelNode filter =
- filterFactory.createFilter(bb.root(), convertedWhere2,
ImmutableSet.of());
- final RelNode r;
+ filterFactory.createFilter(bb.root(), condition, ImmutableSet.of());
final CorrelationUse p = getCorrelationUse(bb, filter);
- if (p != null) {
- assert p.r instanceof Filter;
- Filter f = (Filter) p.r;
- r =
- LogicalFilter.create(f.getInput(), f.getCondition(),
- ImmutableSet.of(p.id));
- } else {
- r = filter;
+ if (p == null) {
+ return filter;
}
-
- bb.setRoot(r, false);
+ assert p.r instanceof Filter;
+ Filter f = (Filter) p.r;
+ return LogicalFilter.create(f.getInput(), f.getCondition(),
+ ImmutableSet.of(p.id));
}
private void replaceSubQueries(
@@ -3697,6 +3698,16 @@ private void convertJoin(Blackboard bb, SqlJoin join) {
SqlNode sqlCondition =
requireNonNull(join.getCondition(),
() -> "getCondition for join " + join);
+ findSubQueries(fromBlackboard, sqlCondition,
+ RelOptUtil.Logic.UNKNOWN_AS_FALSE, false);
+ if (config.isExpand()
+ && joinType == JoinType.INNER
+ && !fromBlackboard.subQueryList.isEmpty()) {
+ setRootToJoin(bb,
+ convertInnerJoinWithSubQueries(fromBlackboard, sqlCondition,
+ leftRel, tempRightRel));
+ return;
+ }
Pair<RexNode, RelNode> conditionAndRightNode =
convertOnCondition(fromBlackboard, sqlCondition, leftRel,
tempRightRel);
condition = conditionAndRightNode.left;
@@ -3727,11 +3738,68 @@ private void convertJoin(Blackboard bb, SqlJoin join) {
createJoin(fromBlackboard, leftRel, rightRel, condition,
convertJoinType(joinType));
}
+ setRootToJoin(bb, joinRel);
+ }
+
+ /** Sets a converted join as the root of a blackboard. */
+ private void setRootToJoin(Blackboard bb, RelNode joinRel) {
relBuilder.push(joinRel);
relBuilder.project(relBuilder.fields());
bb.setRoot(relBuilder.build(), false);
}
+ /**
+ * Converts an inner join whose ON condition has sub-queries, when
+ * sub-queries are expanded.
+ *
+ * <p>{@code L JOIN R ON c} is equivalent to
+ * {@code (L CROSS JOIN R) WHERE c}, so the sub-queries are expanded as in
+ * a WHERE clause. For example,
+ *
+ * <blockquote><pre>
+ * SELECT e.ename, d.name
+ * FROM emp AS e JOIN dept AS d
+ * ON e.deptno = d.deptno AND e.sal = (SELECT MAX(s.sal) FROM emp AS s)
+ * </pre></blockquote>
+ *
+ * <p>becomes
+ *
+ * <blockquote><pre>
+ * LogicalProject(ENAME=[$1], NAME=[$10]) -- drops $11;
merged into SELECT
+ * LogicalFilter(condition=[AND(=($7, $9), =($5, $11))]) -- the ON
condition
+ * LogicalJoin(condition=[true], joinType=[left]) -- joins the
sub-query
+ * LogicalJoin(condition=[true], joinType=[inner]) -- cross join
of EMP and DEPT
+ * LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ * LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ * LogicalAggregate(group=[{}], EXPR$0=[MAX($0)]) -- the
sub-query; MAX is $11
+ * LogicalProject(SAL=[$5])
+ * LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ * </pre></blockquote>
+ */
+ private RelNode convertInnerJoinWithSubQueries(Blackboard bb,
+ SqlNode condition, RelNode leftRel, RelNode rightRel) {
+ final RelNode crossJoin =
+ createJoin(bb, leftRel, rightRel, rexBuilder.makeLiteral(true),
+ JoinRelType.INNER);
+ bb.setRoot(crossJoin, false);
+ replaceSubQueries(bb, condition, RelOptUtil.Logic.UNKNOWN_AS_FALSE);
+ final RexNode conditionExp =
+ simplifyPredicate(bb.convertExpression(condition));
+ if (bb.root() == crossJoin) {
+ // No sub-query was joined, for example in "x IN (1, 2)"
+ return createJoin(bb, leftRel, rightRel, conditionExp,
+ JoinRelType.INNER);
+ }
+ final RelNode filter =
+ conditionExp.isAlwaysTrue()
+ ? bb.root()
+ : createFilterWithVariables(bb, conditionExp);
+ final int fieldCount = crossJoin.getRowType().getFieldCount();
+ return relBuilder.push(filter)
+ .project(relBuilder.fields(ImmutableBitSet.range(fieldCount)))
+ .build();
+ }
+
private RexNode convertNaturalCondition(
SqlValidatorNamespace leftNamespace,
SqlValidatorNamespace rightNamespace) {
@@ -5886,9 +5954,19 @@ void setRoot(List<RelNode> inputs) {
isParent = false;
}
if ((inputs != null) && !isParent) {
- final LookupContext rels =
- new LookupContext(this, inputs, systemFieldList.size());
- final RexNode node = lookup(resolve.path.steps().get(0).i, rels);
+ // For a ListScope, use the row types of the scope's children, not the
+ // leaves of the inputs: the leaves can include sub-queries that were
+ // joined to the inputs (see convertInnerJoinWithSubQueries)
+ final RexNode node;
+ if (ancestorScope instanceof ListScope) {
+ node =
+ makeChildReference((ListScope) ancestorScope,
+ resolve.path.steps().get(0).i);
+ } else {
+ final LookupContext rels =
+ new LookupContext(this, inputs, systemFieldList.size());
+ node = lookup(resolve.path.steps().get(0).i, rels);
+ }
return Pair.of(node, (e, fieldName) -> {
final RelDataTypeField field =
requireNonNull(rowType.getField(fieldName, true, false),
@@ -5955,6 +6033,20 @@ RexNode lookup(
false);
}
+ /**
+ * Creates an expression referencing child {@code index} of {@code scope}.
+ */
+ RexNode makeChildReference(ListScope scope, int index) {
+ final List<SqlValidatorNamespace> children = scope.getChildren();
+ // Skip the system fields and the children before it, as in
getCorrelationUse
+ int offset = systemFieldList.size();
+ for (int j = 0; j < index; j++) {
+ offset += children.get(j).getRowType().getFieldCount();
+ }
+ return rexBuilder.makeRangeReference(
+ children.get(index).getRowType(), offset, false);
+ }
+
@Nullable RelDataTypeField getRootField(RexInputRef inputRef) {
List<RelNode> inputs = this.inputs;
if (inputs == null) {
diff --git
a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
index 9fdeb3067a..b793a776ba 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -4199,15 +4199,14 @@ LogicalDelta
<Resource name="planExpanded">
<![CDATA[
LogicalProject(DEPTNO=[$9], SAL=[$7])
- LogicalJoin(condition=[=($9, $0)], joinType=[inner])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
- LogicalFilter(condition=[<($5, $0)])
- LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EXPR$0=[$10])
- LogicalJoin(condition=[=($0, $9)], joinType=[left])
+ LogicalProject(DEPTNO=[$0], NAME=[$1], EMPNO=[$2], ENAME=[$3], JOB=[$4],
MGR=[$5], HIREDATE=[$6], SAL=[$7], COMM=[$8], DEPTNO0=[$9], SLACKER=[$10],
DEPTNO1=[CAST($11):INTEGER], EXPR$0=[CAST($12):INTEGER])
+ LogicalJoin(condition=[AND(=($0, $11), <($7, $12))], joinType=[inner])
+ LogicalJoin(condition=[=($9, $0)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}], EXPR$0=[AVG($1)])
+ LogicalProject(DEPTNO=[$7], SAL=[$5])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
- LogicalAggregate(group=[{0}], EXPR$0=[AVG($1)])
- LogicalProject(DEPTNO=[$7], SAL=[$5])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
<Resource name="planNotExpanded">
@@ -4896,10 +4895,11 @@ ON (SELECT AVG(emp.sal) > 0 FROM emp)]]>
<Resource name="plan">
<![CDATA[
LogicalProject(DEPTNO=[$9], SAL=[$7])
- LogicalJoin(condition=[$11], joinType=[inner])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalFilter(condition=[$11])
LogicalJoin(condition=[true], joinType=[left])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalJoin(condition=[true], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(EXPR$0=[>($0, 0)])
LogicalAggregate(group=[{}], agg#0=[AVG($0)])
LogicalProject(SAL=[$5])
diff --git a/core/src/test/resources/sql/sub-query.iq
b/core/src/test/resources/sql/sub-query.iq
index 094ed12848..4a2231a3f0 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -10297,3 +10297,692 @@ select k from t group by k having max(a) not in
(select s.b from s where s.k = t
!ok
!set expand false
+
+# [CALCITE-7873] Correlated subqueries in ON JOIN conditions crash the
+# compiler with expand=true
+# The results of the following queries were validated using Postgres.
+!set expand true
+
+# Scalar and EXISTS sub-queries
+select e.ename, d.dname
+from emp as e join dept as d
+on e.deptno = d.deptno
+ and e.sal = (select max(s.sal) from emp as s)
+order by e.ename;
++-------+------------+
+| ENAME | DNAME |
++-------+------------+
+| KING | ACCOUNTING |
++-------+------------+
+(1 row)
+
+!ok
+
+select e.ename, d.dname
+from emp as e join dept as d
+on e.mgr = (select max(s.mgr) from emp as s)
+order by d.dname;
++-------+------------+
+| ENAME | DNAME |
++-------+------------+
+| SMITH | ACCOUNTING |
+| SMITH | OPERATIONS |
+| SMITH | RESEARCH |
+| SMITH | SALES |
++-------+------------+
+(4 rows)
+
+!ok
+
+select e.ename, d.dname
+from emp as e join dept as d
+on e.deptno = d.deptno
+ and exists (select 1 from emp as s where s.job = 'NOBODY')
+order by e.ename;
++-------+-------+
+| ENAME | DNAME |
++-------+-------+
++-------+-------+
+(0 rows)
+
+!ok
+
+# IN, key from the left input
+select e.ename, d.dname
+from emp as e join dept as d
+on e.deptno = d.deptno
+ and e.empno in (select s.empno from emp as s where s.ename = 'SCOTT')
+order by e.ename;
++-------+----------+
+| ENAME | DNAME |
++-------+----------+
+| SCOTT | RESEARCH |
++-------+----------+
+(1 row)
+
+!ok
+
+select e.ename, d.dname
+from emp as e join dept as d
+on e.deptno = d.deptno
+ and e.empno in (select s.empno from emp as s where s.job = 'ANALYST')
+order by e.ename;
++-------+----------+
+| ENAME | DNAME |
++-------+----------+
+| FORD | RESEARCH |
+| SCOTT | RESEARCH |
++-------+----------+
+(2 rows)
+
+!ok
+
+select e.ename, d.dname
+from emp as e join dept as d
+on e.deptno = d.deptno
+ and e.mgr in (select s.mgr from emp as s where s.job = 'ANALYST')
+order by e.ename;
++-------+----------+
+| ENAME | DNAME |
++-------+----------+
+| FORD | RESEARCH |
+| SCOTT | RESEARCH |
++-------+----------+
+(2 rows)
+
+!ok
+
+# IN, key from the right input
+select e.ename, d.dname
+from emp as e join dept as d
+on e.deptno = d.deptno
+ and d.deptno in (select s.deptno from emp as s where s.job = 'ANALYST')
+order by e.ename;
++-------+----------+
+| ENAME | DNAME |
++-------+----------+
+| ADAMS | RESEARCH |
+| FORD | RESEARCH |
+| JONES | RESEARCH |
+| SCOTT | RESEARCH |
+| SMITH | RESEARCH |
++-------+----------+
+(5 rows)
+
+!ok
+
+# Correlated sub-queries
+select e.ename, d.dname
+from emp as e join dept as d
+on e.deptno = d.deptno
+ and e.sal = (select max(s.sal) from emp as s where s.deptno = d.deptno)
+order by e.ename;
++-------+------------+
+| ENAME | DNAME |
++-------+------------+
+| BLAKE | SALES |
+| FORD | RESEARCH |
+| KING | ACCOUNTING |
+| SCOTT | RESEARCH |
++-------+------------+
+(4 rows)
+
+!ok
+
+select e.ename, d.dname
+from emp as e join dept as d
+on e.deptno = d.deptno
+ and e.sal = (select max(s.sal) from emp as s where s.deptno = e.deptno)
+order by e.ename;
++-------+------------+
+| ENAME | DNAME |
++-------+------------+
+| BLAKE | SALES |
+| FORD | RESEARCH |
+| KING | ACCOUNTING |
+| SCOTT | RESEARCH |
++-------+------------+
+(4 rows)
+
+!ok
+
+select e.ename, d.dname
+from emp as e join dept as d
+on e.deptno = d.deptno
+ and exists (select 1 from emp as s where s.job = 'ANALYST' and s.deptno =
d.deptno)
+order by e.ename;
++-------+----------+
+| ENAME | DNAME |
++-------+----------+
+| ADAMS | RESEARCH |
+| FORD | RESEARCH |
+| JONES | RESEARCH |
+| SCOTT | RESEARCH |
+| SMITH | RESEARCH |
++-------+----------+
+(5 rows)
+
+!ok
+
+select e.ename, d.dname
+from emp as e join dept as d
+on e.deptno = d.deptno
+ and exists (select 1 from emp as s where s.mgr = e.empno)
+order by e.ename;
++-------+------------+
+| ENAME | DNAME |
++-------+------------+
+| BLAKE | SALES |
+| CLARK | ACCOUNTING |
+| FORD | RESEARCH |
+| JONES | RESEARCH |
+| KING | ACCOUNTING |
+| SCOTT | RESEARCH |
++-------+------------+
+(6 rows)
+
+!ok
+
+select e.ename, d.dname
+from emp as e join dept as d
+on e.deptno = d.deptno
+ and e.empno not in (select s.mgr from emp as s where s.deptno = d.deptno)
+order by e.ename;
++--------+----------+
+| ENAME | DNAME |
++--------+----------+
+| ADAMS | RESEARCH |
+| ALLEN | SALES |
+| JAMES | SALES |
+| MARTIN | SALES |
+| SMITH | RESEARCH |
+| TURNER | SALES |
+| WARD | SALES |
++--------+----------+
+(7 rows)
+
+!ok
+
+# Nested joins, two sub-queries, join inside a sub-query
+select e.ename, d.dname, x.loc
+from emp as e join dept as d
+on e.deptno = d.deptno
+ and e.sal = (select max(s.sal) from emp as s)
+join dept as x on x.deptno = d.deptno
+order by e.ename;
++-------+------------+----------+
+| ENAME | DNAME | LOC |
++-------+------------+----------+
+| KING | ACCOUNTING | NEW YORK |
++-------+------------+----------+
+(1 row)
+
+!ok
+
+select e.ename, m.ename as mname
+from emp as e join dept as d
+on e.deptno = d.deptno
+join emp as m
+on m.empno = e.mgr
+ and m.sal = (select max(s.sal) from emp as s where s.deptno = d.deptno)
+order by e.ename;
++--------+-------+
+| ENAME | MNAME |
++--------+-------+
+| ADAMS | SCOTT |
+| ALLEN | BLAKE |
+| CLARK | KING |
+| JAMES | BLAKE |
+| MARTIN | BLAKE |
+| SMITH | FORD |
+| TURNER | BLAKE |
+| WARD | BLAKE |
++--------+-------+
+(8 rows)
+
+!ok
+
+select e.ename, d.dname
+from emp as e join dept as d
+on e.deptno = d.deptno
+ and e.mgr in (select s.empno from emp as s where s.job = 'MANAGER')
+ and d.deptno in (select s.deptno from emp as s where s.job = 'ANALYST')
+order by e.ename;
++-------+----------+
+| ENAME | DNAME |
++-------+----------+
+| FORD | RESEARCH |
+| SCOTT | RESEARCH |
++-------+----------+
+(2 rows)
+
+!ok
+
+select e.ename, d.dname
+from emp as e join dept as d
+on e.deptno = d.deptno
+ and exists (select 1 from emp as s where s.mgr = e.empno and s.deptno =
d.deptno)
+order by e.ename;
++-------+------------+
+| ENAME | DNAME |
++-------+------------+
+| BLAKE | SALES |
+| CLARK | ACCOUNTING |
+| FORD | RESEARCH |
+| JONES | RESEARCH |
+| KING | ACCOUNTING |
+| SCOTT | RESEARCH |
++-------+------------+
+(6 rows)
+
+!ok
+
+select d.dname
+from dept as d
+where exists (
+ select 1 from emp as e join emp as m
+ on m.empno = e.mgr and m.sal = (select max(s.sal) from emp as s)
+ where e.deptno = d.deptno)
+order by d.dname;
++------------+
+| DNAME |
++------------+
+| ACCOUNTING |
+| RESEARCH |
+| SALES |
++------------+
+(3 rows)
+
+!ok
+
+select e.ename, d.dname
+from emp as e join dept as d
+on e.deptno = d.deptno
+ and e.mgr not in (select s.mgr from emp as s where s.job = 'CLERK')
+order by e.ename;
++-------+------------+
+| ENAME | DNAME |
++-------+------------+
+| BLAKE | SALES |
+| CLARK | ACCOUNTING |
+| FORD | RESEARCH |
+| JONES | RESEARCH |
+| SCOTT | RESEARCH |
++-------+------------+
+(5 rows)
+
+!ok
+
+# Several sub-queries in one ON, OR, NOT EXISTS, GROUP BY, IN lists,
+# LATERAL, a sub-query in FROM, composite keys, and correlation with
+# an outer query
+select e.ename, d.dname
+from emp as e join dept as d
+on e.deptno = d.deptno
+ and e.sal < (select max(s.sal) from emp as s)
+ and exists (select 1 from emp as s where s.mgr = e.empno)
+ and e.mgr not in (select s.mgr from emp as s where s.job = 'CLERK')
+order by e.ename;
++-------+------------+
+| ENAME | DNAME |
++-------+------------+
+| BLAKE | SALES |
+| CLARK | ACCOUNTING |
+| FORD | RESEARCH |
+| JONES | RESEARCH |
+| SCOTT | RESEARCH |
++-------+------------+
+(5 rows)
+
+!ok
+
+select e.ename, d.dname, m.ename as mname
+from emp as e join dept as d
+on e.deptno = d.deptno
+ and d.deptno in (select s.deptno from emp as s where s.job = 'ANALYST')
+join emp as m
+on m.empno = e.mgr
+ and m.sal > (select avg(s.sal) from emp as s)
+order by e.ename;
++-------+----------+-------+
+| ENAME | DNAME | MNAME |
++-------+----------+-------+
+| ADAMS | RESEARCH | SCOTT |
+| FORD | RESEARCH | JONES |
+| JONES | RESEARCH | KING |
+| SCOTT | RESEARCH | JONES |
+| SMITH | RESEARCH | FORD |
++-------+----------+-------+
+(5 rows)
+
+!ok
+
+select e.ename, d.dname
+from emp as e join dept as d
+on e.deptno = d.deptno
+ and (e.comm > 0 or e.empno in (select s.mgr from emp as s))
+order by e.ename;
++--------+------------+
+| ENAME | DNAME |
++--------+------------+
+| ALLEN | SALES |
+| BLAKE | SALES |
+| CLARK | ACCOUNTING |
+| FORD | RESEARCH |
+| JONES | RESEARCH |
+| KING | ACCOUNTING |
+| MARTIN | SALES |
+| SCOTT | RESEARCH |
+| WARD | SALES |
++--------+------------+
+(9 rows)
+
+!ok
+
+select e.ename, d.dname
+from emp as e join dept as d
+on e.deptno = d.deptno
+ and not exists (select 1 from emp as s where s.mgr = e.empno)
+order by e.ename;
++--------+------------+
+| ENAME | DNAME |
++--------+------------+
+| ADAMS | RESEARCH |
+| ALLEN | SALES |
+| JAMES | SALES |
+| MARTIN | SALES |
+| MILLER | ACCOUNTING |
+| SMITH | RESEARCH |
+| TURNER | SALES |
+| WARD | SALES |
++--------+------------+
+(8 rows)
+
+!ok
+
+select d.dname, count(*) as c
+from emp as e join dept as d
+on e.deptno = d.deptno
+ and e.sal > (select avg(s.sal) from emp as s)
+group by d.dname
+order by d.dname;
++------------+---+
+| DNAME | C |
++------------+---+
+| ACCOUNTING | 2 |
+| RESEARCH | 3 |
+| SALES | 1 |
++------------+---+
+(3 rows)
+
+!ok
+
+select e.ename, d.dname
+from emp as e join dept as d
+on e.deptno = d.deptno
+ and e.empno in (7369, 7499, 7521, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
+ 13, 14, 15, 16, 17, 18)
+order by e.ename;
++-------+----------+
+| ENAME | DNAME |
++-------+----------+
+| ALLEN | SALES |
+| SMITH | RESEARCH |
+| WARD | SALES |
++-------+----------+
+(3 rows)
+
+!ok
+
+select e.ename, d.dname
+from emp as e join dept as d
+on e.deptno = d.deptno
+ and e.empno in (7369, 7499)
+order by e.ename;
++-------+----------+
+| ENAME | DNAME |
++-------+----------+
+| ALLEN | SALES |
+| SMITH | RESEARCH |
++-------+----------+
+(2 rows)
+
+!ok
+
+select e.ename, d.dname
+from emp as e
+join lateral (select * from dept as x where x.deptno = e.deptno) as d
+on d.loc in (select x.loc from dept as x where x.dname <> 'SALES')
+ and e.sal > (select avg(s.sal) from emp as s)
+order by e.ename;
++-------+------------+
+| ENAME | DNAME |
++-------+------------+
+| CLARK | ACCOUNTING |
+| FORD | RESEARCH |
+| JONES | RESEARCH |
+| KING | ACCOUNTING |
+| SCOTT | RESEARCH |
++-------+------------+
+(5 rows)
+
+!ok
+
+select e.ename, d.dname
+from emp as e join (select deptno, dname from dept where deptno < 40) as d
+on e.deptno = d.deptno
+ and exists (select 1 from emp as s where s.mgr = e.empno and s.job = 'CLERK')
+order by e.ename;
++-------+------------+
+| ENAME | DNAME |
++-------+------------+
+| BLAKE | SALES |
+| CLARK | ACCOUNTING |
+| FORD | RESEARCH |
+| SCOTT | RESEARCH |
++-------+------------+
+(4 rows)
+
+!ok
+
+select e.ename, d.dname
+from emp as e join dept as d
+on e.deptno = d.deptno
+ and (e.deptno, e.job) in (select s.deptno, s.job from emp as s where s.sal >
2900)
+order by e.ename;
++-------+------------+
+| ENAME | DNAME |
++-------+------------+
+| FORD | RESEARCH |
+| JONES | RESEARCH |
+| KING | ACCOUNTING |
+| SCOTT | RESEARCH |
++-------+------------+
+(4 rows)
+
+!ok
+
+select e.ename, d.dname
+from emp as e join dept as d
+on e.deptno = d.deptno
+ and (e.deptno, e.job) not in (select s.deptno, s.job from emp as s where
s.sal > 2900)
+order by e.ename;
++--------+------------+
+| ENAME | DNAME |
++--------+------------+
+| ADAMS | RESEARCH |
+| ALLEN | SALES |
+| BLAKE | SALES |
+| CLARK | ACCOUNTING |
+| JAMES | SALES |
+| MARTIN | SALES |
+| MILLER | ACCOUNTING |
+| SMITH | RESEARCH |
+| TURNER | SALES |
+| WARD | SALES |
++--------+------------+
+(10 rows)
+
+!ok
+
+select count(*) as c
+from emp as e join dept as d
+on e.deptno = d.deptno
+ and d.deptno in (select s.deptno from emp as s);
++----+
+| C |
++----+
+| 14 |
++----+
+(1 row)
+
+!ok
+
+select d.dname
+from dept as d
+where exists (
+ select 1 from emp as e join emp as m
+ on m.empno = e.mgr
+ and m.job in (select s.job from emp as s
+ where s.deptno = d.deptno and s.job = 'PRESIDENT')
+ where e.deptno = d.deptno)
+order by d.dname;
++------------+
+| DNAME |
++------------+
+| ACCOUNTING |
++------------+
+(1 row)
+
+!ok
+
+# Sub-queries in the ON clause and in WHERE, SELECT or HAVING
+select e.ename, d.dname
+from emp as e join dept as d
+on e.deptno = d.deptno
+ and e.sal > (select avg(s.sal) from emp as s)
+where exists (select 1 from emp as s where s.deptno = d.deptno and s.job =
'ANALYST')
+order by e.ename;
++-------+----------+
+| ENAME | DNAME |
++-------+----------+
+| FORD | RESEARCH |
+| JONES | RESEARCH |
+| SCOTT | RESEARCH |
++-------+----------+
+(3 rows)
+
+!ok
+
+select e.ename, (select count(*) from emp as s where s.deptno = d.deptno) as c
+from emp as e join dept as d
+on e.deptno = d.deptno
+ and e.sal = (select max(s.sal) from emp as s where s.job = 'ANALYST')
+order by e.ename;
++-------+---+
+| ENAME | C |
++-------+---+
+| FORD | 5 |
+| SCOTT | 5 |
++-------+---+
+(2 rows)
+
+!ok
+
+select d.dname, count(*) as c
+from emp as e join dept as d
+on e.deptno = d.deptno
+ and e.mgr in (select s.empno from emp as s where s.job = 'MANAGER')
+group by d.dname
+having count(*) > (select count(*) from dept as y where y.loc = 'BOSTON')
+order by d.dname;
++----------+---+
+| DNAME | C |
++----------+---+
+| RESEARCH | 2 |
+| SALES | 5 |
++----------+---+
+(2 rows)
+
+!ok
+
+select e.ename, x.loc
+from emp as e join dept as d
+on e.deptno = d.deptno
+ and e.sal > (select avg(s.sal) from emp as s)
+join dept as x on x.deptno = d.deptno
+where exists (select 1 from emp as s where s.deptno = x.deptno and s.job =
'PRESIDENT')
+order by e.ename;
++-------+----------+
+| ENAME | LOC |
++-------+----------+
+| CLARK | NEW YORK |
+| KING | NEW YORK |
++-------+----------+
+(2 rows)
+
+!ok
+
+# A scalar sub-query and a correlated EXISTS, in both orders
+select e.ename
+from emp as e join dept as d
+on e.deptno = d.deptno
+ and e.sal < (select max(s.sal) from emp as s)
+ and exists (select 1 from emp as s where s.mgr = e.empno)
+order by e.ename;
++-------+
+| ENAME |
++-------+
+| BLAKE |
+| CLARK |
+| FORD |
+| JONES |
+| SCOTT |
++-------+
+(5 rows)
+
+!ok
+
+select e.ename
+from emp as e join dept as d
+on e.deptno = d.deptno
+ and exists (select 1 from emp as s where s.mgr = e.empno)
+ and e.sal < (select max(s.sal) from emp as s)
+order by e.ename;
++-------+
+| ENAME |
++-------+
+| BLAKE |
+| CLARK |
+| FORD |
+| JONES |
+| SCOTT |
++-------+
+(5 rows)
+
+!ok
+
+# A query equivalent to SqlToRelConverterTest.testJoinExpandAndDecorrelation
+select emp.deptno, emp.sal
+from dept
+join emp on emp.deptno = dept.deptno and emp.sal < (
+ select avg(emp.sal)
+ from emp
+ where emp.deptno = dept.deptno)
+order by emp.deptno, emp.sal;
++--------+---------+
+| DEPTNO | SAL |
++--------+---------+
+| 10 | 1300.00 |
+| 10 | 2450.00 |
+| 20 | 800.00 |
+| 20 | 1100.00 |
+| 30 | 950.00 |
+| 30 | 1250.00 |
+| 30 | 1250.00 |
+| 30 | 1500.00 |
++--------+---------+
+(8 rows)
+
+!ok
+!set expand false
