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)