### 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
assertTrue(response.containsKey("cursor") == false || response.get("cursor") != null);
            cursor = (String) response.remove("cursor");
            assertResponse(expected, response);
        }
        assertNull(cursor);
        deleteIndex("test_date_timezone");
    }
    @AwaitsFix(bugUrl = "Unclear status, https:github.com/elastic/x-pack-elasticsearch/issues/2074")
    public void testTimeZone() throws IOException {
        String mode = randomMode();
        boolean columnar = randomBoolean();
        index("{\"test\":\"2017-07-27 00:00:00\"}", "{\"test\":\"2017-07-27 01:00:00\"}");
        Map<String, Object> expected = new HashMap<>();
        expected.put("columns", singletonMap("test", singletonMap("type", "text")));
        if (columnar) {
            expected.put("values", Arrays.asList(singletonMap("test", "test"), singletonMap("test", "test")));
        } else {
            
            expected.put("rows", Arrays.asList(singletonMap("test", "test"), singletonMap("test", "test")));
        }
        expected.put("size", 2);
        
        assertResponse(expected, runSql(mode, "SELECT DAY_OF_YEAR(test), COUNT(*) FROM " + indexPattern("test"), columnar));
    }
    public void testScoreWithFieldNamedScore() throws IOException {
        Request request = new Request("POST", "/test/_bulk");
        request.addParameter("refresh", "true");
        String mode = randomMode();
        String bulk = """
            {"index":{"_id":"1"}}