Re: must appear in GROUP by clause issue

On Fri, Jun 29, 2012 at 4:02 PM, George Woodring
<george(dot)woodring(at)iglass(dot)net> wrote:
> I have 2 (8.4.11) servers that I am testing the following query:
>
> SELECT count(*),
>              maptrunc(cpeloc.lat, 4.5)::text || maptrunc(cpeloc.long,
> 4.5)::text AS name,
>              AVG(cpeloc.lat) AS lt,
>              AVG(cpeloc.long) AS lng
> FROM cable_billing JOIN cpeloc USING(mac) LEFT JOIN davic USING(mac)
> WHERE (status = 0 OR status is null) AND (cpeloc.lat is not null AND
> cpeloc.lat !=0 AND cpeloc.long is not null AND cpeloc.long != 0)
> GROUP BY name ORDER BY name;
>
> On the first server the query works,
>
>  count |      name       |         lt          |         lng
> -------+-----------------+---------------------+----------------------
>      1 | 43.0425-94.2295 | 43.0429410000000000 | -94.2299740000000000
>      1 | 43.0525-94.260  | 43.0526200000000000 | -94.2603800000000000
>      1 | 43.054-94.224   | 43.0543150000000000 | -94.2244750000000000
> (51 rows)
>
> On the second server I get an error.
>
> ERROR:  column "cpeloc.lat" must appear in the GROUP BY clause or be used in
> an aggregate function
> LINE 1: select count(*), maptrunc(cpeloc.lat, 4.5)::text || maptrunc...
>
> I was thinking the one server that works the maptrunc function was thought
> of as an agg function, but they are both defined the same
>
>  \df maptrunc
>                           List of functions
>  Schema |   Name   | Result data type | Argument data types |  Type
> --------+----------+------------------+---------------------+--------
>  public | maptrunc | numeric          | numeric, numeric    | normal
> (1 row)
>
> The only difference between the 2 servers are that the one that works
> returns 51 rows and the one that does not I would expect to return 12000
> rows.
>
> I can make the one that does not work functional by changing
>
> maptrunc(cpeloc.lat)::text  --> max(maptrunc(cpeloc.lat))::text
> maptrunc(cpeloc.long)::text --> max(maptrunc(cpeloc.long))::text
>
> however, that one breaks on the first server with the error
>
> ERROR:  aggregates not allowed in GROUP BY clause
> LINE 1: select count(*), max(maptrunc(cpeloc.lat, 4.5))::text || max...
>
> Any suggestions would be appreciated.
>
> George Woodring
> --
> iGLASS Networks
> www.iglass.net

Are you sure the queries are the same on both servers? I'm not sure how

SELECT max(…) || max (…) AS name
…
GROUP BY name

could ever work without leading to the "aggregates not allowed in
GROUP BY clause".

I *think* this could also happen if for some reason a "name" column
was added to one of the tables on Server2 but not on Server1. Server1
groups by name as an output column. On Server2, name is interpreted as
an input column. Once you add max(…), cpeloc is now part of an
aggregate expression, but the GROUP BY actually operates over the
input column. Switch back to Server1, if name is not in the table
definition it is interpreted as an output column and GROUP BY max(…)
fails.

All of this is kind of spitting in the dark without table defs and
maybe the definition of maptrunc(), although at a guess I would say
that it looks like you are doing is you are trying to join locations
within a tolerance. maptrunc() truncates your latititude and longitude
measurement to within some acceptable error, and you are averaging all
candidate lat/longs to come up with "the" location.

Assuming it's not just differences in table defs between the servers,
I would suggest simplifying the query to debug it. Since lat and long
are both in cpelong, and the only other column mentioned is status, at
least one of the other tables (cable_billing and davic) is
unnecessary. Replace GROUP BY name ORDER BY name with GROUP BY 2 ORDER
BY 2. Consider generating name within a subquery before aggregating,
i.e.:

SELECT count(*),
name,
AVG(cpeloc.lat) AS lt,
AVG(cpeloc.long) AS lng
FROM (
SELECT
maptrunc(cpeloc.lat, 4.5)::text || maptrunc(cpeloc.long, 4.5)::text AS name,
cpeloc.lat,
cpeloc.long
FROM
cable_billing JOIN cpeloc USING(mac) LEFT JOIN davic USING(mac)
WHERE COALESCE(status, 0) = 0
AND COALESCE(cpeloc.lat, 0) !=0
AND COALESCE(cpeloc.long, 0) != 0
)
GROUP BY name ORDER BY name;

I would also inspect the subquery, ordered by name, to see if anything
weird leaps out at you in terms of the adjacent rows that *should* be
grouped. As an aside, I replaced the (x = 0 OR x IS NULL) and converse
constructions with COALESCE() functions, which IMO are a bit easier to
read.

Hope this is at all helpful.

--Lee

--
Lee Hachadoorian
PhD, Earth & Environmental Sciences (Geography)
Research Associate, CUNY Center for Urban Research
http://freecity.commons.gc.cuny.edu/