Edgewall Software
Modify

Opened 8 years ago

Closed 8 years ago

#11204 closed defect (worksforme)

SQL error on custom query: column reference "id" is ambiguous

Reported by: ebouaziz@… Owned by:
Priority: normal Milestone:
Component: query system Version:
Severity: normal Keywords:
Cc: ebouaziz@… Branch:
Release Notes:
API Changes:
Internal Changes:

Description

Trac version 1.1.2dev, Python 2.7.3, PostgreSQL 9.1, psycopg2 2.4.5

In the View tickets tab, run a custom query with a custom field in the filters, then in the resulting table, click on a row name on top of the row to order the results.

I get this error:

ProgrammingError: column reference "id" is ambiguous LINE 7: ...'reopened') AND (COALESCE(c."soc",'')='xyz')) OR id in (692... ^

In the logs, I get:

2013-06-12 19:29:16,924 Trac[main] ERROR: Internal Server Error:.
Traceback (most recent call last):
  File "/local/engine/trac/trac/web/main.py", line 498, in _dispatch_request
    dispatcher.dispatch(req)
  File "/local/engine/trac/trac/web/main.py", line 214, in dispatch
    resp = chosen_handler.process_request(req)
  File "/local/engine/trac/trac/ticket/query.py", line 963, in process_request
    return self.display_html(req, query)
  File "/local/engine/trac/trac/ticket/query.py", line 1069, in display_html
    tickets = query.execute(req, cached_ids=orig_list)
  File "/local/engine/trac/trac/ticket/query.py", line 303, in execute
    self.num_items = self._count(sql, args)
  File "/local/engine/trac/trac/ticket/query.py", line 284, in _count
    % sql, args)[0][0]
  File "/local/engine/trac/trac/db/api.py", line 122, in execute
    return db.execute(query, params)
  File "/local/engine/trac/trac/db/util.py", line 121, in execute
    cursor.execute(query, params)
  File "/local/engine/trac/trac/db/util.py", line 65, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
ProgrammingError: column reference "id" is ambiguous
LINE 7: ...'reopened') AND (COALESCE(c."soc",'')='xyz')) OR id in (692...
                                                             ^
ProgrammingError: column reference "id" is ambiguous

The IterableCursor object is not instanciated with a logger, so I did a ugly hack and got the offending request and args:

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,priority.value AS priority_
FROM ticket AS t
  LEFT JOIN (SELECT id,
    (SELECT c.value FROM ticket_custom c WHERE c.ticket=t.id AND c.name='soc') AS "soc"                                                                                                                                                      
    FROM ticket t) AS c ON (c.id=t.id)
  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)
WHERE (COALESCE(t.status,'') IN (%s,%s,%s) AND (COALESCE(c."soc",'')=%s)) OR id in (7013,6876,7029,3021,6875,7065,7824,6759,3326,4772)
ORDER BY COALESCE(t.component,'')='' DESC,t.component DESC,t.id) AS x

[u'assigned', u'new', u'reopened', u'xyz']

I don't know enough SQL to understand what is wrong here.

Attachments (0)

Change History (2)

comment:1 by Jun Omae, 8 years ago

It seems to be the same as comment:ticket:11140:4. The issue has been fixed in [11787].

comment:2 by Jun Omae, 8 years ago

Resolution: worksforme
Status: newclosed

Please update to latest trunk.

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The ticket will remain with no owner.
The resolution will be deleted. Next status will be 'reopened'.
to The owner will be changed from (none) 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.