Julian Hyde created CALCITE-2672:
------------------------------------
Summary: In JOIN ... USING and NATURAL JOIN, common columns should
be unambiguous
Key: CALCITE-2672
URL: https://issues.apache.org/jira/browse/CALCITE-2672
Project: Calcite
Issue Type: Bug
Components: core
Reporter: Julian Hyde
Assignee: Julian Hyde
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.
{noformat}
# 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
{noformat}
Here's a patch for {{join.iq}}:
{noformat}
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
{noformat}
Currently, the first 3 queries wrongly give an error {{Column 'DEPTNO' is
ambiguous}}, and the last query succeeds when it should fail.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
