Обсуждение: I'm using both window and agg, is this expected sorting behavior?

Hello Friends,
I encountered a behavior that confused me when using window function
and group aggregate at the same time.

simple table:
CREATE TABLE t (a int, b int);

add some data to represent the behavior:
insert into t values
    (1, 39),
    (1, 95),
    (2, 48),
    (3, 87),
    (4, 19),
    (4, 78),
    (4, 53);

When I execute:

-- query #1
select
    a,
    row_number() over (partition by a order by a)
from t
group by a;

| a   | row_number |
| --- | ---------- |
| 1   | 1          |
| 2   | 1          |
| 3   | 1          |
| 4   | 1          |

everything works as usual, but if I add desc sorting in over window:

-- query #2
select
    a,
    row_number() over (partition by a order by a desc)
from t
group by a;

| a   | row_number |
| --- | ---------- |
| 4   | 1          |
| 3   | 1          |
| 2   | 1          |
| 1   | 1          |

the sorting of the entire table also follows the constraints (desc) in over.

This is what confuses me, I've tried many versions of pgsql, at least
including 11-16, and the behavior is consistent with the above; but I
feel that maybe the order by in the window function should not affect
the final result.
Also, I don't know if I can mention this, but the results of both
queries tested on mysql 8.0 are the same.

I searched the mailing list for a while but couldn't find it. If
anyone has already mentioned this, sorry, please feel free to point it
out.

jinser <aimer@purejs.icu> writes:
> This is what confuses me, I've tried many versions of pgsql, at least
> including 11-16, and the behavior is consistent with the above; but I
> feel that maybe the order by in the window function should not affect
> the final result.

Since you have not specified an ORDER BY for the overall query
result, the implementation is entitled to return the rows in
any order it pleases.  Ours happens to sort by the window ordering
before computing the window functions, so that's what you get.
In this case anyway --- you shouldn't rely on that.

            regards, tom lane