Invalid aggregate query

[x@…]

Saw this in my logs:

Apr  6 16:29:53 ops5 postgres[17289]: [2-1] ERROR:  column "topic.subject" must appear in the GROUP BY clause or be used in an aggregate function
Apr  6 16:29:53 ops5 postgres[17289]: [2-2] STATEMENT:  SELECT m.id, m.forum, m.topic, m.time, m.author, m.body, t.subject FROM message m LEFT JOIN (SELECT subject, id FROM
Apr  6 16:29:53 ops5 postgres[17289]: [2-3]  topic GROUP BY id) t ON t.id = m.topic WHERE body LIKE '%subversion%'

Appears to be coming from:

sql = "SELECT m.id, m.forum, m.topic, m.time, m.author, m.body," \
" t.subject FROM message m LEFT JOIN (SELECT subject, id FROM" \
" topic GROUP BY id) t ON t.id = m.topic WHERE body LIKE '%%%s%%'" \
 % (query)

That subquery is not valid usage of a group by; ie, which subject should it be yielding for each id? Using min() or max() might be a reasonable stop gap. However, AFA Postgres is concerned, I think DISTINCT ON () would be the usual way to go. Of course, I'm not sure of DISTINCT ON's portability(dont use mysql or sqlite).

[Noah Kantrowitz]

Fixing formatting

[Radek Bartoň]

I'm not sure why there was GROUP BY anyway, maybe piece of some orphaned code. Should be fixed with changeset 2166.