query returns incorrect results.

Hello,

I've run into a really strange problem that's taken a while for
me to track down, but I think I have enough information here
for people to verify what I'm saying and hopefully enough information
to point someone in the right direction for fixing.

My basic problem is that several queries are returning less results
than expected, more results than expected, or no results at all. I've
been able to create a test case that causes the failure which
produces no results. All of the failures involve this basic type of
query:

select * from foo where
exists (select * from bar1..barN where join1..joinN)

I have attached a test database with enough for me to reproduce a
failure all of the time. I'm running RedHat-7.0/i686 and PG-7.0.2
I've also tested this on RedHat-6.2

To reproduce the failure:

1) create a test db
2) load database from attachment
3) ---> VACUUM ANALYZE <----
4) check plan; run query --> you get 0 rows
5) drop index c_key
6) check plan; run query --> you get 4 rows

You MUST VACUUM before running the query otherwise a different plan
will be choosen and the problem won't be observed.

QUERY that fails:
select g.id from g where exists ( select * from a, b, c, c_c, j
where a.v = 2 and a.id = g.id and substr(b.v,1,4) = '1990' and
b.id = g.id and c.v = c_c.id and c_c.v = 1 and c.v = 2 and
c.id = g.id and j.v = 2 and j.id = g.id);

This is the plan that is known to fail on my machine:

Seq Scan on g (cost=0.00..22619.63 rows=2402 width=4)
SubPlan
-> Nested Loop (cost=0.00..9.40 rows=1 width=52)
-> Nested Loop (cost=0.00..7.37 rows=1 width=44)
-> Index Scan using c_key on c (cost=0.00..2.02 rows=1 width=8)
-> Materialize (cost=5.35..5.35 rows=1 width=36)
-> Nested Loop (cost=0.00..5.35 rows=1 width=36)
-> Nested Loop (cost=0.00..3.78 rows=1 width=28)
-> Seq Scan on j (cost=0.00..2.12 rows=1 width=12)
-> Seq Scan on b (cost=0.00..1.65 rows=1 width=16)
-> Seq Scan on a (cost=0.00..1.55 rows=1 width=8)
-> Index Scan using c_c_key on c_c (cost=0.00..2.02 rows=1 width=8)

Other plans not using Materialize seem to work okay.

Please contact me if I can help someone solve this problem or supply more
information. I want to help out since I rely heavily on postgres!

--Brian Hirt

--
The world's most ambitious and comprehensive PC game database project.

http://www.mobygames.com