Thread: Is this a bug?

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'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:

> 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)

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