Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id BAA33999 for ; Tue, 25 Jan 2000 01:13:08 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id BAA12272; Tue, 25 Jan 2000 01:12:46 -0500 (EST) To: Philip Warner cc: Don Baccus , Chris Bitmead , pgsql-hackers@postgreSQL.org Subject: Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace In-reply-to: <3.0.5.32.20000125170026.0357d8f0@mail.rhyme.com.au> References: <11879.948776470@sss.pgh.pa.us> <3.0.5.32.20000125150557.036121c0@mail.rhyme.com.au> <2295.948765015@sss.pgh.pa.us> <3.0.5.32.20000125140349.035768d0@mail.rhyme.com.au> <3.0.5.32.20000125150557.036121c0@mail.rhyme.com.au> <3.0.5.32.20000125170026.0357d8f0@mail.rhyme.com.au> Comments: In-reply-to Philip Warner message dated "Tue, 25 Jan 2000 17:00:26 +1100" Date: Tue, 25 Jan 2000 01:12:46 -0500 Message-ID: <12269.948780766@sss.pgh.pa.us> From: Tom Lane Philip Warner writes: >>> 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. > 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? Well, SQL's conceptual model is perfectly clear about the processing work flow of a SELECT: after you've constructed the tuple set (which might involve joining multiple relations), you apply the WHERE condition to filter out uninteresting tuples. Then you apply GROUP BY (if given) to divvy the tuples into groups. Then you apply HAVING to eliminate uninteresting groups. Then you apply aggregate functions (if any) to individual groups, or to the whole filtered result set if no groups. Finally you apply ORDER BY to whatever's left. Note that at no point except the final output is there any notion of the tuples being generated or processed in a particular order. Given appropriate hardware, much of this could be done in parallel. Parallel or not, an implementation is free to choose the processing order for its convenience. regards, tom lane