[
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. 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 fails:
{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 error is "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.
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}
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. 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 fails:
> {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 error is "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.
>
>
> 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)
