You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by "Fang, Ningli" <NI...@ca.com> on 2016/12/12 22:01:05 UTC
Group by alias name ?
Dear Calcite developers,
When we execute this query:
select "apiName" as "API_NAME", SUM("respSize") AS FUNC_SUM_RESP_SIZE from "api_metrics" group by "API_NAME";
we receive the exception:
Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Column 'API_NAME' not found in any table
Stack trace:
Error: Error while executing SQL "select "apiName" as "API_NAME", SUM("respSize") AS FUNC_SUM_RESP_SIZE from "api_metrics" group by "API_NAME"": From line 1, co
line 1, column 108: Column 'API_NAME' not found in any table (state=,code=0)
java.sql.SQLException: Error while executing SQL "select "apiName" as "API_NAME", SUM("respSize") AS FUNC_SUM_RESP_SIZE from "api_metrics" group by "API_NAME"":
1, column 99 to line 1, column 108: Column 'API_NAME' not found in any table
at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:156)
at org.apache.calcite.avatica.AvaticaStatement.execute(AvaticaStatement.java:209)
at sqlline.Commands.execute(Commands.java:822)
at sqlline.Commands.sql(Commands.java:732)
at sqlline.SqlLine.dispatch(SqlLine.java:813)
at sqlline.SqlLine.begin(SqlLine.java:686)
at sqlline.SqlLine.start(SqlLine.java:398)
at sqlline.SqlLine.main(SqlLine.java:291)
Caused by: org.apache.calcite.runtime.CalciteContextException: From line 1, column 99 to line 1, column 108: Column 'API_NAME' not found in any table
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:405)
at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:799)
at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:784)
at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:4102)
at org.apache.calcite.sql.validate.EmptyScope.findQualifyingTableName(EmptyScope.java:104)
at org.apache.calcite.sql.validate.DelegatingScope.findQualifyingTableName(DelegatingScope.java:149)
at org.apache.calcite.sql.validate.ListScope.findQualifyingTableName(ListScope.java:122)
at org.apache.calcite.sql.validate.DelegatingScope.fullyQualify(DelegatingScope.java:198)
at org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit(SqlValidatorImpl.java:4601)
at org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit(SqlValidatorImpl.java:4583)
at org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:324)
at org.apache.calcite.sql.validate.SqlValidatorImpl.expand(SqlValidatorImpl.java:4276)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateGroupClause(SqlValidatorImpl.java:3444)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3081)
at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:86)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:898)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:879)
at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:208)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:854)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:565)
at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:549)
at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:231)
at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:195)
at org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:735)
at org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:598)
at org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:568)
at org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:215)
at org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:594)
at org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:616)
at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:149)
... 7 more
Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Column 'API_NAME' not found in any table
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:405)
at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:514)
How can we make this query to work?
Thank you very much for your help :)
Best Regards,
Ningli Fang
[cid:image001.png@01D0A519.385E6460]
Re: Group by alias name ?
Posted by Julian Hyde <jh...@apache.org>.
Your query is not standard SQL; some databases support it, but Calcite does not. It is covered by https://issues.apache.org/jira/browse/CALCITE-1306 <https://issues.apache.org/jira/browse/CALCITE-1306>.
> On Dec 12, 2016, at 2:01 PM, Fang, Ningli <NI...@ca.com> wrote:
>
> Dear Calcite developers,
>
> When we execute this query:
> select "apiName" as "API_NAME", SUM("respSize") AS FUNC_SUM_RESP_SIZE from "api_metrics" group by "API_NAME";
>
> we receive the exception:
> Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Column 'API_NAME' not found in any table
>
> Stack trace:
> Error: Error while executing SQL "select "apiName" as "API_NAME", SUM("respSize") AS FUNC_SUM_RESP_SIZE from "api_metrics" group by "API_NAME"": From line 1, co
> line 1, column 108: Column 'API_NAME' not found in any table (state=,code=0)
> java.sql.SQLException: Error while executing SQL "select "apiName" as "API_NAME", SUM("respSize") AS FUNC_SUM_RESP_SIZE from "api_metrics" group by "API_NAME"":
> 1, column 99 to line 1, column 108: Column 'API_NAME' not found in any table
> at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
> at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
> at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:156)
> at org.apache.calcite.avatica.AvaticaStatement.execute(AvaticaStatement.java:209)
> at sqlline.Commands.execute(Commands.java:822)
> at sqlline.Commands.sql(Commands.java:732)
> at sqlline.SqlLine.dispatch(SqlLine.java:813)
> at sqlline.SqlLine.begin(SqlLine.java:686)
> at sqlline.SqlLine.start(SqlLine.java:398)
> at sqlline.SqlLine.main(SqlLine.java:291)
> Caused by: org.apache.calcite.runtime.CalciteContextException: From line 1, column 99 to line 1, column 108: Column 'API_NAME' not found in any table
> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
> at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
> at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
> at java.lang.reflect.Constructor.newInstance(Unknown Source)
> at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:405)
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:799)
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:784)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:4102)
> at org.apache.calcite.sql.validate.EmptyScope.findQualifyingTableName(EmptyScope.java:104)
> at org.apache.calcite.sql.validate.DelegatingScope.findQualifyingTableName(DelegatingScope.java:149)
> at org.apache.calcite.sql.validate.ListScope.findQualifyingTableName(ListScope.java:122)
> at org.apache.calcite.sql.validate.DelegatingScope.fullyQualify(DelegatingScope.java:198)
> at org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit(SqlValidatorImpl.java:4601)
> at org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit(SqlValidatorImpl.java:4583)
> at org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:324)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.expand(SqlValidatorImpl.java:4276)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateGroupClause(SqlValidatorImpl.java:3444)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3081)
> at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
> at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:86)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:898)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:879)
> at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:208)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:854)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:565)
> at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:549)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:231)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:195)
> at org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:735)
> at org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:598)
> at org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:568)
> at org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:215)
> at org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:594)
> at org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:616)
> at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:149)
> ... 7 more
> Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Column 'API_NAME' not found in any table
> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
> at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
> at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
> at java.lang.reflect.Constructor.newInstance(Unknown Source)
> at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:405)
> at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:514)
>
> How can we make this query to work?
>
> Thank you very much for your help J
>
> Best Regards,
> Ningli Fang
>
>