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 b1ba4e9aec [CALCITE-4693] Decorrelation drops rows for inner LATERAL
join over aggregate without GROUP BY
b1ba4e9aec is described below
commit b1ba4e9aecae70586eaa8a442397c2bc3dda55e1
Author: Gian Merlino <[email protected]>
AuthorDate: Wed Oct 28 22:11:04 2026 -0700
[CALCITE-4693] Decorrelation drops rows for inner LATERAL join over
aggregate without GROUP BY
---
.../apache/calcite/sql2rel/RelDecorrelator.java | 15 +-
.../sql2rel/TopDownGeneralDecorrelator.java | 7 +-
core/src/test/resources/sql/lateral.iq | 225 +++++++++++++++++++++
3 files changed, 238 insertions(+), 9 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
b/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
index 02ec8afb66..585b9d0196 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
@@ -581,7 +581,7 @@ protected RexNode removeCorrelationExpr(
}
final RelNode oldInput = rel.getInput();
- final Frame frame = getInvoke(oldInput, isCorVarDefined, rel, true);
+ final Frame frame = getInvoke(oldInput, isCorVarDefined, rel,
parentPropagatesNullValues);
if (frame == null) {
// If input has not been rewritten, do not rewrite this rel.
return null;
@@ -1910,8 +1910,7 @@ private static boolean isWidening(RelDataType type,
RelDataType type1) {
frameStack.push(Pair.of(rel.getCorrelationId(), leftFrame));
final Frame rightFrame =
- getInvoke(oldRight, true, rel,
- rel.getJoinType() == JoinRelType.LEFT ||
parentPropagatesNullValues);
+ getInvoke(oldRight, true, rel, rel.getJoinType() == JoinRelType.LEFT);
frameStack.pop();
if (rightFrame == null || rightFrame.corDefOutputs.isEmpty()) {
@@ -2028,8 +2027,14 @@ private static boolean isWidening(RelDataType type,
RelDataType type1) {
final RelNode oldLeft = rel.getInput(0);
final RelNode oldRight = rel.getInput(1);
- final Frame leftFrame = getInvoke(oldLeft, isCorVarDefined, rel,
parentPropagatesNullValues);
- final Frame rightFrame = getInvoke(oldRight, isCorVarDefined, rel,
parentPropagatesNullValues);
+ // Decorrelate recursively. Setting parentPropagatesNullValues lets a
scalar aggregate return
+ // no row for a corVar value, rather than a row of NULLs. That is safe
only for the side of a
+ // LEFT or RIGHT join that generates NULL values, since the join then
supplies those NULLs.
+ // FULL does not qualify, because it must output the aggregate's row even
when it has no match.
+ final Frame leftFrame =
+ getInvoke(oldLeft, isCorVarDefined, rel, rel.getJoinType() ==
JoinRelType.RIGHT);
+ final Frame rightFrame =
+ getInvoke(oldRight, isCorVarDefined, rel, rel.getJoinType() ==
JoinRelType.LEFT);
if (leftFrame == null || rightFrame == null) {
// If any input has not been rewritten, do not rewrite this rel.
diff --git
a/core/src/main/java/org/apache/calcite/sql2rel/TopDownGeneralDecorrelator.java
b/core/src/main/java/org/apache/calcite/sql2rel/TopDownGeneralDecorrelator.java
index ead86414f2..8b31b5cdd1 100644
---
a/core/src/main/java/org/apache/calcite/sql2rel/TopDownGeneralDecorrelator.java
+++
b/core/src/main/java/org/apache/calcite/sql2rel/TopDownGeneralDecorrelator.java
@@ -330,7 +330,7 @@ private RelNode correlateElimination(RelNode rel, boolean
allowEmptyOutputFromRe
// decorrelate right side
detectCorrelatedExpressions(correlate.getRight());
- allowEmptyOutputFromRewrite &= correlate.getJoinType() !=
JoinRelType.LEFT_MARK;
+ allowEmptyOutputFromRewrite &= correlate.getJoinType() == JoinRelType.LEFT;
RelNode newRight = unnest(correlate.getRight(),
allowEmptyOutputFromRewrite);
UnnestedQuery rightInfo =
requireNonNull(mapRelToUnnestedQuery.get(correlate.getRight()));
@@ -748,7 +748,7 @@ public RelNode unnestInternal(Join join, boolean
allowEmptyOutputFromRewrite) {
} else {
// when neither the left nor the right side has correlation,
// but the join condition has correlation, domain D is pushed down to
the left by default.
- newLeft = unnest(join.getLeft(), allowEmptyOutputFromRewrite);
+ newLeft = unnest(join.getLeft(), join.getJoinType() ==
JoinRelType.RIGHT);
pushDownToLeft = true;
leftInfo = requireNonNull(mapRelToUnnestedQuery.get(join.getLeft()));
}
@@ -770,8 +770,7 @@ public RelNode unnestInternal(Join join, boolean
allowEmptyOutputFromRewrite) {
rightInfo =
new UnnestedQuery(join.getRight(), newRight, new TreeMap<>(),
rightOldToNewOutputs);
} else {
- allowEmptyOutputFromRewrite &= join.getJoinType() !=
JoinRelType.LEFT_MARK;
- newRight = unnest(join.getRight(), allowEmptyOutputFromRewrite);
+ newRight = unnest(join.getRight(), join.getJoinType() ==
JoinRelType.LEFT);
pushDownToRight = true;
rightInfo = requireNonNull(mapRelToUnnestedQuery.get(join.getRight()));
}
diff --git a/core/src/test/resources/sql/lateral.iq
b/core/src/test/resources/sql/lateral.iq
index 12fe348741..f560c78d67 100644
--- a/core/src/test/resources/sql/lateral.iq
+++ b/core/src/test/resources/sql/lateral.iq
@@ -331,4 +331,229 @@ EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC],
dir1=[ASC])
!set planner-rules original
+# [CALCITE-4693] An aggregate without GROUP BY returns one row even for
+# empty input, so every department must appear.
+select d.deptno, t.s
+from dept as d
+cross join lateral (select sum(e.sal) as s from emp as e where e.deptno =
d.deptno) as t
+order by d.deptno;
++--------+----------+
+| DEPTNO | S |
++--------+----------+
+| 10 | 8750.00 |
+| 20 | 10875.00 |
+| 30 | 9400.00 |
+| 40 | |
++--------+----------+
+(4 rows)
+
+!ok
+
+# [CALCITE-4693] As above, but with a LIMIT 1 in the subquery.
+select d.deptno, t.s
+from dept as d
+cross join lateral (select sum(e.sal) as s from emp as e where e.deptno =
d.deptno limit 1) as t;
++--------+----------+
+| DEPTNO | S |
++--------+----------+
+| 10 | 8750.00 |
+| 20 | 10875.00 |
+| 30 | 9400.00 |
+| 40 | |
++--------+----------+
+(4 rows)
+
+!ok
+
+# [CALCITE-4693] The aggregate is the left input of an inner join inside a
+# LEFT JOIN LATERAL, so department 40 must have X = 1, not NULL.
+select d.deptno, t.s, t.x
+from dept as d
+left join lateral (
+ select a.s, b.x
+ from (select sum(e.sal) as s from emp as e where e.deptno = d.deptno) as a
+ join (values (1)) as b(x) on true) as t on true
+order by d.deptno;
++--------+----------+---+
+| DEPTNO | S | X |
++--------+----------+---+
+| 10 | 8750.00 | 1 |
+| 20 | 10875.00 | 1 |
+| 30 | 9400.00 | 1 |
+| 40 | | 1 |
++--------+----------+---+
+(4 rows)
+
+!ok
+
+# [CALCITE-4693] As above, but the aggregate is the right input of the inner
+# join.
+select d.deptno, t.s, t.x
+from dept as d
+left join lateral (
+ select a.s, b.x
+ from (values (1)) as b(x)
+ join (select sum(e.sal) as s from emp as e where e.deptno = d.deptno) as a
on true) as t on true
+order by d.deptno;
++--------+----------+---+
+| DEPTNO | S | X |
++--------+----------+---+
+| 10 | 8750.00 | 1 |
+| 20 | 10875.00 | 1 |
+| 30 | 9400.00 | 1 |
+| 40 | | 1 |
++--------+----------+---+
+(4 rows)
+
+!ok
+
+# [CALCITE-4693] The aggregate is the preserved input of a LEFT JOIN, so
+# department 40 must have X = 1, not NULL.
+select d.deptno, t.s, t.x
+from dept as d
+cross join lateral (
+ select a.s, b.x
+ from (select sum(e.sal) as s from emp as e where e.deptno = d.deptno) as a
+ left join (values (1)) as b(x) on true) as t
+order by d.deptno;
++--------+----------+---+
+| DEPTNO | S | X |
++--------+----------+---+
+| 10 | 8750.00 | 1 |
+| 20 | 10875.00 | 1 |
+| 30 | 9400.00 | 1 |
+| 40 | | 1 |
++--------+----------+---+
+(4 rows)
+
+!ok
+
+# [CALCITE-4693] The aggregate is the null-generating input of a LEFT JOIN,
+# where no row has the same effect as a row of nulls.
+select d.deptno, t.x, t.s
+from dept as d
+cross join lateral (
+ select b.x, a.s
+ from (values (1)) as b(x)
+ left join (select sum(e.sal) as s from emp as e where e.deptno = d.deptno)
as a on true) as t
+order by d.deptno;
++--------+---+----------+
+| DEPTNO | X | S |
++--------+---+----------+
+| 10 | 1 | 8750.00 |
+| 20 | 1 | 10875.00 |
+| 30 | 1 | 9400.00 |
+| 40 | 1 | |
++--------+---+----------+
+(4 rows)
+
+!ok
+
+# [CALCITE-4693] As above, but with a condition on the aggregate.
+select d.deptno, t.x, t.s
+from dept as d
+cross join lateral (
+ select b.x, a.s
+ from (values (1)) as b(x)
+ left join (select sum(e.sal) as s from emp as e where e.deptno = d.deptno)
as a
+ on a.s > 9000) as t
+order by d.deptno;
++--------+---+----------+
+| DEPTNO | X | S |
++--------+---+----------+
+| 10 | 1 | |
+| 20 | 1 | 10875.00 |
+| 30 | 1 | 9400.00 |
+| 40 | 1 | |
++--------+---+----------+
+(4 rows)
+
+!ok
+
+# [CALCITE-4693] As above, but with COUNT, which is 0 rather than NULL for
+# department 40.
+select d.deptno, t.x, t.c
+from dept as d
+cross join lateral (
+ select b.x, a.c
+ from (values (1)) as b(x)
+ left join (select count(*) as c from emp as e where e.deptno = d.deptno) as
a on true) as t
+order by d.deptno;
++--------+---+---+
+| DEPTNO | X | C |
++--------+---+---+
+| 10 | 1 | 3 |
+| 20 | 1 | 5 |
+| 30 | 1 | 6 |
+| 40 | 1 | 0 |
++--------+---+---+
+(4 rows)
+
+!ok
+
+# [CALCITE-4693] The aggregate is the null-generating input of a RIGHT JOIN.
+select d.deptno, t.s, t.x
+from dept as d
+cross join lateral (
+ select a.s, b.x
+ from (select sum(e.sal) as s from emp as e where e.deptno = d.deptno) as a
+ right join (values (1)) as b(x) on true) as t
+order by d.deptno;
++--------+----------+---+
+| DEPTNO | S | X |
++--------+----------+---+
+| 10 | 8750.00 | 1 |
+| 20 | 10875.00 | 1 |
+| 30 | 9400.00 | 1 |
+| 40 | | 1 |
++--------+----------+---+
+(4 rows)
+
+!ok
+
+# [CALCITE-4693] The aggregate is an input of a FULL JOIN, which preserves it
+# as well as generating nulls for it, so department 40 must have both a row
+# with X = 1 and an unmatched row with X = NULL.
+select d.deptno, t.s, t.x
+from dept as d
+cross join lateral (
+ select a.s, b.x
+ from (select sum(e.sal) as s from emp as e where e.deptno = d.deptno) as a
+ full join (values (1)) as b(x) on a.s > 9000) as t
+order by d.deptno, t.s, t.x;
++--------+----------+---+
+| DEPTNO | S | X |
++--------+----------+---+
+| 10 | 8750.00 | |
+| 10 | | 1 |
+| 20 | 10875.00 | 1 |
+| 30 | 9400.00 | 1 |
+| 40 | | 1 |
+| 40 | | |
++--------+----------+---+
+(6 rows)
+
+!ok
+
+# [CALCITE-4693] An aggregate without GROUP BY returns one row even for
+# empty input, so every department must appear.
+!use post
+select k.deptno,
+ k.deptno - t2.totalAmount as amountDue
+from dept as k,
+ lateral (select sum(emp.deptno) as totalAmount
+ from emp
+ where emp.deptno = k.deptno) as t2;
++--------+-----------+
+| DEPTNO | AMOUNTDUE |
++--------+-----------+
+| 10 | -10 |
+| 20 | 0 |
+| 30 | -30 |
+| 40 | |
++--------+-----------+
+(4 rows)
+
+!ok
+
# End lateral.iq
