sorry my bad. i meant the query which was failing was with alias following is output on drill 1.13.0
bash-3.2$ bin/drill-embedded Apr 20, 2019 2:46:45 AM org.glassfish.jersey.server.ApplicationHandler initialize INFO: Initiating Jersey application, version Jersey: 2.8 2014-04-29 01:25:26... apache drill 1.13.0-SNAPSHOT "a drill in the hand is better than two in the bush" 0: jdbc:drill:zk=local> select max(last_name) last_name from cp.`employee.json` group by . . . . . . . . . . . > last_name limit 5; +------------+ | last_name | +------------+ | Nowmer | | Whelply | | Spence | | Gutierrez | | Damstra | +------------+ On Sat, Apr 20, 2019 at 1:40 AM Aman Sinha <[email protected]> wrote: > 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 > > > -- Nitin Pawar
