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