Problem with sql - Mailing list pgsql-bugs

i have four same tables in postgres and oracle.I'm
using the postgres 7.1 version.
listed below

GROUPSMS=# select * from group_smu_trans ;
 sub_id |  con_phone   |
message                              |
--------+--------------+------------------------------------------------------------------+---------
      2 | 919810058237 | hi checking
                                   | 2001-05-01
      2 | 919810299102 | hi checking
                                   | 2001-05-01
      2 | 919810058237 | hi checking
                                   | 2001-05-01
      2 | 919810299102 | hi checking
                                   | 2001-05-01
      2 | 919810058237 | hi checking
                                   | 2001-05-01
      2 | 919810299102 | hi checking
                                   | 2001-05-01
      2 | 9810125422   | Kindly Check .                           |
2001-05-01
(7 rows)

GROUPSMS=# select * from subscriber_mast ;
 sub_id | sub_name  | sub_username |  sub_pwd  |
sub_phone |    sub_add     | sub_type | plan
--------+-----------+--------------+-----------+-----------+----------------+----------+------
      2 | veeren    | veeren       | veeren    |
8976548   | kljhjkhdslkjhf | o        | c
     10 | textarea1 | textarea1    | textarea1 |
textarea1 | textarea1      | o        | c
(2 rows)

GROUPSMS=# select * from sub_group_contact_lnk;
 sub_id | group_id | contact_id
--------+----------+------------
      2 |       18 |
      2 |       18 |         27
      2 |       21 |
      2 |       21 |         33
      2 |       21 |         37
(5 rows)

GROUPSMS=# select * from pre_sub_bal   ;
 sub_id | sub_total | sub_current | msg_rate
--------+-----------+-------------+----------
      3 |       350 |         200 |      125
      4 |       250 |         200 |       75
      7 |       500 |         500 |       75
      8 |       250 |         250 |       75
      1 |       250 |         125 |      125
      2 |       250 |      117.25 |      125
(6 rows)

create view mis_group_smu_trans
as
  select sub_id,count(message) as
tot_message,count(con_phone) as con_phone  from
group_smu_trans
        group by sub_id;
GROUPSMS=# select * from  mis_group_smu_trans  ;
 sub_id | tot_message | con_phone
--------+-------------+-----------
      2 |           7 |         7
(1 row)

create view mis_sub_group_contact
as
  select sub_id,count(distinct group_id) as
group_id,count(distinct contact_id) as contact_id from
 sub_group_contact_lnk
        group by sub_id;
GROUPSMS=# select * from  mis_sub_group_contact;
 sub_id | group_id | contact_id
--------+----------+------------
      2 |        2 |          3
(1 row)

create view test1 as
select
a.sub_id,a.sub_name,c.tot_message,c.con_phone,d.sub_total,d.sub_current
        from
                subscriber_mast a,
                mis_group_smu_trans c,
                pre_sub_bal d
        where
                a.sub_id=c.sub_id and
a.sub_id=d.sub_id;
GROUPSMS=# select * from test1;
 sub_id | sub_name | tot_message | con_phone |
sub_total | sub_current
--------+----------+-------------+-----------+-----------+-------------
      2 | veeren   |           7 |         7 |
250 |      117.25
(1 row)

create view test2 as
select
a.sub_id,b.group_id,b.contact_id
        from
                subscriber_mast a,
                mis_sub_group_contact b
        where
                a.sub_id=b.sub_id;

GROUPSMS=# select * from test2;
 sub_id | group_id | contact_id
--------+----------+------------
      2 |        2 |          3
(1 row)


select a.*,b.* from test1 a ,test2 b
where b.sub_id=a.sub_id;

GROUPSMS-# where b.sub_id=a.sub_id;
 sub_id | sub_name | tot_message | con_phone |
sub_total | sub_current | sub_id | group_id | contac
--------+----------+-------------+-----------+-----------+-------------+--------+----------+--------
      2 | veeren   |          35 |        35 |
250 |      117.25 |      2 |        2 |       3
(1 row)






       Now wht happen is in my final query of test1
and test2, "total_message" and "con_phone" both
the columns output is changed by 5 times which is a
random value.


   The same test case i have taken to the "oracle"
there i find the it is working absolutly fine.
Kindly handle this issue because one of major
application at my customor end is running.If find
problem in reading pls check the attachment file.




   Regards
    Shashi Bhushan Ahuja



__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35
a year!  http://personal.mail.yahoo.com/i have four same tables in postgres and oracle.I'm using the postgres 7.1
version.
listed below

GROUPSMS=# select * from group_smu_trans ;
 sub_id |  con_phone   |                             message                              |
--------+--------------+------------------------------------------------------------------+---------
      2 | 919810058237 | hi checking                                                      | 2001-05-01
      2 | 919810299102 | hi checking                                                      | 2001-05-01
      2 | 919810058237 | hi checking                                                      | 2001-05-01
      2 | 919810299102 | hi checking                                                      | 2001-05-01
      2 | 919810058237 | hi checking                                                      | 2001-05-01
      2 | 919810299102 | hi checking                                                      | 2001-05-01
      2 | 9810125422   | Kindly Check .                           | 2001-05-01
(7 rows)

GROUPSMS=# select * from subscriber_mast ;
 sub_id | sub_name  | sub_username |  sub_pwd  | sub_phone |    sub_add     | sub_type | plan
--------+-----------+--------------+-----------+-----------+----------------+----------+------
      2 | veeren    | veeren       | veeren    | 8976548   | kljhjkhdslkjhf | o        | c
     10 | textarea1 | textarea1    | textarea1 | textarea1 | textarea1      | o        | c
(2 rows)

GROUPSMS=# select * from sub_group_contact_lnk;
 sub_id | group_id | contact_id
--------+----------+------------
      2 |       18 |
      2 |       18 |         27
      2 |       21 |
      2 |       21 |         33
      2 |       21 |         37
(5 rows)

GROUPSMS=# select * from pre_sub_bal   ;
 sub_id | sub_total | sub_current | msg_rate
--------+-----------+-------------+----------
      3 |       350 |         200 |      125
      4 |       250 |         200 |       75
      7 |       500 |         500 |       75
      8 |       250 |         250 |       75
      1 |       250 |         125 |      125
      2 |       250 |      117.25 |      125
(6 rows)

create view mis_group_smu_trans
as
  select sub_id,count(message) as tot_message,count(con_phone) as con_phone  from
group_smu_trans
        group by sub_id;
GROUPSMS=# select * from  mis_group_smu_trans  ;
 sub_id | tot_message | con_phone
--------+-------------+-----------
      2 |           7 |         7
(1 row)

create view mis_sub_group_contact
as
  select sub_id,count(distinct group_id) as
group_id,count(distinct contact_id) as contact_id from
 sub_group_contact_lnk
        group by sub_id;
GROUPSMS=# select * from  mis_sub_group_contact;
 sub_id | group_id | contact_id
--------+----------+------------
      2 |        2 |          3
(1 row)

create view test1 as
select
a.sub_id,a.sub_name,c.tot_message,c.con_phone,d.sub_total,d.sub_current
        from
                subscriber_mast a,
                mis_group_smu_trans c,
                pre_sub_bal d
        where
                a.sub_id=c.sub_id and a.sub_id=d.sub_id;
GROUPSMS=# select * from test1;
 sub_id | sub_name | tot_message | con_phone | sub_total | sub_current
--------+----------+-------------+-----------+-----------+-------------
      2 | veeren   |           7 |         7 |       250 |      117.25
(1 row)

create view test2 as
select
a.sub_id,b.group_id,b.contact_id
        from
                subscriber_mast a,
                mis_sub_group_contact b
        where
                a.sub_id=b.sub_id;

GROUPSMS=# select * from test2;
 sub_id | group_id | contact_id
--------+----------+------------
      2 |        2 |          3
(1 row)


select a.*,b.* from test1 a ,test2 b
where b.sub_id=a.sub_id;

GROUPSMS-# where b.sub_id=a.sub_id;
 sub_id | sub_name | tot_message | con_phone | sub_total | sub_current | sub_id | group_id | contac
--------+----------+-------------+-----------+-----------+-------------+--------+----------+--------
      2 | veeren   |          35 |        35 |       250 |      117.25 |      2 |        2 |       3
(1 row)






       Now wht happen is in my final query of test1 and test2, "total_message" and "con_phone" both
the columns output is changed by 5 times which is a random value.


   The same test case i have taken to the "oracle"  there i find the it is working absolutly fine.
Kindly handle this issue because one of major application at my customor end is running.




   Regards
    Shashi Bhushan Ahuja