[CALCITE-4693] Query with Lateral Join should converted to Left Join …

[NobiGo]

…When sub-query is aggregate qurey

[sonarqubecloud[bot]]

Quality Gate passed

Issues
2 New issues
0 Accepted issues

Measures
0 Security Hotspots
100.0% Coverage on New Code
0.0% Duplication on New Code

See analysis details on SonarQube Cloud

[suibianwanwank]

I believe this is not the root cause of the incorrect query results. It's related to https://github.com/apache/calcite/pull/4375.

The issue occurs in the Decorrelator. PR-4375 appears to have missed some cases where parentPropagatesNullValues should be set to false when Correlate's joinType is not Left.

I'm confident the following modification can fix all tests in this PR.

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 b5500c871..e150079c7 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
@@ -1629,7 +1629,8 @@ private static boolean isWidening(RelDataType type, RelDataType type1) {
     }
 
     frameStack.push(Pair.of(rel.getCorrelationId(), leftFrame));
-    final Frame rightFrame = getInvoke(oldRight, true, rel, parentPropagatesNullValues);
+    final Frame rightFrame = getInvoke(oldRight, true, rel,
+        parentPropagatesNullValues && rel.getJoinType() == JoinRelType.LEFT);
     frameStack.pop();

[NobiGo]

I believe this is not the root cause of the incorrect query results. It's related to #4375.

The issue occurs in the Decorrelator. PR-4375 appears to have missed some cases where parentPropagatesNullValues should be set to false when Correlate's joinType is not Left.

I'm confident the following modification can fix all tests in this PR.

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 b5500c871..e150079c7 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
@@ -1629,7 +1629,8 @@ private static boolean isWidening(RelDataType type, RelDataType type1) {
     }
 
     frameStack.push(Pair.of(rel.getCorrelationId(), leftFrame));
-    final Frame rightFrame = getInvoke(oldRight, true, rel, parentPropagatesNullValues);
+    final Frame rightFrame = getInvoke(oldRight, true, rel,
+        parentPropagatesNullValues && rel.getJoinType() == JoinRelType.LEFT);
     frameStack.pop();

I will recheck this.

[github-actions[bot]]

This pull request has been marked as stale due to 30 days of inactivity. It will be closed in 90 days if no further activity occurs. If you think that’s incorrect or this pull request requires a review, please simply write any comment. If closed, you can revive the PR at any time and @mention a reviewer or discuss it on the [email protected] list. Thank you for your contributions.

[github-actions[bot]]

This pull request has been marked as stale due to 30 days of inactivity. It will be closed in 90 days if no further activity occurs. If you think that’s incorrect or this pull request requires a review, please simply write any comment. If closed, you can revive the PR at any time and @mention a reviewer or discuss it on the [email protected] list. Thank you for your contributions.