### 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
put("tsv", "text/tab-separated-values");
            }
        };
        final int fetchSize = randomIntBetween(1, 10);
        final int fetchCount = randomIntBetween(1, 9);
        bulkLoadTestData(fetchSize * fetchCount); 
        String format = randomFrom(acceptMap.keySet());
        String mode = randomMode();
        String cursor = null;
        for (int i = 0; i <= fetchCount; i++) { 
            
            RequestObjectBuilder builder = (hasText(cursor) ? cursor(cursor) : query("SELECT text, number FROM " + indexPattern("test")))
                .fetchSize(fetchSize)
                .waitForCompletionTimeout("0d") 
                .keepOnCompletion(true)
                .keepAlive("1d") 
                .mode(mode)
                .binaryFormat(false); 
            Response response = runSqlAsTextWithFormat(builder, format);
            Character csvDelimiter = ',';
            assertEquals(200, response.getStatusLine().getStatusCode());
            assertTrue(response.getHeader(HEADER_NAME_ASYNC_PARTIAL).equals(response.getHeader(HEADER_NAME_ASYNC_RUNNING)));
            String asyncId = response.getHeader(HEADER_NAME_ASYNC_ID);
            assertTrue(hasText(asyncId));
            
            if (response.getHeader(HEADER_NAME_ASYNC_PARTIAL).equals("true")) {
                
                boolean pollForCompletion = randomBoolean();
                if (pollForCompletion) {
                    Request request = new Request("GET", SQL_ASYNC_STATUS_REST_ENDPOINT + asyncId);