Is this a bug?

I'm going to guess you're using 7.0, right?
This should work under 7.1 (or at least your test case
does), but there were serious problems with views containing
group by in earlier versions.

On Tue, 27 Feb 2001, Limin Liu wrote:

Limin Liu wrote:

Hi,

Can anyone please tell me whether this is a bug or not?

I used the following SQL to create my table and view with some data.

=======================
create table tbl (no int, cnt int);
create view tbl_view as
select no, count(cnt) as count
from tbl
group by no
having count(cnt) > 2;
insert into tbl values(1, 1);
insert into tbl values(1, 2);
insert into tbl values(1, 3);
insert into tbl values(1, 4);
insert into tbl values(2, 1);
insert into tbl values(2, 2);
insert into tbl values(2, 3);
=======================

Here is soem SQL I issued; first one looks fine to me, but the second
one is weird.

=======================
db=> select * from tbl_view;
no | count
----+-------
1 | 4
2 | 3
(2 rows)

db=> select * from tbl_view where count > 5;
no | count
----+-------
1 | 4
2 | 3
(2 rows)

db2=>
=======================

Thanx

LM.Liu

I get ERROR: Rewrite: Aggregate of views only allowed on single tables
for now

I think it may have something to do with the reserved sql keyword
'count' being used.

Ian