[jira] [Comment Edited] (CALCITE-6370) AS operator problems with USING clause

["Norman Jordan (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-6370?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17848766#comment-17848766 ] 

Norman Jordan edited comment on CALCITE-6370 at 5/22/24 10:16 PM:
------------------------------------------------------------------

I was able to reproduce this issue by adding a test to the {*}RelToSqlConverterTest{*}.
{code:java}
@Test void testUsingClause() {
  String query = "select \"product_id\" as \"x\" from \"foodmart\".\"product\" p0 join "
      + " \"foodmart\".\"product\" p1 using (\"product_id\")";
  String expectedQuery = "select \"product_id\" as \"x\" from \"foodmart\".\"product\" join "
      + " \"foodmart\".\"product\" using (\"product_id\")";
  sql(query)
      .withPostgresql().ok(expectedQuery);
} {code}


was (Author: njordan):
I was able to reproduce this issue by adding a test to the `RelToSqlConverterTest`.
{code:java}
@Test void testUsingClause() {
  String query = "select \"product_id\" as \"x\" from \"foodmart\".\"product\" p0 join "
      + " \"foodmart\".\"product\" p1 using (\"product_id\")";
  String expectedQuery = "select \"product_id\" as \"x\" from \"foodmart\".\"product\" join "
      + " \"foodmart\".\"product\" using (\"product_id\")";
  sql(query)
      .withPostgresql().ok(expectedQuery);
} {code}

> AS operator problems with USING clause
> --------------------------------------
>
>                 Key: CALCITE-6370
>                 URL: https://issues.apache.org/jira/browse/CALCITE-6370
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.36.0
>            Reporter: Claude Brisson
>            Priority: Major
>
> In some cases, with the {{USING}} clause, the used column generates ambiguity exceptions with the {{AS}} operator.
>  
> OK : {{select r_regionkey from region r0 join region r1 using (r_regionkey)}}
> OK : {{select r_regionkey * 2 as rk2 from region r0 join region r1 using (r_regionkey)}}
> KO : {{select r_regionkey as rk from region r0 join region r1 using (r_regionkey)}}
>  
> The last query generates the following error:
>  
> {code:java}
>  org.apache.calcite.sql.validate.SqlValidatorException: Column 'r_regionkey' is ambiguous
>     at jdk.internal.reflect.DirectConstructorHandleAccessor.newInstance (DirectConstructorHandleAccessor.java:62)
>     at java.lang.reflect.Constructor.newInstanceWithCaller (Constructor.java:502)
>     at java.lang.reflect.Constructor.newInstance (Constructor.java:486)
>     at org.apache.calcite.runtime.Resources$ExInstWithCause.ex (Resources.java:507)
>     at org.apache.calcite.runtime.Resources$ExInst.ex (Resources.java:601)
>     at org.apache.calcite.sql.SqlUtil.newContextException (SqlUtil.java:948)
>     at org.apache.calcite.sql.SqlUtil.newContextException (SqlUtil.java:933)
>     at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError (SqlValidatorImpl.java:5517)
>     at org.apache.calcite.sql.validate.DelegatingScope.fullyQualify (DelegatingScope.java:296)
>     at org.apache.calcite.sql.validate.SqlValidatorImpl.findTableColumnPair (SqlValidatorImpl.java:3989)
>     at org.apache.calcite.sql.validate.SqlValidatorImpl.isRolledUpColumn (SqlValidatorImpl.java:4032)
>     at org.apache.calcite.sql.validate.SqlValidatorImpl.checkRollUp (SqlValidatorImpl.java:3945)
>     at org.apache.calcite.sql.validate.SqlValidatorImpl.checkRollUp (SqlValidatorImpl.java:3940)
>     at org.apache.calcite.sql.validate.SqlValidatorImpl.checkRollUp (SqlValidatorImpl.java:3959)
>     at org.apache.calcite.sql.validate.SqlValidatorImpl.checkRollUpInSelectList (SqlValidatorImpl.java:3861)
>     at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect (SqlValidatorImpl.java:3849)
> {code}
>  



--
This message was sent by Atlassian Jira
(v8.20.10#820010)