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
>