[CALCITE-5253] NATURAL join and USING should fail if join columns are not unique - expression validation partially broken

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.31.0
• Fix Version/s: 1.33.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Evgeny Stanilovsky
• Reporter: Evgeny Stanilovsky
• Created: 30/Aug/22 09:22
• Updated: 07/Feb/23 22:33
• Resolved: 03/Feb/23 10:30

Description

Such issue is not possible for now:

CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER);
CREATE TABLE t2(b INTEGER, c INTEGER, d INTEGER);
CREATE TABLE t3(c INTEGER, d INTEGER, e INTEGER);

SELECT t1.c, t2.d, t1.b, t1.a, t3.e FROM t1 natural join t2 natural join t3;

cause:

SqlValidatorException: Column name 'C' in NATURAL join or USING clause is not unique on one side of join

This validation is correct for example for case :

select e.ename, d.name from dept as d natural join (select ename, sal as deptno, deptno from emp) as e

but fails as described above.

Was broken by:
[1] https://issues.apache.org/jira/browse/CALCITE-5171

Issue Links

• Blocked: IGNITE-17644 Some natural join syntax is erroneously reported as not valid [CALCITE-5253]
• is caused by: CALCITE-5171 NATURAL join and USING should fail if join columns are not unique
• links to: GitHub Pull Request #2889