Window function without ORDER BY

Question

There is a window function without ORDER BY in OVER () clause. Is there a guarantee that the rows will be processed in the order specified by the ORDER BY expression in SELECT itself?

For example:

SELECT tt.*
     , row_number() OVER (PARTITION BY tt."group") AS npp --without ORDER BY
FROM
  (
   SELECT SUBSTRING(random() :: text, 3, 1) AS "group"
        , random() :: text          AS "data"
   FROM generate_series(1, 100) t(ser)
   ORDER BY "group", "data"
  ) tt
ORDER BY tt."group", npp;

In this example the subquery returns the data sorted in ascending order in each group. The window function handles the rows in the same order, and so the line numbers go in ascending order of the data. Can I rely on this?

Answer 1

Good question!

No, you cannot rely on that.

Window functions are processed before the query's ORDER BY clause, and without an ORDER BY in the window definition, the rows will be processed in the order in which they happen to come from the subselect.