Re: [HACKERS] Odd subselect in target list behavior WRT aggregates
Mike Mascari [EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Odd subselect in target list behavior WRT aggregates
Tom Lane wrote:
Mike Mascari [EMAIL PROTECTED] 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.
Thanks, Tom. I should have tried the current version before posting.
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.
Normally, the grouping is done on two or more distantly related
pieces of data:
How many widgets were sold by John on Mondays?
What is the most popular hour for sales by quarter?
etc.
So the nature of the data is such that to dynamically generate
the proper joins in the FROM/WHERE clause was too complex (for
me). :-)
Thanks again,
Mike Mascari
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Odd subselect in target list behavior WRT aggregates
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? The only difference between Query #1 and Query #2 is
that the second subselect in the target list of Query #2
aggregates on the 'day' of a sale as opposed to the 'hour':
Query #1
SELECT SUM(p.dstqty) as agg,
(SELECT date_trunc('hour', sales.active)
FROM sales
WHERE p.purchase = sales.purchase) as field1,
(SELECT date_trunc('hour', sales.active)
FROM sales
WHERE p.purchase = sales.purchase) as field2
FROM purchases p
WHERE ...
GROUP BY 2,3;
ERROR: Sub-SELECT uses un-GROUPed attribute p.purchase from
outer query
Query #2
SELECT SUM(p.dstqty) as agg,
(SELECT date_trunc('hour', sales.active)
FROM sales
WHERE p.purchase = sales.purchase) as field1,
(SELECT date_trunc('day', sales.active)
FROM sales WHERE p.purchase = sales.purchase) as field2
FROM purchases p
WHERE ...
GROUP BY 2,3;
agg | field1 | field2
---++
1. | 2002-12-27 18:00:00-05 | 2002-12-27 00:00:00-05
I also failed to mention in the original post that this is
PostgreSQL version 7.2.1.
Any help or instruction would be greatly appreciated.
Mike Mascari
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
