You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Nitin Pawar <ni...@gmail.com> on 2019/04/19 05:48:24 UTC
Blocker on drill upgrade path
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
Re: Blocker on drill upgrade path
Posted by Nitin Pawar <ni...@gmail.com>.
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 <vv...@gmail.com> 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 <ni...@gmail.com>
> 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 <am...@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 <ni...@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 <am...@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 <
> > nitinpawar432@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 <nitinpawar432@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
> >
>
--
Nitin Pawar
Re: Blocker on drill upgrade path
Posted by Vova Vysotskyi <vv...@gmail.com>.
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 <ni...@gmail.com> 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 <am...@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 <ni...@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 <am...@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 <
> nitinpawar432@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 <ni...@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
>
Re: Blocker on drill upgrade path
Posted by Nitin Pawar <ni...@gmail.com>.
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 <am...@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 <ni...@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 <am...@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 <ni...@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 <ni...@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
Re: Blocker on drill upgrade path
Posted by Aman Sinha <am...@gmail.com>.
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 <ni...@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 <am...@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 <ni...@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 <ni...@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
>
Re: Blocker on drill upgrade path
Posted by Nitin Pawar <ni...@gmail.com>.
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 <am...@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 <ni...@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 <ni...@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
Re: Blocker on drill upgrade path
Posted by Aman Sinha <am...@gmail.com>.
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 <ni...@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 <ni...@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
>
Re: Blocker on drill upgrade path
Posted by Nitin Pawar <ni...@gmail.com>.
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 <ni...@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
Re: Blocker on drill upgrade path
Posted by Kunal Khatua <ku...@apache.org>.
(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 <ni...@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