Re: [HACKERS] Re:ORDER BY

Chris Bitmead <chris(dot)bitmead(at)bigfoot(dot)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 have
SELECT 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