topic and message Trac links fail on postgresql

[Kevin Thomas]

The topic and message Trac links don't work on an installation of Trac using Postgresql. Any page that contains these links generates the following error:

Traceback (most recent call last):
  File "C:\Python24\lib\site-packages\trac\web\main.py", line 387, in dispatch_request
    dispatcher.dispatch(req)
  File "C:\Python24\lib\site-packages\trac\web\main.py", line 237, in dispatch
    resp = chosen_handler.process_request(req)
  File "C:\Python24\lib\site-packages\trac\ticket\web_ui.py", line 302, in process_request
    get_reporter_id(req, 'author'))
  File "C:\Python24\lib\site-packages\trac\ticket\web_ui.py", line 625, in _insert_ticket_data
    change['comment'] = wiki_to_html(comment, self.env, req, db)
  File "C:\Python24\lib\site-packages\trac\wiki\formatter.py", line 999, in wiki_to_html
    Formatter(env, req, absurls, db).format(wikitext, out, escape_newlines)
  File "C:\Python24\lib\site-packages\trac\wiki\formatter.py", line 822, in format
    result = re.sub(self.wiki.rules, self.replace, line)
  File "C:\Python24\Lib\sre.py", line 142, in sub
    return _compile(pattern, 0).sub(repl, string, count)
  File "C:\Python24\lib\site-packages\trac\wiki\formatter.py", line 768, in replace
    replacement = self.handle_match(fullmatch)
  File "C:\Python24\lib\site-packages\trac\wiki\formatter.py", line 764, in handle_match
    return internal_handler(match, fullmatch)
  File "C:\Python24\lib\site-packages\trac\wiki\formatter.py", line 342, in _shref_formatter
    return self._make_link(ns, target, match, match)
  File "C:\Python24\lib\site-packages\trac\wiki\formatter.py", line 369, in _make_link
    escape(label, False))
  File "build\bdist.win32\egg\tracdiscussion\wiki.py", line 111, in _discussion_link
  File "C:\Python24\lib\site-packages\trac\db\util.py", line 50, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "C:\Python24\lib\site-packages\trac\db\util.py", line 50, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
ProgrammingError: column "forum.subject" must appear in the GROUP BY clause or be used in an aggregate function

I've given this a high priority and severity because this bug causes all pages containing these links to crash, and if these Trac links are used in a ticket comment, then there is no way to view that ticket again.

[Kevin Thomas]

The problem appears to be in the following SQL for generating the topic link in source:discussionplugin/0.10/tracdiscussion/wiki.py​:

SELECT t.forum, f.subject, t.subject FROM topic t
LEFT JOIN (SELECT subject, id FROM forum GROUP BY id) f
ON t.forum = f.id WHERE t.id = %s

Postgresql doesn't like the GROUP BY clause unless there is a composite function on all other fields in the SELECT clause.

Is there a good reason as to why this couldn't be simplified to:

SELECT t.forum, f.subject, t.subject FROM topic t
LEFT JOIN forum f
ON t.forum = f.id WHERE t.id = %s

?

This would work across all database back ends.

There is a similar problem in the SQL for generating the message link.

[Radek Bartoň]

Please check if 2236 works on your PostreSQL installation, it does on mine.

[Kevin Thomas]

Yes, the latest download works fine.

Thanks very much for fixing this so quickly- that was an impressive response time!:-)