OperationalError: ambiguous column name: id

[Ryan J Ollos]

How to Reproduce

While doing a POST operation on /newticket, Trac issued an internal error.

(please provide additional details here)

Request parameters:

{'__FORM_TOKEN': u'5e5bc52cee75efac19dd14a7',
 'field_blockedby': u'',
 'field_blocking': u'',
 'field_cc': u'',
 'field_component': u'component1',
 'field_description': u'',
 'field_due_assign': u'',
 'field_due_close': u'',
 'field_estimatedhours': u'',
 'field_keywords': u'',
 'field_milestone': u'',
 'field_owner': u'user1',
 'field_parents': u'',
 'field_priority': u'major',
 'field_reporter': u'user1',
 'field_summary': u'fsadfdasfsad',
 'field_totalhours': u'',
 'field_type': u'defect',
 'field_version': u'',
 'submit': u'Create ticket'}

User agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.17 (KHTML, like Gecko) Chrome/24.0.1312.68 Safari/537.17

System Information

Trac	1.0.2dev
Genshi	0.6 (without speedups)
pysqlite	2.6.0
Python	2.7.3 (default, Jan 2 2013, 13:56:14) [GCC 4.7.2]
pytz	2012c
setuptools	0.6
SQLite	3.7.13
jQuery	1.7.2

Enabled Plugins

Trac-jsGantt

0.10

Python Traceback

Traceback (most recent call last):
  File "/home/user/Workspace/th10966/trac.git/trac/web/main.py", line 497, in _dispatch_request
    dispatcher.dispatch(req)
  File "/home/user/Workspace/th10966/trac.git/trac/web/main.py", line 214, in dispatch
    resp = chosen_handler.process_request(req)
  File "/home/user/Workspace/th10966/trac.git/trac/ticket/web_ui.py", line 180, in process_request
    return self._process_newticket_request(req)
  File "/home/user/Workspace/th10966/trac.git/trac/ticket/web_ui.py", line 465, in _process_newticket_request
    self._do_create(req, ticket) # (redirected if successful)
  File "/home/user/Workspace/th10966/trac.git/trac/ticket/web_ui.py", line 1289, in _do_create
    ticket.insert()
  File "/home/user/Workspace/th10966/trac.git/trac/ticket/model.py", line 256, in insert
    listener.ticket_created(self)
  File "/home/user/Workspace/trac-hacks.git/tracjsganttplugin/0.11/tracjsgantt/tracpm.py", line 2943, in ticket_created
    self.rescheduleTickets(ticket, {})
  File "/home/user/Workspace/trac-hacks.git/tracjsganttplugin/0.11/tracjsgantt/tracpm.py", line 2753, in rescheduleTickets
    tickets = self.queryTickets(ids)
  File "/home/user/Workspace/trac-hacks.git/tracjsganttplugin/0.11/tracjsgantt/tracpm.py", line 2549, in queryTickets
    return self.pm.query(options, set())
  File "/home/user/Workspace/trac-hacks.git/tracjsganttplugin/0.11/tracjsgantt/tracpm.py", line 1193, in query
    tickets = query.execute(req)
  File "/home/user/Workspace/th10966/trac.git/trac/ticket/query.py", line 302, in execute
    self.num_items = self._count(sql, args)
  File "/home/user/Workspace/th10966/trac.git/trac/ticket/query.py", line 283, in _count
    % sql, args)[0][0]
  File "/home/user/Workspace/th10966/trac.git/trac/db/api.py", line 122, in execute
    return db.execute(query, params)
  File "/home/user/Workspace/th10966/trac.git/trac/db/util.py", line 121, in execute
    cursor.execute(query, params)
  File "/home/user/Workspace/th10966/trac.git/trac/db/util.py", line 65, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "/home/user/Workspace/th10966/trac.git/trac/db/sqlite_backend.py", line 78, in execute
    result = PyFormatCursor.execute(self, *args)
  File "/home/user/Workspace/th10966/trac.git/trac/db/sqlite_backend.py", line 56, in execute
    args or [])
  File "/home/user/Workspace/th10966/trac.git/trac/db/sqlite_backend.py", line 48, in _rollback_on_error
    return function(self, *args, **kwargs)
OperationalError: ambiguous column name: id

[Ryan J Ollos]

It looks like, at least in Trac 1.0.2dev, id should be replaced with ticketid.

[Jun Omae]

Oh... The changes, ​trac:r11753 and ​trac:r11754, may break the compatibilities in Query module.

Ryan, could you please apply the following patch and retry?

trac/ticket/query.py

@@ -461 +461 @@
 
         # Join with ticket_custom table as necessary
         if any(k in custom_fields for k in cols):
-            sql.append("\n  LEFT JOIN (SELECT id")
+            sql.append("\n  LEFT JOIN (SELECT id AS ticket")
             sql.extend(",\n    (SELECT c.value FROM ticket_custom c "
                        "WHERE c.ticket=t.id AND c.name='%s') AS %s"
                        % (k, db.quote(k))
                        for k in cols if k in custom_fields)
-            sql.append("\n    FROM ticket t) AS c ON (c.id=t.id)")
+            sql.append("\n    FROM ticket t) AS c ON (c.ticket=t.id)")
 
         # Join with the enum table for proper sorting
         for col in [c for c in enum_columns

[Jun Omae]

Reproduced without this plugin. The issue is a Trac issue. See ​trac:ticket:11140#comment:4.

[Ryan J Ollos]

Thanks, Jun. I'll track that ticket.