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
>
