Khurram Faraaz created DRILL-5165: ------------------------------------- Summary: wrong results - LIMIT ALL and OFFSET clause in same query Key: DRILL-5165 URL: https://issues.apache.org/jira/browse/DRILL-5165 Project: Apache Drill Issue Type: Bug Components: Query Planning & Optimization Affects Versions: 1.10.0 Reporter: Khurram Faraaz Priority: Critical
This issue was reported by a user on Drill's user list. Drill 1.10.0 commit ID : bbcf4b76 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 ALL 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} -- This message was sent by Atlassian JIRA (v6.3.4#6332)