Обсуждение: Bad error message
From -HEAD: ERROR: aggregates not allowed in WHERE clause STATEMENT: SELECT * FROM loans l WHERE id IN ( SELECT max(l.id) FROM loans JOIN customers c ON c.id = l.customer_id JOIN people p ON p.id = c.person_id WHERE p.first_name = 'Test person' GROUPBY l.loan_type_cd ) ; The real issue is this: ERROR: missing FROM-clause entry for table "l" at character 132 STATEMENT: SELECT max(l.id) FROM loans JOIN customers c ON c.id = l.customer_id JOIN people p ON p.id = c.person_id WHERE p.first_name = 'Test person' GROUP BY l.loan_type_cd; And if I change the FROM loans to be FROM loans l, the original select does work fine. Let me know if I need to create a full test case for this... -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828
On Wed, Oct 1, 2008 at 3:07 AM, Decibel! <decibel@decibel.org> wrote:
No, the real issue is that you are referencing the outer table's column's max() in the inner query (correlated sub-query). The table in the outer query is aliased 'l' and the sub-query is trying to aggregate that, which is in the outer query's WHERE clause; and that is not allowed.
Renaming the outer query's alias to something other than 'l' would show you the real error (which again would be 'missing FROM-clause entry).
Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
>From -HEAD:
ERROR: aggregates not allowed in WHERE clause
STATEMENT: SELECT *
FROM loans l
WHERE id IN ( SELECT max(l.id)
FROM loans
JOIN customers c ON c.id = l.customer_id
JOIN people p ON p.id = c.person_id
WHERE p.first_name = 'Test person'
GROUP BY l.loan_type_cd
)
;
The real issue is this:
ERROR: missing FROM-clause entry for table "l" at character 132
STATEMENT: SELECT max(l.id)
FROM loans
JOIN customers c ON c.id = l.customer_id
JOIN people p ON p.id = c.person_id
WHERE p.first_name = 'Test person'
GROUP BY l.loan_type_cd;
And if I change the FROM loans to be FROM loans l, the original select does work fine.
Let me know if I need to create a full test case for this...
No, the real issue is that you are referencing the outer table's column's max() in the inner query (correlated sub-query). The table in the outer query is aliased 'l' and the sub-query is trying to aggregate that, which is in the outer query's WHERE clause; and that is not allowed.
Renaming the outer query's alias to something other than 'l' would show you the real error (which again would be 'missing FROM-clause entry).
Best regards,
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
"Gurjeet Singh" <singh.gurjeet@gmail.com> writes: > On Wed, Oct 1, 2008 at 3:07 AM, Decibel! <decibel@decibel.org> wrote: >> ERROR: aggregates not allowed in WHERE clause > No, the real issue is that you are referencing the outer table's column's > max() in the inner query (correlated sub-query). Yeah. It's not easy to see how the software could guess your real intentions here. We could maybe offer a vaguely-worded HINT but I'm not able to think of wording that would be very helpful. regards, tom lane
On Wed, Oct 1, 2008 at 9:39 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Can we do something like this in the code:
if( "level of the referenced column's relation" != "level of the (sub)query being processed" )
errhint( "The subquery may be unintentionally referencing an outer query's column!" );
Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
"Gurjeet Singh" <singh.gurjeet@gmail.com> writes:
> On Wed, Oct 1, 2008 at 3:07 AM, Decibel! <decibel@decibel.org> wrote:>> ERROR: aggregates not allowed in WHERE clause> No, the real issue is that you are referencing the outer table's column'sYeah. It's not easy to see how the software could guess your real
> max() in the inner query (correlated sub-query).
intentions here. We could maybe offer a vaguely-worded HINT but I'm
not able to think of wording that would be very helpful.
Can we do something like this in the code:
if( "level of the referenced column's relation" != "level of the (sub)query being processed" )
errhint( "The subquery may be unintentionally referencing an outer query's column!" );
Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
On Oct 1, 2008, at 12:12 AM, Gurjeet Singh wrote: > On Wed, Oct 1, 2008 at 9:39 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Gurjeet Singh" <singh.gurjeet@gmail.com> writes: > > On Wed, Oct 1, 2008 at 3:07 AM, Decibel! <decibel@decibel.org> > wrote: > >> ERROR: aggregates not allowed in WHERE clause > > > No, the real issue is that you are referencing the outer table's > column's > > max() in the inner query (correlated sub-query). > > Yeah. It's not easy to see how the software could guess your real > intentions here. We could maybe offer a vaguely-worded HINT but I'm > not able to think of wording that would be very helpful. > > Can we do something like this in the code: > > if( "level of the referenced column's relation" != "level of > the (sub)query being processed" ) > errhint( "The subquery may be unintentionally referencing > an outer query's column!" ); Yeah, something like that would be very helpful. > Mail sent from my BlackLaptop device Haha. +1 -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828