#32699: Comparisons using TruncTime are unreliable in MySQL
-----------------------------------------+------------------------
Reporter: Alex Hill | Owner: nobody
Type: Bug | Status: new
Component: Uncategorized | Version: 3.2
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 1
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-----------------------------------------+------------------------
To reproduce:
{{{
class DTModel(models.Model):
dt = models.DateTimeField()
obj = DTModel.objects.create(dt=datetime(2021, 4, 30, 10, 30))
DTModel.objects.filter(pk=obj.pk, dt__time=time(10, 30)).count() # should
be 1 but returns 0
}}}
TruncTime is implemented in MySQL using the `TIME()` function. When we
pass time values to MySQL, we return `str(value)` from
`adapt_timefield_value` which, when the microseconds field of the value is
zero, yields a string in "HH:MM:SS" format.
And it looks like comparing time values with strings is problematic and
its success depends on including or not including the microseconds value.
* `SELECT TIME('10:30:00.000000') = '10:30:00'` returns 0
* `SELECT TIME('10:30:00.000000') = '10:30:00.000000'` returns 1
But if you make a TIME column and insert '10:30:00.000000' into it, then
`SELECT ... WHERE TIME(dt) = '10:30:00.000000'` won't find the row. It's a
mess.
The problem I'm seeing can be fixed by returning a time string including
the microseconds component from `adapt_timefield_value`, but I wouldn't be
surprised if that breaks something else given the above. What does appear
to work in all cases is passing the value to TIME() or preceding it with
the TIME keyword.
This affects all current versions of Django. I'll open a PR once I have a
ticket number :)
--
Ticket URL: <https://code.djangoproject.com/ticket/32699>
Django <https://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 unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/051.5ba3963dd27c020619a32d1ee286cfb7%40djangoproject.com.
