Thread: must appear in GROUP by clause issue

On 06/29/2012 01:02 PM, George Woodring wrote:

>
> Any suggestions would be appreciated.

Are there any other errors before or after the above that might relate?

Are the function bodies the same on both servers?

>
> George Woodring
> --
> iGLASS Networks
> www.iglass.net <http://www.iglass.net>


-- 
Adrian Klaver
adrian.klaver@gmail.com

On Fri, Jun 29, 2012 at 4:02 PM, George Woodring
<george.woodring@iglass.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.longFROM   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/

Hello all,
How far can I get to a higher version of PostgreSQL by just entering a
command line instruction to upgrade without any major effort?

Regards

Chris Preston

On 07/10/2012 08:28 AM, Chris Preston wrote:
> Hello all,
> How far can I get to a higher version of PostgreSQL by just entering a
> command line instruction to upgrade without any major effort?
>
> Regards
>
> Chris Preston
>
>
At the simplest you just do a dump (using the dump tools from the *new* 
version of PostgreSQL) and restore to the new version. If you aren't 
worried about down-time and have the absolute simplest use-case then it 
isn't too difficult.

Most people have to deal with reviewing the release notes to see how 
they affect the overall system, setting up a test of the new version to 
shake out issues and planning the upgrade to minimize down-time. Among 
the things that have changed over the past few releases are tightening 
up of casting rules and changing the default for 
standard_conforming_strings to on either of which could cause serious 
breakage.

Once you get to a newer version (anything past 8.3? 8.4) you can use the 
new upgrade tools moving forward to minimize downtime during the upgrade 
process but you will *always* need to test and evaluate before deploying.

Cheers,
Steve

<div class="moz-cite-prefix">On 07/10/2012 11:28 PM, Chris Preston wrote:<br /></div><blockquote
cite="mid:3EED475E754849429C74FCAB58B57CFF@chrisp"type="cite"><pre wrap="">
 
Hello all,
How far can I get to a higher version of PostgreSQL by just entering a
command line instruction to upgrade without any major effort?
</pre></blockquote><br /> Not very, unless your database and queries are very simple.<br /><br /> At minimum you'll
haveto deal with the removal of implicit casts to text ( <a
href="http://www.postgresql.org/docs/current/static/release-8-3.html#AEN116407">http://www.postgresql.org/docs/current/static/release-8-3.html#AEN116407</a>)
and,if you use bytea, the change of default bytea format ( <a
href="http://www.postgresql.org/docs/9.0/static/runtime-config-client.html#GUC-BYTEA-OUTPUT"></a><a
href="http://www.postgresql.org/docs/current/static/release-9-0.html#AEN109764">http://www.postgresql.org/docs/current/static/release-9-0.html#AEN109764</a>).<br
/><br/> In general, you will want to read the "Migration to" notes for each version:<br /><br /><a
href="http://www.postgresql.org/docs/current/static/release-9-0.html">http://www.postgresql.org/docs/current/static/release-8-2.html</a><br
/><a
href="http://www.postgresql.org/docs/current/static/release-9-0.html">http://www.postgresql.org/docs/current/static/release-8-3.html</a><br
/><a
href="http://www.postgresql.org/docs/current/static/release-9-0.html">http://www.postgresql.org/docs/current/static/release-8-4.html</a><br
/><a
href="http://www.postgresql.org/docs/current/static/release-9-0.html">http://www.postgresql.org/docs/current/static/release-9-0.html</a><br
/><a
href="http://www.postgresql.org/docs/current/static/release-9-0.html">http://www.postgresql.org/docs/current/static/release-9-1.html</a><br
/><br/> Alternately, you could try using PostgreSQL 9.1's pg_dump on your 8.1 database, load the dump into a 9.1
instance,run your client against it and see what breaks. For simpler apps that can be a reasonable choice.<br /><br />
--<br/> Craig Ringer<br /> 

On 2012-07-10, Chris Preston <chris@thetrafalgartravel.com> wrote:
>
> Hello all,
> How far can I get to a higher version of PostgreSQL by just entering a
> command line instruction to upgrade without any major effort?

8.1.23

going to 8.2.x or later is going to need more effort, especially if any
of your code relies on any of 8.1's quirks (like comparing integers to
strings) - if you do decide to abandon 8.1.x you may as well make it a
big change and go to 9.1

-- 
⚂⚃ 100% natural