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

["Chunwei Lei (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-2672?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Chunwei Lei reassigned CALCITE-2672:
------------------------------------

    Assignee: Chunwei Lei

> 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: Chunwei Lei
>            Priority: Major
>
> 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
(v8.3.4#803005)