### 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
+ " GROUP BY gender ORDER BY gender",
            columnar
        );
        String cursor = (String) response.remove("cursor");
        assertNull(cursor);
        assertResponse(expected, response);
    }
    @Override
    public void testSelectScoreSubField() throws Exception {
        index("{\"foo\":1}");
        expectBadRequest(
            () -> runSql(randomMode(), "SELECT SCORE().bar FROM " + indexPattern("test")),
            containsString(": mismatched input '.' expecting {<EOF>, ")
        );
    }
    @Override
    public void testHardLimitForSortOnAggregate() throws Exception {
        index("{\"a\": 1, \"b\": 2}");
        expectBadRequest(
            () -> runSql(randomMode(), "SELECT max(a) max FROM " + indexPattern("test") + " GROUP BY b ORDER BY max LIMIT 120000"),
            containsString("The maximum LIMIT for aggregate sorting is [65536], received [120000]")
        );
    }
    public void testUseColumnarForUnsupportedFormats() throws Exception {
        String format = randomFrom("txt", "csv", "tsv");
        index("{\"foo\":1}");
        Request request = new Request("POST", SQL_QUERY_REST_ENDPOINT);
        request.addParameter("error_trace", "true");
        request.addParameter("pretty", "true");
        request.addParameter("format", format);