[
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 have set caseSensitive as false(and
quotedCasing,unquotedCasing=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 not ignored when determining whether the identifier
in selectItem 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 ignored 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.
I think it is because that caseSensitive is not handled well in the following
method.
[https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L529]
Add one example for natural join, which will also failed.
{code:java}
select deptno from emp natural join dept{code}
was:
In JOIN ... USING, if have set caseSensitive as false(and
quotedCasing,unquotedCasing=UNCHANGED), common columns should be success
regardless of case. For example, the following 4 queries are valid on Presto,
and both of them maybe failed, it depends on the case of column name in
catalog. In each case, {{DEPTNO}} is the common column.
{code:java}
select DEPTNO from emp join dept using (DEPTNO);
select deptno from emp join dept using (deptno);
select DEPTNO from emp join dept using (deptno);
select deptno from emp join dept using (DEPTNO); {code}
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 not ignored when determining whether the identifier
in selectItem 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}
{code}
I think it is because that caseSensitive is not handled well in the following
method.
[https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L529]
Add one example for natural join, which will also failed.
{code:java}
select deptno from emp natural join dept{code}
> 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 have set caseSensitive as false(and
> quotedCasing,unquotedCasing=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 not ignored when determining whether the
> identifier in selectItem 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 ignored 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.
>
> I think it is because that caseSensitive is not handled well in the
> following method.
> [https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L529]
>
>
> Add one example for natural join, which will also failed.
> {code:java}
> select deptno from emp natural join dept{code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
