bym@xxxxxxxx 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
