Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-5165

wrong results - LIMIT ALL and OFFSET clause in same query

    Details

      Description

      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.

      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)
      

      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

      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`]]])
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                cshi Chunhui Shi
                Reporter:
                khfaraaz Khurram Faraaz
                Reviewer:
                Jinfeng Ni
              • Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: