Subselect left join / not exists()

Hi there,

I refer to this post:
http://www.postgresql.org/message-id/25301.1291044420@sss.pgh.pa.us

I'm not convinced the problem is fully exposed.

I also followed Tom's advice by adding a is-not-null check to my clause in
the not exists() section of my query. It did not fully solve my problem of
returning no rows or bogus rows. I had to remove an aggregate max() and
replace it with an order by desc limit 1, and I also had to relocate a date
cast.

On Postgres 8.4.22.

The query references the same table 3 times:
In the main "from",
In a subselect from the column perspective of the above "from",
And in a subselect again of the above subselect in a not-exists test.

The first form of the query looked like:

select lots, of, stuff,
(select max(ls2.fiscal_ts)::date
from long_story ls2
where ls2.contract_id = ls.contract_id and ls2.tr_value > 0 and
sp_tr_is_cash(ls2.primary_key_id)
and not exists(select * from long_story ls2r where ls2r.reverse_of_pk_id =
ls2.primary_key_id)
) as last_cash_tr_ts
from long_story ls
where ls.create_ts >= current_date and ls.tr_type_id = 4;

The subselect columm "last_cash_tr_ts" produces null or bogus result.

I tried wrapping the subselect in a stored function but came up with the
same broken result. I tried a left join on the reversal self-referencing PK
with a null test as a substitute for not exists() but that also produced
weird results. The only way I could produce the correct results was by
rewriting the subselect like this:

select lots, of, stuff,
(select ls2.fiscal_ts
from long_story ls2
where ls2.contract_id = ls.contract_id and ls2.tr_value > 0 and
sp_tr_is_cash(ls2.primary_key_id)
and not exists(select * from long_story ls2r where ls2r.reverse_of_pk_id
is not null and ls2r.reverse_of_pk_id = ls2.primary_key_id)
order by 1 desc
limit 1
)::date as last_cash_tr_ts
from long_story ls
where ls.create_ts >= current_date and ls.tr_type_id = 4;

Including the not-null test in the subselect of the first query that
produced weird results did not solve the problem.
Notice the ::date cast outside of the subselect. If I include within the
subselect, I also get the same weird results.

Note that long_story.reverse_of_pk_id is a foreign key to itself,
long_story.primary_key_id and the FK column is nullable.

It's not the best DB design but the query without the null test and the max
aggregate should have worked. I am convinced there must be a bug exposed
when doing nested sub queries on the same table and the bug may show itself
the deeper you stack - stack meaning nested subselect on the same table. I
am also convinced that I am completely insane and may be missing something
very obvious like a noob.

Any help/comment highly appreciated in advance.