Edgewall Software

Opened 3 years ago

Closed 3 years ago

Last modified 3 years ago

#13068 closed defect (fixed)

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

Reported by: mdraghi@… Owned by: Jun Omae
Priority: normal Milestone: 1.0.18
Component: query system Version: 1.2.3
Severity: major Keywords:
Cc: Branch:
Release Notes:

Fix ambiguous column name, regression in Query module from #12967.

API Changes:
Internal Changes:


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
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
    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)))

Attachments (0)

Change History (12)

comment:1 by Jun Omae, 3 years ago

Milestone: 1.0.18
Owner: set to Jun Omae
Status: newassigned

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


  • trac/ticket/query.py

    diff --git a/trac/ticket/query.py b/trac/ticket/query.py
    index 68c0328ff..b967af5d8 100644
    a b class Query(object):  
    654654            sql.append(" OR ".join('(%s)' % c for c in clauses))
    655655            if cached_ids:
    656656                sql.append(" OR ")
    657                 sql.append("id in (%s)" %
     657                sql.append("t.id IN (%s)" %
    658658                           (','.join([str(id) for id in cached_ids])))
    660660        sql.append("\nORDER BY ")

I'll push the patch with unit tests.

comment:2 by Jun Omae, 3 years ago

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

comment:3 by sandeep.nadkarni@…, 3 years ago

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

in reply to:  3 comment:4 by Jun Omae, 3 years ago

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.

comment:5 by Ryan J Ollos, 3 years ago

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

comment:6 by Jun Omae, 3 years ago

Release Notes: modified (diff)
Resolution: fixed
Status: assignedclosed

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

in reply to:  6 comment:7 by Jun Omae, 3 years ago

Replying to Jun Omae:

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

Oh…, fixed wrong tests in [16821-16823].

comment:8 by n.scheer@…, 3 years ago


I applied the patch to our 1.2.3 version manually. After restarting apache, I'm stuck with a "database locked" error for every action that writes the database. Any ideas?

in reply to:  8 comment:9 by Jun Omae, 3 years ago

Replying to n.scheer@…:

After restarting apache, I'm stuck with a "database locked" error for every action that writes the database. Any ideas?

See MostFrequentDuplicates#DatabaseIsLocked. Also, use the MailingList for support questions.

comment:10 by mikko.rantalainen@…, 3 years ago

Seems to happen in Trac 1.2.3 with combination of multiple custom fields, "OR" and postgres backend. Is this fixed in 1.0.x branch only?

comment:11 by anonymous, 3 years ago

The fix in comment:6 was applied in three branches for release in 1.3.4 (trunk branch), 1.2.4 (1.2-stable branch) and 1.0.18 (1.0-stable branch), so Trac 1.2.3 does not yet have the fix.

(The reason that the "Version:" field of this ticket is set to 1.2.3 is just that the problem was first encountered in Trac 1.2.3.)

comment:12 by anonymous, 3 years ago

Thanks for adding it to the 1.0-stable branch, I have lots of plugins that prevent me from upgraded to 1.2 branch.

Modify Ticket

Change Properties
Set your email in Preferences
as closed The owner will remain Jun Omae.
The resolution will be deleted. Next status will be 'reopened'.
to The owner will be changed from Jun Omae to the specified user.

Add Comment

E-mail address and name can be saved in the Preferences .
Note: See TracTickets for help on using tickets.