You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Sanjiv Kumar <kt...@gmail.com> on 2016/12/27 08:19:14 UTC
Limit ALL not working with offset
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
Re: Limit ALL not working with offset
Posted by Khurram Faraaz <kf...@maprtech.com>.
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 <kf...@maprtech.com>
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 <kt...@gmail.com> 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
>>
>
>
Re: Limit ALL not working with offset
Posted by Khurram Faraaz <kf...@maprtech.com>.
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 <kt...@gmail.com> 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
>