Track total hits always seems to return 10k on larger data sets

[fpasomeillan]

I've been noticing when running a sql query I don't seem to have a way to accurately bring back the total hits, on my over 10k results the total hits always top out at 10k, I run the same query using _search and will bring back the true number of total hits (around 74k), does the sql functionality have the ability to track total hits or is that still a wip ?

[penghuo]

Hi, Thanks for reaching out.
There are two reasons we didn't response total hits in SQL response.

1. In SQL, there is no similar concept of total hits. e.g. SELECT age, balance FROM account LIMIT 1, the expection is two columns and 1 row.
2. Most customer using BI tools to talk with SQL plugin which doesn't require total hits.
   Could we know your use case of using total hits with SQL plugin?

[fpasomeillan]

I was doing some research on using the SQL plugin to handle easily grouping /aggregating data, i.e summarizing data based on a certain field or grouping of fields.
Is there a different tool in opensearch you would recommend?

[rafaelnec]

I'm using total hits to calculate pagination and the track_total_hits parameter will help me a lot with results with more than 10,000 records