Skip site navigation (1) Skip section navigation (2)

Re: Bad error message

From: "Gurjeet Singh" <singh(dot)gurjeet(at)gmail(dot)com>
To: Decibel! <decibel(at)decibel(dot)org>
Cc: "Pg Hackers" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Bad error message
Date: 2008-10-01 02:36:53
Message-ID: 65937bea0809301936m8c7a73cu8a70b56ff8f5a42@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Wed, Oct 1, 2008 at 3:07 AM, Decibel! <decibel(at)decibel(dot)org> wrote:

> 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[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB      http://www.enterprisedb.com

Mail sent from my BlackLaptop device

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2008-10-01 03:10:51
Subject: Re: Common Table Expressions (WITH RECURSIVE) patch
Previous:From: Andrew ChernowDate: 2008-10-01 01:20:30
Subject: Re: Block-level CRC checks

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group