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

From: David Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Re: BUG #10256: COUNT(*) behaves sort of like RANK() when used over a window containing an ORDER BY
Date: 2014-05-08 00:29:26
Message-ID: CAKFQuwb5vGLgA78ubffcNL-gH3eYTMJRaX05n2yrwTmsECp5HA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

On Wed, May 7, 2014 at 8:07 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > 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.
>
> AFAIK we've only used "peers" in this context to mean "rows with equal
> sort-column values". I don't think we have a specific term for "rows
> appearing in the same partition", but certainly neither the docs nor the
> code mean that when they say "peer".
>
> [ looks at SQL standard... ] The standard uses "peer" in this way too,
> so that's where we got the term from. Because of that, I'm unwilling
> to adopt your suggestion of thinking that "peer" means "member of the
> same partition". However, it seems like maybe we need to clarify the
> term some more, eg define what we mean by it in more places. Are there
> any specific places that you think this should be done?
>
>
I'm pretty sure I simply considered table rows falling into the same
partition as "peers" without anything actually saying that - and for lack
of a better term. So when I read the quoted paragraph in took some mental
processing to put it in context.

I guess rows falling into the same partition could be deemed "member" rows;
as in having membership in the partition. Then two members are of equal
stature (rank) UNLESS other attributes, defined in the ORDER BY, cause them
to be ranked, in which case members having equal ranking according to the
ORDER BY (i.e. identical values for all specified columns) are considered
peers. The default FRAME clause then only considers members of equal and
lesser rank relative to the current member.​ You can provide an explicit
FRAME clause to look both ahead and behind the current member's ranking
group.

Would need to see where to stick something like that but at least we would
have two terms: member and peer matching with PARTITION and ORDER
respectively.

Does the standard provide a word for tuples that get placed into the same
partition?

David J.

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2014-05-08 02:08:41 Re: Re: BUG #10256: COUNT(*) behaves sort of like RANK() when used over a window containing an ORDER BY
Previous Message Stephen Frost 2014-05-08 00:07:26 Re: BUG #10250: pgAdmin III 1.16.1 stores unescaped plaintext password