Re: Problem with group by command
От | Tom Lane |
---|---|
Тема | Re: Problem with group by command |
Дата | |
Msg-id | 6058.972694237@sss.pgh.pa.us обсуждение исходный текст |
Ответ на | Problem with group by command ("Jessica Ord" <jo@uk.xo.com>) |
Список | pgsql-bugs |
"Jessica Ord" <jo@uk.xo.com> writes: > I have written a perl program which runs fine on a machine that was using > PostgreSQL 6.4.2. I setup another machine which was running PostgreSQL > 6.5.3 bundled by the Linux 6.2. > The same error message keeps on appear said that: > Invalid use of aggregates or non-group column in target list. > select username, user_index, date, status from subscribers, cellnet_mesg_log > where status != 3 and date >= '25/10/2000' and date < '01/11/2000' and > cellnet_mesg_log.user_index = subscribers.oid group by username; This statement is not valid SQL, but 6.4.* failed to detect that, and instead gave you some randomly-chosen result. 6.5 is pickier. You need to revise the query to conform to SQL specs: the SELECT output list can't refer to columns that aren't grouped by GROUP BY, except as arguments of an aggregate function like MIN() or MAX(). The reason is that GROUP BY implies that only one output tuple will be produced per distinct value of the group-by column(s). There might be more than one input tuple, with different values of user_index, date, etc, for each value of username --- in which case, what output do you expect to get? It's not well-defined. regards, tom lane
В списке pgsql-bugs по дате отправления: