[CALCITE-2672] In JOIN ... USING and NATURAL JOIN, common columns should be unambiguous

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.22.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Chunwei Lei
• Reporter: Julian Hyde
• Created: 14/Nov/18 19:14
• Updated: 06/Mar/20 04:34
• Resolved: 07/Jan/20 02:49

Description

In JOIN ... USING and NATURAL JOIN, common columns should be unambiguous. For example, the following 3 queries are valid on Oracle, and the last is invalid. In each case, DEPTNO is the common column.

# DEPTNO is common to left and right side. It does not need to
# be qualified with "EMP." or "DEPT."
select deptno from emp natural join dept;
select count(deptno) from emp join dept using (deptno);   
select count(*) from emp natural join dept group by deptno;

# In fact, it is illegal to qualify
SQL> select dept.deptno from emp natural join dept;
ORA-25155: column used in NATURAL join cannot have qualifier

Here's a patch for join.iq:

diff --git a/core/src/test/resources/sql/join.iq b/core/src/test/resources/sql/join.iq
index 18a20fe8aa..4c1a5ab4c4 100644
--- a/core/src/test/resources/sql/join.iq
+++ b/core/src/test/resources/sql/join.iq
@@ -36,6 +36,22 @@ on emp.deptno = dept.deptno or emp.ename = dept.dname;
 
 !ok
 
+# Common column of NATURAL JOIN does not need to be qualified
+select deptno from emp natural join dept;
+!ok
+
+# Common column of JOIN ... USING does not need to be qualified
+select count(deptno) from emp join dept using (deptno);
+!ok
+
+# Common column of JOIN ... USING does not need to be qualified
+select count(*) from emp join dept using (deptno) group by deptno;
+!ok
+
+# Qualifying the common column is an error
+select dept.deptno from emp natural join dept;
+!error
+
 # As an INNER join, it can be executed as an equi-join followed by a filter

Currently, the first 3 queries wrongly give an error Column 'DEPTNO' is ambiguous, and the last query succeeds when it should fail.

Issue Links

• is related to: CALCITE-3387 Query with GROUP BY and JOIN ... USING wrongly fails with "Column 'DEPTNO' is ambiguous" error
• links to: GitHub Pull Request #1710