[
https://issues.apache.org/jira/browse/CALCITE-7051?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zhen Chen resolved CALCITE-7051.
--------------------------------
Fix Version/s: 1.41.0
Resolution: Fixed
> NATURAL JOIN and 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
> Fix For: 1.41.0
>
>
> 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)
