Hi Nitin, This behavior to allow aliases in a group by clause is driven by Calcite and commonly used in other projects. I think the workaround proposed by Aman is the best solution for this problem, since for example if you have several aggregate functions in the project for the same columns, it will cause problems with such naming.
Kind regards, Volodymyr Vysotskyi On Sat, Apr 20, 2019 at 8:44 AM Nitin Pawar <[email protected]> wrote: > 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 <[email protected]> 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 <[email protected]> > > 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 <[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 > > > > > > > > -- > Nitin Pawar >
