You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@drill.apache.org by Bob Rumsby <br...@maprtech.com> on 2014/07/24 02:37:39 UTC
Column aliases
Do we know what the current rules are for using column aliases in queries?
It looks like references to select list aliases don't work in where or
group by clauses but they do work in order by.
0: jdbc:drill:zk=local> select type t, count(distinct id) from
dfs.`/Users/brumsby/drill/donuts.json` where type='donut' group by type;
+------------+------------+
| t | EXPR$1 |
+------------+------------+
| donut | 1 |
+------------+------------+
1 row selected (0.325 seconds)
0: jdbc:drill:zk=local> select type t, count(distinct id) from
dfs.`/Users/brumsby/drill/donuts.json` where type='donut' group by t;
Jul 23, 2014 5:31:59 PM org.eigenbase.sql.validate.SqlValidatorException
<init>
SEVERE: org.eigenbase.sql.validate.SqlValidatorException: Expression 'type'
is not being grouped
Jul 23, 2014 5:31:59 PM org.eigenbase.util.EigenbaseException <init>
SEVERE: org.eigenbase.util.EigenbaseContextException: From line 1, column 8
to line 1, column 11
Query failed: org.apache.drill.exec.rpc.RpcException: Remote failure while
running query.[error_id: "3233778a-e9f0-42ef-8ca8-8103ec793f6e"
endpoint {
address: "192.168.168.86"
user_port: 31010
control_port: 31011
data_port: 31012
}
error_type: 0
message: "Failure while parsing sql. < ValidationException:[
org.eigenbase.util.EigenbaseContextException: From line 1, column 8 to line
1, column 11 ] < EigenbaseContextException:[ From line 1, column 8 to line
1, column 11 ] < SqlValidatorException:[ Expression 'type' is not being
grouped ]"
]
Error: exception while executing query (state=,code=0)
0: jdbc:drill:zk=local>
Thanks,
Bob
Re: Column aliases
Posted by Aman Sinha <as...@maprtech.com>.
Cross joins are currently not supported. Non-equijoins are supported as
long as there is another equijoin between the same pair of tables as part
of a conjunction: for example : T1.c1 = T2.c1 AND T1.c2 < T2.c2 will
work. This applies to both inner and outer joins.
Subqueries in general are supported in various clauses. The restriction on
having a correlation condition is only for certain types of subqueries... I
can talk to you offline to identify those.
Aman
On Thu, Jul 24, 2014 at 3:52 PM, Bob Rumsby <br...@maprtech.com> wrote:
> Thanks--also would like to clarify our support for joins and subqueries.
>
> Looks like we support standard inner and outer equijoins but not
> cross-joins or non-equijoins. Is this correct?
>
> Do we support subqueries in general in the select list, from clause, and
> where clause? I assume so. We already have a restriction documented for
> correlated subqueries (they fail if not rewritten as equijoins). Any other
> subquery notes or limitations?
>
> Bob
>
>
> On Wed, Jul 23, 2014 at 6:16 PM, Aman Sinha <as...@maprtech.com> wrote:
>
> > Yes, this is expected failure for us since GROUP-BY is evaluated before
> the
> > final projections whereas ORDER-BY is done after the projection.
> > Some database vendors do support group-by on aliased columns but I don't
> > think it is allowed in the WHERE clause since the WHERE conditions are
> > evaluated much earlier in the query plan.
> >
> > Aman
> >
> >
> > On Wed, Jul 23, 2014 at 5:37 PM, Bob Rumsby <br...@maprtech.com>
> wrote:
> >
> > > Do we know what the current rules are for using column aliases in
> > queries?
> > > It looks like references to select list aliases don't work in where or
> > > group by clauses but they do work in order by.
> > >
> > > 0: jdbc:drill:zk=local> select type t, count(distinct id) from
> > > dfs.`/Users/brumsby/drill/donuts.json` where type='donut' group by
> type;
> > >
> > > +------------+------------+
> > >
> > > | t | EXPR$1 |
> > >
> > > +------------+------------+
> > >
> > > | donut | 1 |
> > >
> > > +------------+------------+
> > >
> > > 1 row selected (0.325 seconds)
> > >
> > > 0: jdbc:drill:zk=local> select type t, count(distinct id) from
> > > dfs.`/Users/brumsby/drill/donuts.json` where type='donut' group by t;
> > >
> > > Jul 23, 2014 5:31:59 PM
> org.eigenbase.sql.validate.SqlValidatorException
> > > <init>
> > >
> > > SEVERE: org.eigenbase.sql.validate.SqlValidatorException: Expression
> > 'type'
> > > is not being grouped
> > >
> > > Jul 23, 2014 5:31:59 PM org.eigenbase.util.EigenbaseException <init>
> > >
> > > SEVERE: org.eigenbase.util.EigenbaseContextException: From line 1,
> > column 8
> > > to line 1, column 11
> > >
> > > Query failed: org.apache.drill.exec.rpc.RpcException: Remote failure
> > while
> > > running query.[error_id: "3233778a-e9f0-42ef-8ca8-8103ec793f6e"
> > >
> > > endpoint {
> > >
> > > address: "192.168.168.86"
> > >
> > > user_port: 31010
> > >
> > > control_port: 31011
> > >
> > > data_port: 31012
> > >
> > > }
> > >
> > > error_type: 0
> > >
> > > message: "Failure while parsing sql. < ValidationException:[
> > > org.eigenbase.util.EigenbaseContextException: From line 1, column 8 to
> > line
> > > 1, column 11 ] < EigenbaseContextException:[ From line 1, column 8 to
> > line
> > > 1, column 11 ] < SqlValidatorException:[ Expression 'type' is not being
> > > grouped ]"
> > >
> > > ]
> > >
> > > Error: exception while executing query (state=,code=0)
> > >
> > > 0: jdbc:drill:zk=local>
> > >
> > > Thanks,
> > > Bob
> > >
> >
>
Re: Column aliases
Posted by Bob Rumsby <br...@maprtech.com>.
Thanks--also would like to clarify our support for joins and subqueries.
Looks like we support standard inner and outer equijoins but not
cross-joins or non-equijoins. Is this correct?
Do we support subqueries in general in the select list, from clause, and
where clause? I assume so. We already have a restriction documented for
correlated subqueries (they fail if not rewritten as equijoins). Any other
subquery notes or limitations?
Bob
On Wed, Jul 23, 2014 at 6:16 PM, Aman Sinha <as...@maprtech.com> wrote:
> Yes, this is expected failure for us since GROUP-BY is evaluated before the
> final projections whereas ORDER-BY is done after the projection.
> Some database vendors do support group-by on aliased columns but I don't
> think it is allowed in the WHERE clause since the WHERE conditions are
> evaluated much earlier in the query plan.
>
> Aman
>
>
> On Wed, Jul 23, 2014 at 5:37 PM, Bob Rumsby <br...@maprtech.com> wrote:
>
> > Do we know what the current rules are for using column aliases in
> queries?
> > It looks like references to select list aliases don't work in where or
> > group by clauses but they do work in order by.
> >
> > 0: jdbc:drill:zk=local> select type t, count(distinct id) from
> > dfs.`/Users/brumsby/drill/donuts.json` where type='donut' group by type;
> >
> > +------------+------------+
> >
> > | t | EXPR$1 |
> >
> > +------------+------------+
> >
> > | donut | 1 |
> >
> > +------------+------------+
> >
> > 1 row selected (0.325 seconds)
> >
> > 0: jdbc:drill:zk=local> select type t, count(distinct id) from
> > dfs.`/Users/brumsby/drill/donuts.json` where type='donut' group by t;
> >
> > Jul 23, 2014 5:31:59 PM org.eigenbase.sql.validate.SqlValidatorException
> > <init>
> >
> > SEVERE: org.eigenbase.sql.validate.SqlValidatorException: Expression
> 'type'
> > is not being grouped
> >
> > Jul 23, 2014 5:31:59 PM org.eigenbase.util.EigenbaseException <init>
> >
> > SEVERE: org.eigenbase.util.EigenbaseContextException: From line 1,
> column 8
> > to line 1, column 11
> >
> > Query failed: org.apache.drill.exec.rpc.RpcException: Remote failure
> while
> > running query.[error_id: "3233778a-e9f0-42ef-8ca8-8103ec793f6e"
> >
> > endpoint {
> >
> > address: "192.168.168.86"
> >
> > user_port: 31010
> >
> > control_port: 31011
> >
> > data_port: 31012
> >
> > }
> >
> > error_type: 0
> >
> > message: "Failure while parsing sql. < ValidationException:[
> > org.eigenbase.util.EigenbaseContextException: From line 1, column 8 to
> line
> > 1, column 11 ] < EigenbaseContextException:[ From line 1, column 8 to
> line
> > 1, column 11 ] < SqlValidatorException:[ Expression 'type' is not being
> > grouped ]"
> >
> > ]
> >
> > Error: exception while executing query (state=,code=0)
> >
> > 0: jdbc:drill:zk=local>
> >
> > Thanks,
> > Bob
> >
>
Re: Column aliases
Posted by Aman Sinha <as...@maprtech.com>.
Yes, this is expected failure for us since GROUP-BY is evaluated before the
final projections whereas ORDER-BY is done after the projection.
Some database vendors do support group-by on aliased columns but I don't
think it is allowed in the WHERE clause since the WHERE conditions are
evaluated much earlier in the query plan.
Aman
On Wed, Jul 23, 2014 at 5:37 PM, Bob Rumsby <br...@maprtech.com> wrote:
> Do we know what the current rules are for using column aliases in queries?
> It looks like references to select list aliases don't work in where or
> group by clauses but they do work in order by.
>
> 0: jdbc:drill:zk=local> select type t, count(distinct id) from
> dfs.`/Users/brumsby/drill/donuts.json` where type='donut' group by type;
>
> +------------+------------+
>
> | t | EXPR$1 |
>
> +------------+------------+
>
> | donut | 1 |
>
> +------------+------------+
>
> 1 row selected (0.325 seconds)
>
> 0: jdbc:drill:zk=local> select type t, count(distinct id) from
> dfs.`/Users/brumsby/drill/donuts.json` where type='donut' group by t;
>
> Jul 23, 2014 5:31:59 PM org.eigenbase.sql.validate.SqlValidatorException
> <init>
>
> SEVERE: org.eigenbase.sql.validate.SqlValidatorException: Expression 'type'
> is not being grouped
>
> Jul 23, 2014 5:31:59 PM org.eigenbase.util.EigenbaseException <init>
>
> SEVERE: org.eigenbase.util.EigenbaseContextException: From line 1, column 8
> to line 1, column 11
>
> Query failed: org.apache.drill.exec.rpc.RpcException: Remote failure while
> running query.[error_id: "3233778a-e9f0-42ef-8ca8-8103ec793f6e"
>
> endpoint {
>
> address: "192.168.168.86"
>
> user_port: 31010
>
> control_port: 31011
>
> data_port: 31012
>
> }
>
> error_type: 0
>
> message: "Failure while parsing sql. < ValidationException:[
> org.eigenbase.util.EigenbaseContextException: From line 1, column 8 to line
> 1, column 11 ] < EigenbaseContextException:[ From line 1, column 8 to line
> 1, column 11 ] < SqlValidatorException:[ Expression 'type' is not being
> grouped ]"
>
> ]
>
> Error: exception while executing query (state=,code=0)
>
> 0: jdbc:drill:zk=local>
>
> Thanks,
> Bob
>