You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by "mihaibudiu (via GitHub)" <gi...@apache.org> on 2026/10/22 01:44:11 UTC
[PR] [CALCITE-7873] Correlated subqueries in ON JOIN conditions crash the compiler with expand=true [calcite]
mihaibudiu opened a new pull request, #5350:
URL: https://github.com/apache/calcite/pull/5350
## Jira Link
[CALCITE-7873](https://issues.apache.org/jira/browse/CALCITE-7873)
## Changes Proposed
Perform correct subquery expansion in the ON condition.
There are 37 different quidem tests which fail without this fix in various way.
I also seems that the plans for `testJoinExpandAndDecorrelation` in SqlToRelConverter.xml was actually incorrect.
There's a quidem test proving it.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-7873] Correlated subqueries in ON JOIN conditions crash the compiler with expand=true [calcite]
Posted by "mihaibudiu (via GitHub)" <gi...@apache.org>.
mihaibudiu commented on code in PR #5350:
URL: https://github.com/apache/calcite/pull/5350#discussion_r4350733425
##########
core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java:
##########
@@ -301,6 +301,12 @@ public class SqlToRelConverter {
private final Map<CorrelationId, DeferredLookup> mapCorrelToDeferred =
new HashMap<>();
+ /**
+ * Maps an inner join whose ON condition has expanded sub-queries to the
+ * cross join of its inputs.
+ */
+ private final Map<RelNode, RelNode> mapJoinToCrossJoin = new HashMap<>();
Review Comment:
I will push a commit with this approach.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-7873] Correlated subqueries in ON JOIN conditions crash the compiler with expand=true [calcite]
Posted by "sonarqubecloud[bot] (via GitHub)" <gi...@apache.org>.
sonarqubecloud[bot] commented on PR #5350:
URL: https://github.com/apache/calcite/pull/5350#issuecomment-6330934497
## [](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=5350) **Quality Gate passed**
Issues
 [0 New issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=5350&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)
 [0 Accepted issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=5350&issueStatuses=ACCEPTED)
Measures
 [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=5350&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)
 [96.0% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=5350&metric=new_coverage&view=list)
 [0.0% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=5350&metric=new_duplicated_lines_density&view=list)
<!-- sqra-placement-anchor -->
[See analysis details on SonarQube Cloud](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=5350)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-7873] Correlated subqueries in ON JOIN conditions crash the compiler with expand=true [calcite]
Posted by "sonarqubecloud[bot] (via GitHub)" <gi...@apache.org>.
sonarqubecloud[bot] commented on PR #5350:
URL: https://github.com/apache/calcite/pull/5350#issuecomment-6346569277
## [](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=5350) **Quality Gate passed**
Issues
 [1 New issue](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=5350&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)
 [0 Accepted issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=5350&issueStatuses=ACCEPTED)
Measures
 [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=5350&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)
 [92.9% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=5350&metric=new_coverage&view=list)
 [0.0% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=5350&metric=new_duplicated_lines_density&view=list)
<!-- sqra-placement-anchor -->
[See analysis details on SonarQube Cloud](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=5350)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-7873] Correlated subqueries in ON JOIN conditions crash the compiler with expand=true [calcite]
Posted by "sonarqubecloud[bot] (via GitHub)" <gi...@apache.org>.
sonarqubecloud[bot] commented on PR #5350:
URL: https://github.com/apache/calcite/pull/5350#issuecomment-6351182532
## [](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=5350) **Quality Gate passed**
Issues
 [1 New issue](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=5350&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)
 [0 Accepted issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=5350&issueStatuses=ACCEPTED)
Measures
 [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=5350&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)
 [92.9% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=5350&metric=new_coverage&view=list)
 [0.0% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=5350&metric=new_duplicated_lines_density&view=list)
<!-- sqra-placement-anchor -->
[See analysis details on SonarQube Cloud](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=5350)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-7873] Correlated subqueries in ON JOIN conditions crash the compiler with expand=true [calcite]
Posted by "zzwqqq (via GitHub)" <gi...@apache.org>.
zzwqqq commented on code in PR #5350:
URL: https://github.com/apache/calcite/pull/5350#discussion_r4345678785
##########
core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java:
##########
@@ -301,6 +301,12 @@ public class SqlToRelConverter {
private final Map<CorrelationId, DeferredLookup> mapCorrelToDeferred =
new HashMap<>();
+ /**
+ * Maps an inner join whose ON condition has expanded sub-queries to the
+ * cross join of its inputs.
+ */
+ private final Map<RelNode, RelNode> mapJoinToCrossJoin = new HashMap<>();
Review Comment:
Could we derive the field offsets from the scope's namespaces, as getCorrelationUse does, instead of adding mapJoinToCrossJoin?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-7873] Correlated subqueries in ON JOIN conditions crash the compiler with expand=true [calcite]
Posted by "iwanttobepowerful (via GitHub)" <gi...@apache.org>.
iwanttobepowerful commented on code in PR #5350:
URL: https://github.com/apache/calcite/pull/5350#discussion_r4356731915
##########
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
Review Comment:
add a case from CALCITE-7882:
```sql
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;
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-7873] Correlated subqueries in ON JOIN conditions crash the compiler with expand=true [calcite]
Posted by "sonarqubecloud[bot] (via GitHub)" <gi...@apache.org>.
sonarqubecloud[bot] commented on PR #5350:
URL: https://github.com/apache/calcite/pull/5350#issuecomment-6356677484
## [](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=5350) **Quality Gate passed**
Issues
 [0 New issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=5350&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)
 [0 Accepted issues](https://sonarcloud.io/project/issues?id=apache_calcite&pullRequest=5350&issueStatuses=ACCEPTED)
Measures
 [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=apache_calcite&pullRequest=5350&issueStatuses=OPEN,CONFIRMED&sinceLeakPeriod=true)
 [92.9% Coverage on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=5350&metric=new_coverage&view=list)
 [0.0% Duplication on New Code](https://sonarcloud.io/component_measures?id=apache_calcite&pullRequest=5350&metric=new_duplicated_lines_density&view=list)
<!-- sqra-placement-anchor -->
[See analysis details on SonarQube Cloud](https://sonarcloud.io/dashboard?id=apache_calcite&pullRequest=5350)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] [CALCITE-7873] Correlated subqueries in ON JOIN conditions crash the compiler with expand=true [calcite]
Posted by "iwanttobepowerful (via GitHub)" <gi...@apache.org>.
iwanttobepowerful commented on code in PR #5350:
URL: https://github.com/apache/calcite/pull/5350#discussion_r4356731915
##########
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
Review Comment:
add a case from CALCITE-7882:
```sql
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;
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@calcite.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org