Opened 4 years ago
Closed 4 years ago
#32699 closed Bug (fixed)
Comparisons using TruncTime are unreliable in MySQL
Reported by: | Alex Hill | Owned by: | Alex Hill |
---|---|---|---|
Component: | Uncategorized | Version: | 3.2 |
Severity: | Normal | Keywords: | |
Cc: | Triage Stage: | Ready for checkin | |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
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 0SELECT 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 :)
Change History (5)
comment:1 by , 4 years ago
Triage Stage: | Unreviewed → Accepted |
---|
comment:2 by , 4 years ago
I appreciate the insight, thanks! Good to hear it shouldn't be more complicated than the existing patch.
Which is kind of expected as TIME returns a string representation of the temporal value
All representations of the same time passed to TIME seem to be considered equal to one another when compared, even though the strings they evaluate to may not equal to one another. Can you shed any light?
mysql> SELECT TIME('10:30:000000') = TIME('10:30'); 1 mysql> SELECT TIME('10:30:00.000000'), TIME('10:30'); 10:30:00.000000 10:30:00
comment:3 by , 4 years ago
Not sure I can explain that, maybe that MySQL wanted preserve the equality behaviour or TIME
for columns without microsecond precision when it introduced support for the later?
mysql> SELECT t = TIME('10:30:00.00000'), t = '10:30:00.00000', TIME(t) = TIME(t6), t = 103000.00, TIME(t) = 103000.00 FROM foo; +----------------------------+----------------------+--------------------+---------------+---------------------+ | t = TIME('10:30:00.00000') | t = '10:30:00.00000' | TIME(t) = TIME(t6) | t = 103000.00 | TIME(t) = 103000.00 | +----------------------------+----------------------+--------------------+---------------+---------------------+ | 1 | 1 | 1 | 1 | 1 | +----------------------------+----------------------+--------------------+---------------+---------------------+
Given that TIME('10:30:000000') = TIME('10:30')
an alternative to the patch could be always wrap TIME
comparison in the TIME
function on MySQL.
comment:4 by , 4 years ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
Triage Stage: | Accepted → Ready for checkin |
It doesn't happen if you create the column as
TIME(6)
to get microseconds precision though which is something Django does.Which is kind of expected as
TIME
returns a string representation of the temporal value and theTIME
column type doesn't have microsecond precision and thus doesn't represent them.I think the patch makes sense since the time you'd be passing a literal
time
instance would be whenTimeField
column from a model and MySQL work properly in this case.DateTimeField
transform such as__time
as reported here whereTIME
is used and works properly withDATETIME(6)
which are used by default since Django 1.8.