Re: 'within group'- or percentile_cont-expression seems to have ramifications on table ordering

Thank you for the clarification

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> schrieb am So., 20. Juni 2021, 19:30:

> Bernd Hopp <berndjhopp(at)gmail(dot)com> writes:
> > you can see that the rows are not in order of insertion any more, but
> > in descending order of value. That may not violate the specification,
> > however I find it
> > to be counterintuitive that a non-updating query would have such side
> effects.
>
> On what grounds do you say that UPDATE is a non-updating query?
>
> UPDATE will always place the new row version somewhere else than the
> old row version; it cannot simply overwrite the row without violating
> ACID semantics. In the case you show here, the updated versions are
> all added at the end of the table, leading to the apparent ordering
> change. This indeed does not violate the specification, because so
> far as the SQL spec is concerned, physical row order is simply not
> of interest.
>
> regards, tom lane
>