### 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
expectBadRequest(() -> {
            client().performRequest(request);
            return emptyMap();
        }, containsString("No type specified for runtime field [address]"));
        runtimeMappings = "{\"address\": [{\"script\": \"return\"}]}";
        request.setEntity(
            new StringEntity(
                query("SELECT * FROM " + indexPattern("test")).mode(mode).runtimeMappings(runtimeMappings).toString(),
                ContentType.APPLICATION_JSON
            )
        );
        expectBadRequest(() -> {
            client().performRequest(request);
            return emptyMap();
        }, containsString("Expected map for runtime field [address] definition but got [String]"));
    }
    public static void expectBadRequest(CheckedSupplier<Map<String, Object>, Exception> code, Matcher<String> errorMessageMatcher) {
        try {
            Map<String, Object> result = code.get();
            fail("expected ResponseException but got " + result);
        } catch (ResponseException e) {
            if (400 != e.getResponse().getStatusLine().getStatusCode()) {
                String body;
                try {
                    body = Streams.copyToString(new InputStreamReader(e.getResponse().getEntity().getContent(), StandardCharsets.UTF_8));
                } catch (IOException bre) {
                    throw new RuntimeException("error reading body after remote sent bad status", bre);
                }
                fail("expected [400] response but get [" + e.getResponse().getStatusLine().getStatusCode() + "] with body:\n" + body);
            }