Aman This issue occurs for the subquery as well. For ex: the following returns no rows.
select state, cnt from ( select t.user_info.state as state, count(*) as cnt from `Clickstream.clicks`.`/json/clicks.json` as t group by t.user_info.state ) where cnt > 100 order by cnt desc limit 10; On Tue, Sep 9, 2014 at 5:23 PM, Aman Sinha (JIRA) <[email protected]> wrote: > Aman Sinha created DRILL-1393: > --------------------------------- > > Summary: Wrong result for query with Having filter, Order-by > and Limit > Key: DRILL-1393 > URL: https://issues.apache.org/jira/browse/DRILL-1393 > Project: Apache Drill > Issue Type: Bug > Components: Execution - Operators > Affects Versions: 0.5.0 > Reporter: Aman Sinha > > > The following query without LIMIT gives correct results: > 0: jdbc:drill:zk=local> select o_custkey from orders group by o_custkey > having o_custkey < 10 order by o_custkey; > +------------+ > | o_custkey | > +------------+ > | 1 | > | 2 | > | 4 | > | 5 | > | 7 | > | 8 | > +------------+ > > Adding a LIMIT gives 0 rows: > > 0: jdbc:drill:zk=local> select o_custkey from orders group by o_custkey > having o_custkey < 10 order by o_custkey limit 10; > +------------+ > | o_custkey | > +------------+ > +------------+ > No rows selected > > Here's the EXPLAIN plan: > 0: jdbc:drill:zk=local> explain plan for select o_custkey from orders > group by o_custkey having o_custkey < 10 order by o_custkey limit 10; > +------------+------------+ > | text | json | > +------------+------------+ > | 00-00 Screen > 00-01 SelectionVectorRemover > 00-02 Limit(fetch=[10]) > 00-03 SelectionVectorRemover > 00-04 TopN(limit=[10]) > 00-05 Filter(condition=[<($0, 10)]) > 00-06 HashAgg(group=[{0}]) > 00-07 HashAgg(group=[{0}]) > 00-08 Scan(groupscan=[ParquetGroupScan > [entries=[ReadEntryWithPath > [path=file:/Users/asinha/data/tpch-sf1/orders]], > selectionRoot=/Users/asinha/data/tpch-sf1/orders, columns=[SchemaPath > [`o_custkey`]]]]) > > > > -- > This message was sent by Atlassian JIRA > (v6.3.4#6332) >
