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