Trac trunk search fails against a postgresql 8.3 database.

[Van.]

Trac[sample-project:main] DEBUG: Dispatching <Request "GET u'/search'">
Trac[sample-project:svn_fs] DEBUG: Subversion bindings imported
Trac[sample-project:search] DEBUG: SELECT id, forum, time, subject, body, author FROM topic WHERE (author ILIKE %s ESCAPE '/' OR subject ILIKE %s ESCAPE '/' OR body ILIKE %s ESCAPE '/')
Trac[sample-project:search] DEBUG: SELECT m.id, m.forum, m.topic, m.time, m.author, m.body, t.subject FROM message m LEFT JOIN (SELECT subject, id FROM topic) t ON t.id = m.topic WHERE (m.author ILIKE %s ESCAPE '/' OR m.body ILIKE %s ESCAPE '/' OR t.subject ILIKE %s ESCAPE '/')
Trac[sample-project:chrome] DEBUG: Prepare chrome data for request
Trac[sample-project:main] ERROR: operator does not exist: integer ~~* text at character 440
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
Traceback (most recent call last):
  File "/usr/lib/python2.4/site-packages/Trac-0.11dev_r0-py2.4.egg/trac/web/main.py", line 406, in _dispatch_request
    dispatcher.dispatch(req)
  File "/usr/lib/python2.4/site-packages/Trac-0.11dev_r0-py2.4.egg/trac/web/main.py", line 195, in dispatch
    resp = chosen_handler.process_request(req)
  File "/usr/lib/python2.4/site-packages/Trac-0.11dev_r0-py2.4.egg/trac/search/web_ui.py", line 106, in process_request
    results += list(source.get_search_results(req, terms, filters))
  File "/usr/lib/python2.4/site-packages/Trac-0.11dev_r0-py2.4.egg/trac/ticket/web_ui.py", line 196, in get_search_results
    args + args2)
  File "/usr/lib/python2.4/site-packages/Trac-0.11dev_r0-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_r0-py2.4.egg/trac/db/util.py", line 50, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
ProgrammingError: operator does not exist: integer ~~* text at character 440
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

[Christian Boos]

Another hint: use Wiki blocks: {{{ ... }}} for such backtraces.

Plus:

SELECT id, forum, time, subject, body, author FROM topic 

This is coming from a plugin, so you should identify that plugin and report the problem to its author, not here (as we don't maintain that plugin here).

[anonymous]

Thanks for the reply.

I temporarily solved by creating implicit cast for operator ILIKE (easier than hunting those implicit cast conversion errors en each plugin).

I will use wiki blocks next time.