Skip site navigation (1) Skip section navigation (2)

Re: COALESCE implementation question

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Philip Warner <pjw(at)rhyme(dot)com(dot)au>
Cc: pgsql-hackers(at)hub(dot)org
Subject: Re: COALESCE implementation question
Date: 2000-08-06 02:27:37
Message-ID: 15334.965528857@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2000-08-06 02:36:14
Subject: Re: COALESCE implementation question
Previous:From: Tom LaneDate: 2000-08-06 02:19:56
Subject: Re: Quoting fun

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group