This is an automated email from the ASF dual-hosted git repository.
mihaibudiu 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 d51ec640a8 [CALCITE-7543] RelBuilder.join should preserve variablesSet
for RIGHT/FULL joins
d51ec640a8 is described below
commit d51ec640a81b6674945933af56d8a801e421834b
Author: Weihua Zhang <[email protected]>
AuthorDate: Mon May 25 10:31:45 2026 +0800
[CALCITE-7543] RelBuilder.join should preserve variablesSet for RIGHT/FULL
joins
---
.../java/org/apache/calcite/tools/RelBuilder.java | 20 ++--
.../org/apache/calcite/test/RelBuilderTest.java | 46 +++++---
core/src/test/resources/sql/sub-query.iq | 123 +++++++++++++++++++++
3 files changed, 162 insertions(+), 27 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
index 9551c45395..cf9ccecdef 100644
--- a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
+++ b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
@@ -4465,21 +4465,17 @@ boolean isSimple() {
/**
* Checks for {@link CorrelationId}, then validates the id is not used on
left,
- * and finally checks if id is actually used on right.
+ * and finally checks whether the join should be converted to a {@link
Correlate}.
*
- * @return true if a correlate id is present and used
+ * @return true if the join should be converted to a Correlate; false if it
should remain a Join
*
- * @throws IllegalArgumentException if the {@link CorrelationId} is used by
left side or if the a
- * {@link CorrelationId} is present and the {@link JoinRelType} is FULL or
RIGHT.
+ * @throws IllegalArgumentException if the {@link CorrelationId} is used by
left side
*/
private boolean checkIfCorrelated(Set<CorrelationId> variablesSet,
JoinRelType joinType, RelNode leftNode, RelNode rightRel) {
if (variablesSet.size() != 1) {
return false;
}
- if (!config.convertCorrelateToJoin()) {
- return true;
- }
CorrelationId id = Iterables.getOnlyElement(variablesSet);
if (!RelOptUtil.notContainsCorrelation(leftNode, id, Litmus.IGNORE)) {
throw new IllegalArgumentException("variable " + id
@@ -4490,12 +4486,14 @@ private boolean checkIfCorrelated(Set<CorrelationId>
variablesSet,
case ASOF:
case RIGHT:
case FULL:
- throw new IllegalArgumentException("Correlated " + joinType + " join is
not supported");
+ return false;
default:
- return !RelOptUtil.correlationColumns(
- Iterables.getOnlyElement(variablesSet),
- rightRel).isEmpty();
+ break;
+ }
+ if (!config.convertCorrelateToJoin()) {
+ return true;
}
+ return !RelOptUtil.correlationColumns(id, rightRel).isEmpty();
}
diff --git a/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
b/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
index b54e08e26d..f1a4f1c271 100644
--- a/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
@@ -5090,28 +5090,42 @@ void testInnerCorrelateViaJoin(boolean
convertCorrelateToJoin) {
hasTree(expected));
}
- @Test void testSimpleRightCorrelateViaJoinThrowsException() {
- assertThrows(IllegalArgumentException.class,
- () -> buildSimpleCorrelateWithJoin(JoinRelType.RIGHT),
- "Right outer joins with correlated ids are invalid even if id is not
used.");
+ @Test void testSimpleRightCorrelateViaJoin() {
+ RelNode root = buildSimpleCorrelateWithJoin(JoinRelType.RIGHT);
+ final String expected = ""
+ + "LogicalJoin(condition=[=($7, $8)], joinType=[right],
variablesSet=[[$cor0]])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n";
+ assertThat(root, hasTree(expected));
}
- @Test void testSimpleFullCorrelateViaJoinThrowsException() {
- assertThrows(IllegalArgumentException.class,
- () -> buildSimpleCorrelateWithJoin(JoinRelType.FULL),
- "Full outer joins with correlated ids are invalid even if id is not
used.");
+ @Test void testSimpleFullCorrelateViaJoin() {
+ RelNode root = buildSimpleCorrelateWithJoin(JoinRelType.FULL);
+ final String expected = ""
+ + "LogicalJoin(condition=[=($7, $8)], joinType=[full],
variablesSet=[[$cor0]])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n";
+ assertThat(root, hasTree(expected));
}
- @Test void testRightCorrelateViaJoinThrowsException() {
- assertThrows(IllegalArgumentException.class,
- () -> buildCorrelateWithJoin(JoinRelType.RIGHT),
- "Right outer joins with correlated ids are invalid.");
+ @Test void testRightCorrelateViaJoin() {
+ RelNode root = buildCorrelateWithJoin(JoinRelType.RIGHT);
+ final String expected = ""
+ + "LogicalJoin(condition=[=($7, $8)], joinType=[right],
variablesSet=[[$cor0]])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalFilter(condition=[=($cor0.EMPNO, 'NaN')])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n";
+ assertThat(root, hasTree(expected));
}
- @Test void testFullCorrelateViaJoinThrowsException() {
- assertThrows(IllegalArgumentException.class,
- () -> buildCorrelateWithJoin(JoinRelType.FULL),
- "Full outer joins with correlated ids are invalid.");
+ @Test void testFullCorrelateViaJoin() {
+ RelNode root = buildCorrelateWithJoin(JoinRelType.FULL);
+ final String expected = ""
+ + "LogicalJoin(condition=[=($7, $8)], joinType=[full],
variablesSet=[[$cor0]])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalFilter(condition=[=($cor0.EMPNO, 'NaN')])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n";
+ assertThat(root, hasTree(expected));
}
private static RelNode buildSimpleCorrelateWithJoin(JoinRelType type) {
diff --git a/core/src/test/resources/sql/sub-query.iq
b/core/src/test/resources/sql/sub-query.iq
index a04c8634d2..2860493795 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -4975,6 +4975,129 @@ ORDER BY e1.empno, e1.deptno;
!ok
+# [CALCITE-7543] RelBuilder.join should preserve variablesSet for RIGHT/FULL
joins
+# this was validated using postgres
+SELECT empno
+FROM emp e
+RIGHT JOIN dept d
+ ON e.deptno = d.deptno
+ AND e.sal < (
+ SELECT MAX(e2.sal)
+ FROM emp e2
+ WHERE e2.deptno = d.deptno
+ )
+ORDER BY empno;
++-------+
+| EMPNO |
++-------+
+| 7369 |
+| 7499 |
+| 7521 |
+| 7566 |
+| 7654 |
+| 7782 |
+| 7844 |
+| 7876 |
+| 7900 |
+| 7934 |
+| |
++-------+
+(11 rows)
+
+!ok
+
+SELECT empno
+FROM emp e
+FULL JOIN dept d
+ ON e.deptno = d.deptno
+ AND e.sal < (
+ SELECT MAX(e2.sal)
+ FROM emp e2
+ WHERE e2.deptno = d.deptno
+ )
+ORDER BY empno;
++-------+
+| EMPNO |
++-------+
+| 7369 |
+| 7499 |
+| 7521 |
+| 7566 |
+| 7654 |
+| 7698 |
+| 7782 |
+| 7788 |
+| 7839 |
+| 7844 |
+| 7876 |
+| 7900 |
+| 7902 |
+| 7934 |
+| |
++-------+
+(15 rows)
+
+!ok
+
+SELECT empno
+FROM emp e
+RIGHT JOIN dept d
+ ON e.deptno = d.deptno
+ AND d.deptno <= ALL (
+ SELECT d2.deptno
+ FROM dept d2
+ WHERE d2.dname <> d.dname
+ )
+ORDER BY empno;
++-------+
+| EMPNO |
++-------+
+| 7782 |
+| 7839 |
+| 7934 |
+| |
+| |
+| |
++-------+
+(6 rows)
+
+!ok
+
+SELECT empno
+FROM emp e
+FULL JOIN dept d
+ ON e.deptno = d.deptno
+ AND d.deptno <= ALL (
+ SELECT d2.deptno
+ FROM dept d2
+ WHERE d2.dname <> d.dname
+ )
+ORDER BY empno;
++-------+
+| EMPNO |
++-------+
+| 7369 |
+| 7499 |
+| 7521 |
+| 7566 |
+| 7654 |
+| 7698 |
+| 7782 |
+| 7788 |
+| 7839 |
+| 7844 |
+| 7876 |
+| 7900 |
+| 7902 |
+| 7934 |
+| |
+| |
+| |
++-------+
+(17 rows)
+
+!ok
+
# [CALCITE-6041] MAP sub-query gives NullPointerException
# map size > 1
SELECT map(SELECT empno, deptno from emp where deptno < 20);
