If you do a select query with limit and order by a column, does that guarantee the results are deterministic? Even if the column has a lot of same values, like a boolean column? Or is the determinism guaranteed only if each row has a unique value on that column?
3 Answers
The sort has to be stable to get replicable results. For instance for:
CREATE TABLE t(id INT, col VARCHAR);
INSERT INTO t
VALUES (1, 'a'), (2, 'a'), (3, 'b);
Query:
SELECT *
FROM t
ORDER BY col
LIMIT 1;
It could return either 1, 'a' or 2, 'a'. It means that there is a tie which is not resolved and another column should be used to provide stable sort.
To easily check if columns provide stable sort the following query could be used:
SELECT *
FROM t
QUALIFY COUNT(*) OVER(PARTITION BY col_list_here) > 1
Did a quick test of this and it returned non-deterministic results (i.e. it changes every single time).
Adding more column in the ORDER BY clause to increase the cardinality still produced inconsistent result.
I was only able to get deterministic results when the ORDER BY columns produces unique combinations of values.
If a non-unique column is used in the ORDER BY clause, the result order is non-deterministic.
Moreover, for the LIMIT / TOP, please note that ORDER BY must be at the same level.
Another example of non-deterministic order behavior is if the combination of the keys in the OVER clause of a window function doesn’t form a composite unique key in the table.
Related