Agreed. I will see what kind of alternative we can do on our side On Mon, Apr 22, 2019 at 2:53 PM Vova Vysotskyi <[email protected]> wrote:
> 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 > > > -- Nitin Pawar
