Re: COALESCE implementation question

Philip Warner <pjw@rhyme.com.au> writes:
> explain update t1 set f1 = Coalesce( (select f2 from t2 x where x.f1 =
> t1.f1), t1.f1);
> NOTICE:  QUERY PLAN:

> Seq Scan on t1  (cost=0.00..20.00 rows=1000 width=10)
>   SubPlan
>     -> Index Scan using t2f1 on t2 x  (cost=0.00..8.14 rows=10 width=4)
>     -> Seq Scan on t2 x  (cost=0.00..22.50 rows=1000 width=4)

This is a bug caused by interaction between two planning passes run
on the same Query node.  The parser thinks it's cool to generate a
CASE parsetree with multiple paths to the same sub-select Query node,
but in fact it is not cool because planning destructively alters the
Query node contents.  I'm amazed it didn't crash, to tell the truth.

I have a patch but haven't applied it yet (been offline for most of
two days due to telco idiocy :-().
        regards, tom lane