# Dremio Rest API not retrieving 500 records with offset and limit

**URL:** <https://community.dremio.com/t/dremio-rest-api-not-retrieving-500-records-with-offset-and-limit/2948>\
**Category:** Uncategorized\
**Created:** [March 11, 2019, 5:32pm UTC](https://community.dremio.com/t/dremio-rest-api-not-retrieving-500-records-with-offset-and-limit/2948 "2019-03-11T17:32:53Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Lakshmi\_D](https://avatars.discourse-cdn.com/v4/letter/l/bc8723/32.png) [@Lakshmi\_D](https://community.dremio.com/u/Lakshmi_D)\
**Post date:** [March 11, 2019, 5:32pm UTC](https://community.dremio.com/t/dremio-rest-api-not-retrieving-500-records-with-offset-and-limit/2948/1 "2019-03-11T17:32:53Z")

</div>

We are using Dremio server 3.1.6 on Linux. Dremio Rest API not retrieving 500 records with offset and limit. Is this 500 configurable?

---

<div class="post-metadata">

**Author:** ![doron](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/doron/32/294_2.png) [@doron](https://community.dremio.com/u/doron)\
**Post date:** [March 11, 2019, 8:04pm UTC](https://community.dremio.com/t/dremio-rest-api-not-retrieving-500-records-with-offset-and-limit/2948/2 "2019-03-11T20:04:44Z")

</div>

The 500 limit per call is not configurable. Are you saying that using limit/offset to retrieve [job results](http://docs.dremio.com/rest-api/jobs/get-job.html) is not working correctly?

REST is not really designed to get large amounts of data which is why we have to limit each call.

---

<div class="post-metadata">

**Author:** ![Lakshmi\_D](https://avatars.discourse-cdn.com/v4/letter/l/bc8723/32.png) [@Lakshmi\_D](https://community.dremio.com/u/Lakshmi_D)\
**Post date:** [March 11, 2019, 11:30pm UTC](https://community.dremio.com/t/dremio-rest-api-not-retrieving-500-records-with-offset-and-limit/2948/3 "2019-03-11T23:30:13Z")

</div>

Thanks for the reply Doron.

One observation when using the API ’ select distinct col from table \*\*\*" , dremio job shows 2000+ records  
Where as API retrieves first 500 and later calls with offset and limit randomly gets less than 500. So instead of 2000+ we get 650+ \< 800.

it’s only to do with that distinct query. The other queries with 20000 records all work fine.

Work around is create a data set and use , data set also we have to use limit in where clause.  
or if the original select query has limit, then it works fine.

It has some thing to do with distinct query I guess. Any inputs that you can suggest ?

---

<div class="post-metadata">

**Author:** ![Farhan](https://avatars.discourse-cdn.com/v4/letter/f/b2d939/32.png) [@Farhan](https://community.dremio.com/u/Farhan)\
**Post date:** [May 23, 2020, 11:46pm UTC](https://community.dremio.com/t/dremio-rest-api-not-retrieving-500-records-with-offset-and-limit/2948/4 "2020-05-23T23:46:27Z")

</div>

I am also facing the same issue.

I have a query like this :

`select distinct columnA "value" from "spaceA"."datasetA" where columnA is not null and columnA not in (select distinct "value" from "spaceA"."datasetB" where "type" = 'someType')`

This query returns 27,000+ results.

I am using SQL rest API to execute this.

Then I use jobs API to retrieve results in batches, each of size 500 (max limit), by explicitly setting offset and limit in each rest call like this :

I start from offset 0 and keep shifting in each call.

`http://localhost:9047/api/v3/job/21365044-f881-d591-7e91-3dc1284fb400/results?offset=2000&limit=500`

The **rows** list returned after offset 2000 is empty… In all, I don’t get results more than 2.5k…

Response with empty result:

```
{
    "rowCount": 27527,
    "schema": [
        {
            "name": "value",
            "type": {
                "name": "VARCHAR"
            }
        }
    ],
    "rows": []
}

```

I am expecting close to millions of records like this, to be retrieved sequentially.

Where am I going wrong ?

Is there a setting/config which I have missed ? Please advice.

---

<div class="post-metadata">

**Author:** ![ben](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/ben/32/1367_2.png) [@ben](https://community.dremio.com/u/ben)\
**Post date:** [May 24, 2020, 8:59pm UTC](https://community.dremio.com/t/dremio-rest-api-not-retrieving-500-records-with-offset-and-limit/2948/5 "2020-05-24T20:59:54Z")

</div>

@Farhan, this is a known issue. We are currently working on a fix and will update the community threads when it’s available.

---

<div class="post-metadata">

**Author:** ![mingfang](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/mingfang/32/1628_2.png) [@mingfang](https://community.dremio.com/u/mingfang)\
**Post date:** [July 7, 2020, 5:35pm UTC](https://community.dremio.com/t/dremio-rest-api-not-retrieving-500-records-with-offset-and-limit/2948/6 "2020-07-07T17:35:31Z")

</div>

Is my understanding of this issue that REST pagination is broken?  
I just want to confirm that’s the same issue I’m seeing.  
If so then this is a critical problem.  
What is the status of the fix?

---

<div class="post-metadata">

**Author:** ![ben](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/ben/32/1367_2.png) [@ben](https://community.dremio.com/u/ben)\
**Post date:** [July 21, 2020, 2:37pm UTC](https://community.dremio.com/t/dremio-rest-api-not-retrieving-500-records-with-offset-and-limit/2948/7 "2020-07-21T14:37:25Z")

</div>

@mingfang,

The pagination was “broken” but the issue has been addressed in a release from the past month. Please upgrade to the most recent version of Dremio at your convenience.