[
https://issues.apache.org/jira/browse/CALCITE-7051?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17966809#comment-17966809
]
Julian Hyde commented on CALCITE-7051:
--------------------------------------
I added some comments to the PR.
It is not clear to me what should happen if the underlying columns have
different case. E.g. one table has "DEPTNO", the other table has "Deptno" and
the user writes "using (deptno)" or "natural join". Should this be a match?
Your tests don't cover this case, and I think they should.
> JOIN with USING does not match the appropriate columns when caseSensitive is
> false
> ----------------------------------------------------------------------------------
>
> Key: CALCITE-7051
> URL: https://issues.apache.org/jira/browse/CALCITE-7051
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Xiong Tenghui
> Assignee: Xiong Tenghui
> Priority: Minor
> Labels: pull-request-available
>
> In JOIN ... USING, if caseSensitive is false(and quotedCasing,unquotedCasing
> is UNCHANGED), common columns should be success regardless of case.
> The following tests in SqlValidatorTest fail:
> {code:java}
> sql("select DEPTNO from emp join dept using (deptno)")
> .withCaseSensitive(false)
> .withValidatorIdentifierExpansion(true)
> .withQuotedCasing(Casing.UNCHANGED)
> .withUnquotedCasing(Casing.UNCHANGED)
> .ok();
> sql("select deptno from emp join dept using (DEPTNO)")
> .withCaseSensitive(false)
> .withValidatorIdentifierExpansion(true)
> .withQuotedCasing(Casing.UNCHANGED)
> .withUnquotedCasing(Casing.UNCHANGED)
> .ok(); {code}
> and errors both are"Column 'DEPTNO' is ambiguous".
> This is because the case is always sensitive when determining whether the
> identifier in select item and common column in USING are equal.
> [https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L539]
>
> The following test in SqlValidatorTest also fails:
> {code:java}
> sql("select deptno from emp join dept using (deptno)")
> .withCaseSensitive(false)
> .withValidatorIdentifierExpansion(true)
> .withQuotedCasing(Casing.UNCHANGED)
> .withUnquotedCasing(Casing.UNCHANGED)
> .ok();{code}
> and the error is "AssertionError" on "assert qualifiedNode.size() == 2"
> [https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L551]
> This is because that the case is always sensitive when determining whether
> fieldNames contains the common column in USING.
> [https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L542]
> And because of the same reason above, the following test also fails:
> {code:java}
> final SchemaPlus rootSchema = Frameworks.createRootSchema(true);
> final FrameworkConfig config = Frameworks.newConfigBuilder()
> .parserConfig(SqlParser.config().withLex(Lex.MYSQL))
> .defaultSchema(CalciteAssert.addSchema(rootSchema,
> CalciteAssert.SchemaSpec.HR))
> .traitDefs((List<RelTraitDef>) null)
> .programs(Programs.ofRules(Programs.RULE_SET))
> .build();
> Planner planner = Frameworks.getPlanner(config);
> SqlNode parse = planner.parse("select DEPTNO from emps join depts using
> (DEPTNO)");
> SqlNode validate = planner.validate(parse); {code}
> The case of fieldNames is affected by the case of column name in the schema,
> so this test use the schema different to the schema in SqlValidatorTest to
> reproduce the error.
>
>
> For NATURAL JOIN, the following test fails:
> {code:java}
> sql("select deptno from emp natural join dept")
> .withCaseSensitive(false)
> .withValidatorIdentifierExpansion(true)
> .withQuotedCasing(Casing.UNCHANGED)
> .withUnquotedCasing(Casing.UNCHANGED)
> .ok(); {code}
> This is because the method "expandExprFromJoin" does not handle common
> columns in NATURAL JOIN clause.
> [https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L529]
>
> In conclusion, the method "expandExprFromJoin" should be changed to resolve
> the issues above.
> [https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L529]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
