[jira] [Created] (CALCITE-5316) query two same filed with order by will report an error

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

benwei created CALCITE-5316:
-------------------------------

             Summary: query two same filed with order by will report an error
                 Key: CALCITE-5316
                 URL: https://issues.apache.org/jira/browse/CALCITE-5316
             Project: Calcite
          Issue Type: Bug
          Components: core
    Affects Versions: 1.32.0
            Reporter: benwei


Execute query like
{code:java}
select name,name from user order by name;
{code}
will report en error in the verification phase.

The reason is that there is a method called aliasCount() in OrderByScope.class,this method will return each  order by filed's  aliasCount. And in this method,the field in the selectList will also be considered as alias. so, in this case, it will return 2 ,which is greater than 1.

Here is the code for getAlias in SqlValidatorUtil.class
{code:java}
public static @Nullable String getAlias(SqlNode node, int ordinal) {
  switch (node.getKind()) {
  case AS:
    // E.g. "1 + 2 as foo" --> "foo"
    return ((SqlCall) node).operand(1).toString();

  case OVER:
    // E.g. "bids over w" --> "bids"
    return getAlias(((SqlCall) node).operand(0), ordinal);

  case IDENTIFIER:
    // E.g. "foo.bar" --> "bar"
    return Util.last(((SqlIdentifier) node).names);

  default:
    if (ordinal < 0) {
      return null;
    } else {
      return SqlUtil.deriveAliasFromOrdinal(ordinal);
    }
  }
} {code}
otherwise, when aliasCount() return 1, it won't rewrite orderby's field, eg:
{code:java}
original sql: 
       select name from user order by id;
SQL after rewriting:
       select user.name from user order by user.id 

original sql: 
       select name from user order by name;
SQL after rewriting:
       select user.name from user order by name  // order by's name Will not be rewritten as user.name{code}
I think,We only need to deal with the kind of AS in orderby scope.

At last, my first language is not English, so my description may not be very clear



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