### Description

Only "search hits" based queries (no aggregations) provide a consistent view on the data when paginating through the result set. With point-in-time, it is possible to support transactional semantics for aggregation queries as well.

This issue is somewhat blocked by https://github.com/elastic/elasticsearch/issues/75528 because a PIT can not immediately be closed if the aggregation result set contains less than `fetch_size` records. This could cause some increased resource utilization due to open PITs. Especially if the client does not close the SQL cursor.
x-pack/plugin/sql/qa/server/src/main/java/org/elasticsearch/xpack/sql/qa/rest/RestSqlTestCase.java
* 3. Optionally: That the last page is not empty.
     */
    private void testFetchAllPages(String format, String query, List<String> expectedValues, int pageSize, boolean emptyLastPage)
        throws IOException {
        int remainingPages = expectedValues.size() / pageSize + 1;
        Tuple<String, String> response = runSqlAsText(
            StringUtils.EMPTY,
            new StringEntity(query(query).fetchSize(pageSize).toString(), ContentType.APPLICATION_JSON),
            format
        );
        StringBuilder allResults = new StringBuilder(response.v1());
        while (response.v2() != null && remainingPages > 0) {
            response = runSqlAsText(
                StringUtils.EMPTY,
                new StringEntity(cursor(response.v2()).toString(), ContentType.APPLICATION_JSON),
                format
            );
            allResults.append("\n").append(response.v1());
            assertThat(response.v1().split("\n").length, lessThanOrEqualTo(pageSize));
            remainingPages--;
        }
        if (emptyLastPage) {
            assertTrue("Expected empty last page but got " + response.v1(), Strings.isNullOrEmpty(response.v1()));
        } else {
            assertFalse("Expected non-empty last page but got " + response.v1(), Strings.isNullOrEmpty(response.v1()));
        }
        assertNull(response.v2());
        String results = allResults.toString();
        for (String v : expectedValues) {
            assertThat(results, containsString(v));