Re: Nested CASE-WHEN scoping

On 25.05.2011 17:47, Tom Lane wrote:
> Heikki Linnakangas<heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
>> While looking at fixing the multiple-evaluation issue in IN and BETWEEN
>> discussed a while ago, I realized that the current assumption that only
>> one CaseTestExpr placeholder needs to be valid at any given time is not
>> true.
>
> [ scratches head ... ] Why does the save/restore in ExecEvalCase not
> take care of this?

The mistake happens during planning, when the SQL function is inlined
and pre-evaluated. It's a bit hard to see what happened once the
planning is finished because the whole expression is folded into a
constant, but here's goes:

The original expression is:

CASE now() WHEN 29 THEN 'foo' ELSE 'bar' END;

In parse analysis, it is turned into this:

CASE WHEN CaseTestExpr = 29 THEN 'foo' ELSE 'bar' END;

where CaseTestExpr stands for the now(). Next the planner tries to
simplify the WHEN condition, "CaseTestExpr = 29". The equality operator
is implemented by the evileq(timestamptz, int4) SQL function, defined as:

CASE $2 WHEN length($1::text) THEN true ELSE false END;

That SQL-function is transformed at parse analysis into:

CASE CaseTestExpr = length($1::text) THEN true ELSE false END;

This CaseTestExpr stands for the Param to the function, $2. When that
tranformed SQL function body is inlined into the outer WHEN clause,
"CaseTestExpr = 29", and Params are substituted, it becomes:

CASE CaseTestExpr = length(CaseTestExpr::text) THEN true ELSE false END.

(you can see the expression tree for that if you print out 'newexpr' in
inline_function(), just after the call to substitute_actual_parameters())

At this point it's easy to see that we have screwed up. The first
CaseTestExpr stands for the inner CASE-value, which is $2, which stands
for 29, and the second CaseTestExpr stands for the *outer* CASE-value,
which is supposed to be now(). The planner cannot distinguish between
the two anymore.

Both CaseTestExprs are then incorrectly replaced with constant 29, and
the whole expression is constant-folded into 'bar'.

>> So I'm going to put the BETWEEN/IN fix aside for now, and refactor the
>> placeholder infrastructure to handle several simultaneous placeholders,
>
> That sounds like a mess, and I'm not even convinced it'll solve the
> problem ...

Hmm, it would solve the above by if we can keep the CaseTestExprs
separate. It's not quite as straightforward as I originally thought, as
the parse analysis of the inlined SQL function needs to use placeholder
numbers that are different from those used in the outer context. But it
seems doable.

BTW, i just stumbled into this:

postgres=# explain verbose SELECT CASE now() WHEN (29+random()::int4)
THEN 'foo' ELSE 'bar' END;
ERROR: unexpected CASE WHEN clause: 326

Looks like ruleutils.c is also not prepared for the case that the
implicit equality operation gets inlined into something else than an OpExpr.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com