1. DRILL-5165 <https://issues.apache.org/jira/browse/DRILL-5165> is reported to track this issue.
On Tue, Dec 27, 2016 at 2:06 PM, Khurram Faraaz <[email protected]> wrote: > This looks like a bug. Thanks for reporting this issue. > I tried a similar query on apache Drill 1.10.0 and Drill returns wrong > results when compared to Postgres, for a query that uses LIMIT and OFFSET > clause in the same query. We need to file a JIRA to track this issue. > > {noformat} > 0: jdbc:drill:schema=dfs.tmp> select col_int from typeall_l order by 1 > limit all offset 10; > +----------+ > | col_int | > +----------+ > +----------+ > No rows selected (0.211 seconds) > 0: jdbc:drill:schema=dfs.tmp> select col_int from typeall_l order by > col_int limit all offset 10; > +----------+ > | col_int | > +----------+ > +----------+ > No rows selected (0.24 seconds) > {noformat} > > Query => select col_int from typeall_l limit all offset 10; > Drill 1.10.0 returns 85 rows > > whereas for same query, > postgres=# select col_int from typeall_l limit all offset 10; > Postgres 9.3 returns 95 rows, which is the correct expected result. > > Query plan for above query that returns wrong results > > {noformat} > 0: jdbc:drill:schema=dfs.tmp> explain plan for select col_int from > typeall_l limit all offset 10; > +------+------+ > | text | json | > +------+------+ > | 00-00 Screen > 00-01 Project(col_int=[$0]) > 00-02 SelectionVectorRemover > 00-03 Limit(offset=[10]) > 00-04 Scan(groupscan=[ParquetGroupScan > [entries=[ReadEntryWithPath [path=maprfs:///tmp/typeall_l]], > selectionRoot=maprfs:/tmp/typeall_l, numFiles=1, usedMetadataFile=false, > columns=[`col_int`]]]) > {noformat} > > On Tue, Dec 27, 2016 at 1:49 PM, Sanjiv Kumar <[email protected]> wrote: > >> Hello >> I trying to use limit all with offset, but it not working. >> My query:- >> select c.CategoryID from demoPlugin.dbo.Category as c >> group >> by c.CategoryID order by 1 desc limit ALL offset 10; >> >> While firing this query its showing:- "No result found." >> >> But if i remove offset 10, then it working fine. >> select c.CategoryID from demoPlugin.dbo.Category as c group by >> c.CategoryID order by 1 desc limit ALL; >> >> What is the problem.? Is limit all is not supported with offset? >> >> >> ------------------- >> Thanks & Regards >> Sanjiv Kumar >> > >
