To: Nicholas Allen <nallen@freenet.co.uk>
Cc: Peter Childs <blue.dragon@blueyonder.co.uk>,
	pgsql-sql@postgresql.org
Subject: Re: Debugging postmaster to fix possible bug in Postgres? Followup to
	"How do you select
In-reply-to: <200302131139.05868.nallen@freenet.co.uk> 
References: <Pine.LNX.4.44.0302131019020.6771-100000@RedDragon.Childs>
	<200302131139.05868.nallen@freenet.co.uk>
Comments: In-reply-to Nicholas Allen <nallen@freenet.co.uk>
	message dated "Thu, 13 Feb 2003 11:39:05 +0100"
Date: Thu, 13 Feb 2003 10:17:57 -0500
Message-ID: <13266.1045149477@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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 = 'Asurname' and s_alias <= 'CISX' and s_loginid <=
> '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