Possible outer join bug with coalesce in 8.2 - Mailing list pgsql-general

My development machine is PostgreSQL 8.1.5, and my production machine is
PostgreSQL 8.2.  Until now I haven't run into any differences in
behavior.  I have a query with a relatively wacky join, and while it was
working on my development machine, it wouldn't work on the production
machine.  The query is as follows:

SELECT tbl_claim.claimnum
FROM tbl_claim INNER JOIN vw_evaldate ON tbl_claim.claim_id =
vw_evaldate.claim_id
LEFT OUTER JOIN tbl_claimbatchitem ON tbl_claim.claim_id =
tbl_claimbatchitem.claim_id
LEFT OUTER JOIN
tbl_claimbatch ON (tbl_claimbatchitem.claimbatch_id =
tbl_claimbatch.claimbatch_id AND coalesce(tbl_claimbatch.complete,0) = 0 )
LEFT OUTER JOIN tbl_subcontractor ON tbl_claim.subcontractor_id =
tbl_subcontractor.subcontractor_id
LEFT OUTER JOIN tbl_claimstate ON tbl_claim.claimstate_id =
tbl_claimstate.claimstate_id
LEFT OUTER JOIN tbl_employee ON tbl_claim.emp_id = tbl_employee.emp_id
LEFT OUTER JOIN tblworkorder ON tbl_claim.claimnum = tblworkorder.claimnum
LEFT OUTER JOIN tbl_claimqc ON tbl_claim.claim_id = tbl_claimqc.claim_id
WHERE tbl_claimbatch.claimbatch_id IS NULL AND tbl_claim.claimnum LIKE
'%foo%'
ORDER BY tbl_claim.subcontractor_id, emp_lname, emp_fname, addr_zip,
claimnum LIMIT 30000

The basic idea is that if a record is found in tbl_claimbatchitem for
the claimnum like '%foo%', it will not return a record unless the
corresponding active column in tbl_claimbatch is 0 or null.  This query
worked as expected on my 8.1.5 box, but not on the 8.2 box.  After some
poking, I discovered that it was the coalesce.  I modified the query to:

SELECT tbl_claim.claimnum
FROM tbl_claim INNER JOIN vw_evaldate ON tbl_claim.claim_id =
vw_evaldate.claim_id
LEFT OUTER JOIN tbl_claimbatchitem ON tbl_claim.claim_id =
tbl_claimbatchitem.claim_id
LEFT OUTER JOIN
tbl_claimbatch ON (tbl_claimbatchitem.claimbatch_id =
tbl_claimbatch.claimbatch_id AND tbl_claimbatch.complete = 0 )
LEFT OUTER JOIN tbl_subcontractor ON tbl_claim.subcontractor_id =
tbl_subcontractor.subcontractor_id
LEFT OUTER JOIN tbl_claimstate ON tbl_claim.claimstate_id =
tbl_claimstate.claimstate_id
LEFT OUTER JOIN tbl_employee ON tbl_claim.emp_id = tbl_employee.emp_id
LEFT OUTER JOIN tblworkorder ON tbl_claim.claimnum = tblworkorder.claimnum
LEFT OUTER JOIN tbl_claimqc ON tbl_claim.claim_id = tbl_claimqc.claim_id
WHERE tbl_claimbatch.claimbatch_id IS NULL AND tbl_claim.claimnum LIKE
'%foo%'
ORDER BY tbl_claim.subcontractor_id, emp_lname, emp_fname, addr_zip,
claimnum LIMIT 30000


Which is just a removal of the coalesce, and the query works on both
boxes.  (I didn't have any nulls in the column anyway).


What exactly is going on here?