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 >
