### 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
assertThat(e.getMessage(), errorMessageMatcher);
        } catch (Exception e) {
            throw new AssertionError("expected ResponseException but got [" + e.getClass() + "]", e);
        }
    }
    private Map<String, Object> runSql(String mode, String sql) throws IOException {
        return runSql(mode, sql, StringUtils.EMPTY, randomBoolean());
    }
    private Map<String, Object> runSql(String mode, String sql, boolean columnar) throws IOException {
        return runSql(mode, sql, StringUtils.EMPTY, columnar);
    }
    private Map<String, Object> runSql(String mode, String sql, String suffix, boolean columnar) throws IOException {
        
        return runSql(
            new StringEntity(query(sql).mode(mode).columnar(columnarValue(columnar)).toString(), ContentType.APPLICATION_JSON),
            suffix,
            mode
        );
    }
    protected Map<String, Object> runTranslateSql(String sql) throws IOException {
        return runSql(new StringEntity(sql, ContentType.APPLICATION_JSON), "/translate/", Mode.PLAIN.toString());
    }
    private static Boolean columnarValue(boolean columnar) {
        return columnar ? Boolean.TRUE : (randomBoolean() ? null : Boolean.FALSE);
    }
    protected Map<String, Object> runSql(HttpEntity sql, String suffix, String mode) throws IOException {
        Request request = new Request("POST", SQL_QUERY_REST_ENDPOINT + suffix);
        request.addParameter("error_trace", "true");   
        request.addParameter("pretty", "true");        
        if (randomBoolean()) {