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

Re: BUG #4290: wrong double subselect with aggregate function

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Andreas <postgresql(at)elbrief(dot)de>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #4290: wrong double subselect with aggregate function
Date: 2008-07-09 18:52:04
Message-ID: (view raw or whole thread)
Lists: pgsql-bugs
I wrote:
> I think one of the subplans isn't getting re-evaluated when it should
> be, which would be a botch in the extParam/parParam signaling.  Not sure
> yet if it's a planner or executor bug, but more likely the former.

So, stripping the problem query down to its essentials, we have

regression=# explain select
regression-#   ( select min( from c
regression(#     where c.aid = ( select b.aid from b where = ) ) as min_c_id
regression-# from d ;
                                       QUERY PLAN                               
 Seq Scan on d  (cost=0.00..31202.28 rows=1940 width=4)
     ->  Result  (cost=16.06..16.07 rows=1 width=0)
             ->  Index Scan using b_pkey on b  (cost=0.00..8.27 rows=1 width=4)
                   Index Cond: (id = $0)
             ->  Limit  (cost=0.00..7.79 rows=1 width=4)
                   ->  Index Scan using c_pkey on c  (cost=0.00..85.70 rows=11 width=4)
                         Filter: ((id IS NOT NULL) AND (aid = $1))
(9 rows)

Looking into the plan tree details, the first InitPlan has
	      :extParam (b 0)
	      :allParam (b 0)
which correctly indicates that it depends on the value of $0
(which represents passed down from the outer query).
The second InitPlan has
	      :extParam (b)
	      :allParam (b 1)
which indicates that it depends on the value of $1, which is the output
if the first InitPlan.  I think it's probably a bug that the extParam
doesn't include 1, since the first InitPlan isn't a sub-InitPlan of
the second one (it could be, but we choose not to do things that way).
However I'm not sure that that has any real consequences in this

The *real* problem here is that the second InitPlan doesn't show any
dependency on $0.  This means that when we re-evaluate the subquery at
the second row of d, we correctly mark the first InitPlan as needing
recalculation (because we know $0 changed), but the second InitPlan is
thought not to need recalculation, and so we just use its previous
result as-is.  The outer subquery is merely demanding $2 (the result
of the second InitPlan) and doesn't know that this isn't up-to-date.

Clearly, if an InitPlan depends on the result of a sibling InitPlan,
it had better be made to depend on all the extParams of that sibling

This seems like a fairly serious bug, and one that affects all branches
back to 8.1 (before that, InitPlans couldn't depend on their siblings,
or at least so claims a comment in subselect.c).  I'm surprised we've
not seen reports of it before.

			regards, tom lane

In response to

pgsql-bugs by date

Next:From: DuprezDate: 2008-07-10 07:08:26
Subject: BUG #4291: Inheritance fails on column suppression
Previous:From: Tom LaneDate: 2008-07-09 16:42:14
Subject: Re: BUG #4290: wrong double subselect with aggregate function

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