Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux