Debugging postmaster to fix possible bug in Postgres? Followup to "How do you select

Ok I have rebuilt the postmaster in bebug mode tracked down where the server
throws the error (parse_agg.c line 112 in postgres 7.3).

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.

The only thing is I need to find out why that code was in there and make sure
nothing else gets broken. May be there is a problem with that small pieece of
code or the check is not needed for count? I don't know postgres is a huge
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
course if it really is a problem and I fix it I'll put the code back to the
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 that
> > is returned by the SELECT query. When I execute this:
> >
> > select * 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:
> >
> >  s_alias |  s_surname  | s_name  | s_midname | s_loginid | b_disabled |
> > s_4eyestype   |     s_usertype     | b_hasvaluation |   d_dob    |
> > s_email    | s_phone | s_phone_mb
> > ---------+-------------+---------+-----------+-----------+------------+--
> >---------------+--------------------+----------------+------------+-------
> >--------+---------+------------ CISX    | 3 A Surname | 2aname  |
> >  | Loginid3  |            | No restrictions | CISX Administrator |
> >         | 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 |
> >       |         | CISX    | Asurname    | Aname   |           | Demolish4
> > |            | No restrictions | CISX Administrator |                |
> > 1966-12-12 |               |         | CISX    | Asurname    | Aname   |
> >          | Demolish5 |            | No restrictions | CISX Administrator
> > |                | 1966-12-12 |               |         | CISX    |
> > Asurname    | Aname   |           | Demolish6 |            | No
> > restrictions | CISX Administrator |                | 1966-12-12 |
> >       |         | CISX    | Asurname    | Aoname  |           | Lloginid
> > |            | No restrictions | CISX Administrator |                |
> > 1966-12-12 |               |         | CISX    | Asurname    | Aname   |
> >          | Loginid   |            | No restrictions | CISX Administrator
> > |                |            | asdf@asdf.com |         | CISX    |
> > Asurname    | Aname   |           | Loginid8  |            | No
> > restrictions | CISX Administrator |                | 1966-12-12 |
> >       |         | (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 = '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
> >
> > 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 = 'Asurname' and s_alias <= 'CISX' and s_loginid <=
> '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 = 'Asurname' and s_alias <= 'CISX' and s_loginid <=
> '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 = 'Asurname' and s_alias <= 'CISX' and s_loginid <=
> > > > '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 thought
> > > > 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 bug
> > > 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 through
> > > 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