This is an automated email from the ASF dual-hosted git repository.
iwanttobepowerful 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 cf01ee8205 [CALCITE-7899] SubQueryRemoveRule binds an outer query's
correlation variable to the wrong input when a Project has several sub-queries
cf01ee8205 is described below
commit cf01ee82056237eb2dfb58d93dc184331ce80527
Author: Weihua Zhang <[email protected]>
AuthorDate: Wed Oct 28 14:48:51 2026 +0800
[CALCITE-7899] SubQueryRemoveRule binds an outer query's correlation
variable to the wrong input when a Project has several sub-queries
---
.../calcite/rel/rules/SubQueryRemoveRule.java | 38 +++++++----
core/src/test/resources/sql/sub-query.iq | 74 ++++++++++++++++++++++
2 files changed, 98 insertions(+), 14 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/rel/rules/SubQueryRemoveRule.java
b/core/src/main/java/org/apache/calcite/rel/rules/SubQueryRemoveRule.java
index c14e654a01..74cab57980 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/SubQueryRemoveRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/SubQueryRemoveRule.java
@@ -967,24 +967,34 @@ private static void matchProject(SubQueryRemoveRule rule,
final Project project = call.rel(0);
final Set<CorrelationId> projectVariablesSet = project.getVariablesSet();
final RelBuilder builder = call.builder();
- final RexSubQuery e =
- requireNonNull(RexUtil.SubQueryFinder.find(project.getProjects()));
- final RelOptUtil.Logic logic =
- LogicVisitor.find(RelOptUtil.Logic.TRUE_FALSE_UNKNOWN,
- project.getProjects(), e);
builder.push(project.getInput());
- final int fieldCount = builder.peek().getRowType().getFieldCount();
- final Set<CorrelationId> variablesSet =
- RelOptUtil.getVariablesUsed(e.rel);
- if (!projectVariablesSet.isEmpty()) {
+ // Rewrite all sub-queries in one call, as matchFilter does. If only one
+ // were rewritten per call, the Project created for the remaining ones
+ // would no longer define the correlation variables that they use, and a
+ // later call could not tell this Project's variables from those of an
+ // outer scope.
+ int count = 0;
+ List<RexNode> projects = project.getProjects();
+ while (true) {
+ final RexSubQuery e = RexUtil.SubQueryFinder.find(projects);
+ if (e == null) {
+ assert count > 0;
+ break;
+ }
+ ++count;
+ final RelOptUtil.Logic logic =
+ LogicVisitor.find(RelOptUtil.Logic.TRUE_FALSE_UNKNOWN, projects, e);
+ final Set<CorrelationId> variablesSet =
+ RelOptUtil.getVariablesUsed(e.rel);
// Only consider the correlated variables which originated from this
sub-query level.
variablesSet.retainAll(projectVariablesSet);
+ final RexNode target =
+ rule.apply(e, variablesSet, logic,
+ builder, 1, builder.peek().getRowType().getFieldCount(), count);
+ final RexShuttle shuttle = new ReplaceSubQueryShuttle(e, target);
+ projects = shuttle.apply(projects);
}
- final RexNode target =
- rule.apply(e, variablesSet, logic, builder, 1, fieldCount, 0);
- final RexShuttle shuttle = new ReplaceSubQueryShuttle(e, target);
- builder.project(shuttle.apply(project.getProjects()),
- project.getRowType().getFieldNames());
+ builder.project(projects, project.getRowType().getFieldNames());
call.transformTo(builder.build());
}
diff --git a/core/src/test/resources/sql/sub-query.iq
b/core/src/test/resources/sql/sub-query.iq
index 5df47b92f3..97ddb5c65d 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -11168,3 +11168,77 @@ ORDER BY empno;
(14 rows)
!ok
+
+# [CALCITE-7887] SubQueryRemoveRule fails for a sub-query inside a window
function
+# SELECT list with a sub-query correlated to the current scope followed by
+# one correlated to an outer scope. SubQueryRemoveRule used to rewrite one
+# sub-query of a Project per call, so the second one was bound to the inner
+# input instead of DEPT ("Correlation variable $cor1 should be defined").
+select d.dname,
+ (select max((select count(*) from emp as s where s.mgr = e.empno)
+ + (select count(*) from emp as s where s.deptno = d.deptno))
+ from emp as e) as c
+from dept as d
+order by d.dname;
++------------+----+
+| DNAME | C |
++------------+----+
+| ACCOUNTING | 8 |
+| OPERATIONS | 5 |
+| RESEARCH | 10 |
+| SALES | 11 |
++------------+----+
+(4 rows)
+
+!ok
+
+# The same sub-queries in the other order
+select d.dname,
+ (select max((select count(*) from emp as s where s.deptno = d.deptno)
+ + (select count(*) from emp as s where s.mgr = e.empno))
+ from emp as e) as c
+from dept as d
+order by d.dname;
++------------+----+
+| DNAME | C |
++------------+----+
+| ACCOUNTING | 8 |
+| OPERATIONS | 5 |
+| RESEARCH | 10 |
+| SALES | 11 |
++------------+----+
+(4 rows)
+
+!ok
+
+# Several sub-queries of different kinds in one SELECT list, each adding
+# auxiliary fields to the input
+select e.ename,
+ (select count(*) from emp as s where s.mgr = e.empno) as c1,
+ e.sal < (select max(s.sal) from emp as s) as c2,
+ exists (select 1 from emp as s where s.mgr = e.empno) as c3,
+ e.deptno in (select d.deptno from dept as d where d.loc = 'DALLAS') as c4,
+ e.mgr not in (select s.mgr from emp as s where s.job = 'CLERK') as c5
+from emp as e
+order by e.ename;
++--------+----+-------+-------+-------+-------+
+| ENAME | C1 | C2 | C3 | C4 | C5 |
++--------+----+-------+-------+-------+-------+
+| ADAMS | 0 | true | false | true | false |
+| ALLEN | 0 | true | false | false | false |
+| BLAKE | 5 | true | true | false | true |
+| CLARK | 1 | true | true | false | true |
+| FORD | 1 | true | true | true | true |
+| JAMES | 0 | true | false | false | false |
+| JONES | 2 | true | true | true | true |
+| KING | 3 | false | true | false | |
+| MARTIN | 0 | true | false | false | false |
+| MILLER | 0 | true | false | false | false |
+| SCOTT | 1 | true | true | true | true |
+| SMITH | 0 | true | false | true | false |
+| TURNER | 0 | true | false | false | false |
+| WARD | 0 | true | false | false | false |
++--------+----+-------+-------+-------+-------+
+(14 rows)
+
+!ok
