Postgres Pro
Downloads
Home   >   mailing lists

Thread: How do you select from a table until a condition is met?

How do you select from a table until a condition is met?

From
Nicholas Allen
Date:
Hi,

I am hoping that someone can help me with a problem I have. I would like to be
able to perform a SELECT query to select values from a table upto a certain
row. The select may be ordered on a number of items. Is it possible to do
this and how should I do it?

The reason I need this is because I have created a virtual table browser class
that performs SQL queries to get the sections of the table the user is
interested in. This way I don't load the whole table onto the client side. If
the user changes the sort ordering I want to determine the row index that was
previously selected and scroll to that location.

Any help would be very much appreciated.

Thanks in advance,

Nicholas Allen

Re: How do you select from a table until a condition is met?

From
Dmitry Tkach
Date:
select * from table order by x limit 10; -- get first 10
select * from table order by x offset 10 limit 10; -- get 10 - 20
select * from table order by x offset 40 limit 10; -- get 40-50

OR

begin;
declare mycursor cursor for select * from table order by x;
fetch 10 from mycursor; -- get first 10
fetch 10 from mycursor; -- get 10-20
move 20 in mycursor;
fetch 10 from mycursor; -- get 40-50



Is that what you are talking about?

Dima


Nicholas Allen wrote:
> Hi,
> 
> I am hoping that someone can help me with a problem I have. I would like to=
>  be=20
> able to perform a SELECT query to select values from a table upto a certain=
> =20
> row. The select may be ordered on a number of items. Is it possible to do=
> =20
> this and how should I do it?
> 
> The reason I need this is because I have created a virtual table browser cl=
> ass=20
> that performs SQL queries to get the sections of the table the user is=20
> interested in. This way I don't load the whole table onto the client side. =
> If=20
> the user changes the sort ordering I want to determine the row index that w=
> as=20
> previously selected and scroll to that location.
> 
> Any help would be very much appreciated.
> 
> Thanks in advance,
> 
> Nicholas Allen
> 
> ---------------------------(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

Re: How do you select from a table until a condition is met?

From
Nicholas Allen
Date:
Thanks, but I don't think thats what I want. Basically I need to get the row
offset of a record (ie row) in a result set returned from the database. I
know all the details of the row in question but when the search order changes
I need to know the new index the row will have.

In other words, I was hoping I would be able to select upto a particular row
(not knowing its offset but knowing everything else about it) in a select
query.

Ideally I could do something like SELECT count(*) UNTIL col1=value1 &&
col2=val2 etc....

Of course there is no UNTIL in a select statement just a WHERE clause.

I know I didn't explain that very well!

Well I hope you can still help?
On Wednesday 12 Feb 2003 9:39 pm, Dmitry Tkach wrote:
> select * from table order by x limit 10; -- get first 10
> select * from table order by x offset 10 limit 10; -- get 10 - 20
> select * from table order by x offset 40 limit 10; -- get 40-50
>
> OR
>
> begin;
> declare mycursor cursor for select * from table order by x;
> fetch 10 from mycursor; -- get first 10
> fetch 10 from mycursor; -- get 10-20
> move 20 in mycursor;
> fetch 10 from mycursor; -- get 40-50
>
>
>
> Is that what you are talking about?
>
> Dima
>
> Nicholas Allen wrote:
> > Hi,
> >
> > I am hoping that someone can help me with a problem I have. I would like
> > to= be=20
> > able to perform a SELECT query to select values from a table upto a
> > certain= =20
> > row. The select may be ordered on a number of items. Is it possible to
> > do= =20
> > this and how should I do it?
> >
> > The reason I need this is because I have created a virtual table browser
> > cl= ass=20
> > that performs SQL queries to get the sections of the table the user is=20
> > interested in. This way I don't load the whole table onto the client
> > side. = If=20
> > the user changes the sort ordering I want to determine the row index that
> > w= as=20
> > previously selected and scroll to that location.
> >
> > Any help would be very much appreciated.
> >
> > Thanks in advance,
> >
> > Nicholas Allen
> >
> > ---------------------------(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

Re: How do you select from a table until a condition is met?

From
Dmitry Tkach
Date:
Nicholas Allen wrote:

>Thanks, but I don't think thats what I want. Basically I need to get the row 
>offset of a record (ie row) in a result set returned from the database. I 
>know all the details of the row in question but when the search order changes 
>I need to know the new index the row will have. 
>
>In other words, I was hoping I would be able to select upto a particular row 
>(not knowing its offset but knowing everything else about it) in a select 
>query. 
>
>Ideally I could do something like SELECT count(*) UNTIL col1=value1 && 
>col2=val2 etc....
>
>Of course there is no UNTIL in a select statement just a WHERE clause.
>
>I know I didn't explain that very well!
>
>Well I hope you can still help?
>
I still don't understand what it is you are trying to do...

Perhaps, an example would help... Suppose you have a table like this:

create table people
(   id serial primary key,   first_name text,   last_name   text
);


Now,  suppose, the user gets the list of people, ordered by the 
last_name, scrolls through, say, 100 of them, and then switches the 
order to sort by the first_name...
Are you saying that in this case you want to start with the 101st entry 
in the first name order?
It makes little sense to me (the entries you are going to skip have 
nothing to do with the ones the user already saw - so, I can't imagine 
the reason not to just start
with the first entry, using the new sort order)...
And you can still do it with the offsets:

select * from people order by last_name limit 10;
...
select * from people order by last_name offset 90 limit 10;
...
select * from people order by first_name offset 100 limit 10;

This will still work... But I doubt that's what you want... I must be 
missing something...

Dima

Re: How do you select from a table until a condition is met?

From
Bruno Wolff III
Date:
On Wed, Feb 12, 2003 at 20:10:15 +0100, Nicholas Allen <nallen@freenet.co.uk> wrote:
> 
> Thanks, but I don't think thats what I want. Basically I need to get the row 
> offset of a record (ie row) in a result set returned from the database. I 
> know all the details of the row in question but when the search order changes 
> I need to know the new index the row will have. 
> 
> In other words, I was hoping I would be able to select upto a particular row 
> (not knowing its offset but knowing everything else about it) in a select 
> query. 
> 
> Ideally I could do something like SELECT count(*) UNTIL col1=value1 && 
> col2=val2 etc....

Since you are ordering the data you can use where clauses with < (and/or >)
operators to stop counting at the desired row. For this to work the ordering
needs to be well defined. You can tack on the primary key to your list
of columns to order by to make the ordering well defined if it isn't
already.

Re: How do you select from a table until a condition is met?

From
Nicholas Allen
Date:
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.

eg where surname <= 'Jones' could return hundreds of records and probably some
records for people with the same surname but which appear after the selected
record.

I had another idea though. Do you know if it is possible to write a function
for postgresql thatwill return true until a condition is met and then for
every row after that return false? This way I could write a function that
could select up to a particular row using the where clause and calling the
function from the where claus.

I'm sure there must be a way of doing this as it is such an obvious thing to
need to do...

Thanks for the help!

On Wednesday 12 Feb 2003 8:35 pm, Bruno Wolff III wrote:
> On Wed, Feb 12, 2003 at 20:10:15 +0100,
>
>   Nicholas Allen <nallen@freenet.co.uk> wrote:
> > Thanks, but I don't think thats what I want. Basically I need to get the
> > row offset of a record (ie row) in a result set returned from the
> > database. I know all the details of the row in question but when the
> > search order changes I need to know the new index the row will have.
> >
> > In other words, I was hoping I would be able to select upto a particular
> > row (not knowing its offset but knowing everything else about it) in a
> > select query.
> >
> > Ideally I could do something like SELECT count(*) UNTIL col1=value1 &&
> > col2=val2 etc....
>
> Since you are ordering the data you can use where clauses with < (and/or >)
> operators to stop counting at the desired row. For this to work the
> ordering needs to be well defined. You can tack on the primary key to your
> list of columns to order by to make the ordering well defined if it isn't
> already.

Re: How do you select from a table until a condition is met?

From
Nicholas Allen
Date:
On Wednesday 12 Feb 2003 9:59 pm, Dmitry Tkach wrote:
> Nicholas Allen wrote:
> >Thanks, but I don't think thats what I want. Basically I need to get the
> > row offset of a record (ie row) in a result set returned from the
> > database. I know all the details of the row in question but when the
> > search order changes I need to know the new index the row will have.
> >
> >In other words, I was hoping I would be able to select upto a particular
> > row (not knowing its offset but knowing everything else about it) in a
> > select query.
> >
> >Ideally I could do something like SELECT count(*) UNTIL col1=value1 &&
> >col2=val2 etc....
> >
> >Of course there is no UNTIL in a select statement just a WHERE clause.
> >
> >I know I didn't explain that very well!
> >
> >Well I hope you can still help?
>
> I still don't understand what it is you are trying to do...
>
> Perhaps, an example would help... Suppose you have a table like this:
>
> create table people
> (
>     id serial primary key,
>     first_name text,
>     last_name   text
> );
>
>
> Now,  suppose, the user gets the list of people, ordered by the
> last_name, scrolls through, say, 100 of them, and then switches the
> order to sort by the first_name...
> Are you saying that in this case you want to start with the 101st entry
> in the first name order?

No. What I'm saying is the user scrolls through a list then selects an item.
They then decide to sort on something else but they still want the item they
selected to be selected after the sort has completed. So I need some way to
work out where the previously selected item will be in the new sort order so
I can scroll to that position and select it again.

eg If user selects "John Doe" when sorted by First name and then decides to
sort by Surname I still want John Doe selected and the user should be able to
see the record.

> It makes little sense to me (the entries you are going to skip have
> nothing to do with the ones the user already saw - so, I can't imagine
> the reason not to just start
> with the first entry, using the new sort order)...
> And you can still do it with the offsets:
>
> select * from people order by last_name limit 10;
> ...
> select * from people order by last_name offset 90 limit 10;
> ...
> select * from people order by first_name offset 100 limit 10;
>
> This will still work... But I doubt that's what you want... I must be
> missing something...
>
> Dima
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

Re: How do you select from a table until a condition is met?

From
Bruno Wolff III
Date:
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.

Re: How do you select from a table until a condition is met?

From
Dmitry Tkach
Date:
>
>
>No. What I'm saying is the user scrolls through a list then selects an item. 
>They then decide to sort on something else but they still want the item they 
>selected to be selected after the sort has completed. So I need some way to 
>work out where the previously selected item will be in the new sort order so 
>I can scroll to that position and select it again.
>
>eg If user selects "John Doe" when sorted by First name and then decides to 
>sort by Surname I still want John Doe selected and the user should be able to 
>see the record.
>  
>
What do you mean by 'select the item'? Get all the rows before it?
This still doesn't make much sense to me - you said before, that the 
whole purpose of your 'browser' was to avoid sending tons of data to the 
client side,  but if I understand correctly what you are thinking about 
doing, it won't work, because you do not know how many rows are before 
the 'selected' item. Am I still missing something?

Anyway, what about this:

select * from people order by last_name, id limit 100; -- get first 100, 
ordered by the last_name

select * from people where first_name < 'John' or first_name = 'John' 
and last_name <= 'Doe'  and id <= "JohnDoe's ID" order by first_name, id;
-- get everything before the last seen John Doe ordered by the first name

select * from people where first_name > 'John'  or first_name = 'John' 
and  last_name >= 'Doe'  and id >= "JohDoe's ID" order by first_name, id 
limit 100;
-- get 100 rows after the selected one sorted by first name

etc...

Still can't imagine how this can be useful though :-(
Dima

Re: How do you select from a table until a condition is met?

From
Nicholas Allen
Date:
Ok I thought of that but what happens if there is no primary key in the table?
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.

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.org

Re: How do you select from a table until a condition is met?

From
Dmitry Tkach
Date:
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
>  
>

Possible bug in Postgres? Followup to "How do you select from a table until a condition is met?"

From
Nicholas Allen
Date:
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
definea row I should be able to select up to a known row with a clever where clause. For example a table contains the
followingdummy 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?
Isthis 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

Re: Possible bug in Postgres? Followup to "How do you select from a table until a condition is met?"

From
Bruno Wolff III
Date:
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.

Re: Possible bug in Postgres? Followup to "How do you select from a table until a condition is met?"

From
Nicholas Allen
Date:
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

Re: Possible bug in Postgres? Followup to "How do you select

From
Peter Childs
Date:
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

Re: Possible bug in Postgres? Followup to "How do you select

From
Nicholas Allen
Date:
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?

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

Re: Possible bug in Postgres? Followup to "How do you select

From
Peter Childs
Date:
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
> 
>

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

From
Nicholas Allen
Date:
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

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

From
Tom Lane
Date:
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

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

From
Nicholas Allen
Date:
Because the WHERE clause is directly affected by the ORDER BY clause. If you
leave out the order by clause then the row count will be completely different
and therefore wrong. The ORDER BY clause is just as important as the WHERE
clause when counting rows.  It should be possible to get a count for the rows
for any query that can be done which can return row data as I understand it.


I have tried to find a definition for SQL SELECT command but everywhere I have
looked so far makes no mention of this being invalid SQL syntax. Can you let
me know where you got this information?


Thanks,



On Thursday 13 Feb 2003 4:17 pm, Tom Lane wrote:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

Re: Debugging postmaster to fix possible bug in Postgres?

From
Stephan Szabo
Date:
On Thu, 13 Feb 2003, Nicholas Allen wrote:

> Because the WHERE clause is directly affected by the ORDER BY clause. If you
> leave out the order by clause then the row count will be completely different
> and therefore wrong. The ORDER BY clause is just as important as the WHERE
> clause when counting rows.  It should be possible to get a count for the rows
> for any query that can be done which can return row data as I understand it.

WHERE effectively occurs before ORDER BY, so unless it's an order by in a
subselect I don't think the where clause can be affected by the ORDER BY
legally.

select count(*) from foo where a<3 order by a;
and
select count(*) from foo where a<3;
must give the same results AFAICT.

> I have tried to find a definition for SQL SELECT command but everywhere I have
> looked so far makes no mention of this being invalid SQL syntax. Can you let
> me know where you got this information?

I think this may follow from the syntax rules of the general select from
a strict reading of SQL92 20.2 and 9.7 (which implies to me that any
form of select a from table order by b; would be an extension) or a looser
reading of syntax rule 7 in 9.7 which disallows value expressions
referencing a column that wasn't specified in a set function specification
for non-grouped tables.

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

From
Manfred Koizar
Date:
On Thu, 13 Feb 2003 18:28:50 +0100, Nicholas Allen
<nallen@freenet.co.uk> wrote:
>Because the WHERE clause is directly affected by the ORDER BY clause.

No, it's not (at least in your query).

> If you 
>leave out the order by clause then the row count will be completely different 
>and therefore wrong.

I must be missing something.  Please give an example.

> The ORDER BY clause is just as important as the WHERE 
>clause when counting rows.  It should be possible to get a count for the rows 
>for any query that can be done which can return row data as I understand it.

If you have a set of numbers, say {1, 9, 5, 3, 7}, and want to know
how many elements of the set are <= 7 (SELECT COUNT(*) FROM s WHERE
n<=7), you simply look a each element - no matter in what order - and
increase your counter, if the element satifies your condition.  I
can't see how you get different numbers when you count {1, 3, 5, 7},
{1, 5, 3, 7} or any other permutation.

>I have tried to find a definition for SQL SELECT command but everywhere I have 
>looked so far makes no mention of this being invalid SQL syntax. Can you let 
>me know where you got this information?

SQL92 says:   <direct select statement: multiple rows> ::=        <query expression> [ <order by clause> ]   [...]   3)
LetT be the table specified by the <query expression>.
 
   4) If ORDER BY is specified, then each <sort specification> in the      <order by clause> shall identify a column of
T.

ServusManfred

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

From
Nicholas Allen
Date:
Ooops! Appologies to all for being sooo stupid!

I thought the order by was applied before the WHERE and if ordering in DESC
order for example < would mean greater than and so on in the where clause
because I assumed < meant it would appear before in the returned order.

Of course the where just selects which values and the order by orders them!

Don't know how I even got confused in the first place. So there's no bug in
POstgres and I just wasted a day being dumb (guess I should get more than 2
hours sleep before tackling this kinda stuff!).

Thanks all,

On Thursday 13 Feb 2003 8:39 pm, Manfred Koizar wrote:
> On Thu, 13 Feb 2003 18:28:50 +0100, Nicholas Allen
>
> <nallen@freenet.co.uk> wrote:
> >Because the WHERE clause is directly affected by the ORDER BY clause.
>
> No, it's not (at least in your query).
>
> > If you
> >leave out the order by clause then the row count will be completely
> > different and therefore wrong.
>
> I must be missing something.  Please give an example.
>
> > The ORDER BY clause is just as important as the WHERE
> >clause when counting rows.  It should be possible to get a count for the
> > rows for any query that can be done which can return row data as I
> > understand it.
>
> If you have a set of numbers, say {1, 9, 5, 3, 7}, and want to know
> how many elements of the set are <= 7 (SELECT COUNT(*) FROM s WHERE
> n<=7), you simply look a each element - no matter in what order - and
> increase your counter, if the element satifies your condition.  I
> can't see how you get different numbers when you count {1, 3, 5, 7},
> {1, 5, 3, 7} or any other permutation.
>
> >I have tried to find a definition for SQL SELECT command but everywhere I
> > have looked so far makes no mention of this being invalid SQL syntax. Can
> > you let me know where you got this information?
>
> SQL92 says:
>     <direct select statement: multiple rows> ::=
>          <query expression> [ <order by clause> ]
>     [...]
>     3) Let T be the table specified by the <query expression>.
>
>     4) If ORDER BY is specified, then each <sort specification> in the
>        <order by clause> shall identify a column of T.
>
> Servus
>  Manfred

Re: Debugging postmaster to fix possible bug in Postgres? Followup

From
Dmitry Tkach
Date:
> 
> SQL92 says:
>     <direct select statement: multiple rows> ::=
>          <query expression> [ <order by clause> ]
>     [...]
>     3) Let T be the table specified by the <query expression>.
> 
>     4) If ORDER BY is specified, then each <sort specification> in the
>        <order by clause> shall identify a column of T.
> 

Then it looks like postgres behaviour is still not compliant, if I read it correctly, because

select x from mytable order by y;

should be invalid according to this, but works just fine in postres.

Dima.

P.S. I think, this is a great feature actually (missed it a lot in informix), so, I hope, you guys won't start
discussinghow to fix it :-)

Re: Debugging postmaster to fix possible bug in Postgres? Followup

From
Manfred Koizar
Date:
On Thu, 13 Feb 2003 17:50:22 -0500, Dmitry Tkach
<dmitry@openratings.com> wrote:
>Then it looks like postgres behaviour is still not compliant, if I read it correctly, because
>
>select x from mytable order by y;
>
>should be invalid according to this, but works just fine in postres.

Yes, this is a Postgres extension to the standard.  Your query is
handled like
SELECT x, y FROM mytable ORDER BY y

with y being eliminated after the sort step.  This also explains why
the OP got the error message

ERROR:  Attribute t.y must be GROUPed or used in an aggregate function

because the implicitely rewritten form would look like
SELECT COUNT(*), y FROM t WHERE ... ORDER BY y

>P.S. I think, this is a great feature actually (missed it a lot in informix),
> so, I hope, you guys won't start discussing how to fix it :-)

AFAICT there's no need to worry.  Everyone agrees that this is a good
feature and it does not break standard SQL queries.

ServusManfred

Re: Debugging postmaster to fix possible bug in Postgres? Followup

From
Tom Lane
Date:
Dmitry Tkach <dmitry@openratings.com> writes:
> Then it looks like postgres behaviour is still not compliant, if I read it correctly, because
> select x from mytable order by y;
> should be invalid according to this, but works just fine in postres.

Yup, it's an extension --- as indeed is pointed out at the bottom of our
SELECT reference page.  But it's a well-defined extension, because every
row of the result does have a clearly associated value of y.  Once you
throw in GROUP BY or aggregates, you can't order by values that aren't
constrained by the grouping.
        regards, tom lane