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?
