[
https://issues.apache.org/jira/browse/DRILL-6474?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16506587#comment-16506587
]
Timothy Farkas commented on DRILL-6474:
---------------------------------------
The planner is inserting an TopN and then a Limit. So if we do
explain plan for select full_name from cp.`employee.json` order by full_name
offset 10;
We get a plan like this
00-00 Screen
00-01 Project(full_name=[$0])
00-02 SelectionVectorRemover
00-03 Limit(offset=[10])
00-04 SelectionVectorRemover
00-05 TopN(limit=[10])
00-06 Scan(groupscan=[EasyGroupScan
[selectionRoot=classpath:/employee.json, numFiles=1, columns=[`full_name`],
files=[classpath:/employee.json]]])
This means we first get the top 10 results in the topN operator and then we
skip those 10 results in the limit operator and then return nothing.
This is happening because the PushLimitToTopN rule uses topN even when a limit
is not defined. The fix is to not match that rule when there is no limit
defined.
> Queries with ORDER BY and OFFSET (w/o LIMIT) do not return any rows
> -------------------------------------------------------------------
>
> Key: DRILL-6474
> URL: https://issues.apache.org/jira/browse/DRILL-6474
> Project: Apache Drill
> Issue Type: Bug
> Reporter: Timothy Farkas
> Assignee: Timothy Farkas
> Priority: Major
>
> This is easily reproduced with the following test
> {code}
> final ClusterFixtureBuilder builder = new
> ClusterFixtureBuilder(baseDirTestWatcher);
> try (ClusterFixture clusterFixture = builder.build();
> ClientFixture clientFixture = clusterFixture.clientFixture()) {
> clientFixture.testBuilder()
> .sqlQuery("select name_s10 from `mock`.`employees_100000` order by
> name_s10 offset 100")
> .expectsNumRecords(99900)
> .build()
> .run();
> }
> {code}
> That fails with
> java.lang.AssertionError:
> Expected :99900
> Actual :0
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
