### 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/src/test/java/org/elasticsearch/xpack/sql/analysis/CancellationTests.java
Client client = mock(Client.class);
        SqlQueryTask task = randomTask();
        TaskCancelHelper.cancel(task, "simulated");
        ClusterService mockClusterService = mockClusterService();
        IndexResolver indexResolver = indexResolver(client);
        PlanExecutor planExecutor = new PlanExecutor(client, indexResolver, new NamedWriteableRegistry(Collections.emptyList()));
        CountDownLatch countDownLatch = new CountDownLatch(1);
        SqlQueryRequest request = new SqlQueryRequestBuilder(client, SqlQueryAction.INSTANCE).query("SELECT foo FROM bar").request();
        TransportSqlQueryAction.operation(planExecutor, task, request, new ActionListener<>() {
            @Override
            public void onResponse(SqlQueryResponse sqlSearchResponse) {
                fail("Shouldn't be here");
                countDownLatch.countDown();
            }
            @Override
            public void onFailure(Exception e) {
                assertThat(e, instanceOf(TaskCancelledException.class));
                countDownLatch.countDown();
            }
        }, "", mock(TransportService.class), mockClusterService);
        countDownLatch.await();
        verify(client, times(1)).settings();
        verify(client, times(1)).threadPool();
        verifyNoMoreInteractions(client);
    }
    private Map<String, Map<String, FieldCapabilities>> fields(String[] indices) {
        FieldCapabilities fooField = new FieldCapabilities("foo", "integer", false, true, true, indices, null, null, emptyMap());
        FieldCapabilities categoryField = new FieldCapabilities(
            "event.category",
            "keyword",