Thread: HSavage bug in Postgresql beta?

I'm using the 990329 snap shot.

I was doing this query...
SELECT story.approved, story.oid FROM story, webuser, category* WHERE
story.webuser = webuser.oid AND story.category = category.oid;
approved|   oid
--------+------
f       |181760
t       |179383
t       |179384
t       |179385
t       |179386
t       |179387
(6 rows)

So far, so good. "approved" is a boolean field. Now I try...

SELECT story.approved, story.oid FROM story, webuser, category* WHERE
story.webuser = webuser.oid AND story.category = category.oid and
approved;
approved|oid
--------+---
(0 rows)

Yow! I expect 5 of the above 6 rows, but now I get none!
I tried simplifying the SELECT to only join 2 of the above tables but
the problem didn't arise.

Has anything been fixed recently that might account for this? If not, is
there anyway I can help you guys to get it fixed?

Chris Bitmead <chris.bitmead@bigfoot.com> writes:
> SELECT story.approved, story.oid FROM story, webuser, category* WHERE
> story.webuser = webuser.oid AND story.category = category.oid and
> approved;
> [ fails to find tuples it should find ]

Youch.  I could not duplicate that here on a toy example, which may
mean there is a recently-fixed bug, or it may just mean that there
are additional conditions required to trigger the bug.

What does EXPLAIN say about the plans used for the two queries?
        regards, tom lane

Well I destroyed the database and recreated it, and the problem didn't
happen any more. So unfortunately I can't reproduce it now.

I can tell you that the "approved" field that was causing the problem
was added with an ALTER TABLE ADD COLUMN statement. That's the most
"unusual" thing about the situation. Maybe I'll just have to put it down
to an aberation :-()



Tom Lane wrote:
> 
> Chris Bitmead <chris.bitmead@bigfoot.com> writes:
> > SELECT story.approved, story.oid FROM story, webuser, category* WHERE
> > story.webuser = webuser.oid AND story.category = category.oid and
> > approved;
> > [ fails to find tuples it should find ]
> 
> Youch.  I could not duplicate that here on a toy example, which may
> mean there is a recently-fixed bug, or it may just mean that there
> are additional conditions required to trigger the bug.
> 
> What does EXPLAIN say about the plans used for the two queries?
> 
>                         regards, tom lane