Thread: Bug #731: Same SQL produces different results before and after VACUUM FULL

Henry  (henry@ssc-ltd.com) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
Same SQL produces different results before and after VACUUM FULL

Long Description
We have run the SQL statement below before and after VACUUM FULL.
Before VACUUM FULL, it returns 3615 records.
After VACUUM FULL, it return 34123 records which is the result I expect.
Before VACUUM FULL, if I change the LEFT OUTER JOIN to join the table a, it returns 34123 records.

I have a daily backup job which executes VACUUM ANALYSE daily.

I don't know what causes the problem.  Please help!!!!


Sample Code

        SELECT *
        from (SELECT pbdedup_party_1.party_id
              from pbdedup_party_1
              where pbdedup_party_1.batch_no = ?
              UNION
              SELECT pbdedup_party_2.party_id
              from pbdedup_party_2
              where pbdedup_party_2.batch_no = ?
              ) as a
           , pb_person
                left outer join pbdedup_person_parts ON pb_person.party_id = pbdedup_person_parts.party_id
        where a.party_id = pb_person.party_id



No file was uploaded with this report

pgsql-bugs@postgresql.org writes:
> Same SQL produces different results before and after VACUUM FULL

What Postgres version is this?

What does EXPLAIN show for the query plan in each state?

            regards, tom lane

Dear Tom,

Our Postgresql version is 7.2. We have not upgraded to 7.2.1 yet.

I cannot simulate what happened that time.  But however, during that
time, we have also tried the 2 SQL in the attached file but also
produced different results before VACUUM FULL.

(I expect them to produce same result)

However, they produced same result after VACUUM FULL.

I hope this could help your investigation.

Thanks.

Regards,
Henry





-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, August 07, 2002 9:46 PM
To: henry@ssc-ltd.com; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] Bug #731: Same SQL produces different results before
and after VACUUM FULL

pgsql-bugs@postgresql.org writes:
> Same SQL produces different results before and after VACUUM FULL

What Postgres version is this?

What does EXPLAIN show for the query plan in each state?

            regards, tom lane