[
https://issues.apache.org/jira/browse/CALCITE-5253?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17608888#comment-17608888
]
Julian Hyde commented on CALCITE-5253:
--------------------------------------
Can you add a couple more tests?
{noformat}
diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
index 823e11c212..c9043d9bd9 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -6204,6 +6204,22 @@ private ImmutableList<ImmutableBitSet>
cube(ImmutableBitSet... sets) {
// Previous join chain does not affect validation.
sql("select * from EMP natural join EMPNULLABLES natural join DEPT")
.ok();
+
+ // Invalid. NATURAL JOIN eliminates duplicate columns from its output but
+ // requires input columns to be unique.
+ sql("select *\n"
+ + "from (emp as e cross join dept as d)\n"
+ + "natural join\n"
+ + "(emp as e2 cross join dept as d2)")
+ .fails("xx");
+
+ // Should produce two DEPTNO columns.
+ sql("select *\n"
+ + "from emp as e\n"
+ + "natural join dept as d\n"
+ + "join (select deptno as x, deptno from dept) as d2"
+ + " on d2.deptno = e.deptno")
+ .type("xx");
}
@Test void testNaturalEmptyKey() {
{noformat}
> NATURAL join and USING should fail if join columns are not unique -
> expression validation partially broken.
> -----------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-5253
> URL: https://issues.apache.org/jira/browse/CALCITE-5253
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.31.0
> Reporter: Evgeny Stanilovsky
> Assignee: Evgeny Stanilovsky
> Priority: Major
> Labels: pull-request-available
>
> Such issue is not possible for now:
> {code:java}
> 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;
> {code}
> cause:
> {noformat}
> SqlValidatorException: Column name 'C' in NATURAL join or USING clause is not
> unique on one side of join
> {noformat}
> This validation is correct for example for case :
>
> {noformat}
> select e.ename, d.name from dept as d natural join (select ename, sal as
> deptno, deptno from emp) as e
> {noformat}
> but fails as described above.
> Was broken by:
> [1] https://issues.apache.org/jira/browse/CALCITE-5171
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
