pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDebugging postmaster to fix possible bug in Postgres? Followup to "How do you select
9+ messages / 5 participants
[nested] [flat]
* Debugging postmaster to fix possible bug in Postgres? Followup to "How do you select
@ 2003-02-13 10:39 Nicholas Allen <nallen@freenet.co.uk>
0 siblings, 1 reply; 9+ messages in thread
From: Nicholas Allen @ 2003-02-13 10:39 UTC (permalink / raw)
To: Peter Childs <blue.dragon@blueyonder.co.uk>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Debugging postmaster to fix possible bug in Postgres? Followup to "How do you select
@ 2003-02-13 15:17 Tom Lane <tgl@sss.pgh.pa.us>
parent: Nicholas Allen <nallen@freenet.co.uk>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2003-02-13 15:17 UTC (permalink / raw)
To: Nicholas Allen <nallen@freenet.co.uk>; +Cc: Peter Childs <blue.dragon@blueyonder.co.uk>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Debugging postmaster to fix possible bug in Postgres? Followup to "How do you select
@ 2003-02-13 17:28 Nicholas Allen <nallen@freenet.co.uk>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 9+ messages in thread
From: Nicholas Allen @ 2003-02-13 17:28 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Peter Childs <blue.dragon@blueyonder.co.uk>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Debugging postmaster to fix possible bug in Postgres?
@ 2003-02-13 19:27 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Nicholas Allen <nallen@freenet.co.uk>
1 sibling, 0 replies; 9+ messages in thread
From: Stephan Szabo @ 2003-02-13 19:27 UTC (permalink / raw)
To: Nicholas Allen <nallen@freenet.co.uk>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Peter Childs <blue.dragon@blueyonder.co.uk>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Debugging postmaster to fix possible bug in Postgres? Followup to "How do you select
@ 2003-02-13 19:39 Manfred Koizar <mkoi-pg@aon.at>
parent: Nicholas Allen <nallen@freenet.co.uk>
1 sibling, 2 replies; 9+ messages in thread
From: Manfred Koizar @ 2003-02-13 19:39 UTC (permalink / raw)
To: Nicholas Allen <nallen@freenet.co.uk>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Peter Childs <blue.dragon@blueyonder.co.uk>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Debugging postmaster to fix possible bug in Postgres? Followup to "How do you select
@ 2003-02-13 20:51 Nicholas Allen <nallen@freenet.co.uk>
parent: Manfred Koizar <mkoi-pg@aon.at>
1 sibling, 0 replies; 9+ messages in thread
From: Nicholas Allen @ 2003-02-13 20:51 UTC (permalink / raw)
To: Manfred Koizar <mkoi-pg@aon.at>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Peter Childs <blue.dragon@blueyonder.co.uk>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Debugging postmaster to fix possible bug in Postgres? Followup
@ 2003-02-13 22:50 Dmitry Tkach <dmitry@openratings.com>
parent: Manfred Koizar <mkoi-pg@aon.at>
1 sibling, 2 replies; 9+ messages in thread
From: Dmitry Tkach @ 2003-02-13 22:50 UTC (permalink / raw)
To: Manfred Koizar <mkoi-pg@aon.at>
>
> 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 discussing how to fix it :-)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Debugging postmaster to fix possible bug in Postgres? Followup
@ 2003-02-13 23:01 Manfred Koizar <mkoi-pg@aon.at>
parent: Dmitry Tkach <dmitry@openratings.com>
1 sibling, 0 replies; 9+ messages in thread
From: Manfred Koizar @ 2003-02-13 23:01 UTC (permalink / raw)
To: Dmitry Tkach <dmitry@openratings.com>; +Cc: pgsql-sql
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.
Servus
Manfred
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Debugging postmaster to fix possible bug in Postgres? Followup
@ 2003-02-13 23:48 Tom Lane <tgl@sss.pgh.pa.us>
parent: Dmitry Tkach <dmitry@openratings.com>
1 sibling, 0 replies; 9+ messages in thread
From: Tom Lane @ 2003-02-13 23:48 UTC (permalink / raw)
To: Dmitry Tkach <dmitry@openratings.com>; +Cc: Manfred Koizar <mkoi-pg@aon.at>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2003-02-13 23:48 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-13 10:39 Debugging postmaster to fix possible bug in Postgres? Followup to "How do you select Nicholas Allen <nallen@freenet.co.uk>
2003-02-13 15:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-13 17:28 ` Nicholas Allen <nallen@freenet.co.uk>
2003-02-13 19:27 ` Re: Debugging postmaster to fix possible bug in Postgres? Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-02-13 19:39 ` Manfred Koizar <mkoi-pg@aon.at>
2003-02-13 20:51 ` Nicholas Allen <nallen@freenet.co.uk>
2003-02-13 22:50 ` Re: Debugging postmaster to fix possible bug in Postgres? Followup Dmitry Tkach <dmitry@openratings.com>
2003-02-13 23:01 ` Re: Debugging postmaster to fix possible bug in Postgres? Followup Manfred Koizar <mkoi-pg@aon.at>
2003-02-13 23:48 ` Re: Debugging postmaster to fix possible bug in Postgres? Followup Tom Lane <tgl@sss.pgh.pa.us>
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