Example
I'm trying to annotate objects using data from a related model's DateTimeField.
class Author(models.Model):
name = models.CharField(max_length=256)
class Book(models.Model):
name = models.CharField(max_length=256)
is_published = models.BooleanField()
publication_date = models.DateTimeField()
author = models.ForeignKey(Author, on_delete=models.PROTECT)
queryset = Author.objects.annotate(
has_unpublished=Exists(
Book.objects.filter(
author=OuterRef('pk'),
is_published=False))
).annotate(
last_published_date=Max(
'book__publication_date',
filter=Q(book__is_published=True)
)
)
I alternate between the default local sqlite3 database and MySQL (with mysqlclient). SQLite works and MySQL crashes.
Here's the reason: compilers for both databases
results = compiler.execute_sql(**kwargs)
return a list of lists of tuples of int and string, like so:
SQLite
<class 'list'>: [[(1, 'Alice', 1, '2017-01-01 01:00:00'), (2, 'Bob', 0, '2019-01-05 13:00:00'), (3, 'Carol', 1, None), (4, 'Dan', 0, None)]]
MySQL
<class 'list'>: [((1, 'Alice', 1, '2017-01-01 01:00:00.000000'), (2, 'Bob', 0, '2019-01-05 13:00:00.000000'), (3, 'Carol', 1, None), (4, 'Dan', 0, None))]
Now, when the SQLite backend sees a supposed datetime field, it generates a converter at runtime using this method:
django.db.backends.sqlite3.operations.DatabaseOperations
def convert_datetimefield_value(self, value, expression, connection):
if value is not None:
if not isinstance(value, datetime.datetime):
value = parse_datetime(value)
if settings.USE_TZ and not timezone.is_aware(value):
value = timezone.make_aware(value, self.connection.timezone)
return value
which works ok.
The MySQL backend, however, does this:
django.db.backends.mysql.operations.DatabaseOperations
def convert_datetimefield_value(self, value, expression, connection):
if value is not None:
value = timezone.make_aware(value, self.connection.timezone)
return value
Django then proceeds to crash trying to check an <str> for timezone awareness.
Context
The above is a toy example I used to reproduce the crash.
In our actual production environment, the MySQL compiler's execute_sql, bizarrely, returns a list of lists of tuples of "raw" data with datetime.datetime objects if I request a full list of annotated authors (action=='list'), and the same list with a string if I request a particular one (action=='retrieve'). The querysets' respective sql queries only differ by the presence of a single WHERE clause.
This leads me to think I'm doing something wrong and not getting the raw datetime.datetime I'm supposed to get from the driver (rather than the far lower probability of a bug in Django).
What should I do? N+1 is out of the question, this will be the app's main operator view and the datetime field is actually two one-to-many relationships deep.
