Possible outer join bug with coalesce in 8.2

From: John McCawley <nospam(at)hardgeus(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Possible outer join bug with coalesce in 8.2
Date: 2007-02-13 14:34:42
Message-ID: 45D1CC82.3050705@hardgeus.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Peter Eisentraut 2007-02-13 14:54:49 Re: Keyword OWNED not recognized in pg v. 8.1
Previous Message Magnus Hagander 2007-02-13 14:32:43 Re: PostgreSQL and OpenLdap