### 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/main/java/org/elasticsearch/xpack/sql/execution/search/Querier.java
*     <li>
         *         If a tuple in {@code sortingColumns} has a null comparator, it is assumed the sorting
         *         happened in ES and the results are left as is (by using the row ordering), otherwise it is
         *         sorted based on the given criteria.
         *     </li>
         *     <li>
         *         If no tuple exists in {@code sortingColumns} for an output column, it means this column
         *         is not included at all in the ORDER BY
         *     </li>
         *</ul>
         *
         * Take for example ORDER BY a, x, b, y
         * a, b - are sorted in ES
         * x, y - need to be sorted client-side
         * sorting on x kicks in only if the values for a are equal.
         * sorting on y kicks in only if the values for a, x and b are all equal
         *
         */
        
        @SuppressWarnings("unchecked")
        @Override
        protected boolean lessThan(Tuple<List<?>, Integer> l, Tuple<List<?>, Integer> r) {
            for (Tuple<Integer, Comparator> tuple : sortingColumns) {
                int columnIdx = tuple.v1().intValue();
                Comparator comparator = tuple.v2();
                
                Object vl = l.v1().get(columnIdx);
                Object vr = r.v1().get(columnIdx);
                if (comparator != null) {
                    int result = comparator.compare(vl, vr);