X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id DE630474E4F for ; Thu, 13 Feb 2003 05:41:03 -0500 (EST) Received: from localhost.localdomain (B3d50.pppool.de [213.7.61.80]) by postgresql.org (Postfix) with ESMTP id CA692474E42 for ; Thu, 13 Feb 2003 05:41:00 -0500 (EST) Received: from localhost.localdomain (localhost.localdomain [127.0.0.1]) by localhost.localdomain (8.11.6/8.11.6) with ESMTP id h1DAd6704852; Thu, 13 Feb 2003 11:39:07 +0100 Content-Type: text/plain; charset="iso-8859-1" From: Nicholas Allen To: Peter Childs Subject: Debugging postmaster to fix possible bug in Postgres? Followup to "How do you select Date: Thu, 13 Feb 2003 11:39:05 +0100 User-Agent: KMail/1.4.1 Cc: pgsql-sql@postgresql.org References: In-Reply-To: MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200302131139.05868.nallen@freenet.co.uk> X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/284 X-Sequence-Number: 11682 Ok I have rebuilt the postmaster in bebug mode tracked down where the serve= r=20 throws the error (parse_agg.c line 112 in postgres 7.3).=20 I then commented out the line just to see if this would fix the problem. Th= en=20 I rebuilt it started the server up and connected. I performed the count que= ry=20 as I described bfore and it worked perfectly! It did exactly what I wanted!= =20 Now obviously the code was in there for some reason but it seems that it is= =20 not necessary to check it in this case. There must be a bug here surely.=20 MySQL also allows it so I don't think it is invalid SQL on my part. And the= =20 fact it works perfectly if I disable this check is very promising. The only thing is I need to find out why that code was in there and make su= re=20 nothing else gets broken. May be there is a problem with that small pieece = of=20 code or the check is not needed for count? I don't know postgres is a huge= =20 program and this is the first time I've even looked at the code! Any more help with fixing the problem in Postgres would be appreciated. Of= =20 course if it really is a problem and I fix it I'll put the code back to the= =20 main source tree. Thanks in advance... On Thursday 13 Feb 2003 11:25 am, Peter Childs wrote: > On Thu, 13 Feb 2003, Nicholas Allen wrote: > > Sorry, I don't think you understand. I HAVE surnames and I have data th= at > > is returned by the SELECT query. When I execute this: > > > > select * 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: > > > > s_alias | s_surname | s_name | s_midname | s_loginid | b_disabled |= =20=20 > > s_4eyestype | s_usertype | b_hasvaluation | d_dob |=20= =20=20 > > s_email | s_phone | s_phone_mb > > ---------+-------------+---------+-----------+-----------+------------+= -- > >---------------+--------------------+----------------+------------+-----= -- > >--------+---------+------------ CISX | 3 A Surname | 2aname |=20=20= =20=20=20=20=20=20=20 > > | Loginid3 | | No restrictions | CISX Administrator |=20= =20=20=20=20=20=20 > > | 1966-12-12 | | | CISX | A Asurname | > > A Aname | | Loginid2 | | No restrictions | CISX > > Administrator | | 1966-12-12 | | | > > CISX | Asurname | Aname | | Demolish3 | | = No > > restrictions | CISX Administrator | | 1966-12-12 |=20=20= =20=20=20=20=20=20 > > | | CISX | Asurname | Aname | | Demolis= h4 > > | | No restrictions | CISX Administrator | | > > 1966-12-12 | | | CISX | Asurname | Aname = |=20 > > | Demolish5 | | No restrictions | CISX Administrator > > | | 1966-12-12 | | | CISX | > > Asurname | Aname | | Demolish6 | | No > > restrictions | CISX Administrator | | 1966-12-12 |=20=20= =20=20=20=20=20=20 > > | | CISX | Asurname | Aoname | | Llogini= d=20 > > | | No restrictions | CISX Administrator | | > > 1966-12-12 | | | CISX | Asurname | Aname = |=20 > > | Loginid | | No restrictions | CISX Administrator > > | | | asdf@asdf.com | | CISX | > > Asurname | Aname | | Loginid8 | | No > > restrictions | CISX Administrator | | 1966-12-12 |=20=20= =20=20=20=20=20=20 > > | | (9 rows) > > > > 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 > > > > My point is if there is data to be returned in a SELECT statement then > > postgres should be able to count the number of rows that would be > > returned without actually returning me the data for the rows. > > > > Surely any SELECT statement that returns data can also be used to count > > data that would be returned? > > They what I think is happerning is that Postgres can only order by > parts of the result set not parts of the orignal table(s). The order by > will not make any difference to the result of the query anyhow. I suspect > there is some complex reason why postgres thorws and error rather than > ignor the order by I think you will find that it does not know what you > want. > > 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 group by s_surname,s_loginid; > > or > > 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'); > > and since you have said sort by it, it want to know what to sort. > > Peter Childs > > > Thanks again! > > > > On Thursday 13 Feb 2003 10:58 am, Peter Childs wrote: > > > On Thu, 13 Feb 2003, Nicholas Allen wrote: > > > > But I'm not returning the surnames. I just execute this query (sorry > > > > I forgot to put it in my last email): > > > > > > > > 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; > > > > > > Excuse me but why are you ordering a SELECT count(*) statement. > > > The ORDER BY clause is probably why you it wants to group it because > > > postgres whats somthing to order. > > > > > > > So all I am returning is the count right? If I execute the query > > > > using * instead of count(*) to actually see the rows it returns the > > > > rows as expected upto the row I was interested in. I would assume if > > > > postgres can execute a query to give me row data it should also be > > > > able to execute the query and count the rows without actually giving > > > > me the row data when I use count(*). > > > > > > > > I have tried this on MySQL and it works just not on Postgres so it > > > > doesn't seem to be something I am doing wrong. Which is why I thoug= ht > > > > it must be a bug in Postgres. > > > > > > I can't see the bug its saying order by surname so it looks for > > > some surnames to order by finds none so complains. If there is any b= ug > > > its likly to be that this works in MySQL. :) > > > > > > > Am I not missing something here. Is there something wrong with the > > > > syntax of the above query? > > > > > > Sorry but don't try sorting unless you have somthing to sort. > > > > > > > Thjanks again for your help! I really appreciate it. > > > > > > Peter Childs > > > > > > > > > ---------------------------(end of > > > broadcast)--------------------------- TIP 3: if posting/reading throu= gh > > > Usenet, please send an appropriate subscribe-nomail command to > > > majordomo@postgresql.org so that your message can get through to the > > > mailing list cleanly > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org