You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "benwei (Jira)" <ji...@apache.org> on 2022/10/09 08:24:00 UTC
[jira] [Updated] (CALCITE-5316) query two same filed with order by will report an error
[ https://issues.apache.org/jira/browse/CALCITE-5316?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
benwei updated CALCITE-5316:
----------------------------
Description:
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
was:
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
> 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)