Content-Type: text/plain;
  charset="iso-8859-1"
From: Nicholas Allen <nallen@freenet.co.uk>
To: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Debugging postmaster to fix possible bug in Postgres? Followup to
	"How do you select
Date: Thu, 13 Feb 2003 18:28:50 +0100
User-Agent: KMail/1.4.1
Cc: Peter Childs <blue.dragon@blueyonder.co.uk>,
	pgsql-sql@postgresql.org
References: <Pine.LNX.4.44.0302131019020.6771-100000@RedDragon.Childs>
	<200302131139.05868.nallen@freenet.co.uk>
	<13266.1045149477@sss.pgh.pa.us>
In-Reply-To: <13266.1045149477@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200302131828.50026.nallen@freenet.co.uk>

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


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


Thanks,


On Thursday 13 Feb 2003 4:17 pm, Tom Lane wrote:
> Nicholas Allen <nallen@freenet.co.uk> writes:
> > I then commented out the line just to see if this would fix the problem.
> > Then I rebuilt it started the server up and connected. I performed the
> > count query as I described bfore and it worked perfectly! It did exactly
> > what I wanted! Now obviously the code was in there for some reason but =
it
> > seems that it is not necessary to check it in this case. There must be a
> > bug here surely. MySQL also allows it so I don't think it is invalid SQL
> > on my part. And the fact it works perfectly if I disable this check is
> > very promising.
>
> There is no bug here, or wasn't until you broke it.  The given query is
> illegal according to the SQL standard (MySQL is a fairly unreliable
> guide to standard behavior :-().  It seems quite useless anyway:
> "SELECT count(*)" will return exactly one row, so what's the meaning of
> putting an ORDER BY clause on it?
>
> > Now if I execute this (note only difference is change from * to
> > count(*)):
> >
> > select count(*) FROM vu_tbl_user_all_s WHERE s_surname < 'Asurname' or
> > (s_surname =3D 'Asurname' and s_alias <=3D 'CISX' and s_loginid <=3D
> > 'Loginid8') ORDER BY s_surname, s_loginid;
> >
> > I get this:
> >
> > ERROR:  Attribute vu_tbl_user_all_s.s_surname must be GROUPed or used in
> > an aggregate function
>
> 			regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html