Right now the aliases are derived programmatically and we use the same name in group by as an alias and these are already defined in the jobs so we can not change them now That's one reason it became blocker as these jobs are configured and were running fine and suddenly started breaking.
On Sat, Apr 20, 2019 at 5:24 AM Aman Sinha <amansi...@gmail.com> wrote: > Interesting that it ran on 1.13..but I still think the new behavior is the > right one. Several changes went into Calcite between Drill's 1.13 and 1.15 > release, so I cannot identify when this behavior changed. Can you use a > slightly different alias name ? The following should work: > select max(last_name) *max_last_name* from cp.`employee.json` group by > last_name limit 5; > > On Fri, Apr 19, 2019 at 2:24 PM Nitin Pawar <nitinpawar...@gmail.com> > wrote: > > > 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 <amansi...@gmail.com> 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 <nitinpawar...@gmail.com> > > > 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 <ku...@apache.org> > > 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 <nitinpawar...@gmail.com> > > 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 > > > -- Nitin Pawar
