Date: Mon, 9 Jun 2003 10:35:10 +0200 (MEST)
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 
In-Reply-To: <3649.1055120755@sss.pgh.pa.us>
Message-ID: <Pine.SOL.4.51.0306091020450.2075@fimm.ifi.uio.no>
References: <200306061003.29430.mike@nachbaur.com>
	<Pine.SOL.4.51.0306072257180.25070@tva.ifi.uio.no>
	<3649.1055120755@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=ISO-8859-1
Content-Transfer-Encoding: QUOTED-PRINTABLE

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 ma=
ybe
> > 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

--=20

| Mail: eivindkv@ifi.uio.no               | Lazy on IRC
| HP: www.stud.ifi.uio.no/~eivindkv       | "Jeg skal vrenge deg med h=E5ret
| Tlf: 22187123/93249534                  |  inn."
|                                         |               -- Yang Tse Lyse