Edgewall Software
Modify

Opened 15 months ago

Closed 14 months ago

Last modified 8 months 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:

Description

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

Attachments (0)

Change History (12)

comment:1 by Jun Omae, 15 months 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).

Patch:

  • 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])))
    659659
    660660        sql.append("\nORDER BY ")

I'll push the patch with unit tests.

comment:2 by Jun Omae, 15 months ago

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

comment:3 by sandeep.nadkarni@…, 15 months 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, 15 months 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, 14 months ago

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

comment:6 by Jun Omae, 14 months 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, 14 months 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@…, 11 months ago

Hi,

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, 11 months 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@…, 8 months 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, 8 months 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, 8 months 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
Action
as closed The owner will remain Jun Omae.
The resolution will be deleted. Next status will be 'reopened'.
to as closed 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.