Re: BUG #5025: Aggregate function with subquery in 8.3 and 8.4.

I blithely opined:
> I believe BTW that there are related issues in other places where we
> expand composites into RowExprs.  But the other places have been doing
> that for awhile.  I think that for 8.4 our goals should be limited to
> not changing the behavior compared to prior releases.

So while I was testing my fix for this, I found out that that's more
complicated than I thought.  Consider these examples in the regression
database:

select t1.q2, count(t2.*)
from int8_tbl t1 left join int8_tbl t2 on (t1.q2 = t2.q1)
group by t1.q2 order by 1;

select t1.q2, count(t2.*)
from int8_tbl t1 left join (select * from int8_tbl) t2 on (t1.q2 = t2.q1)
group by t1.q2 order by 1;

select t1.q2, count(t2.*)
from int8_tbl t1 left join (select * from int8_tbl offset 0) t2 on (t1.q2 = t2.q1)
group by t1.q2 order by 1;

select t1.q2, count(t2.*)
from int8_tbl t1 left join
  (select q1, case when q2=1 then 1 else q2 end as q2 from int8_tbl) t2
  on (t1.q2 = t2.q1)
group by t1.q2 order by 1;

If you believe that "t2.*" should go to NULL in a join-extended row,
then the correct answer for all four of these is

        q2         | count
-------------------+-------
 -4567890123456789 |     0
               123 |     2
               456 |     0
  4567890123456789 |     6
(4 rows)

However, the actual behavior of every release since 8.0 has been that
the second case gives

        q2         | count
-------------------+-------
 -4567890123456789 |     1
               123 |     2
               456 |     1
  4567890123456789 |     6
(4 rows)

ie, t2.* fails to go to NULL because it's expanded as ROW(t2.q1,t2.q2).
The OFFSET 0 in the third case restores expected behavior by preventing
flattening of the subquery, and up till 8.4 the CASE expression in the
fourth case did too.

With the fix I was just about to apply, all four cases give the first
set of results.  This clearly satisfies the principle of least
astonishment, at least more nearly than what we have; but it equally
clearly is *not* going to restore 8.4 to work just like 8.3.

I'm inclined to apply the patch to 8.4 anyway, because it seems like a
bug fix.  I would consider patching further back except there's no
chance of making it work in older branches, at least not without
destabilizing them quite a bit (the PlaceHolderVar mechanism would have
to be back-ported).

It might be possible to fix the older branches by not flattening
subqueries that have whole-row references; but even that would take
nontrivial work, and it'd be sacrificing performance to fix a corner
case no one has previously complained about.  So I'm leaning to patching
8.4 and leaving the older branches alone.

Thoughts?

            regards, tom lane