Possible outer join bug with coalesce in 8.2
От | John McCawley |
---|---|
Тема | Possible outer join bug with coalesce in 8.2 |
Дата | |
Msg-id | 45D1CC82.3050705@hardgeus.com обсуждение исходный текст |
Ответы |
Re: Possible outer join bug with coalesce in 8.2
(Tom Lane <tgl@sss.pgh.pa.us>)
Re: Possible outer join bug with coalesce in 8.2 (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | 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?
В списке pgsql-general по дате отправления: