Re: Nested CASE-WHEN scoping - Mailing list pgsql-hackers

On 25.05.2011 17:47, Tom Lane wrote:
> Heikki Linnakangas<heikki.linnakangas@enterprisedb.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