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

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.22.0
• Labels: pull-request-available
• Assignee: Chunwei Lei
• Reporter: Julian Hyde
• Created: 04/Oct/19 21:34
• Updated: 02/Dec/21 07:45
• Resolved: 23/Dec/19 02:44

Description

Query with GROUP BY and JOIN ... USING wrongly fails with "Column 'DEPTNO' is ambiguous" error. Here is the query:

select deptno, count(*)
from emp
join dept using (deptno)
group by deptno;

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

select deptno
from emp
join dept using (deptno)

works correctly in Calcite, and has since CALCITE-2227 was fixed.

Issue Links

• is related to: CALCITE-2227 "SELECT *" in a NATURAL JOIN and JOIN USING query should output join columns only once
• relates to: CALCITE-4915 Query with unqualified common column and NATURAL JOIN fails
• relates to: CALCITE-2672 In JOIN ... USING and NATURAL JOIN, common columns should be unambiguous
• links to: GitHub Pull Request #1655