[jira] [Resolved] (CALCITE-7051) NATURAL JOIN and JOIN with USING does not match the appropriate columns when caseSensitive is false

["Zhen Chen (Jira)" <ji...@apache.org>]

     [ 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)