BUG #7787: ERROR: could not find RelOptInfo for given relids

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
  =