### 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 query = "SELECT * FROM " + indexPattern("test") + " ORDER BY number";
        Tuple<String, String> response = runSqlAsText(query, "text/tab-separated-values");
        assertEquals(expected, response.v1());
        response = runSqlAsTextWithFormat(query, "tsv");
        assertEquals(expected, response.v1());
    }
    public void testNextPageTSV() throws IOException {
        executeQueryWithNextPage("text/tab-separated-values", "text\tnumber\tsum\n", "%s\t%d\t%d\n");
    }
    public void testBinaryFieldFiltering() throws IOException {
        XContentBuilder createIndex = JsonXContent.contentBuilder().startObject();
        createIndex.startObject("mappings");
        {
            createIndex.startObject("properties");
            {
                createIndex.startObject("id").field("type", "long").endObject();
                createIndex.startObject("binary").field("type", "binary").field("doc_values", true).endObject();
            }
            createIndex.endObject();
        }
        createIndex.endObject().endObject();
        Request request = new Request("PUT", "/test_binary");
        request.setJsonEntity(Strings.toString(createIndex));
        assertEquals(200, provisioningClient().performRequest(request).getStatusLine().getStatusCode());
        long nonNullId = randomLong();
        long nullId = randomLong();
        StringBuilder bulk = new StringBuilder();
        bulk.append("{").append(toJson("index")).append(":{}}\n");
        bulk.append("{");
        {