This is legal:
select max(last_name) from cp.`employee.json` group by last_name limit 5;
But this is not:
select max(last_name) last_name from cp.`employee.json` group by
last_name limit 5;
The reason is the second query is aliasing the max() output to 'last_name'
which is being referenced in the group-by clause. Referencing an aggregate
expr in the group-by is not allowed by SQL standards, hence Calcite (which
does the parsing and validation, not Drill) throws this error during
validation phase. Detailed error stack is below. I don't think this would
have worked in 1.13 either. My guess is you may have run the first query
in 1.13 and that should still continue to work.
Validation error thrown by Calcite:
Caused By (org.apache.calcite.sql.validate.SqlValidatorException) Aggregate
expression is illegal in GROUP BY clause
sun.reflect.NativeConstructorAccessorImpl.newInstance0():-2
sun.reflect.NativeConstructorAccessorImpl.newInstance():62
sun.reflect.DelegatingConstructorAccessorImpl.newInstance():45
java.lang.reflect.Constructor.newInstance():423
org.apache.calcite.runtime.Resources$ExInstWithCause.ex():463
org.apache.calcite.runtime.Resources$ExInst.ex():572
org.apache.calcite.sql.SqlUtil.newContextException():787
org.apache.calcite.sql.SqlUtil.newContextException():772
org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError():4788
org.apache.calcite.sql.validate.SqlValidatorImpl.validateGroupClause():3941
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect():3306
org.apache.calcite.sql.validate.SelectNamespace.validateImpl():60
org.apache.calcite.sql.validate.AbstractNamespace.validate():84
org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():977
org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():953
org.apache.calcite.sql.SqlSelect.validate():216
org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression():928
org.apache.calcite.sql.validate.SqlValidatorImpl.validate():632
org.apache.drill.exec.planner.sql.SqlConverter.validate():207
On Fri, Apr 19, 2019 at 12:39 PM Nitin Pawar <[email protected]>
wrote:
> I think the error is not with storage plugin but with query parsing
>
> here is the exception
> 0: jdbc:drill:zk=local> select max(last_name) last_name from
> cp.`employee.json` group by last_name limit 5;
> Error: VALIDATION ERROR: From line 1, column 8 to line 1, column 21:
> Aggregate expression is illegal in GROUP BY clause
>
> On Fri, Apr 19, 2019 at 11:57 PM Kunal Khatua <[email protected]> wrote:
>
> > (Replying on the Drill user list)
> >
> > This is odd. The CP storage plugin is inbuilt with Drill and that hasn't
> > changed. 1.15 by itself works fine.
> >
> > What is the error you are seeing, Nitin?
> >
> >
> > On 4/18/2019 10:58:48 PM, Nitin Pawar <[email protected]> wrote:
> > Hi,
> >
> > We are trying to upgrade drill from 1.13 to 1.15
> > following query works in drill 1.13 but not in 1.15
> >
> > select max(last_name) from cp.`employee.json` group by last_name limit 5
> >
> > can you let us know if this backward compatibility issue will be fixed ??
> >
> > --
> > Nitin Pawar
> >
>
>
> --
> Nitin Pawar
>
