### 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
);
    }
    private void executeQueryWithNextPage(String format, String expectedHeader, String expectedLineFormat) throws IOException {
        int size = 20;
        String[] docs = new String[size];
        for (int i = 0; i < size; i++) {
            docs[i] = """
                {"text":"text%s", "number":%s}
                """.formatted(i, i);
        }
        index(docs);
        String optionalGroupBy = randomFrom("", " GROUP BY text, number, sum");
        String request = query("SELECT text, number, number + 5 AS sum FROM " + indexPattern("test") + optionalGroupBy + " ORDER BY number")
            .fetchSize(2)
            .toString();
        String cursor = null;
        for (int i = 0; i <= size; i += 2) {
            Tuple<String, String> response;
            if (i == 0) {
                response = runSqlAsText(StringUtils.EMPTY, new StringEntity(request, ContentType.APPLICATION_JSON), format);
            } else {
                response = runSqlAsText(
                    StringUtils.EMPTY,
                    new StringEntity(cursor(cursor).toString(), ContentType.APPLICATION_JSON),
                    format
                );
            }
            StringBuilder expected = new StringBuilder();
            if (i == 0) {
                expected.append(expectedHeader);