Re: pgsql: Fix calculation of plan node extParams to account for the

I trid the following two queries in the version before your patch:
(1) which is reported in the bug("plan should not reference subplan's
variable") reported by Catalin Pitis:

INSERT INTO PROJECT(PROJECT_ID,PROJECT_DESC)(SELECT
MAX(PROJECT_ID),'MYPROJECT' FROM PROJECT WHERE NOT EXISTS (
SELECT PROJECT_DESC FROM PROJECT WHERE PROJECT_DESC = 'MYPROJECT' ) );

it reported an error;
(2)
select * from project where (1,1) = (SELECT MAX(PROJECT_ID),1 FROM
PROJECT WHERE NOT EXISTS (SELECT PROJECT_DESC FROM PROJECT WHERE
PROJECT_DESC = 'MYPROJECT'))

but, there was no error at all!!

Then I noticed that when add IDs of all PARAM_EXEC params appearing in the
given expression tree to the result set:
(1) for subplans corresponding to a SubLink, it was processed like this:
/* in finalize_primnode */

if (is_subplan(node))
{
SubPlan *subplan = (SubPlan *) node;

/* Add outer-level params needed by the subplan to paramids */
context->paramids = bms_join(context->paramids,
bms_intersect(subplan->plan->extParam,
context->outer_params));
/* fall through to recurse into subplan args */
}

Attention: there's a bms_intersect op here before bms_join.

(2) but for subplans correspoonding to a RangeTable, say SubqueryScan, it
was processed like this:

/* in finalize_plan */
case T_SubqueryScan:

/*
* In a SubqueryScan, SS_finalize_plan has already been run on the
* subplan by the inner invocation of subquery_planner, so there's
* no need to do it again. Instead, just pull out the subplan's
* extParams list, which represents the params it needs from my
* level and higher levels.
*/
context.paramids = bms_add_members(context.paramids,
((SubqueryScan *) plan)->subplan->extParam);
break;

Attention: there's no bms_intersect .

So, my question is why not just add a bms_intersect in the second occasion
just like the first one? Do we need to change so much?

"Tom Lane" <tgl(at)postgresql(dot)org>
:20060503002507(dot)562419FB1F8(at)postgresql(dot)org(dot)(dot)(dot)
> Log Message:
> -----------
> Fix calculation of plan node extParams to account for the possibility that
one
> initPlan sets a parameter for another. This could not (I think) happen
before
> 8.1, but it's possible now because the initPlans generated by MIN/MAX
> optimization might themselves use initPlans. We attach those initPlans as
> siblings of the MIN/MAX ones, not children, to avoid duplicate computation
> when multiple MIN/MAX aggregates are present; so this leads to the case of
an
> initPlan needing the result of a sibling initPlan, which is not possible
with
> ordinary query nesting. Hadn't been noticed because in most contexts
having
> too much stuff listed in extParam is fairly harmless. Fixes "plan should
not
> reference subplan's variable" bug reported by Catalin Pitis.
>
> Tags:
> ----
> REL8_1_STABLE
>
> Modified Files:
> --------------
> pgsql/src/backend/optimizer/plan:
> subselect.c (r1.100.2.2 -> r1.100.2.3)
>
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/plan
/subselect.c.diff?r1=1.100.2.2&r2=1.100.2.3)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>