>>> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> That doesn't seem to be the only issue in your example, but it's
> definitely one of 'em.
FWIW, the pattern causing the problem here is a pretty common one in
court business logic: join (often outer join or an exists test) to a
set of candidate rows WHERE NOT EXISTS the same set of candidate rows
with tests to see if there is a better choice from among the
candidates. In the simplest cases (like the current example) this
could be rewritten using a correlated subquery with a MAX function;
however, the test for a better candidate often is too complex for that
technique to work, that technique has (until now) been slower, and
programmers were much more prone to writing code with logic errors
that way.
I don't know if that matters to you in your current debugging, but I
just wanted to point out that we do it in hundreds of places in the
code.
-Kevin