From: Nicholas Allen <nallen@freenet.co.uk>
To: Manfred Koizar <mkoi-pg@aon.at>
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 21:51:15 +0100
Message-ID: <200302132151.15664.nallen@freenet.co.uk> (raw)
In-Reply-To: <44sn4vgisulljq45qo35bug12sed1ll3ce@4ax.com>
References: <Pine.LNX.4.44.0302131019020.6771-100000@RedDragon.Childs>
<200302131828.50026.nallen@freenet.co.uk>
<44sn4vgisulljq45qo35bug12sed1ll3ce@4ax.com>
Ooops! Appologies to all for being sooo stupid!
I thought the order by was applied before the WHERE and if ordering in DESC
order for example < would mean greater than and so on in the where clause
because I assumed < meant it would appear before in the returned order.
Of course the where just selects which values and the order by orders them!
Don't know how I even got confused in the first place. So there's no bug in
POstgres and I just wasted a day being dumb (guess I should get more than 2
hours sleep before tackling this kinda stuff!).
Thanks all,
On Thursday 13 Feb 2003 8:39 pm, Manfred Koizar wrote:
> 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: nallen@freenet.co.uk, mkoi-pg@aon.at, 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: <200302132151.15664.nallen@freenet.co.uk>
* 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