Problem with group by in conjuction with Views

This seems to work for me. I used the snapshot from 3/28 on Solaris 8

SELECT service, count(*) AS GebruikersAantal
FROM tbtrouble GROUP BY service;
service | gebruikersaantal
-----------+------------------
Service 1 | 2
Service 3 | 2
Service 4 | 1
(3 rows)

SELECT service, count(*) AS GebruikersAantal
FROM vwtrouble GROUP BY service;
service | gebruikersaantal
-----------+------------------
Service 1 | 2
Service 3 | 2
Service 4 | 1
(3 rows)

This message is in MIME format. Since your mail reader does not

understand

this format, some or all of this message may not be legible.

Hi there,

I have found a small but annoying bug. I have created a view. The
SQL-statement in the view contains a GROUP BY statement. Then I

compose a

SQL-statement using this view and another GROUP BY statement and a

COUNT(*)

statement. The count(*) statement doesn't count the amount of grouped
record's of the view, but it count's the amount of grouped records of

the

GROUP BY in the view and of the GROUP BY in the select statement. It

counts

all the records grouped instead of only the records grouped from the

view.

This is wrong (IMHO). When I use a temporary table instead of a view

all

things work OK. IMHO views shouldn't differ from temporary tables.

To make things a bit more clear I have add an SQL-attachment. You can

run

the attachment in an empty database form psql to have a look at the

problem.

I sometimes get another <<database.sql>> error too while executing

these

group statements: 'My bit's blew right of the end of the world'. (This

is

Jeroen Eitjes <Eitjes@WalRas.nl> writes:

I have found a small but annoying bug. I have created a view. The
SQL-statement in the view contains a GROUP BY statement. Then I compose a
SQL-statement using this view and another GROUP BY statement and a COUNT(*)
statement.

This sort of thing will not work at all in Postgres releases earlier
than 7.1. Sorry.

regards, tom lane