Date: Thu, 13 Feb 2003 11:27:18 -0800 (PST)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Nicholas Allen <nallen@freenet.co.uk>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Peter Childs <blue.dragon@blueyonder.co.uk>,
	<pgsql-sql@postgresql.org>
Subject: Re: Debugging postmaster to fix possible bug in Postgres?
In-Reply-To: <200302131828.50026.nallen@freenet.co.uk>
Message-ID: <20030213110929.X47380-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Thu, 13 Feb 2003, Nicholas Allen wrote:

> Because the WHERE clause is directly affected by the ORDER BY clause. If you
> leave out the order by clause then the row count will be completely different
> and therefore wrong. The ORDER BY clause is just as important as the WHERE
> clause when counting rows.  It should be possible to get a count for the rows
> for any query that can be done which can return row data as I understand it.

WHERE effectively occurs before ORDER BY, so unless it's an order by in a
subselect I don't think the where clause can be affected by the ORDER BY
legally.

select count(*) from foo where a<3 order by a;
and
select count(*) from foo where a<3;
must give the same results AFAICT.

> I have tried to find a definition for SQL SELECT command but everywhere I have
> looked so far makes no mention of this being invalid SQL syntax. Can you let
> me know where you got this information?

I think this may follow from the syntax rules of the general select from
a strict reading of SQL92 20.2 and 9.7 (which implies to me that any
form of select a from table order by b; would be an extension) or a looser
reading of syntax rule 7 in 9.7 which disallows value expressions
referencing a column that wasn't specified in a set function specification
for non-grouped tables.