[
https://issues.apache.org/jira/browse/CALCITE-5316?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17614873#comment-17614873
]
Julian Hyde commented on CALCITE-5316:
--------------------------------------
Did you execute it in any other databases? I bet there are plenty of others
where this query is invalid. SqlServer's behavior matches Calcite.
> 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
> Priority: Major
>
> 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)
