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 <[email protected]> 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 <[email protected]> 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 > > >
