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