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 <[email protected]> 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 > >
