[
https://issues.apache.org/jira/browse/CALCITE-2672?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17009389#comment-17009389
]
Chunwei Lei commented on CALCITE-2672:
--------------------------------------
I also removed some dead codes and duplicate codes in RexBuilder which
mentioned in the commit message.
> 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
> Labels: pull-request-available
> Fix For: 1.22.0
>
> Time Spent: 2.5h
> Remaining Estimate: 0h
>
> 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)
