From: Eivind Kvedalen <eivindkv@ifi.uio.no>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: "Join" on delimeter aggregate query
Date: Mon, 9 Jun 2003 10:35:10 +0200 (MEST)
Message-ID: <Pine.SOL.4.51.0306091020450.2075@fimm.ifi.uio.no> (raw)
In-Reply-To: <3649.1055120755@sss.pgh.pa.us>
References: <200306061003.29430.mike@nachbaur.com>
<Pine.SOL.4.51.0306072257180.25070@tva.ifi.uio.no>
<3649.1055120755@sss.pgh.pa.us>
On Sun, 8 Jun 2003, Tom Lane wrote:
> Eivind Kvedalen <eivindkv@ifi.uio.no> writes:
> > SELECT a, concat(b) FROM (SELECT a,b FROM test ORDER BY a,b) T GROUP BY a;
>
> > The ORDER BY is included to sort the rows before they are aggregated. I'm
> > not sure that this guarantees that they actually will be sorted, but maybe
> > some of the postgresql hackers can confirm/deny this?
>
> This technique will work reliably as of 7.4, but it's not reliable
> in existing releases. The GROUP BY will do its own sort on A, and
> unless qsort() is stable on your machine (which it's not, in most
> implementations) the secondary ordering by B will be destroyed.
>
> The fix in 7.4 simply makes the planner smart enough to notice that
> the sub-select's output is already adequately sorted for grouping
> by A.
Ok. What I actually had in mind was whether the optimizer would remove the
ORDER BY clause completely or not, as it isn't used in the top-level
SELECT query, and SQL doesn't in general guarantee ordered rows back
unless there's an ORDER BY in the top-level SELECT (I haven't read the SQL
standard, so I might very well be wrong here).
The GROUP BY sorts on A to do the grouping correctly, right? (That is,
removing the duplicates from A)
(Now, thinking more about this, removing the ORDER BY in the optimizer as
I suggested above would effectively remove the ORDER BY in created views,
right?)
Eivind
--
| Mail: eivindkv@ifi.uio.no | Lazy on IRC
| HP: www.stud.ifi.uio.no/~eivindkv | "Jeg skal vrenge deg med håret
| Tlf: 22187123/93249534 | inn."
| | -- Yang Tse Lyse
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: eivindkv@ifi.uio.no, tgl@sss.pgh.pa.us
Subject: Re: "Join" on delimeter aggregate query
In-Reply-To: <Pine.SOL.4.51.0306091020450.2075@fimm.ifi.uio.no>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox