"OperationalError: ambiguous column name: id" ordering a custom query including more than one custom field

[mdraghi@…]

Trac produces an "OperationalError: ambiguous column name: id" when ordering the results of a custom query which includes at least two custom fields as report columns.

How To Reproduce

1. Create a project with at least 2 custom fields (the type of the custom fields is irrelevant) and some tickets
2. Create a New Custom Query
   • Filters: seems to be irrelevant (leave the defaults)
   • Columns: tick at least two (2) custom fields
3. Update the results
4. Click the Ticket column header to reorder the results

Trac fails with an internal error:

OperationalError: ambiguous column name: id

The error cannot be reproduced if there's just one (1) custom fields selected (or none).

Python Traceback

Most recent call last:

File "/opt/test-trac/lib/python2.7/site-packages/trac/web/main.py", line 647, in _dispatch_request
  dispatcher.dispatch(req)
File "/opt/test-trac/lib/python2.7/site-packages/trac/web/main.py", line 248, in dispatch
  resp = chosen_handler.process_request(req)
File "/opt/test-trac/lib/python2.7/site-packages/trac/ticket/query.py", line 1016, in process_request
  return self.display_html(req, query)
File "/opt/test-trac/lib/python2.7/site-packages/trac/ticket/query.py", line 1122, in display_html
  tickets = query.execute(req, cached_ids=orig_list)
File "/opt/test-trac/lib/python2.7/site-packages/trac/ticket/query.py", line 303, in execute
  self.num_items = self._count(sql, args)
File "/opt/test-trac/lib/python2.7/site-packages/trac/ticket/query.py", line 284, in _count
  % sql, args)[0][0]
File "/opt/test-trac/lib/python2.7/site-packages/trac/db/api.py", line 122, in execute
  return db.execute(query, params)
File "/opt/test-trac/lib/python2.7/site-packages/trac/db/util.py", line 128, in execute
  cursor.execute(query, params if params is not None else [])
File "/opt/test-trac/lib/python2.7/site-packages/trac/db/util.py", line 61, in execute
  r = self.cursor.execute(sql_escape_percent(sql), args)
File "/opt/test-trac/lib/python2.7/site-packages/trac/db/sqlite_backend.py", line 88, in execute
  result = PyFormatCursor.execute(self, *args)
File "/opt/test-trac/lib/python2.7/site-packages/trac/db/sqlite_backend.py", line 64, in execute
  args or [])
File "/opt/test-trac/lib/python2.7/site-packages/trac/db/sqlite_backend.py", line 55, in _rollback_on_error
  return function(self, *args, **kwargs)

System Information

• OS: Ubuntu 12.04.5 LTS
• Python 2.7.3
• Database backend: SQLite
• Trac 1.2.3

Diagnostic (and possible fix?)

Here's the generated SQL:

2018-08-13 16:09:03,346 Trac[util] DEBUG: SQL: 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,t.owner AS owner,priority.value AS _priority_value,c.`estimated_effort` AS `estimated_effort`,c.`query_test` AS `query_test`
FROM ticket AS t
  LEFT OUTER JOIN (SELECT
    ticket AS id,
    MAX(CASE WHEN name='estimated_effort' THEN value END) AS `estimated_effort`,
    MAX(CASE WHEN name='query_test' THEN value END) AS `query_test`
    FROM ticket_custom AS tc
    WHERE name IN ('estimated_effort','query_test')
    GROUP BY tc.ticket) AS c ON c.id=t.id
  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
WHERE ((COALESCE(t.owner,'')=%s) AND COALESCE(t.status,'') IN (%s,%s,%s,%s,%s,%s,%s)) OR id in (112,113,129,200,206,454,569,1242,1461,2088,2089,2090,2091,2092,2093,2094,2095,2096,2097,2098,2099,2101,2102,2103,2104,2105,2106,2107,2108,2109,2110,2111,2112,2138,8,2141,2145,2146,2147,2131,2133,2134,2137)
ORDER BY COALESCE(t.id,0)=0,t.id) AS x
2018-08-13 16:09:03,346 Trac[util] DEBUG: args: [u'mdraghi', u'in progress', u'new', u'next release', u'reopened', u'testing', u'to do', u'waiting']                    

The ambiguous column seems to be the id that follows the OR operator to include the ticket IDs:

WHERE ((COALESCE(t.owner,'')=%s) AND COALESCE(t.status,'') IN (%s,%s,%s,%s,%s,%s,%s)) OR id in (112,113, /* edited for brevity */)

If we add a t alias to that column, the error goes away:

WHERE ((COALESCE(t.owner,'')=%s) AND COALESCE(t.status,'') IN (%s,%s,%s,%s,%s,%s,%s)) OR t.id in (112,113, /* edited for brevity */)

This seems to be in trac/ticket/query.py, in the get_clause_sql method, lines 646-652:

           if clauses:
                sql.append("\nWHERE ")
                sql.append(" OR ".join('(%s)' % c for c in clauses))
                if cached_ids:
                    sql.append(" OR ")
                    sql.append("id in (%s)" %
                               (','.join(str(id) for id in cached_ids)))

[Jun Omae]

Thanks for the investigation. That is a regression introduced in #12967. Reproduced the issue on demo-1.0 (using PostgreSQL).

Patch:

trac/ticket/query.py

@@ -654 +654 @@
             sql.append(" OR ".join('(%s)' % c for c in clauses))
             if cached_ids:
                 sql.append(" OR ")
-                sql.append("id in (%s)" %
+                sql.append("t.id IN (%s)" %
                            (','.join([str(id) for id in cached_ids])))
 
         sql.append("\nORDER BY ")

I'll push the patch with unit tests.

[Jun Omae]

Proposed changes in [26786c23f/jomae.git].

[sandeep.nadkarni@…]

We are also facing same issue. even if we try to sort the report it gives same error.

CentOS 7.5 Trac 1.2.3 Python 2.7.5

[Jun Omae]

Replying to sandeep.nadkarni@…:

We are also facing same issue. even if we try to sort the report it gives same error.

If an error is raised from report view (/report/{id}), that is an issue of your defined SQL in the report view, not the same issue.

[Ryan J Ollos]

Looks good. I can create a new release once the change is pushed.

[Jun Omae]

Thanks for the reviewing. Committed in [16818-16820].