pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedI'm using both window and agg, is this expected sorting behavior?
2+ messages / 2 participants
[nested] [flat]
* I'm using both window and agg, is this expected sorting behavior?
@ 2023-10-04 16:05 jinser <aimer@purejs.icu>
2023-10-04 16:32 ` Re: I'm using both window and agg, is this expected sorting behavior? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: jinser @ 2023-10-04 16:05 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: I'm using both window and agg, is this expected sorting behavior?
2023-10-04 16:05 I'm using both window and agg, is this expected sorting behavior? jinser <aimer@purejs.icu>
@ 2023-10-04 16:32 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2023-10-04 16:32 UTC (permalink / raw)
To: jinser <aimer@purejs.icu>; +Cc: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2023-10-04 16:32 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-04 16:05 I'm using both window and agg, is this expected sorting behavior? jinser <aimer@purejs.icu>
2023-10-04 16:32 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox