### 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
new StringEntity(
                query(query).mode(randomValueOtherThan(Mode.JDBC.toString(), BaseRestSqlTestCase::randomMode)).toString(),
                ContentType.APPLICATION_JSON
            )
        );
        expectBadRequest(() -> {
            client().performRequest(badRequest);
            return emptyMap();
        }, containsString("request [/_sql] contains unrecognized parameter: [delimiter]"));
        Request csvRequest = new Request("POST", SQL_QUERY_REST_ENDPOINT + "?format=csv&delimiter=%3B");
        csvRequest.setEntity(
            new StringEntity(
                query(query).mode(randomValueOtherThan(Mode.JDBC.toString(), BaseRestSqlTestCase::randomMode)).toString(),
                ContentType.APPLICATION_JSON
            )
        );
        assertOK(client().performRequest(csvRequest));
    }
    public void testQueryInTSV() throws IOException {
        index(
            "{\"name\":" + toJson("first") + ", \"number\" : 1 }",
            "{\"name\":" + toJson("second\t") + ", \"number\": 2 }",
            "{\"name\":" + toJson("\"third,\"") + ", \"number\": 3 }"
        );
        String expected = """
            name\tnumber
            first\t1
            second\\t\t2
            "third,"\t3
            """;