SQL error on custom query: column reference "id" is ambiguous

[ebouaziz@…]

Trac version 1.1.2dev, Python 2.7.3, PostgreSQL 9.1, psycopg2 2.4.5

In the View tickets tab, run a custom query with a custom field in the filters, then in the resulting table, click on a row name on top of the row to order the results.

I get this error:

ProgrammingError: column reference "id" is ambiguous LINE 7: ...'reopened') AND (COALESCE(c."soc",'')='xyz')) OR id in (692... ^

In the logs, I get:

2013-06-12 19:29:16,924 Trac[main] ERROR: Internal Server Error:.
Traceback (most recent call last):
  File "/local/engine/trac/trac/web/main.py", line 498, in _dispatch_request
    dispatcher.dispatch(req)
  File "/local/engine/trac/trac/web/main.py", line 214, in dispatch
    resp = chosen_handler.process_request(req)
  File "/local/engine/trac/trac/ticket/query.py", line 963, in process_request
    return self.display_html(req, query)
  File "/local/engine/trac/trac/ticket/query.py", line 1069, in display_html
    tickets = query.execute(req, cached_ids=orig_list)
  File "/local/engine/trac/trac/ticket/query.py", line 303, in execute
    self.num_items = self._count(sql, args)
  File "/local/engine/trac/trac/ticket/query.py", line 284, in _count
    % sql, args)[0][0]
  File "/local/engine/trac/trac/db/api.py", line 122, in execute
    return db.execute(query, params)
  File "/local/engine/trac/trac/db/util.py", line 121, in execute
    cursor.execute(query, params)
  File "/local/engine/trac/trac/db/util.py", line 65, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
ProgrammingError: column reference "id" is ambiguous
LINE 7: ...'reopened') AND (COALESCE(c."soc",'')='xyz')) OR id in (692...
                                                             ^
ProgrammingError: column reference "id" is ambiguous

The IterableCursor object is not instanciated with a logger, so I did a ugly hack and got the offending request and args:

SELECT COUNT(*) FROM (SELECT t.id AS id,t.summary AS summary,t.status AS status,t.type AS type,t.priority AS priority,t.milestone AS milestone,t.component AS component,t.time AS time,t.changetime AS changetime,priority.value AS priority_
FROM ticket AS t
  LEFT JOIN (SELECT id,
    (SELECT c.value FROM ticket_custom c WHERE c.ticket=t.id AND c.name='soc') AS "soc"                                                                                                                                                      
    FROM ticket t) AS c ON (c.id=t.id)
  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)
WHERE (COALESCE(t.status,'') IN (%s,%s,%s) AND (COALESCE(c."soc",'')=%s)) OR id in (7013,6876,7029,3021,6875,7065,7824,6759,3326,4772)
ORDER BY COALESCE(t.component,'')='' DESC,t.component DESC,t.id) AS x

[u'assigned', u'new', u'reopened', u'xyz']

I don't know enough SQL to understand what is wrong here.

[Jun Omae]

It seems to be the same as comment:ticket:11140:4. The issue has been fixed in [11787].

[Jun Omae]

Please update to latest trunk.