### 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
"{\"gender\":\"m\", \"langs\": 2}",
            "{\"gender\":\"m\", \"langs\": 3}",
            "{\"gender\":\"m\", \"langs\": 3}",
            "{\"gender\":\"f\", \"langs\": 1}",
            "{\"gender\":\"f\", \"langs\": 2}",
            "{\"gender\":\"f\", \"langs\": 2}",
            "{\"gender\":\"f\", \"langs\": 2}",
            "{\"gender\":\"f\", \"langs\": 3}",
            "{\"gender\":\"f\", \"langs\": 3}"
        );
        Map<String, Object> expected = new HashMap<>();
        boolean columnar = randomBoolean();
        expected.put(
            "columns",
            Arrays.asList(
                columnInfo(mode, "gender", "text", JDBCType.VARCHAR, Integer.MAX_VALUE),
                columnInfo(mode, "cnt", "long", JDBCType.BIGINT, 20),
                columnInfo(mode, "cnt_dist", "long", JDBCType.BIGINT, 20)
            )
        );
        if (columnar) {
            expected.put("values", Arrays.asList(Arrays.asList("f", "m"), Arrays.asList(6, 5), Arrays.asList(3, 3)));
        } else {
            expected.put("rows", Arrays.asList(Arrays.asList("f", 6, 3), Arrays.asList("m", 5, 3)));
        }
        Map<String, Object> response = runSql(
            mode,
            "SELECT gender, COUNT(langs) AS cnt, COUNT(DISTINCT langs) AS cnt_dist "
                + "FROM "
                + indexPattern("test")