From: Manfred Koizar <mkoi-pg@aon.at>
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? Followup to "How do you select
Date: Thu, 13 Feb 2003 20:39:38 +0100
Message-ID: <44sn4vgisulljq45qo35bug12sed1ll3ce@4ax.com> (raw)
In-Reply-To: <200302131828.50026.nallen@freenet.co.uk>
References: <Pine.LNX.4.44.0302131019020.6771-100000@RedDragon.Childs>
<200302131139.05868.nallen@freenet.co.uk>
<13266.1045149477@sss.pgh.pa.us>
<200302131828.50026.nallen@freenet.co.uk>
On Thu, 13 Feb 2003 18:28:50 +0100, Nicholas Allen
<nallen@freenet.co.uk> wrote:
>Because the WHERE clause is directly affected by the ORDER BY clause.
No, it's not (at least in your query).
> If you
>leave out the order by clause then the row count will be completely different
>and therefore wrong.
I must be missing something. Please give an example.
> 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.
If you have a set of numbers, say {1, 9, 5, 3, 7}, and want to know
how many elements of the set are <= 7 (SELECT COUNT(*) FROM s WHERE
n<=7), you simply look a each element - no matter in what order - and
increase your counter, if the element satifies your condition. I
can't see how you get different numbers when you count {1, 3, 5, 7},
{1, 5, 3, 7} or any other permutation.
>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?
SQL92 says:
<direct select statement: multiple rows> ::=
<query expression> [ <order by clause> ]
[...]
3) Let T be the table specified by the <query expression>.
4) If ORDER BY is specified, then each <sort specification> in the
<order by clause> shall identify a column of T.
Servus
Manfred
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: mkoi-pg@aon.at, nallen@freenet.co.uk, tgl@sss.pgh.pa.us, blue.dragon@blueyonder.co.uk
Subject: Re: Debugging postmaster to fix possible bug in Postgres? Followup to "How do you select
In-Reply-To: <44sn4vgisulljq45qo35bug12sed1ll3ce@4ax.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