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
