[jira] [Commented] (CALCITE-2904) Column not found in table when query contain duplicate table alias

[Danny Chan (JIRA)]

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

Danny Chan commented on CALCITE-2904:
-------------------------------------

Hi [~julianhyde] [~hyuan], i debug and check the reason, it is a bug, but hard 
to fix, i need your suggestions.

In DelegatingScope#fullQualify -> findQualifyingTableNames(columnName, 
identifier, nameMatcher)

{code:java}
// ListScope: line 150
  @Override public Map<String, ScopeChild>
  findQualifyingTableNames(String columnName, SqlNode ctx,
      SqlNameMatcher nameMatcher) {
    final Map<String, ScopeChild> map = new HashMap<>();
    for (ScopeChild child : children) {
      final ResolvedImpl resolved = new ResolvedImpl();
      resolve(ImmutableList.of(child.name, columnName), nameMatcher, true,
          resolved);
      if (resolved.count() > 0) {
        map.put(child.name, child);
      }
    }
    switch (map.size()) {
    case 0:
      return parent.findQualifyingTableNames(columnName, ctx, nameMatcher);
    default:
      return map;
    }
  }


// ListScope:170
    for (ScopeChild child : children) {
      final ResolvedImpl resolved = new ResolvedImpl();
      resolve(ImmutableList.of(child.name, columnName), nameMatcher, true,
          resolved);
      if (resolved.count() > 0) {
        map.put(child.name, child);
      }
    }
{code}

The resolve func will search and find the ScopeChild among its chilren and 
parent scope, for this case , we did found it first in the parent, but the 
method findQualifyingTableNames only put the child scope in the returned map, 
this is wrong behavior.

In the `DelegatingScope#fullQualify`, we also try to resolve identifier with 
only the table name, for this case there are 2 tables named T so we can not 
distinguish what we need.

{code:java}
// DelegatingScope: line 291
        default: {
      SqlValidatorNamespace fromNs = null;
      Path fromPath = null;
      RelDataType fromRowType = null;
      final ResolvedImpl resolved = new ResolvedImpl();
      int size = identifier.names.size();
      int i = size - 1;
      for (; i > 0; i--) {
        final SqlIdentifier prefix = identifier.getComponent(0, i);
        resolved.clear();
        resolve(prefix.names, nameMatcher, false, resolved);
        if (resolved.count() == 1) {
          final Resolve resolve = resolved.only();
          fromNs = resolve.namespace;
          fromPath = resolve.path;
          fromRowType = resolve.rowType();
          break;
        }
{code}




>  Column not found in table when query contain duplicate table alias
> -------------------------------------------------------------------
>
>                 Key: CALCITE-2904
>                 URL: https://issues.apache.org/jira/browse/CALCITE-2904
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>            Reporter: Haisheng Yuan
>            Assignee: Danny Chan
>            Priority: Major
>
> Repro:
> In SqlToRelConverterTest.java, add this test.
> {code:java}
> @Test public void testDuplicateTableAlias() {
>     final String sql = "select * from (values 4) as t(c) "
>         + "left join lateral "
>         + "(select c,c*a from "
>         + "    (values 2) as t(a)"
>         + ") as r(d,c) "
>         + "using(c)";
>     sql(sql).ok();
>   }
> {code}
> Error message:
> {code:java}
> org.apache.calcite.runtime.CalciteContextException: At line 1, column 60: 
> Column 'C' not found in table 'T'
> {code}
> The tables with same aliases are at different level. Postgres can parse this 
> query without any issue. Not sure if this is expected behavior or not in 
> calcite.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)