Thread: Slightly confused error message

Hi,

I just stumbled over a slightly confused error message:

mydb=# select count(*),coverage_area from myschema.streets except select
cd as coverage_area from countryref.disks group by streets.coverage_area;
ERROR:  column "streets.coverage_area" must appear in the GROUP BY
clause or be used in an aggregate function

As the query looks, streets.coverage_area is actually used in the GROUP BY.

I know how to fix the query, but I wonder whether it is worth the effort
to try improving the error message.

Thanks,
Markus
-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

Markus Schaber <schabi@logix-tt.com> writes:
> I just stumbled over a slightly confused error message:

> mydb=# select count(*),coverage_area from myschema.streets except select
> cd as coverage_area from countryref.disks group by streets.coverage_area;
> ERROR:  column "streets.coverage_area" must appear in the GROUP BY
> clause or be used in an aggregate function

> As the query looks, streets.coverage_area is actually used in the GROUP BY.

The complaint is 100% accurate; the subquery that it's unhappy about is
select count(*),coverage_area from myschema.streets

which is an aggregating query, but coverage_area is being used outside
an aggregate without having been grouped by.
        regards, tom lane

Hi, Tom,

Tom Lane wrote:

>>I just stumbled over a slightly confused error message:
> 
>>mydb=# select count(*),coverage_area from myschema.streets except select
>>cd as coverage_area from countryref.disks group by streets.coverage_area;
>>ERROR:  column "streets.coverage_area" must appear in the GROUP BY
>>clause or be used in an aggregate function
> 
>>As the query looks, streets.coverage_area is actually used in the GROUP BY.
> 
> The complaint is 100% accurate;

I know that, and won't deny.

> the subquery that it's unhappy about is
> 
>     select count(*),coverage_area from myschema.streets
> 
> which is an aggregating query, but coverage_area is being used outside
> an aggregate without having been grouped by.

Yes, and my question is whether it is easy and worth the effort. to add
that information (about the acutally offending subquery) to the message.

Thanks,
Markus

-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

On Fri, 28 Apr 2006 12:07:04 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Markus Schaber <schabi@logix-tt.com> writes:
>> I just stumbled over a slightly confused error message:
> 
>> mydb=# select count(*),coverage_area from myschema.streets except select
>> cd as coverage_area from countryref.disks group by
> streets.coverage_area;
>> ERROR:  column "streets.coverage_area" must appear in the GROUP BY
>> clause or be used in an aggregate function
> 
>> As the query looks, streets.coverage_area is actually used in the GROUP
> BY.
> 
> The complaint is 100% accurate; the subquery that it's unhappy about is
> 
>     select count(*),coverage_area from myschema.streets
> 
> which is an aggregating query, but coverage_area is being used outside
> an aggregate without having been grouped by.

I see lack of parenthesis in the sub-query:

select count(*),coverage_area from myschema.streets except   (select cd as coverage_area from countryref.disks)
groupby streets.coverage_area;
 

-- 
---------------------------------------------------------
Lic. Martín Marqués         |   SELECT 'mmarques' || 
Centro de Telemática        |       '@' || 'unl.edu.ar';
Universidad Nacional        |   DBA, Programador,    del Litoral             |   Administrador
---------------------------------------------------------

Markus Schaber <schabi@logix-tt.com> writes:
> Yes, and my question is whether it is easy and worth the effort. to add
> that information (about the acutally offending subquery) to the message.

I'm not sure about localizing the subquery per se, but it might be
possible to add a syntax pointer to the specific variable occurrence
that it's complaining about.  That would help at least somewhat in
cases like this.
        regards, tom lane