[BUG] ODBC driver not properly paging

[andybeaulieu]

Describe the bug
When using the ODBC driver from some SQL clients, only the first 200 rows are retrieved by default. Normally we would use a "LIMIT" clause to get more than 200 rows, but LIMIT is not supported by all SQL clients. We can manually increase the cluster opendistro.query.size_limit and the index.max_result_window settings, but this has significant memory overhead.

Can the ODBC driver support an efficient paging mechanism for these?

To Reproduce
Steps to reproduce the behavior:

1. In SQL Server (or a different SQL client tool), add a linked server to the OpenSearch ODBC DSN:
   EXEC sp_addlinkedserver
   @server = N'OpenSearch',
   @srvproduct = N'',
   @Provider = N'MSDASQL',
   @datasrc = N'OpenSearch SQL ODBC DSN';

2. try to query the Linked Server using SQL (ensure the OpenSearch index has more than 200 rows)
   select top 1000 * from [OpenSearch]...[myopensearchindex]

What happens
only the first 200 rows are retrieved. We can force more rows by changing the cluster's opendistro.query.size_limit and each index's index.max_result_window --- but that is not optimal for memory.

Expected behavior
The ODBC driver should implement proper paging to get the data down to the client. Maybe it could honor the T-SQL "top" syntax instead of a "LIMIT" clause?

[dratasich]

Any news? Any solution for this issue? How can we get all results, e.g., for a PowerBI dashboard?

[Yury-Fridlyand]

Hi @dratasich @andybeaulieu,
Sorry for the late response, I got unsubscribed from all ODBC tickets because they were moved to another repo.

I tested ODBC driver with a sample application and it supports pagination/cursor.
Unfortunately, OpenSearch SQL plugin does not support TOP option in SELECT clause. You are welcome to open a ticket - client's/customer's issues are more valued.

[Yury-Fridlyand]

@dratasich @andybeaulieu,
I found an incompatibility between ODBC driver and PBI connector - the connector can't set the fetch_size parameter.
I made a private build for you of the PBI connector which sets fetch_size to 10 for all queries. Try using it and post your feedback:
OpenSearchProject#16.zip

[dratasich]

Thanks @Yury-Fridlyand - we will try and come back to you!