The following bug has been logged on the website:
Bug reference: 7787
Logged by: Gary Durbin
Email address: gdurbin@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=3Dpdr.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=3Dde.dependentid
AND de.createts=3DTIMESTAMP WITH time ZONE '2199-12-31 +0'
JOIN person_bene_election pbe ON pbe.personid =3D p.personid
AND pbe.effectivedate =3D de.effectivedate
AND pbe.createts =3D TIMESTAMP WITH time ZONE '2199-12-31 +0'
AND pbe.benefitsubclass =3D de.benefitsubclass
AND pbe.selectedoption =3D 'Y'
AND pbe.benefitelection =3D '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 =3D 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 =3D de.dependentid and de.effectivedate between
pdr.effectivedate and pdr.enddate
JOIN dependent_desc dd
ON dd.dependentid=3Dde.dependentid
AND dd.effectivedate <=3D dd.enddate
AND de.effectivedate BETWEEN dd.effectivedate AND dd.enddate
LEFT JOIN person_vitals pv on pdr.dependentid =3D 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 =3D bpd.benefitcalcruleid
AND de.effectivedate between bcrd.EffectiveDate and bcrd.EndDate
and current_timestamp between bcrd.CreateTS and bcrd.EndTs
where de.selectedoption=3D'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 =3D p.personid
AND cx.dependentid =3D de.dependentid
AND cx.benefitplanid =3D pbe.benefitplanid
WHERE p.personid=3D'25237'
AND cx.dependentid IS NULL
Not fail:
SELECT *
FROM person p
JOIN person_dependent_relationship pdr ON p.personid=3Dpdr.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=3Dde.dependentid
AND de.createts=3DTIMESTAMP WITH time ZONE '2199-12-31 +0'
JOIN person_bene_election pbe ON pbe.personid =3D p.personid
AND pbe.effectivedate =3D de.effectivedate
AND pbe.createts =3D TIMESTAMP WITH time ZONE '2199-12-31 +0'
AND pbe.benefitsubclass =3D de.benefitsubclass
AND pbe.selectedoption =3D 'Y'
AND pbe.benefitelection =3D '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 =3D 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 =3D de.dependentid and de.effectivedate between
pdr.effectivedate and pdr.enddate
JOIN dependent_desc dd
ON dd.dependentid=3Dde.dependentid
AND dd.effectivedate <=3D dd.enddate
AND de.effectivedate BETWEEN dd.effectivedate AND dd.enddate
LEFT JOIN person_vitals pv on pdr.dependentid =3D 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 =3D bpd.benefitcalcruleid
AND de.effectivedate between bcrd.EffectiveDate and bcrd.EndDate
and current_timestamp between bcrd.CreateTS and bcrd.EndTs
where de.selectedoption=3D'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 =3D bpd.benefitcalcruleid or
bcrd.benefitcalcruleid is null) )
cx ON cx.userid =3D p.personid
AND cx.dependentid =3D de.dependentid
AND cx.benefitplanid =3D pbe.benefitplanid
WHERE p.personid=3D'25237'
AND cx.dependentid IS NULL
=