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
>
