Using aliases in the GROUP BY clause isn’t standard SQL, and Calcite doesn’t support it.
However, according to http://stackoverflow.com/questions/3841295/sql-using-alias-in-group-by <http://stackoverflow.com/questions/3841295/sql-using-alias-in-group-by>, MySQL and PostgreSQL support it. I logged https://issues.apache.org/jira/browse/CALCITE-1306 <https://issues.apache.org/jira/browse/CALCITE-1306> to track this request. Julian > On Jul 3, 2016, at 11:28 PM, Pany Yue <[email protected]> wrote: > > but, order by alias is already supported: > > SQL: select id as a from test4dmp.test order by a; > > validated ok. > > I'm using calcite-1.7.0 > > > 2016-07-04 14:26 GMT+08:00 Pany Yue <[email protected]>: > >> I' m using calcite for some testing, I found calcite does not support >> grouping alias: >> >> SQL: select id as a from table group by a >> >> calcite throws flowing exception in validate phase: >> >> Caused by: org.apache.calcite.runtime.CalciteContextException: At line 1, >> column 44: Column 'A' not found in any table >> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) >> ~[na:1.7.0_79] >> at >> sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) >> ~[na:1.7.0_79] >> at >> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) >> ~[na:1.7.0_79] >> at java.lang.reflect.Constructor.newInstance(Constructor.java:526) >> ~[na:1.7.0_79] >> at >> org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:405) >> ~[classes/:na] >> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:768) >> ~[classes/:na] >> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:753) >> ~[classes/:na] >> at >> org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:3929) >> ~[classes/:na] >> at >> org.apache.calcite.sql.validate.EmptyScope.findQualifyingTableName(EmptyScope.java:108) >> ~[classes/:na] >> at >> org.apache.calcite.sql.validate.DelegatingScope.findQualifyingTableName(DelegatingScope.java:112) >> ~[classes/:na] >> at >> org.apache.calcite.sql.validate.ListScope.findQualifyingTableName(ListScope.java:150) >> ~[classes/:na] >> at >> org.apache.calcite.sql.validate.DelegatingScope.fullyQualify(DelegatingScope.java:155) >> ~[classes/:na] >> at >> org.apache.calcite.sql.validate.SqlValidatorImpl.validateIdentifier(SqlValidatorImpl.java:2589) >> ~[classes/:na] >> at >> org.apache.calcite.sql.SqlIdentifier.validateExpr(SqlIdentifier.java:287) >> ~[classes/:na] >> at >> org.apache.calcite.sql.validate.SqlValidatorImpl.validateGroupClause(SqlValidatorImpl.java:3305) >> ~[classes/:na] >> at >> org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:2959) >> ~[classes/:na] >> at >> org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60) >> ~[classes/:na] >> at >> org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:86) >> ~[classes/:na] >> at >> org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:845) >> ~[classes/:na] >> at >> org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:831) >> ~[classes/:na] >> at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:208) >> ~[classes/:na] >> at >> org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:807) >> ~[classes/:na] >> at >> org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:523) >> ~[classes/:na] >>
