Get database error when using CAST( .. AS ..) syntax

[nicolasm@…]

I get this error after installing this plugin:

11:08:47 Trac[__init__] ERROR: near "as": syntax error
Traceback (most recent call last):
  File "/usr/lib/python2.4/site-packages/Trac-0.11-py2.4.egg/trac/web/main.py", line 423, in _dispatch_request
    dispatcher.dispatch(req)
  File "/usr/lib/python2.4/site-packages/Trac-0.11-py2.4.egg/trac/web/main.py", line 219, in dispatch
    data, content_type)
  File "/usr/lib/python2.4/site-packages/Trac-0.11-py2.4.egg/trac/web/chrome.py", line 690, in render_template
    stream |= self._filter_stream(req, method, filename, stream, data)
  File "/usr/lib/python2.4/site-packages/Genshi-0.5-py2.4-linux-i686.egg/genshi/core.py", line 128, in __or__
  File "/usr/lib/python2.4/site-packages/Trac-0.11-py2.4.egg/trac/web/chrome.py", line 789, in inner
    data)
  File "build/bdist.linux-i686/egg/ticketmodifiedfiles/ticketmodifiedfiles.py", line 78, in filter_stream
  File "build/bdist.linux-i686/egg/ticketmodifiedfiles/ticketmodifiedfiles.py", line 137, in __process_ticket_request
  File "/usr/lib/python2.4/site-packages/Trac-0.11-py2.4.egg/trac/db/util.py", line 51, in execute
    return self.cursor.execute(sql)
  File "/usr/lib/python2.4/site-packages/sqlite/main.py", line 244, in execute
    self.rs = self.con.db.execute(SQL)
DatabaseError: near "as": syntax error

I have fixed it temporarily with:

Line 137:
-            cursor.execute("SELECT change_type FROM node_change WHERE path='" + file + "' ORDER BY CAST(rev as integer) DESC LIMIT 1")
+            cursor.execute("SELECT change_type FROM node_change WHERE path='" + file + "' ORDER BY 1*rev DESC LIMIT 1")

I think I have new enough sqlite installed, but who knows...

[Emilien Klein]

Yes, this happens if you use SQLite < 3.2.3 (the CAST operator has been added to ​SQLite 3.2.3) Which version do you use?

Anyway, I am going to use your hack, as ​it seems that Trac itself uses 1*column.

[Emilien Klein]

fixed in r3891.

[nicolasm@…]

$ dpkg -l sqlite
Desired=Unknown/Install/Remove/Purge/Hold
| Status=Not/Installed/Config-files/Unpacked/Failed-config/Half-installed
|/ Err?=(none)/Hold/Reinst-required/X=both-problems (Status,Err: uppercase=bad)
||/ Name                              Version                           Description
+++-=================================-=================================-==================================================================================
ii  sqlite                            2.8.17-2                          command line interface for SQLite

stock debian

thanks ;)

[nicolasm@…]

stock debian etch that is