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