Limit and Offset restrictions

Hello,
how can I get more than 10000 in SQL queries?
When use something like this “LIMIT 10000, 2000” I get an error.
I am not requesting more than 10000 records at once, but I can’t seem to paginate.
Thank you

Thanks for reporting the issue. I tried the query from my side. Basically it reached the index.max_result_window limit though the size of resulting record is small.

ES doc explains: “The maximum value of from + size for searches to this index. Defaults to 10000 . Search requests take heap memory and time proportional to from + size and this limits that memory.”

So I think our LIMIT clause can only work within this window. Alternatively please check out our pagination doc: https://opendistro.github.io/for-elasticsearch-docs/docs/sql/endpoints/#cursor. Thanks!

Hello, thank you for your response. I am using the JDBC driver inside DBeaver. Isn’t that incorporated into the driver or it depends on the application itself?
Thank you.

Yes, it is. I think it’s because our pagination feature is disabled by default for now. So you need to enabled it by changing your ES cluster setting: https://github.com/opendistro-for-elasticsearch/sql/blob/master/docs/user/admin/settings.rst#opendistro-sql-cursor-enabled

And then you can change page size (“fetch_size”) in your JDBC driver by passing fetchSize parameter in connection url or by setFetchSize() method. Please see more details in our README: https://github.com/opendistro-for-elasticsearch/sql/tree/master/sql-jdbc