The following bug has been logged on the website: Bug reference: 7787 Logged by: Gary Durbin Email address: gdur...@synchr.com PostgreSQL version: 9.1.7 Operating system: "(Red Hat 4.4.6-4), 64-bit" Description:
Two queries follow. The first gets the error, the second doesn't the only difference is the additional expression in the where clause in the inner join. Fail: SELECT * FROM person p JOIN person_dependent_relationship pdr ON p.personid=pdr.personid AND CURRENT_DATE BETWEEN pdr.effectivedate AND pdr.enddate AND CURRENT_TIMESTAMP BETWEEN pdr.createts AND pdr.endts JOIN dependent_enrollment de ON pdr.dependentid=de.dependentid AND de.createts=TIMESTAMP WITH time ZONE '2199-12-31 +0' JOIN person_bene_election pbe ON pbe.personid = p.personid AND pbe.effectivedate = de.effectivedate AND pbe.createts = TIMESTAMP WITH time ZONE '2199-12-31 +0' AND pbe.benefitsubclass = de.benefitsubclass AND pbe.selectedoption = 'Y' AND pbe.benefitelection = 'E' LEFT JOIN /*CX_DEPENROLLAGELIMIT*/ (Select p.personid as userid , de.dependentid , de.benefitplanid , de.dependentenrollpid from dependent_enrollment de cross JOIN person p JOIN benefit_plan_desc bpd on bpd.benefitplanid = de.benefitplanid and de.effectivedate between bpd.effectivedate and bpd.enddate and current_timestamp between bpd.createts and bpd.endts JOIN AllowedDependents pdr on pdr.dependentid = de.dependentid and de.effectivedate between pdr.effectivedate and pdr.enddate JOIN dependent_desc dd ON dd.dependentid=de.dependentid AND dd.effectivedate <= dd.enddate AND de.effectivedate BETWEEN dd.effectivedate AND dd.enddate LEFT JOIN person_vitals pv on pdr.dependentid = pv.personid AND de.effectivedate BETWEEN pv.effectivedate AND pv.enddate AND current_timestamp BETWEEN pv.createts AND pv.endTS LEFT JOIN Benefit_Calc_Rule_Desc bcrd ON bcrd.benefitcalcruleid = bpd.benefitcalcruleid AND de.effectivedate between bcrd.EffectiveDate and bcrd.EndDate and current_timestamp between bcrd.CreateTS and bcrd.EndTs where de.selectedoption='Y' and (pdr.dependentrelationship not IN ('SP','DP','H','NA') AND (pv.birthdate IS NOT NULL AND (bpd.dependentagelimit IS not NULL and extract(years from age( de.EffectiveDate, pv.BirthDate))>bpd.dependentagelimit)) ) AND dd.dependentstatus<> 'D') cx ON cx.userid = p.personid AND cx.dependentid = de.dependentid AND cx.benefitplanid = pbe.benefitplanid WHERE p.personid='25237' AND cx.dependentid IS NULL Not fail: SELECT * FROM person p JOIN person_dependent_relationship pdr ON p.personid=pdr.personid AND CURRENT_DATE BETWEEN pdr.effectivedate AND pdr.enddate AND CURRENT_TIMESTAMP BETWEEN pdr.createts AND pdr.endts JOIN dependent_enrollment de ON pdr.dependentid=de.dependentid AND de.createts=TIMESTAMP WITH time ZONE '2199-12-31 +0' JOIN person_bene_election pbe ON pbe.personid = p.personid AND pbe.effectivedate = de.effectivedate AND pbe.createts = TIMESTAMP WITH time ZONE '2199-12-31 +0' AND pbe.benefitsubclass = de.benefitsubclass AND pbe.selectedoption = 'Y' AND pbe.benefitelection = 'E' LEFT JOIN /*CX_DEPENROLLAGELIMIT*/ (Select p.personid as userid , de.dependentid , de.benefitplanid , de.dependentenrollpid from dependent_enrollment de cross JOIN person p JOIN benefit_plan_desc bpd on bpd.benefitplanid = de.benefitplanid and de.effectivedate between bpd.effectivedate and bpd.enddate and current_timestamp between bpd.createts and bpd.endts JOIN AllowedDependents pdr on pdr.dependentid = de.dependentid and de.effectivedate between pdr.effectivedate and pdr.enddate JOIN dependent_desc dd ON dd.dependentid=de.dependentid AND dd.effectivedate <= dd.enddate AND de.effectivedate BETWEEN dd.effectivedate AND dd.enddate LEFT JOIN person_vitals pv on pdr.dependentid = pv.personid AND de.effectivedate BETWEEN pv.effectivedate AND pv.enddate AND current_timestamp BETWEEN pv.createts AND pv.endTS LEFT JOIN Benefit_Calc_Rule_Desc bcrd ON bcrd.benefitcalcruleid = bpd.benefitcalcruleid AND de.effectivedate between bcrd.EffectiveDate and bcrd.EndDate and current_timestamp between bcrd.CreateTS and bcrd.EndTs where de.selectedoption='Y' and (pdr.dependentrelationship not IN ('SP','DP','H','NA') AND (pv.birthdate IS NOT NULL AND (bpd.dependentagelimit IS not NULL and extract(years from age( de.EffectiveDate, pv.BirthDate))>bpd.dependentagelimit)) ) AND dd.dependentstatus<> 'D' /* Following line was added */ and (bcrd.benefitcalcruleid = bpd.benefitcalcruleid or bcrd.benefitcalcruleid is null) ) cx ON cx.userid = p.personid AND cx.dependentid = de.dependentid AND cx.benefitplanid = pbe.benefitplanid WHERE p.personid='25237' AND cx.dependentid IS NULL -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs