You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Pany Yue <it...@gmail.com> on 2016/07/04 06:26:32 UTC
How to support grouping alias "Select id as a from table group by a"
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]
Re: How to support grouping alias "Select id as a from table group by a"
Posted by Pany Yue <it...@gmail.com>.
Thank you for your answer！
2016-07-06 12:13 GMT+08:00 Julian Hyde <jh...@apache.org>:
> 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 <it...@gmail.com> 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 <it...@gmail.com>:
> >
> >> 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]
> >>
>
>
Re: How to support grouping alias "Select id as a from table group by a"
Posted by Julian Hyde <jh...@apache.org>.
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 <it...@gmail.com> 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 <it...@gmail.com>:
>
>> 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]
>>
Re: How to support grouping alias "Select id as a from table group by a"
Posted by Pany Yue <it...@gmail.com>.
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 <it...@gmail.com>:
> 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]
>