Обсуждение: GROUP BY problem with 6.5

Hello!

I am using PostgreSQL 6.5 on a Debian/Linux 2.2 system. It all seems to
be just fine, but I have got into a problem with GROUP BY.

I have a query which worked OK until now (with all the previous versions
of PostgreSQL) but now it returns the following error message:

'Illegal use of aggregates or non-group column in target list'

So what has been changed at GROUP BY in 6.5?


Here is the whole query if you wish to take a look at it:

SELECT szl.*, sz.szaml_cim, sz.szaml_varos, sz.szaml_irsz, sz.szamlnev,
sz.programcsomagid, p.tulajdonosid
FROM dijbefizetesek szl, szerzodesek sz, programcsomagok p
WHERE (sz.szerzodesid = szl.szerzodesid)
AND (p.p_azonosito = sz.programcsomagid) AND (szl.osszeg > 0)
AND (szl.trdate >= 19990531) AND (szl.trdate <= 19990531)
AND (szl.trnum >= 1) AND (szl.trnum <= 1000) GROUP BY trdate, trnum


Thanking you in advance!

Regards,
Tamas

I believe they fixed GROUP BY to enforce correct usage. I'd expect that
in your query below GROUP BY never did anything useful because you
didn't have any agregate columns.

Tamas Nyitrai wrote:
>
> Hello!
>
> I am using PostgreSQL 6.5 on a Debian/Linux 2.2 system. It all seems to
> be just fine, but I have got into a problem with GROUP BY.
>
> I have a query which worked OK until now (with all the previous versions
> of PostgreSQL) but now it returns the following error message:
>
> 'Illegal use of aggregates or non-group column in target list'
>
> So what has been changed at GROUP BY in 6.5?
>
> Here is the whole query if you wish to take a look at it:
>
> SELECT szl.*, sz.szaml_cim, sz.szaml_varos, sz.szaml_irsz, sz.szamlnev,
> sz.programcsomagid, p.tulajdonosid
> FROM dijbefizetesek szl, szerzodesek sz, programcsomagok p
> WHERE (sz.szerzodesid = szl.szerzodesid)
> AND (p.p_azonosito = sz.programcsomagid) AND (szl.osszeg > 0)
> AND (szl.trdate >= 19990531) AND (szl.trdate <= 19990531)
> AND (szl.trnum >= 1) AND (szl.trnum <= 1000) GROUP BY trdate, trnum
>
> Thanking you in advance!
>
> Regards,
> Tamas

> I have a query which worked OK until now (with all the previous versions
> of PostgreSQL) but now it returns the following error message:
>
> 'Illegal use of aggregates or non-group column in target list'
>
> So what has been changed at GROUP BY in 6.5?
>
>

The target list can contain only aggregates(ie sum), or grouped by
columns.


--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

On Sun, Jul 04, 1999 at 11:19:24PM -0400, Bruce Momjian wrote:
> > I have a query which worked OK until now (with all the previous versions
> > of PostgreSQL) but now it returns the following error message:
> >
> > 'Illegal use of aggregates or non-group column in target list'
> >
> > So what has been changed at GROUP BY in 6.5?
> >
> >
>
> The target list can contain only aggregates(ie sum), or grouped by
> columns.
>

As per the SQL spec. I believe prior to 6.5, postgresql wasn't picky, and
let you get away with having other fields returned. The problem with that
is you got essentially a _random_ choice of value for that field.

So, the short answer is yes, it used to work, no PG's not broken, your SQL
is. Yes, that sucks, but no-one is making you upgrade. If you don't like it,
you can have double your money back ;-) If you really need it to work for
some insane reason, I bet you could throw money at postgresql.com and they
could come up with patches to put the old behavior back, for sufficently
large amounts of money.

Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

On Sun, 4 Jul 1999, Ross J. Reedstrom wrote:

> So, the short answer is yes, it used to work, no PG's not broken, your SQL
> is. Yes, that sucks, but no-one is making you upgrade. If you don't like it,
> you can have double your money back ;-)

Hi Ross,

I am well aware of that I have a 'lacking knowledge' of SQL and I have no
intention to blame it on PostgreSQL. So please don't worry about that. I
was not complaining, I just wished to to know what to do now. ;-))

Regards,
Tamas

On Mon, Jul 05, 1999 at 02:01:11PM +0200, Tamas Nyitrai wrote:

<snipped a gentle rebuff to my earlier slightly high-handed post>

Tamas -
Sorry didn't mean to come across sounding short. Bruce's answer was just a
little ... succinct, since you had mentioned you had queries that used to
work, I thought I should mention _why_ the behavior changed. Then I got
carried away, imaging how people might repond to that.

As to lack of SQL knowledge in this regard, let me suggest you analyze
the queries that you are doing group by on, and only return the fields
you actually need grouped on (and the aggregates, of course.) in particular,
look out for constructs like 'SELECT foo.* ...' that wildcard is almost
never appropriate for a GROUP BY query.

HTH,
Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005