Edgewall Software

Opened 8 years ago

Closed 7 years ago

#12112 closed defect (fixed)

Duplicate column name 'priority_value' is raised in custom query with MySQL

Reported by: Jun Omae Owned by: Jun Omae
Priority: normal Milestone: 1.2
Component: query system Version: 0.12-stable
Severity: normal Keywords: mysql
Cc: Branch:
Release Notes:

Fix conflicted priority_value with the same named custom field in ticket query.

API Changes:

Query uses _priority_value as alias for priority.value in generated SQL statement.

Internal Changes:


When priority_value is defined as custom field and filter with the field, the following error is raised on MySQL with Trac 0.12-stable and 1.0-stable. No errors on SQLite and PostgreSQL.

2015-06-25 16:30:00,151 Trac[main] ERROR: Internal Server Error:
Traceback (most recent call last):
  File "/venv/trac/1.0.6.post2/lib/python2.5/site-packages/trac/web/main.py", line 534, in _dispatch_request
  File "/venv/trac/1.0.6.post2/lib/python2.5/site-packages/trac/web/main.py", line 227, in dispatch
    resp = chosen_handler.process_request(req)
  File "/venv/trac/1.0.6.post2/lib/python2.5/site-packages/trac/ticket/query.py", line 984, in process_request
    return self.display_html(req, query)
  File "/venv/trac/1.0.6.post2/lib/python2.5/site-packages/trac/ticket/query.py", line 1082, in display_html
    tickets = query.execute(req)
  File "/venv/trac/1.0.6.post2/lib/python2.5/site-packages/trac/ticket/query.py", line 301, in execute
    self.num_items = self._count(sql, args)
  File "/venv/trac/1.0.6.post2/lib/python2.5/site-packages/trac/ticket/query.py", line 282, in _count
    % sql, args)[0][0]
  File "/venv/trac/1.0.6.post2/lib/python2.5/site-packages/trac/db/api.py", line 124, in execute
    return db.execute(query, params)
  File "/venv/trac/1.0.6.post2/lib/python2.5/site-packages/trac/db/util.py", line 128, in execute
    cursor.execute(query, params if params is not None else [])
  File "/venv/trac/1.0.6.post2/lib/python2.5/site-packages/trac/db/util.py", line 72, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "/venv/py25/lib/python2.5/site-packages/MySQLdb/cursors.py", line 205, in execute
    self.errorhandler(self, exc, value)
  File "/venv/py25/lib/python2.5/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (1060, "Duplicate column name 'priority_value'")

Attachments (0)

Change History (4)

comment:1 by Jun Omae, 7 years ago

Milestone: next-stable-1.0.xnext-dev-1.1.x

Query in trac/ticket/query.py has no errors on SQLite and PostgreSQL however priority.value AS priority_value and priority_value custom field are conflicted.

We could use _priority_value instead of priority_value because name of custom field should match ^[a-zA-Z][a-zA-Z0-9_]+$. But these changes might be API changes for a user which customizes query_results.html.

  • trac/ticket/query.py

    diff --git a/trac/ticket/query.py b/trac/ticket/query.py
    index 7c3e53958..cefa5e9ad 100644
    a b class Query(object):  
    459459        sql = []
    460460        sql.append("SELECT " + ",".join(['t.%s AS %s' % (c, c) for c in cols
    461461                                         if c not in custom_fields]))
    462         sql.append(",priority.value AS priority_value")
     462        sql.append(",priority.value AS _priority_value")
    464464        if use_joins:
    465465            # Use LEFT OUTER JOIN for ticket_custom table
  • trac/ticket/templates/query_results.html

    diff --git a/trac/ticket/templates/query_results.html b/trac/ticket/templates/query_results.html
    index af850ec8f..9e9d15528 100644
    a b Arguments:  
    7575          <py:with vars="ticket_context = context.child('ticket', result.id)">
    7676            <py:if test="'TICKET_VIEW' in perm(ticket_context.resource)">
    78               <tr class="${'odd' if idx % 2 else 'even'} prio${result.priority_value}${
     78              <tr class="${'odd' if idx % 2 else 'even'} prio${result._priority_value}${
    7979                ' added' if 'added' in result else ''}${
    8080                ' changed' if 'changed' in result else ''}${
    8181                ' removed' if 'removed' in result else ''}">

comment:2 by Jun Omae, 7 years ago

Milestone: next-dev-1.1.x1.2
Owner: set to Jun Omae
Status: newassigned

Proposed changes in log:jomae.git@t12112.

comment:3 by Ryan J Ollos, 7 years ago

Changes look good, and tests pass after rebasing with SQLite, PostgreSQL and MySQL.

comment:4 by Jun Omae, 7 years ago

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

Thanks for the review. Committed in [14570].

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.