pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPossible bug in Postgres? Followup to "How do you select from a table until a condition is met?"
6+ messages / 3 participants
[nested] [flat]
* Possible bug in Postgres? Followup to "How do you select from a table until a condition is met?"
@ 2003-02-12 22:21 Nicholas Allen <nallen@freenet.co.uk>
0 siblings, 1 reply; 6+ messages in thread
From: Nicholas Allen @ 2003-02-12 22:21 UTC (permalink / raw)
To: Dmitry Tkach <dmitry@openratings.com>; +Cc: Bruno Wolff III <bruno@wolff.to>; pgsql-sql
Ok I have decided that this is the best way to do it. Assuming I have a column (or a few columns) which can uniquely define a row I should be able to select up to a known row with a clever where clause. For example a table contains the following dummy data (the combination of s_alias and s_loginid will be unique for each row):
select * FROM vu_tbl_user_all_s ORDER BY s_surname, s_loginid;
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 | | |
CISX | Asurname | Aname | | Loginid9 | | No restrictions | CISX Administrator | | 1966-12-12 | | |
CISX | A Surname | Aname | | Loginid4 | | No restrictions | CISX Administrator | | 1966-12-12 | | |
CISX | Cisx | Cursor | | Systemuser | f | No restrictions | CISX Administrator | f | 1966-01-23 | | |
CISX | Dazor | Ronald | | Demolish | t | No restrictions | CISX Administrator | | 1967-12-12 | | |
CISX | Dazor | Ronald | | Demolish2 | f | No restrictions | CISX Administrator | | 1967-12-12 | | |
(14 rows)
I should be able to select up to row 9 (Loginid8)using this query:
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;
This works and I get the following (expected) output:
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 | Aso it is not the very latest version.name | | 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)
However, if I try to count the records using the count(*) function I get the following error.
ERROR: Attribute vu_tbl_user_all_s.s_surname must be GROUPed or used in an aggregate function
Surely, if postgres can execute the query it should also be able to count the rows that would be returned in the query? Is this a bug in Postgres and is there a work around? I am using Postgres 7.3.
Thanks again!
On Wednesday 12 Feb 2003 11:45 pm, Dmitry Tkach wrote:
> Nicholas Allen wrote:
> >Ok I thought of that but what happens if there is no primary key in the
> > table?
>
> Then tough luck - as you said yourself, since everything else can be
> duplicated, the primary key is the only way to
> tell for sure which exact row you are talking about.
>
> >I can probably add primary keys to the table but I didn't design the
> > tables and so I have little (but luckily some) say over what columns
> > appear in them. What has actually happened is that we have a view on a
> > table and the view doesn't return the primary key. I'll try and ask the
> > database administrator to add the primary keys.
> >
> >Thanks for the help though I guess it is the only way to do it. I was just
> >hoping there would be a way to do it without a promary key to prevent
> > changes to our database views.
>
> I think you are better off redesigning your interface - even if you do
> modify the view, and use those suggestions I mentioned earlier,
> it will be slow like hell, unless your table only contains a few hundred
> rows, in which case it doesn't matter if you send the whole table to the
> client every time,
>
> Dima
>
> >On Wednesday 12 Feb 2003 9:37 pm, Bruno Wolff III wrote:
> >>On Wed, Feb 12, 2003 at 20:55:21 +0100,
> >>
> >> Nicholas Allen <nallen@freenet.co.uk> wrote:
> >>>I thought of this but the problem is that there may be multiple rows
> >>> with the same value for the column I am sorting on. Eg if sorting on a
> >>> surname then there may be 100s of people with the same surname so
> >>> generating a where clause that selects up to the exact person
> >>> previously selected is very difficult.
> >>
> >>Then you should sort on surname AND whatever you are using as the primary
> >>key.
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.or
> >
> >g
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Possible bug in Postgres? Followup to "How do you select from a table until a condition is met?"
@ 2003-02-13 02:19 Bruno Wolff III <bruno@wolff.to>
parent: Nicholas Allen <nallen@freenet.co.uk>
0 siblings, 1 reply; 6+ messages in thread
From: Bruno Wolff III @ 2003-02-13 02:19 UTC (permalink / raw)
To: Nicholas Allen <nallen@freenet.co.uk>; +Cc: Dmitry Tkach <dmitry@openratings.com>; pgsql-sql
On Wed, Feb 12, 2003 at 23:21:18 +0100,
Nicholas Allen <nallen@freenet.co.uk> wrote:
> However, if I try to count the records using the count(*) function I get the following error.
>
> ERROR: Attribute vu_tbl_user_all_s.s_surname must be GROUPed or used in an aggregate function
>
> Surely, if postgres can execute the query it should also be able to count the rows that would be returned in the query? Is this a bug in Postgres and is there a work around? I am using Postgres 7.3.
If you are using count you probably don't want to be returning surnames, just
the count.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Possible bug in Postgres? Followup to "How do you select from a table until a condition is met?"
@ 2003-02-13 09:40 Nicholas Allen <nallen@freenet.co.uk>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 6+ messages in thread
From: Nicholas Allen @ 2003-02-13 09:40 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: Dmitry Tkach <dmitry@openratings.com>; pgsql-sql
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;
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.
Am I not missing something here. Is there something wrong with the syntax of
the above query?
Thjanks again for your help! I really appreciate it.
On Thursday 13 Feb 2003 3:19 am, Bruno Wolff III wrote:
> On Wed, Feb 12, 2003 at 23:21:18 +0100,
>
> Nicholas Allen <nallen@freenet.co.uk> wrote:
> > However, if I try to count the records using the count(*) function I get
> > the following error.
> >
> > ERROR: Attribute vu_tbl_user_all_s.s_surname must be GROUPed or used in
> > an aggregate function
> >
> > Surely, if postgres can execute the query it should also be able to count
> > the rows that would be returned in the query? Is this a bug in Postgres
> > and is there a work around? I am using Postgres 7.3.
>
> If you are using count you probably don't want to be returning surnames,
> just the count.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Possible bug in Postgres? Followup to "How do you select
@ 2003-02-13 09:58 Peter Childs <blue.dragon@blueyonder.co.uk>
parent: Nicholas Allen <nallen@freenet.co.uk>
0 siblings, 1 reply; 6+ messages in thread
From: Peter Childs @ 2003-02-13 09:58 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Possible bug in Postgres? Followup to "How do you select
@ 2003-02-13 10:04 Nicholas Allen <nallen@freenet.co.uk>
parent: Peter Childs <blue.dragon@blueyonder.co.uk>
0 siblings, 1 reply; 6+ messages in thread
From: Nicholas Allen @ 2003-02-13 10:04 UTC (permalink / raw)
To: Peter Childs <blue.dragon@blueyonder.co.uk>; +Cc: pgsql-sql
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?
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Possible bug in Postgres? Followup to "How do you select
@ 2003-02-13 10:25 Peter Childs <blue.dragon@blueyonder.co.uk>
parent: Nicholas Allen <nallen@freenet.co.uk>
0 siblings, 0 replies; 6+ messages in thread
From: Peter Childs @ 2003-02-13 10:25 UTC (permalink / raw)
To: Nicholas Allen <nallen@freenet.co.uk>; +Cc: 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 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
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-02-13 10:25 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-12 22:21 Possible bug in Postgres? Followup to "How do you select from a table until a condition is met?" Nicholas Allen <nallen@freenet.co.uk>
2003-02-13 02:19 ` Bruno Wolff III <bruno@wolff.to>
2003-02-13 09:40 ` Nicholas Allen <nallen@freenet.co.uk>
2003-02-13 09:58 ` Re: Possible bug in Postgres? Followup to "How do you select Peter Childs <blue.dragon@blueyonder.co.uk>
2003-02-13 10:04 ` Re: Possible bug in Postgres? Followup to "How do you select Nicholas Allen <nallen@freenet.co.uk>
2003-02-13 10:25 ` Re: Possible bug in Postgres? Followup to "How do you select Peter Childs <blue.dragon@blueyonder.co.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox