### 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
String actualJson = new BytesArray(content.readAllBytes()).utf8ToString();
            assertEquals(expectedJson, actualJson);
        }
    }
    public void testBasicTranslateQuery() throws IOException {
        index("{\"test\":\"test\"}", "{\"test\":\"test\"}");
        Map<String, Object> response = runTranslateSql(query("SELECT * FROM " + indexPattern("test")).toString());
        assertEquals(1000, response.get("size"));
        assertFalse((Boolean) response.get("_source"));
        @SuppressWarnings("unchecked")
        List<Map<String, Object>> source = (List<Map<String, Object>>) response.get("fields");
        assertEquals(singletonList(singletonMap("field", "test")), source);
    }
    public void testBasicQueryWithFilter() throws IOException {
        String mode = randomMode();
        index("{\"test\":\"foo\"}", "{\"test\":\"bar\"}");
        Map<String, Object> expected = new HashMap<>();
        expected.put("columns", singletonList(columnInfo(mode, "test", "text", JDBCType.VARCHAR, Integer.MAX_VALUE)));
        expected.put("rows", singletonList(singletonList("foo")));
        assertResponse(
            expected,
            runSql(
                new StringEntity(
                    query("SELECT * FROM " + indexPattern("test")).mode(mode).filter("{\"match\": {\"test\": \"foo\"}}").toString(),
                    ContentType.APPLICATION_JSON
                ),
                StringUtils.EMPTY,
                mode
            )
        );