#7074: MySQL error/warning when 'gt' field lookup with a datetime field and
fulltext search.
-------------------------------------+--------------------------------------
Reporter: anonymous | Owner:
Status: new | Milestone: 1.0
Component: Core framework | Version: SVN
Resolution: | Keywords: qsrf-cleanup mysql,
fulltext search, gt
Stage: Accepted | Has_patch: 0
Needs_docs: 0 | Needs_tests: 0
Needs_better_patch: 0 |
-------------------------------------+--------------------------------------
Comment (by Karen Tracey <[EMAIL PROTECTED]>):
I took a look at this. The cast needs to be added on the right-hand-side,
not the left as is done by the use of `lookup_cast` in [8242]. There
doesn't seem to be any general backend hook for adding casting sql to the
right-hand-side.
What there is is `datetime_cast_sql` (currently only implemented by the
Oracle backend) that is called from
source:django/trunk/django/db/models/sql/where.py in `make_atom` in the
case where `value_annot is datetime.datetime`:
{{{
if value_annot is datetime.datetime:
cast_sql = connection.ops.datetime_cast_sql()
else:
cast_sql = '%s'
}}}
Implementing this for mysql as:
{{{
def datetime_cast_sql(self):
return 'CAST(%s AS DATETIME)'
}}}
will actually fix the problem exactly as reported, but not the identical
problem that exists for just plain DATE fields, nor the case where the
values are passed in as strings not date[time] objects. Keeping the above
`datetime_cast_sql`, plus defining a new general right-hand-side casting
hook and implementing it for mysql like so:
{{{
def rhs_cast_sql(self, db_type):
if db_type in ['date', 'datetime']:
return 'CAST(%%s AS %s)' % db_type
return '%s'
}}}
plus changing the code in where.py `make_atom` to call it:
{{{
if value_annot is datetime.datetime:
cast_sql = connection.ops.datetime_cast_sql()
else
cast_sql = connection.ops.rhs_cast_sql(db_type)
}}}
works to fix the reported problem for both DATE and DATETIME fields (and
covers the case where values are passed as strings, not date[time]
objects).
But that puts the mysql fix in two different places and that `if
value_annot...else` is a bit mysterious looking. I'd think it would be
better to have a single general hook that covers both what the existing
Oracle `datetime_cast_sql` function does and what's needed for mysql for
this bug. Unfortunately I have zero knowledge of Oracle so I'm not sure
what the value of db_type is for which the current `datetime_cast_sql`
function is being called. (And value_annot is apparently not useful for
datetime.date values -- it's set to 'True' for them? So value_annot can't
be used in the general hook case.)
So, in summary I think a fix for this would be to implement a general
right-hand-side casting hook for the database backends but doing that
correctly requires some Oracle knowledge I don't have. I'll investigate a
little more if anyone cares to provides clues for me and thinks this
approach is worthwhile.
[Also, I don't know how to write a test for this. The MySQL bug only
manifests itself when you've got a full-text index involved in the WHERE
clause. So it would have to be a test that ran only when the backend was
MySQL with the MyISAM storage engine, that created a full-text index (via
custom sql?) and used it in a lookup in combination with a date/datetime
gt/gte lookup. Not sure it's worth figuring out how to do all that in a
test? Actually I'm not sure this problem is worth fixing in Django since
it's really a MySQL bug, but as of today the MySQL bug is still open with
no fix in sight other than the casting workaround, so it's still there for
Django users to hit.]
--
Ticket URL: <http://code.djangoproject.com/ticket/7074#comment:9>
Django Code <http://code.djangoproject.com/>
The web framework for perfectionists with deadlines
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-updates?hl=en
-~----------~----~----~----~------~----~------~--~---
