Thread: count(*) from view??

create table test (id int, user_name char(30));
insert into test values (1, 'John');
insert into test values (2, 'George');

create view v_test as select * from test where user_name = 'John';

select count(*) from v_test;
 count
-------
     2
(1 row)      Why?...Should returns 1

select count(*) from test where user_name = 'John';
 count
-------
     1
(1 row)

--Greg--

Greg Brzezinski (gregb@amg.gda.pl) napisał:
> create table test (id int, user_name char(30));
> insert into test values (1, 'John');
> insert into test values (2, 'George');
> create view v_test as select * from test where user_name = 'John';
> select count(*) from v_test;
>  count
> -------
>      2
> (1 row)      Why?...Should returns 1
I've noticed the same.

Marcin Mazurek

--
Kierownik Działu Systemowego
MULTINET SA o/Poznan
http://www.multinet.pl/

Greg Brzezinski <gregb@amg.gda.pl> writes:
> create table test (id int, user_name char(30));
> insert into test values (1, 'John');
> insert into test values (2, 'George');

> create view v_test as select * from test where user_name = 'John';

> select count(*) from v_test;
>  count
> -------
>      2
> (1 row)      Why?...Should returns 1

Yup, it should, and 1 is indeed what I get.  How old a version of
Postgres are you using?  I think there used to be bugs like this
a few versions back...

            regards, tom lane

What version are you using?  Current sources
give me 1 for the select count(*) from v_test;
on the same sequence.

Stephan Szabo
sszabo@bigpanda.com

On Fri, 8 Sep 2000, Greg Brzezinski wrote:

> create table test (id int, user_name char(30));
> insert into test values (1, 'John');
> insert into test values (2, 'George');
>
> create view v_test as select * from test where user_name = 'John';
>
> select count(*) from v_test;
>  count
> -------
>      2
> (1 row)      Why?...Should returns 1
>
> select count(*) from test where user_name = 'John';
>  count
> -------
>      1
> (1 row)

Some time ago, I also had similar problems involving sums and maxes within
a view. When I posted a query about the max problem, the response
was that "The rule rewriter has a lot of problems with views that involve
grouping
or aggregation --- until it's fixed, you need to be very wary of that
combination of features".

(Full reply at
http://www.postgresql.org/mhonarc/pgsql-bugs/1999-11/msg00009.html)

Now I'm not sure what the current state of development in that regard is,
but judging
from your experience, it looks like there's still a problem.

Not much help I'm sure, but there you go.

Sean.


> -----Original Message-----
> From: pgsql-general-owner@hub.org [mailto:pgsql-general-owner@hub.org]On
> Behalf Of Greg Brzezinski
> Sent: Friday, 8 September 2000 11:32 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] count(*) from view??
>
>
> create table test (id int, user_name char(30));
> insert into test values (1, 'John');
> insert into test values (2, 'George');
>
> create view v_test as select * from test where user_name = 'John';
>
> select count(*) from v_test;
>  count
> -------
>      2
> (1 row)      Why?...Should returns 1
>
> select count(*) from test where user_name = 'John';
>  count
> -------
>      1
> (1 row)
>
> --Greg--
>
>

I've now tested your problem on the 7.0.2 and the problem doesn't
seem to arise there, so it looks as though the problems with the
rule rewriter have been fixed. Maybe you should try upgrading.

Regards,
Sean.

> -----Original Message-----
> From: pgsql-general-owner@hub.org [mailto:pgsql-general-owner@hub.org]On
> Behalf Of Greg Brzezinski
> Sent: Friday, 8 September 2000 11:32 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] count(*) from view??
>
>
> create table test (id int, user_name char(30));
> insert into test values (1, 'John');
> insert into test values (2, 'George');
>
> create view v_test as select * from test where user_name = 'John';
>
> select count(*) from v_test;
>  count
> -------
>      2
> (1 row)      Why?...Should returns 1
>
> select count(*) from test where user_name = 'John';
>  count
> -------
>      1
> (1 row)
>
> --Greg--
>
>