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
