Thread: Bug #427: joining views

Vladimir Bezak  (bezak@isdd.sk) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
joining views

Long Description
 PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66

 wrong results when joining views.

Sample Code

table ORDER_ITEM

 ID    STATE   ORDER_ID
----  ------ ----------
 1        1          1
 2        1          1
 3        2          1
 4        1          2
 5        2          2

Now i will create two views:

P1V:
select order_id as id1, count(*) as count1 from order_item where state=1 group by order_id;

P2V:
select order_id as id2, count(*) as count2 from order_item where state=2 group by order_id;

----------------------------
SQL> select * from p1v;

       ID1     COUNT1
---------- ----------
         1          2
         2          1

SQL> select * from p2v;

       ID2     COUNT2
---------- ----------
         1          1
         2          1

Here is the problem:

SQL> select id1, count1, count2 from p1v, p2v where id1 = id2;

       ID1     COUNT1     COUNT2
---------- ---------- ----------
         1          2          2
         2          1          1

there shouldn't be any 2's in column COUNT2 ....

In other database systems it works ok.




No file was uploaded with this report

>  PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66
>
>  wrong results when joining views.

Upgrade to the latest version (7.1.3).

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

pgsql-bugs@postgresql.org writes:
> Now i will create two views:

> P1V:
> select order_id as id1, count(*) as count1 from order_item where state=1 group by order_id;

> P2V:
> select order_id as id2, count(*) as count2 from order_item where state=2 group by order_id;

Grouped views don't work very well in Postgres versions before 7.1.
Time to update ... 7.1.3 is the current release.

            regards, tom lane