MySQL error/warning when 'gt' field lookup with a datetime field and fulltext search.

[anonymous]

This query produces the following traceback:

Keyword.objects.filter(keyword__search=term, keyworddata__updated__gt=datetime.datetime.now(), keyworddata__source="1")

term = 'test'

In [28]: Keyword.objects.filter(keyword__search=term, keyworddata__updated__gt=datetime.datetime.now(), keyworddata__source="1"
).select_related()
Out[29]: ---------------------------------------------------------------------------
Warning                                   Traceback (most recent call last)

/home/lybp/dev/lybp/<ipython console> in <module>()

/usr/local/lib/python2.5/site-packages/ipython-0.8.2-py2.5.egg/IPython/Prompts.py in __call__(self, arg)
    533
    534             # and now call a possibly user-defined print mechanism
--> 535             manipulated_val = self.display(arg)
    536
    537             # user display hooks can change the variable to be stored in

/usr/local/lib/python2.5/site-packages/ipython-0.8.2-py2.5.egg/IPython/Prompts.py in _display(self, arg)
    559             return IPython.generics.result_display(arg)
    560         except TryNext:
--> 561             return self.shell.hooks.result_display(arg)
    562
    563     # Assign the default display method:

/usr/local/lib/python2.5/site-packages/ipython-0.8.2-py2.5.egg/IPython/hooks.py in __call__(self, *args, **kw)
    132             #print "prio",prio,"cmd",cmd #dbg
    133             try:
--> 134                 ret = cmd(*args, **kw)
    135                 return ret
    136             except ipapi.TryNext, exc:

/usr/local/lib/python2.5/site-packages/ipython-0.8.2-py2.5.egg/IPython/hooks.py in result_display(self, arg)
    160
    161     if self.rc.pprint:
--> 162         out = pformat(arg)
    163         if '\n' in out:
    164             # So that multi-line strings line up with the left column of

/usr/local/lib/python2.5/pprint.py in pformat(self, object)
    109     def pformat(self, object):
    110         sio = _StringIO()
--> 111         self._format(object, sio, 0, 0, {}, 0)
    112         return sio.getvalue()
    113
/usr/local/lib/python2.5/pprint.py in _format(self, object, stream, indent, allowance, context, level)
    127             self._readable = False
    128             return
--> 129         rep = self._repr(object, context, level - 1)
    130         typ = _type(object)
    131         sepLines = _len(rep) > (self._width - 1 - indent - allowance)

/usr/local/lib/python2.5/pprint.py in _repr(self, object, context, level)
    193     def _repr(self, object, context, level):
    194         repr, readable, recursive = self.format(object, context.copy(),
--> 195                                                 self._depth, level)
    196         if not readable:
    197             self._readable = False

/usr/local/lib/python2.5/pprint.py in format(self, object, context, maxlevels, level)
    205         and whether the object represents a recursive construct.
    206         """
--> 207         return _safe_repr(object, context, maxlevels, level)
    208
    209

/usr/local/lib/python2.5/pprint.py in _safe_repr(object, context, maxlevels, level)
    290         return format % _commajoin(components), readable, recursive
    291
--> 292     rep = repr(object)
    293     return rep, (rep and not rep.startswith('<')), False
    294

/usr/local/lib/python2.5/site-packages/django/db/models/query.py in __repr__(self)
    106
    107     def __repr__(self):
--> 108         return repr(self._get_data())
    109
    110     def __len__(self):

/usr/local/lib/python2.5/site-packages/django/db/models/query.py in _get_data(self)
    484     def _get_data(self):
    485         if self._result_cache is None:
--> 486             self._result_cache = list(self.iterator())
    487         return self._result_cache
    488
/usr/local/lib/python2.5/site-packages/django/db/models/query.py in iterator(self)
    187
    188         cursor = connection.cursor()
--> 189         cursor.execute("SELECT " + (self._distinct and "DISTINCT " or "") + ",".join(select) + sql, params)
    190
    191         fill_cache = self._select_related

/usr/local/lib/python2.5/site-packages/django/db/backends/util.py in execute(self, sql, params)
     16         start = time()
     17         try:
---> 18             return self.cursor.execute(sql, params)
     19         finally:
     20             stop = time()

/home/lybp/dev/lybp/build/bdist.linux-i686/egg/MySQLdb/cursors.py in execute(self, query, args)

/home/lybp/dev/lybp/build/bdist.linux-i686/egg/MySQLdb/cursors.py in _warning_check(self)

/usr/local/lib/python2.5/warnings.py in warn(message, category, stacklevel)
     60     registry = globals.setdefault("__warningregistry__", {})
     61     warn_explicit(message, category, filename, lineno, module, registry,
---> 62                   globals)
     63
     64 def warn_explicit(message, category, filename, lineno,

/usr/local/lib/python2.5/warnings.py in warn_explicit(message, category, filename, lineno, module, registry, module_globals)
    100
    101     if action == "error":
--> 102         raise message
    103     # Other actions
    104     if action == "once":

Warning: Truncated incorrect DOUBLE value: '2008-04-23 14:39:36.133203'

In [30]: Keyword.objects.filter(keyword__search=term, keyworddata__updated=datetime.datetime.now(), keyworddata__source="1"
).select_related()
Out[31]: []
In [12]: Keyword.objects.filter(keyword=term, keyworddata__updated__gt=datetime.datetime.now(), keyworddata__source="1"
).select_related()
Out[13]: []

If I change keyword__search= to keyword=, I don't get the error. Also if I use keyworddata__updated= instead of keyworddata__updated__gt I don't get the error.

KeywordData is a model with a foreign key pointing to keyword - so I guess it's a reverse lookup.

I'm using MySQL 5.0.45.

[Erik Wickstrom <erik@…>]

It looks to me like the date isn't being quoted - or at least that's what connection.queries says...

[Malcolm Tredinnick]

Fixed description.