Possible outer join bug with coalesce in 8.2

Started by John McCawleyabout 19 years ago3 messagesgeneral
Jump to latest
#1John McCawley
nospam@hardgeus.com

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?

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: John McCawley (#1)
Re: Possible outer join bug with coalesce in 8.2

John McCawley <nospam@hardgeus.com> writes:

What exactly is going on here?

EXPLAIN might shed some light. However, if you think this is a bug then
you need to provide a self-contained test case.

regards, tom lane

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: John McCawley (#1)
Re: Possible outer join bug with coalesce in 8.2

John McCawley <nospam@hardgeus.com> writes:

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:

Is this 8.2.0? Because the query seems to match the conditions for this
8.2.1 bug fix:

2006-12-07 14:33 tgl

* src/backend/optimizer/plan/: initsplan.c (REL8_2_STABLE),
initsplan.c: Repair incorrect placement of WHERE clauses when there
are multiple, rearrangeable outer joins and the WHERE clause is
non-strict and mentions only nullable-side relations. New bug in
8.2, caused by new logic to allow rearranging outer joins. Per bug
#2807 from Ross Cohen; thanks to Jeff Davis for producing a usable
test case.

regards, tom lane