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

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: [GENERAL] WindowAgg optimized out of subquery but not out of CTE, generating different results.
Дата
Msg-id 18229.1485584278@sss.pgh.pa.us
обсуждение исходный текст
Ответ на [GENERAL] WindowAgg optimized out of subquery but not out of CTE, generating different results.  (bym@byeh.net)
Список pgsql-general
bym@byeh.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


В списке pgsql-general по дате отправления:

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: [GENERAL] Recovery Assistance
Следующее
От: Brian Mills
Дата:
Сообщение: Re: [GENERAL] Recovery Assistance