Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 7BE2E101438B for ; Thu, 1 Mar 2012 17:29:43 -0400 (AST) Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1S3DZF-0003bE-CR for pgsql-performance@postgresql.org; Thu, 01 Mar 2012 21:29:43 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id q21LTOeB017998; Thu, 1 Mar 2012 16:29:24 -0500 (EST) To: Craig James cc: Kevin Grittner , "Marcin Miros*aw" , pgsql-performance@postgresql.org Subject: Re: [planner] Ignore "order by" in subselect if parrent do count(*) In-reply-to: References: <4F4F6158.7060200@mejor.pl> <4F4F54910200002500045D59@gw.wicourts.gov> <14667.1330624202@sss.pgh.pa.us> Comments: In-reply-to Craig James message dated "Thu, 01 Mar 2012 10:19:31 -0800" Date: Thu, 01 Mar 2012 16:29:24 -0500 Message-ID: <17997.1330637364@sss.pgh.pa.us> From: Tom Lane X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201203/31 X-Sequence-Number: 46481 Craig James writes: > On Thu, Mar 1, 2012 at 9:50 AM, Tom Lane wrote: >> Considering that ORDER BY in a subquery isn't even legal per spec, > That's surprising ... normally it won't affect the result, but with an > offset or limit it would. Does the offset or limit change the "not > even legal" part? Well, actually, the SQL standard didn't have anything comparable to offset/limit until SQL:2008, either. But I have to take back my statement above. It wasn't legal in SQL99, but evidently they added it in the 2003 or 2008 edition, presumably to go with the limit functionality. Anyway, the long and the short of it is that people depend on ORDER BY in subqueries to be honored, and we're not going to break that. regards, tom lane