Обсуждение: Re:ORDER BY

Поиск
Список
Период
Сортировка

Re:ORDER BY

От
Chris Bitmead
Дата:
>From the CVS version of a day or two ago, I'm getting errors on the
following queries, which worked from a snapshot from about a month ago.
One it is rejecting apparently valid syntax. One the backend is
crashing.



SELECT category.oid, category.title FROM category*, urllink WHERE
urllink.category=category.oid AND category.category IS NULL GROUP BY
category.title, category.oid;
pqReadData() -- backend closed the channel unexpectedly.       This probably means the backend terminated abnormally
  before or while processing the request.
 
We have lost the connection to the backend, so further processing is
impossible.  Terminating.


SELECT category.oid, category.title FROM category*, urllink WHERE
urllink.category=category.oid AND category.category IS NULL UNION SELECT
c1.oid, c1.title FROM category* c1, category* c2, urllink WHERE
c1.category IS NULL AND urllink.category = c2.oid and c1.oid =
c2.category GROUP BY c1.title UNION SELECT c1.oid, c1.title FROM
category* c1, story WHERE story.category = c1.oid ORDER BY
category.title;
ERROR:  Illegal use of aggregates or non-group column in target list

-- 
Chris Bitmead
http://www.bigfoot.com/~chris.bitmead
mailto:chris.bitmead@bigfoot.com


Re: [HACKERS] Re:ORDER BY

От
Tom Lane
Дата:
Chris Bitmead <chris.bitmead@bigfoot.com> writes:
>> From the CVS version of a day or two ago, I'm getting errors on the
> following queries, which worked from a snapshot from about a month ago.
> One it is rejecting apparently valid syntax. One the backend is
> crashing.

> SELECT category.oid, category.title FROM category*, urllink WHERE
> urllink.category=category.oid AND category.category IS NULL GROUP BY
> category.title, category.oid;
> pqReadData() -- backend closed the channel unexpectedly.
>         This probably means the backend terminated abnormally
>         before or while processing the request.
> We have lost the connection to the backend, so further processing is
> impossible.  Terminating.

Yeah, I see that too.  Will look into it.

> SELECT category.oid, category.title FROM category*, urllink WHERE
> urllink.category=category.oid AND category.category IS NULL UNION SELECT
> c1.oid, c1.title FROM category* c1, category* c2, urllink WHERE
> c1.category IS NULL AND urllink.category = c2.oid and c1.oid =
> c2.category GROUP BY c1.title UNION SELECT c1.oid, c1.title FROM
> category* c1, story WHERE story.category = c1.oid ORDER BY
> category.title;
> ERROR:  Illegal use of aggregates or non-group column in target list

This one is OK: notice you haveSELECT c1.oid, c1.title FROM ... GROUP BY c1.title
You can't select an ungrouped column in a select with GROUP BY.
Until recently, the system failed to notice this error unless there
was an aggregate function somewhere in the query --- but it catches
it now.
        regards, tom lane