Re: Subselect left join / not exists()

Desmond Coertzen <patrolliekaptein(at)gmail(dot)com> writes:
> On Postgres 8.4.22.

You realize of course that 8.4.x has been out of support for more than
a year ...

> 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.

You haven't provided nearly enough detail for anyone to judge whether
this is actually a bug or just your wrong expectation of what should
happen. If you'd like people to look into it, please provide a
self-contained test case: not only the query but table definitions
and sample data. (Ideally, a SQL script that reproduces the problem
starting from an empty database would make it easy for people to test.
We're not likely to take the time to try to reverse-engineer context
from an incomplete bug report.)

If it is a bug, it will not get fixed in 8.4.x anyway, because there
will never be any more 8.4.x releases. However, if the bug still exists
in newer release branches, we'd definitely endeavor to fix it there.

regards, tom lane