Custom Ticket Field generates error in Query view

[rcasey@…]

How to Reproduce

While doing a GET operation on /query, Trac issued an internal error.

Details

We have a custom ticket field, primary that we store related ticket #s in.

When you are in the custom query mode, and either attempt to filter on the custom field or include the custom field in the query, an error is generated.

Steps to reproduce:

1. Add a custom field for ticket:

   [ticket-custom]
   primary = text
   primary.lable = Primary Ticket
   

2. Click View Tickets
3. Custom Query
4. Select Primary as a column to include
5. click update and the error occurs.

Request parameters:

{'col': [u'id',
         u'summary',
         u'owner',
         u'type',
         u'priority',
         u'component',
         u'version',
         u'primary'],
 'group': u'resolution',
 'milestone': u'1.2.1',
 'order': u'priority',
 'status': u'closed'}

User Agent was: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/525.19 (KHTML, like Gecko) Chrome/1.0.154.36 Safari/525.19

System Information

Trac	0.11.2.1
Python	2.4.3 (#1, May 24 2008, 13:47:28) [GCC 4.1.2 20070626 (Red Hat 4.1.2-14)]
setuptools	0.6c7
MySQL	server: "5.0.45", client: "5.0.45", thread-safe: 0
MySQLdb	1.2.1
Genshi	0.5.1
mod_python	3.2.8
Subversion	1.5.5 (r34862)
jQuery:	1.2.6

Python Traceback

Traceback (most recent call last):
  File "/usr/lib/python2.4/site-packages/trac/web/main.py", line 432, in _dispatch_request
    dispatcher.dispatch(req)
  File "/usr/lib/python2.4/site-packages/trac/web/main.py", line 204, in dispatch
    resp = chosen_handler.process_request(req)
  File "/usr/lib/python2.4/site-packages/trac/ticket/query.py", line 823, in process_request
    return self.display_html(req, query)
  File "/usr/lib/python2.4/site-packages/trac/ticket/query.py", line 876, in display_html
    tickets = query.execute(req, db)
  File "/usr/lib/python2.4/site-packages/trac/ticket/query.py", line 261, in execute
    self.num_items = self._count(sql, args, db)
  File "/usr/lib/python2.4/site-packages/trac/ticket/query.py", line 246, in _count
    cursor.execute(count_sql, args);
  File "/usr/lib/python2.4/site-packages/trac/db/util.py", line 50, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "/usr/lib/python2.4/site-packages/MySQLdb/cursors.py", line 163, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.4/site-packages/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'primary\nFROM ticket AS t\n  LEFT OUTER JOIN ticket_custom AS primary ON (id=prima' at line 1")

[Christian Boos]

primary is SQL reserved word, so we can't use it here as a table alias. We recently added a list of forbidden names for custom fields, but I'm not sure it's a good idea to add the potentially long list of reserved SQL words there (for the 3 SQL backends we support)…

Instead we should alias custom field names in a way which can't possibly clash, e.g. using AS f_primary instead of AS primary here.

[Anders Kaseorg <andersk@…>]

This is the same as #8987, fixed in r9062.