From: | Philip Warner <pjw(at)rhyme(dot)com(dot)au> |
---|---|
To: | Don Baccus <dhogaza(at)pacifier(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Chris Bitmead <chris(at)bitmead(dot)com>, pgsql-hackers(at)postgreSQL(dot)org |
Subject: | Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace |
Date: | 2000-01-25 06:00:26 |
Message-ID: | 3.0.5.32.20000125170026.0357d8f0@mail.rhyme.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-sql |
At 21:20 24/01/00 -0800, Don Baccus wrote:
>At 12:01 AM 1/25/00 -0500, Tom Lane wrote:
>>Philip Warner <pjw(at)rhyme(dot)com(dot)au> writes:
>>> Just a thought, but would I be right in saying that this could be easily
>>> done with the addition of a new aggregate function 'FIRST', which simply
>>> returns the first value sent to it? Since the aggregates operate a row at
>>> a time, you are guaranteed a consistent set of values, I think.
>>
>>No, because the system doesn't guarantee to deliver tuples to the
>>aggregate in any particular order.
>
>In fact, this is a KEY notion behind RDBMS systems...queries return an
>unordered set, pure and simple, unless you supply an "order" clause
>(though "group by" appears to be implemented by ordering in various
>RDBMS systems, that's an efficiency hack not a given AFAIK).
That's why there was an 'order by' in the query (there should have also
been a 'group by', to make it valid SQL). I had (naievely) assumed that the
aggregates would be passed the rows in sorted order.
>>SQL in general doesn't believe that tuple ordering has any semantic
>>significance --- you can ask for ORDER BY, but that's only honored
>>at the end stage of SELECT result delivery, not necessarily anywhere
>>in the bowels of a query.
>
>Yep...
>
Out of curiosity, does the SQL spec give any rules or guidelines about when
aggregates should be applied to resultant rows? Or is it one of the
implementation-dependant things?
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: +61-03-5367 7422 | _________ \
Fax: +61-03-5367 7430 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Robinson | 2000-01-25 06:05:36 | Re: [HACKERS] fatal copy in/out error (6.5.3) |
Previous Message | Tom Lane | 2000-01-25 05:39:14 | Oh btw, about XXX |
From | Date | Subject | |
---|---|---|---|
Next Message | Chris Bitmead | 2000-01-25 06:07:03 | Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace |
Previous Message | Don Baccus | 2000-01-25 05:20:22 | Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace |