You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by mb...@apache.org on 2026/10/23 03:05:07 UTC
(calcite) branch issue7873 created (now bd7b500a9b)
This is an automated email from the ASF dual-hosted git repository.
mihaibudiu pushed a change to branch issue7873
in repository https://gitbox.apache.org/repos/asf/calcite.git
at bd7b500a9b [CALCITE-7873] Correlated subqueries in ON JOIN conditions crash the compiler with expand=true
This branch includes the following new commits:
new bd7b500a9b [CALCITE-7873] Correlated subqueries in ON JOIN conditions crash the compiler with expand=true
The 1 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails. The revisions
listed as "add" were already present in the repository and have only
been added to this reference.
(calcite) 01/01: [CALCITE-7873] Correlated subqueries in ON JOIN conditions crash the compiler with expand=true
Posted by mb...@apache.org.
This is an automated email from the ASF dual-hosted git repository.
mihaibudiu pushed a commit to branch issue7873
in repository https://gitbox.apache.org/repos/asf/calcite.git
commit bd7b500a9b3584eb2e57b4fdeeecb3317c38009a
Author: Mihai Budiu <mb...@feldera.com>
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 <mb...@feldera.com>
---
.../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