Users who want to page their result sets may use ORDER BY in combination with LIMIT and OFFSET. This is expensive, when the only reason for using ORDER BY is to support stable paging.
We can introduce a new ORDER BY function: ORDER BY STABLE_INDEX(?s)
This will guarantee that the data is ordered in the order it is in the index, in a stable manner.
We can also use this to support batching for DISTINCT, where if we know the order of a specific variable then we know that every new value for that variable signals a new set of results and we can clear the existing hash set.
Both of these features are best effort. If the query engine is not able to guarantee that the order will be stable, then it will throw an error if the user has used STABLE_INDEX. For DISTINCT we will just fallback to regular distinct iterator.
Users who want to page their result sets may use ORDER BY in combination with LIMIT and OFFSET. This is expensive, when the only reason for using ORDER BY is to support stable paging.
We can introduce a new ORDER BY function: ORDER BY STABLE_INDEX(?s)
This will guarantee that the data is ordered in the order it is in the index, in a stable manner.
We can also use this to support batching for DISTINCT, where if we know the order of a specific variable then we know that every new value for that variable signals a new set of results and we can clear the existing hash set.
Both of these features are best effort. If the query engine is not able to guarantee that the order will be stable, then it will throw an error if the user has used STABLE_INDEX. For DISTINCT we will just fallback to regular distinct iterator.