Re: WindowAgg optimized out of subquery but not out of CTE, generating different results.

bym(at)byeh(dot)net writes:
> Is this supposed to happen given the queries above? Why? Is there some
> sort of interaction happening between the missing DISTINCT ON ORDER BY
> and the window ORDER BY? is this a bug?

Yes, your query is underspecified, yes, no.

The PG SELECT reference page explains correct usage of DISTINCT ON.
In particular it says

The DISTINCT ON expression(s) must match the leftmost ORDER BY
expression(s). The ORDER BY clause will normally contain additional
expression(s) that determine the desired precedence of rows within each
DISTINCT ON group.

If you don't have additional ORDER BY columns to specify the precedence
order within each DISTINCT ON group, then you get a random row from each
group, which is exactly what you're complaining about.

In the case with the CTE, the row ordering that goes into the DISTINCT
step happens to be determined by the processing needs of the window
function. In the other case, without the optimization fence created by
the CTE, the planner observes that the window function is unreferenced
so it throws it away. Now there's nothing that will determine the row
ordering within the DISTINCT ON groups, and the ordering you happen to get
isn't the one you want. But it's not a bug because you did not do what
you're supposed to do to specify the ordering you want.

In short: write the required ORDER BY.

regards, tom lane