Edgewall Software

Opened 7 years ago

Closed 7 years ago

#12112 closed defect (fixed)

Duplicate column name 'priority_value' is raised in custom query with MySQL — at Version 4

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:

Description

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
    dispatcher.dispatch(req)
  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'")

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")
    463463
    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)">
    7777
    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].

Note: See TracTickets for help on using tickets.