You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by mb...@apache.org on 2025/07/01 18:50:24 UTC
(calcite) branch main updated: [CALCITE-4915] Test for query with unqualified common column and NATURAL JOIN
This is an automated email from the ASF dual-hosted git repository.
mbudiu 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 31c3a9f406 [CALCITE-4915] Test for query with unqualified common column and NATURAL JOIN
31c3a9f406 is described below
commit 31c3a9f406252bb365c629288ffdb9e8dd0e5f81
Author: Aleksey Plekhanov <pl...@gmail.com>
AuthorDate: Thu Dec 2 00:16:05 2021 +0300
[CALCITE-4915] Test for query with unqualified common column and NATURAL JOIN
---
.../apache/calcite/test/SqlToRelConverterTest.java | 51 +++++++++++++
.../apache/calcite/test/SqlToRelConverterTest.xml | 88 ++++++++++++++++++++++
core/src/test/resources/sql/join.iq | 26 +++++++
3 files changed, 165 insertions(+)
diff --git a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
index 761433d147..231b81d371 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -385,6 +385,57 @@ public static void checkActualAndReferenceFiles() {
sql(sql).ok();
}
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-4915">[CALCITE-4915]
+ * Query with unqualified common column and NATURAL JOIN fails</a>. */
+ @Test void testJoinNaturalWithUnqualifiedCommonColumn() {
+ final String sql = "SELECT deptno, name\n"
+ + "FROM emp\n"
+ + "NATURAL JOIN dept";
+ sql(sql).ok();
+ }
+
+ /** Similar to {@link #testJoinNaturalWithUnqualifiedCommonColumn()},
+ * but with nested common column. */
+ @Test void testJoinNaturalWithUnqualifiedNestedCommonColumn() {
+ final String sql =
+ "select (coord).x\n"
+ + "from customer.contact_peek t1\n"
+ + "natural join customer.contact_peek t2";
+ sql(sql).ok();
+ }
+
+ /** Similar to {@link #testJoinNaturalWithUnqualifiedCommonColumn()},
+ * but with aggregate. */
+ @Test void testJoinNaturalWithAggregate() {
+ final String sql = "select deptno, count(*)\n"
+ + "from emp\n"
+ + "natural join dept\n"
+ + "group by deptno";
+ sql(sql).ok();
+ }
+
+ /** Similar to {@link #testJoinNaturalWithUnqualifiedCommonColumn()},
+ * but with grouping sets. */
+ @Test void testJoinNaturalWithGroupingSets() {
+ final String sql = "select deptno, grouping(deptno),\n"
+ + "grouping(deptno, job), count(*)\n"
+ + "from emp\n"
+ + "natural join dept\n"
+ + "group by grouping sets ((deptno), (deptno, job))";
+ sql(sql).ok();
+ }
+
+ /** Similar to {@link #testJoinNaturalWithUnqualifiedCommonColumn()},
+ * but with multiple join. */
+ @Test void testJoinNaturalWithMultipleJoin() {
+ final String sql = "SELECT deptno, ename\n"
+ + "FROM emp\n"
+ + "NATURAL JOIN dept\n"
+ + "NATURAL JOIN (values ('Calcite', 200)) as s(ename, salary)";
+ sql(sql).ok();
+ }
+
/** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-3387">[CALCITE-3387]
* Query with GROUP BY and JOIN ... USING wrongly fails with
diff --git a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
index cde9a7b197..8314beafdf 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -3707,6 +3707,94 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(FOO=[$0], NAME=[$1])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testJoinNaturalWithAggregate">
+ <Resource name="sql">
+ <![CDATA[select deptno, count(*)
+from emp
+natural join dept
+group by deptno]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0], EXPR$1=[$1])
+ LogicalAggregate(group=[{0}], EXPR$1=[COUNT()])
+ LogicalProject($f0=[$7])
+ LogicalJoin(condition=[=($7, $9)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testJoinNaturalWithGroupingSets">
+ <Resource name="sql">
+ <![CDATA[select deptno, grouping(deptno),
+grouping(deptno, job), count(*)
+from emp
+natural join dept
+group by grouping sets ((deptno), (deptno, job))]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0], EXPR$1=[$2], EXPR$2=[$3], EXPR$3=[$4])
+ LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], EXPR$1=[GROUPING($0)], EXPR$2=[GROUPING($0, $1)], EXPR$3=[COUNT()])
+ LogicalProject($f0=[$7], JOB=[$2])
+ LogicalJoin(condition=[=($7, $9)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testJoinNaturalWithMultipleJoin">
+ <Resource name="sql">
+ <![CDATA[SELECT deptno, ename
+FROM emp
+NATURAL JOIN dept
+NATURAL JOIN (values ('Calcite', 200)) as s(ename, salary)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(DEPTNO=[$7], ENAME=[$1])
+ LogicalJoin(condition=[=($1, $13)], joinType=[inner])
+ LogicalJoin(condition=[=($7, $9)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalProject(ENAME=[$0], SALARY=[$1], ENAME0=[CAST($0):VARCHAR(20) NOT NULL])
+ LogicalValues(tuples=[[{ 'Calcite', 200 }]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testJoinNaturalWithUnqualifiedCommonColumn">
+ <Resource name="sql">
+ <![CDATA[SELECT deptno, name
+FROM emp
+NATURAL JOIN dept]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(DEPTNO=[$7], NAME=[$10])
+ LogicalJoin(condition=[=($7, $9)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testJoinNaturalWithUnqualifiedNestedCommonColumn">
+ <Resource name="sql">
+ <![CDATA[select (coord).x
+from customer.contact_peek t1
+natural join customer.contact_peek t2]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EXPR$0=[COALESCE(ROW($4, $5, $6), ROW($14, $15, $16)).X])
+ LogicalJoin(condition=[AND(=($0, $10), =($1, $11), =($2, $12), =($3, $13), AND(AND(=($4, $14), =($5, $15)), =($6, $16)), AND(AND(=($7, $17), =($8, $18)), =($9, $19)))], joinType=[inner])
+ LogicalProject(CONTACTNO=[$0], FNAME=[$1], LNAME=[$2], EMAIL=[$3], X=[$4.X], Y=[$4.Y], unit=[$4.unit], M=[$5.M], A=[$5.SUB.A], B=[$5.SUB.B])
+ LogicalTableScan(table=[[CATALOG, CUSTOMER, CONTACT_PEEK]])
+ LogicalProject(CONTACTNO=[$0], FNAME=[$1], LNAME=[$2], EMAIL=[$3], X=[$4.X], Y=[$4.Y], unit=[$4.unit], M=[$5.M], A=[$5.SUB.A], B=[$5.SUB.B])
+ LogicalTableScan(table=[[CATALOG, CUSTOMER, CONTACT_PEEK]])
]]>
</Resource>
</TestCase>
diff --git a/core/src/test/resources/sql/join.iq b/core/src/test/resources/sql/join.iq
index 5355d57845..1e1f5135e2 100644
--- a/core/src/test/resources/sql/join.iq
+++ b/core/src/test/resources/sql/join.iq
@@ -1004,4 +1004,30 @@ ON t1.a IS NOT DISTINCT FROM t2.a;
!ok
+### [CALCITE-4915] Query with unqualified common column and NATURAL JOIN fails
+select deptno, dname, empno, ename
+from "scott".emp
+natural join "scott".dept order by empno;
++--------+------------+-------+--------+
+| DEPTNO | DNAME | EMPNO | ENAME |
++--------+------------+-------+--------+
+| 20 | RESEARCH | 7369 | SMITH |
+| 30 | SALES | 7499 | ALLEN |
+| 30 | SALES | 7521 | WARD |
+| 20 | RESEARCH | 7566 | JONES |
+| 30 | SALES | 7654 | MARTIN |
+| 30 | SALES | 7698 | BLAKE |
+| 10 | ACCOUNTING | 7782 | CLARK |
+| 20 | RESEARCH | 7788 | SCOTT |
+| 10 | ACCOUNTING | 7839 | KING |
+| 30 | SALES | 7844 | TURNER |
+| 20 | RESEARCH | 7876 | ADAMS |
+| 30 | SALES | 7900 | JAMES |
+| 20 | RESEARCH | 7902 | FORD |
+| 10 | ACCOUNTING | 7934 | MILLER |
++--------+------------+-------+--------+
+(14 rows)
+
+!ok
+
# End join.iq