Re: Bug #526: Three levels deeply nested SELECT goes wrong

pgsql-bugs(at)postgresql(dot)org writes:
> A 3 levels deeply nested SELECT with "R(a)" in the top-most FROM part
> and "R.a=S.a AND XXX" in the middle WHERE part may give different
> results if in a SELECT within XXX the term "S.a" is replaced by "R.a".

> Version: PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc 2.96

I have committed a fix for this problem into current sources
(7.2-to-be). The fix would not apply cleanly in 7.0.*, but if you
don't care to run CVS-tip code you could update to 7.1.3 and apply
the attached patch to it.

regards, tom lane

*** src/backend/optimizer/plan/subselect.c.orig Wed Mar 21 22:59:37 2001
--- src/backend/optimizer/plan/subselect.c Fri Nov 30 14:11:52 2001
***************
*** 324,329 ****
--- 324,335 ----
* is anything more complicated than a plain sequential scan, and
* we do it even for seqscan if the qual appears selective enough
* to eliminate many tuples.
+ *
+ * XXX It's pretty ugly to be inserting a MATERIAL node at this
+ * point. Since subquery_planner has already run SS_finalize_plan
+ * on the subplan tree, we have to kluge up parameter lists for
+ * the MATERIAL node. Possibly this could be fixed by postponing
+ * SS_finalize_plan processing until setrefs.c is run.
*/
if (node->parParam == NIL)
{
***************
*** 362,369 ****
}
if (use_material)
{
! plan = (Plan *) make_material(plan->targetlist, plan);
! node->plan = plan;
}
}

--- 368,380 ----
}
if (use_material)
{
! Plan *matplan;
!
! matplan = (Plan *) make_material(plan->targetlist, plan);
! /* kluge --- see comments above */
! matplan->extParam = listCopy(plan->extParam);
! matplan->locParam = listCopy(plan->locParam);
! node->plan = plan = matplan;
}
}