Search broken on Postgresql 8.3

[pjenvey@…]

Search needs more of the same explicit type casting that #6676 now does

Under trunk r6633, searching yields:

Traceback (most recent call last):
  File "/usr/lib/python2.4/site-packages/Trac-0.11dev_r6621-py2.4.egg/trac/web/api.py", line 339, in send_error
    'text/html')
  File "/usr/lib/python2.4/site-packages/Trac-0.11dev_r6621-py2.4.egg/trac/web/chrome.py", line 690, in render_template
    if not req.session or not int(req.session.get('accesskeys', 0)):
  File "/usr/lib/python2.4/site-packages/Trac-0.11dev_r6621-py2.4.egg/trac/web/api.py", line 168, in __getattr__
    value = self.callbacks[name](self)
  File "/usr/lib/python2.4/site-packages/Trac-0.11dev_r6621-py2.4.egg/trac/web/main.py", line 256, in _get_session
    return Session(self.env, req)
  File "/usr/lib/python2.4/site-packages/Trac-0.11dev_r6621-py2.4.egg/trac/web/session.py", line 48, in __init__
    self.get_session(sid)
  File "/usr/lib/python2.4/site-packages/Trac-0.11dev_r6621-py2.4.egg/trac/web/session.py", line 74, in get_session
    (sid, int(authenticated)))
  File "/usr/lib/python2.4/site-packages/Trac-0.11dev_r6621-py2.4.egg/trac/db/util.py", line 50, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "/usr/lib/python2.4/site-packages/Trac-0.11dev_r6621-py2.4.egg/trac/db/util.py", line 50, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
ProgrammingError: current transaction is aborted, commands ignored until end of transaction block

Postgresql says (a search for 'registry'):

2008-03-03 15:58:11 PST ERROR:  operator does not exist: integer ~~* text at character 440
2008-03-03 15:58:11 PST HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
2008-03-03 15:58:11 PST STATEMENT:  SELECT DISTINCT a.summary,a.description,a.reporter, 
a.type,a.id,a.time,a.status,a.resolution FROM ticket a LEFT JOIN ticket_change b ON a.id = b.ticket WHERE (b.field='comment' 
AND (b.newvalue ILIKE '%registry%' ESCAPE '/') ) OR (summary ILIKE '%registry%' ESCAPE '/' OR keywords ILIKE '%registry%' 
ESCAPE '/' OR description ILIKE '%registry%' ESCAPE '/' OR reporter ILIKE '%registry%' ESCAPE '/' OR cc ILIKE '%registry%' ESCAPE 
'/' OR id ILIKE '%registry%' ESCAPE '/')

So the search query is treating the id column as text when it's an integer. We need to change

id ILIKE '%registry%' ESCAPE '/'

to:

text(id) ILIKE '%registry%' ESCAPE '/'

[Jonas Borgström]

Fixed [6652].