Re: Odd subselect in target list behavior WRT aggregates

"Mike Mascari" <mascarm@mascari.com> writes:
> Hello. I have some code which generates subselects in the target
> list of a query and then aggregates the results. The code allows
> the user to determine the attributes of the aggregation. If the
> user chooses to aggregate on the same value twice, I get the
> "Sub-SELECT" error. If the user chooses a different second
> attribute of aggregation, no error occurs. Is that correct
> behavior?

This seems to be fixed as of 7.3, though I do not recall a previous
bug report like it.

However, I wonder why you are doing it like that, and not with a join:

SELECT SUM(p.dstqty) as agg,      date_trunc('hour', sales.active) as field1,      date_trunc('day', sales.active) as
field2
FROM purchases p, sales
WHERE p.purchase = sales.purchase     AND ...
GROUP BY 2,3;

The multiple-sub-select approach will require a separate probe into
"sales" to retrieve each of the fields; there's no optimization across
different subselects.
        regards, tom lane