Subselect left join / not exists() - Mailing list pgsql-sql

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:

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.