OperationalError: ambiguous column name: priority

[vanaja.konduru@…]

How to Reproduce

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

(please provide additional details here)

Request parameters:

{'col': [u'id', u'summary', u'type', u'priority', u'component'],
 'group': u'milestone',
 'order': u'priority',
 'owner': u'!ilya.gelle@ahamobile.com',
 'report': u'22',
 'status': u'testing'}

User Agent was: Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv:1.8.1.20) Gecko/20081217 Firefox/2.0.0.20 (.NET CLR 3.5.30729)

System Information

Trac	0.11.5stable-r8210
Python	2.5.2 (r252:60911, Oct 5 2008, 19:42:18) [GCC 4.3.2]
setuptools	0.6c9
SQLite	3.5.9
pysqlite	2.4.1
Genshi	0.5
mod_python	3.3.1
Pygments	0.10
Subversion	1.5.1 (r32289)
jQuery:	1.2.6

Python Traceback

Traceback (most recent call last):
  File "/usr/lib/python2.5/site-packages/Trac-0.11.5stable_r8210-py2.5.egg/trac/web/main.py", line 444, in _dispatch_request
    dispatcher.dispatch(req)
  File "/usr/lib/python2.5/site-packages/Trac-0.11.5stable_r8210-py2.5.egg/trac/web/main.py", line 205, in dispatch
    resp = chosen_handler.process_request(req)
  File "/usr/lib/python2.5/site-packages/Trac-0.11.5stable_r8210-py2.5.egg/trac/ticket/query.py", line 834, in process_request
    return self.display_html(req, query)
  File "/usr/lib/python2.5/site-packages/Trac-0.11.5stable_r8210-py2.5.egg/trac/ticket/query.py", line 887, in display_html
    tickets = query.execute(req, db)
  File "/usr/lib/python2.5/site-packages/Trac-0.11.5stable_r8210-py2.5.egg/trac/ticket/query.py", line 264, in execute
    self.num_items = self._count(sql, args, db)
  File "/usr/lib/python2.5/site-packages/Trac-0.11.5stable_r8210-py2.5.egg/trac/ticket/query.py", line 249, in _count
    cursor.execute(count_sql, args);
  File "/usr/lib/python2.5/site-packages/Trac-0.11.5stable_r8210-py2.5.egg/trac/db/util.py", line 50, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "/usr/lib/python2.5/site-packages/Trac-0.11.5stable_r8210-py2.5.egg/trac/db/sqlite_backend.py", line 58, in execute
    args or [])
  File "/usr/lib/python2.5/site-packages/Trac-0.11.5stable_r8210-py2.5.egg/trac/db/sqlite_backend.py", line 50, in _rollback_on_error
    return function(self, *args, **kwargs)
OperationalError: ambiguous column name: priority

[Christian Boos]

You're trying to show the tickets from a saved report ({22}).

Please show us the SQL source of that report, so that we can try to reproduce the issue.

[Christian Boos]

You're trying to show the tickets from a saved report ({22}).

Please show us the SQL source of that report, so that we can try to reproduce the issue.

I wanted to say, the content of the saved query (which is using the TicketQuery syntax, not SQL, of course).

If you upgrade your version of 0.11-stable to at least r8213, you could also enable SQL statement logging, which would allow us to see the problematic SQL statement generated from the saved query.

[anonymous]

If you upgrade your version of 0.11-stable to at least r8213, you could also enable SQL statement logging, which would allow us to see the problematic SQL statement generated from the saved query.

How could I enable SQL Querry? Here is the query which i got from sqlite:

select * from report where id = 22;

22

QA - All tickets In Testing stage|query:?status=testing

& group=milestone & order=priority & report=22 & col=id & col=summary & col=type & col=priority & col=component & owner=%21ilya.gelle%40ahamobile.com|

Let me explain how did i get this issue;

1. I upgraded from .11 to .11-stable r8213 then all of my custom queries are not working which are group results by mile stone. Not able to do any new queries which are group results by mile stone because of this issue. In 0.11 I did not see this issue.

[Christian Boos]

The following query:

query:?status=testing & group=milestone & order=priority & report=22 & col=id & col=summary & col=type & col=priority & col=component & owner=%21ilya.gelle%40ahamobile.com

worked for me (the spaces above need to be replaced by newlines).

I still don't see how such a query could trigger a OperationalError: ambiguous column name: priority error, that query nevertheless won't work because of the embedded spaces.

Try using the following setting in your TracIni [trac] debug_sql = true (upgrade to the release 0.11.5 version, or pick 0.11.6dev from the repository).

[Christian Boos]

No feedback in more than 3 months, closing.

[N@…>]

I am getting this same error. It happens if I try to group or sort by milestone.

My installation:

System Information:

Trac	0.12
Genshi	0.6
mod_wsgi	2.5 (WSGIProcessGroup WSGIApplicationGroup %{GLOBAL})
Pygments	0.10
pysqlite	2.4.1
Python	2.5.2 (r252:60911, Jan 24 2010, 15:24:24) [GCC 4.3.2]
pytz	2008c
setuptools	0.6c12
SQLite	3.5.9
Subversion	1.5.1 (r32289)
jQuery	1.4.2

Enabled Plugins:

BatchModify	0.8.0-trac0.12	/var/lib/trac/project/plugins/BatchModify-0.8.0_trac0.12-py2.5.egg
BreadCrumbsNavPlugin	0.1	/usr/lib/python2.5/site-packages/BreadCrumbsNavPlugin-0.1-py2.5.egg
RepositoryHookSystem	0.1.1	/usr/lib/python2.5/site-packages/RepositoryHookSystem-0.1.1-py2.5.egg
TracAccountManager	0.2.1dev-r7737	/var/lib/trac/project/plugins/TracAccountManager-0.2.1dev_r7737-py2.5.egg

The SQL that prompted the error:

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,t.owner AS owner,
  priority.value AS priority_value
FROM ticket AS t
  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' 
    AND priority.name=priority)
  LEFT OUTER JOIN milestone ON (milestone.name=milestone)
WHERE ((COALESCE(t.status,'')!=%s) AND (COALESCE(t.owner,'')=%s))
ORDER BY COALESCE(t.milestone,'')='',COALESCE(milestone.completed,0)=0,
  milestone.completed,COALESCE(milestone.due,0)=0,
  milestone.due,t.milestone,t.id) AS foo

Just looking at it, it seems that there is either a missing open bracket or a spurious close bracket.

[Remy Blank]

The priority appears in the main SELECT and in the first LEFT OUTER JOIN. The same applies to milestone. It's a bit strange that we didn't notice that sooner, maybe it depends on the SQLite version.

[N@…>]

Replying to rblank:

The priority appears in the main SELECT and in the first LEFT OUTER JOIN. The same applies to milestone. It's a bit strange that we didn't notice that sooner, maybe it depends on the SQLite version.

Hmmm… OK, I'll upgrade SQLite and see what happens. I'll report back with my results.

[N@…>]

Actually, it looks like I am at the highest version I can go to (easily) on debian stable, so sorry, can't help much there. :(

[Remy Blank]

Replying to N@…>:

The SQL that prompted the error:

Are you sure this is the query that produces the error? It executes cleanly here with current 0.12-stable and SQLite. Can you please try that query either as a report, or using the SQLite command line client? You will have to replace the two %s with actual strings.

Also, it might be worth disabling the BatchModify plugin for a quick test.

[N@…>]

Are you sure this is the query that produces the error? It executes cleanly here with current 0.12-stable and SQLite. Can you please try that query either as a report, or using the SQLite command line client? You will have to replace the two %s with actual strings.

Yes, when I paste the same SQL into a report, it fails with the same error.

Also, running the raw SQL in the sqlite command line client fails, again with the same error.

Also, it might be worth disabling the BatchModify plugin for a quick test.

Batch modify was added after the problem started happening, so I know it isn't causing the problem… But just to be on the safe side, I just tested it with the BatchModify plugin disabled and it still fails.

I have also made sure that the database is up to the latest version.

Any other suggestions?

N@

[Remy Blank]

Replying to N@…>:

Any other suggestions?

Unfortunately not from my side. The query works fine here in the command line client (3.7.2). Anything special in your installation (e.g. special compilation flags for SQLite or PySQLite) or environment? The SQLite bug tracker hasn't proved very useful, and neither has a Google search so far.

[Christian Boos]

I tried the SQL query from comment:6 in SQLite 3.5.9, and it worked.

• Is it really that query which triggers the error for you?
• Only in Trac or also on the command line using sqlite3 directly?
• What's the output of sqlite3 <yourenv>/db/trac.db .schema?

[Remy Blank]

Replying to cboos:

• What's the output of sqlite3 <yourenv>/db/trac.db .schema?

That's a good comment. Maybe the presence of a table (due to a plugin) makes the query invalid.

[N@…>]

Replying to rblank:

Replying to cboos: That's a good comment. Maybe the presence of a table (due to a plugin) makes the query invalid.

Could be. I have looked at the schema (I will attach it), and the milestone and ticket tables have a priority field… Should milestone have one? It could be left over from my adventures with Agile-trac.

N@

[N@…>]

Schema of trac.db where error is occuring

[N@…>]

If I qualify priority in the join, it no longer fails.

So, I can obviously fix this in my install by dropping that field on the milestone table… Can I suggest that all field names should be qualified in the join to prevent this from happening in the future?

N@

[Christian Boos]

Replying to N@…>:

If I qualify priority in the join, it no longer fails. So, I can obviously fix this in my install by dropping that field on the milestone table…

Great!

Can I suggest that all field names should be qualified in the join to prevent this from happening in the future?

We should add this to our database API guidelines…

[N@…>]

I can confirm that dropping the priority column from the milestone table fixed the problem for me.

I used ​SQLiteBrowser to remove the column since SQLite doesn't provide an easy way to remove a column with SQL.