[jira] [Commented] (CALCITE-3387) Query with GROUP BY and JOIN ... USING wrongly fails with "Column 'DEPTNO' is ambiguous" error

["Julian Hyde (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-3387?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16944926#comment-16944926 ] 

Julian Hyde commented on CALCITE-3387:
--------------------------------------

Here is a test case:
{noformat}
diff --git a/core/src/test/resources/sql/join.iq b/core/src/test/resources/sql/join.iq
index 18fc8f5c3..bcbe54cf9 100644
--- a/core/src/test/resources/sql/join.iq
+++ b/core/src/test/resources/sql/join.iq
@@ -172,6 +172,38 @@ group by cube(emp.deptno, dept.deptno);
 
 !ok
 
+# [CALCITE-3387] Query with GROUP BY and JOIN ... USING wrongly fails with
+# "Column 'DEPTNO' is ambiguous" error
+select deptno, count(*)
+from "scott".emp
+join "scott".dept using (deptno)
+group by deptno;
+!ok
+
+# Similar, with GROUPING SETS and GROUPING
+select deptno, grouping(deptno), grouping(deptno, job), count(*)
+from "scott".emp
+join "scott".dept using (deptno)
+group by grouping sets ((deptno), (deptno, job));
+!ok
+
+# For this query, Oracle gives
+#   ORA-25154: column part of USING clause cannot have qualifier
+# and Calcite should too.
+select dept.deptno, count(*)
+from "scott".emp
+join "scott".dept using (deptno);
+column part of USING clause cannot have qualifier
+!error
+
+# Ditto
+select count(*)
+from "scott".emp
+join "scott".dept using (deptno)
+group by dept.deptno;
+column part of USING clause cannot have qualifier
+!error
+
 # [CALCITE-688] splitCondition does not behave correctly
 # when one side of the condition references columns from
 # different inputs
{noformat}

> Query with GROUP BY and JOIN ... USING wrongly fails with "Column 'DEPTNO' is ambiguous" error
> ----------------------------------------------------------------------------------------------
>
>                 Key: CALCITE-3387
>                 URL: https://issues.apache.org/jira/browse/CALCITE-3387
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: Julian Hyde
>            Priority: Major
>
> Query with GROUP BY and JOIN ... USING wrongly fails with "Column 'DEPTNO' is ambiguous" error. Here is the query:
> {code}
> select deptno, count(*)
> from emp
> join dept using (deptno)
> group by deptno;
> {code}
> Because of USING, the two deptno fields should be merged into one, and therefore {{deptno}} is not ambiguous. That query works in Oracle. Also note that
> {code}select deptno
> from emp
> join dept using (deptno)
> {code}
> works correctly in Calcite, and has since CALCITE-2227 was fixed.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)