Re: Aggregate function with subquery in 8.3 and 8.4.

On Mon, Aug 31, 2009 at 04:02:43PM -0700, Sheng Cheng wrote:
> I though the following query would give me the same results in 8.4.0 and
> 8.3.1. 

It should give the same results! This looks like a bug in 8.4 to me, in
an attempt to optimize things it's pulling the CASE out from inside the
inner select and this is changing the semantics of the query.  Doing an
EXPLAIN in 8.4 gives the following:

 GroupAggregate  (cost=181.86..387.73 rows=200 width=64)
   ->  Merge Left Join  (cost=181.86..341.83 rows=8580 width=64)
         Merge Cond: (t1.f1 = (CASE WHEN (t2.f1 = '111'::text) THEN '111'::text 
ELSE t2.f1 END))
         ->  Sort  (cost=90.93..94.20 rows=1310 width=32)
               Sort Key: t1.f1
               ->  Seq Scan on t1  (cost=0.00..23.10 rows=1310 width=32)
         ->  Sort  (cost=90.93..94.20 rows=1310 width=64)
               Sort Key: (CASE WHEN (t2.f1 = '111'::text) THEN '111'::text ELSE 
t2.f1 END)
               ->  Seq Scan on t2  (cost=0.00..23.10 rows=1310 width=64)

While in 8.3 I get:

 GroupAggregate  (cost=198.23..378.88 rows=200 width=64)
   ->  Merge Left Join  (cost=198.23..333.48 rows=8580 width=64)
         Merge Cond: (t1.f1 = ts.f1)
         ->  Sort  (cost=90.93..94.20 rows=1310 width=32)
               Sort Key: t1.f1
               ->  Seq Scan on t1  (cost=0.00..23.10 rows=1310 width=32)
         ->  Sort  (cost=107.30..110.58 rows=1310 width=64)
               Sort Key: ts.f1
               ->  Subquery Scan ts  (cost=0.00..39.48 rows=1310 width=64)
                     ->  Seq Scan on t2  (cost=0.00..26.38 rows=1310 width=32)

Notice that the "Merge Cond" is working on the CASE expression in 8.4.
This is too late and is breaking things.

-- 
  Sam  http://samason.me.uk/