Re: Possible bug in Postgres? Followup to "How do you select - Mailing list pgsql-sql

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
executethis:
 
> 
> 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
rowsthat 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
> 
>