Re: COALESCE implementation question

Philip Warner <pjw(at)rhyme(dot)com(dot)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