[
https://issues.apache.org/jira/browse/CALCITE-7051?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Xiong Tenghui updated CALCITE-7051:
-----------------------------------
Description:
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 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 avoid
issues above.
[https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L529]
was:
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 column name in the schema, so this
test use the schema different to the schema of 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 avoid
issues above.
[https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L529]
> 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 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 avoid
> 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)
