Aggregate in Correlated SubQuery

From: "Niederland" <niederland(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Aggregate in Correlated SubQuery
Date: 2006-10-15 07:23:39
Message-ID: 1160897019.236535.38970@b28g2000cwb.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Before postgresql 8.1.5, I could do the following to find the first
lead that created a prospect in my application.

SELECT
Lead.LeadID,
Prospect.ProspectID
FROM
Prospect INNER JOIN Lead USING (ProspectID)
WHERE
Lead.CreationDate = (SELECT MIN(Lead.CreationDate) FROM Lead AS LL
WHERE LL.ProspectID = Lead.ProspectID)

With 8.1.5 this now generates:
ERROR: aggregates not allowed in WHERE clause
SQL state: 42803

Shouldn't aggregate functions still work within correlated subQueries?
I saw in the 8.1.5 release notes that this was disabled due to crashes
this caused in 8.1.X versions for Postgresql. I have never experienced
this with queries similar to the above.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andrew - Supernews 2006-10-15 07:29:52 Re: Aggregate in Correlated SubQuery
Previous Message Ron Johnson 2006-10-15 05:48:41 Re: A query planner that learns