### 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
assertEquals(expected, response.v1());
        response = runSqlAsTextWithFormat(query, "csv");
        assertEquals(expected, response.v1());
    }
    public void testQueryWithoutHeaderInCSV() throws IOException {
        index(
            "{\"name\":" + toJson("first") + ", \"number\" : 1 }",
            "{\"name\":" + toJson("second\t") + ", \"number\": 2 }",
            "{\"name\":" + toJson("\"third,\"") + ", \"number\": 3 }"
        );
        String expected = """
            first,1\r
            second\t,2\r
            ""\"third,""\",3\r
            """;
        String query = "SELECT * FROM " + indexPattern("test") + " ORDER BY number";
        Tuple<String, String> response = runSqlAsText(query, "text/csv; header=absent");
        assertEquals(expected, response.v1());
    }
    public void testNextPageCSV() throws IOException {
        executeQueryWithNextPage("text/csv; header=present", "text,number,sum\r\n", "%s,%d,%d\r\n");
    }
    public void testCSVWithDelimiterParameter() throws IOException {
        String format = randomFrom("txt", "tsv", "json", "yaml", "smile", "cbor");
        String query = "SELECT * FROM " + indexPattern("test");
        index("{\"foo\":1}");
        Request badRequest = new Request("POST", SQL_QUERY_REST_ENDPOINT);
        badRequest.addParameter("format", format);
        badRequest.addParameter("delimiter", ";");
        badRequest.setEntity(