From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Nicholas Allen <nallen@freenet.co.uk>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Peter Childs <blue.dragon@blueyonder.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: Debugging postmaster to fix possible bug in Postgres?
Date: Thu, 13 Feb 2003 11:27:18 -0800 (PST)
Message-ID: <20030213110929.X47380-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <200302131828.50026.nallen@freenet.co.uk>
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.
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: sszabo@megazone23.bigpanda.com, nallen@freenet.co.uk, tgl@sss.pgh.pa.us, blue.dragon@blueyonder.co.uk
Subject: Re: Debugging postmaster to fix possible bug in Postgres?
In-Reply-To: <20030213110929.X47380-100000@megazone23.bigpanda.com>
* 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