osdir.com

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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


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)