Re: Re: BUG #10256: COUNT(*) behaves sort of like RANK() when used over a window containing an ORDER BY

On Wed, May 7, 2014 at 04:08:23PM -0700, David G Johnston wrote:
> Tom's quote is pretty much an abbreviation of the following taken directly
> from:
> http://www.postgresql.org/docs/9.3/interactive/sql-select.html
>
> "The default framing option is RANGE UNBOUNDED PRECEDING, which is the same
> as RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With ORDER BY, this
> sets the frame to be all rows from the partition start up through the
> current row's last peer. Without ORDER BY, all rows of the partition are
> included in the window frame, since all rows become peers of the current
> row."
>
> A more useful description and example is found in the tutorial (about
> halfway down, the sum(*) example):
>
> http://www.postgresql.org/docs/9.3/interactive/tutorial-window.html
>
> The term "peer" in the first quotation is confusing to me. My understanding
> is that "PARTITION BY" defines which rows are "peers" - even if that isn't
> the wording used. So now using "peers" in relation to the FRAME clause
> confuses the issue. IMO the "since all rows..." is really superfluous - I
> would understand:
>
> "In the presence of an ORDER BY only rows up to the current row (including
> all [partition] peers) are considered. Omitting an ORDER BY causes the
> entire PARTITION to be considered the FRAME. Other frame definitions can be
> requested by supplying an explicit FRAME clause."
>
> I knew the answer but in the interest of looking through the user's eyes I
> wanted to go take a look (again, done this before) and really am not
> surprised that these kinds of questions are being asked. Just having to
> scan through 5 sections of the documentation is difficult. That said, the
> tutorial section and inline examples make it quite clear how the
> with/without ORDER BY behavior of window functions (sum in the example)
> cause different results.
>
> At minimum the top of 9.3.4 could provide links to, and
> descriptions/summaries of, what the other 4 sections cover and why things
> are broken out the way they are. The other cross-references could point
> back to that section-subsection as a kind of launch point: "Please see
> section 3.5.1 for an overview of, and links to, other related sections."

Based on this discussion, I developed the attached patch which qualifies
"peer" in every place it is mentioned --- this should reduce future
confusion.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +