prefetch_related fails with sqlite3 database and UUIDField primary keys

[brki]

Here's a test.py that fails on sqlite3, but succeeds if run on postgresql:

import uuid
from django.db import models
from django.test import TestCase


class Award(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4)
    name = models.CharField(max_length=20)


class Player(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4)
    name = models.CharField(max_length=20)
    awards = models.ManyToManyField(Award)


class UUIDSqliteDemo(TestCase):
    def setUp(self):
        awards = [
            Award.objects.create(name='star'),
            Award.objects.create(name='trophy'),
        ]
        player = Player.objects.create(name='Beth')
        player.awards.add(*awards)
        self.player_id = player.id

    # This test passes if the target database is Postgresql, but fails for sqlite3:
    def test_prefetch_related(self):
        players = list(Player.objects.filter(id=self.player_id).prefetch_related('awards'))
        player = players[0]
        # For sqlite3, this fails with "2 != 0":
        self.assertEqual(2, len(player.awards.all()))

I also tried using a CharField instead of a UUIDField as the pk field; that worked fine. So it seems to be a problem with the UUIDField and sqlite3 (or perhaps all non-Postgresql dbs?).

[Alasdair Nicol]

I can reproduce the problem in sqlite.

[Rigel Di Scala]

I am able to replicate this on "1.9.dev20150604085936".

[Rigel Di Scala]

I'm seeing this in the db logs:

>>> players = Player.objects.filter(id=self.player_id)
>>> player_ = players.get()
>>> self.assertEqual(2, player_.awards.count())

(0.000) QUERY = 'SELECT "model_fields_player"."id", "model_fields_player"."name" FROM "model_fields_player" WHERE "model_fields_player"."id" = %s' - PARAMS = ('01e6706e12f6465387abaeb1cf0c6389',); args=('01e6706e12f6465387abaeb1cf0c6389',)

(0.000) QUERY = 'SELECT COUNT(%s) AS "__count" FROM "model_fields_award" INNER JOIN "model_fields_player_awards" ON ( "model_fields_award"."id" = "model_fields_player_awards"."award_id" ) WHERE "model_fields_player_awards"."player_id" = %s' - PARAMS = ('*', '01e6706e12f6465387abaeb1cf0c6389'); args=('*', '01e6706e12f6465387abaeb1cf0c6389')

>>> players = players.prefetch_related('awards')
>>> player = players.get()
>>> # For sqlite3, this fails with "2 != 0":
>>> self.assertEqual(2, player.awards.count())

(0.000) QUERY = 'SELECT "model_fields_player"."id", "model_fields_player"."name" FROM "model_fields_player" WHERE "model_fields_player"."id" = %s' - PARAMS = ('01e6706e12f6465387abaeb1cf0c6389',); args=('01e6706e12f6465387abaeb1cf0c6389',)

(0.000) QUERY = 'SELECT ("model_fields_player_awards"."player_id") AS "_prefetch_related_val_player_id", "model_fields_award"."id", "model_fields_award"."name" FROM "model_fields_award" INNER JOIN "model_fields_player_awards" ON ( "model_fields_award"."id" = "model_fields_player_awards"."award_id" ) WHERE "model_fields_player_awards"."player_id" IN (%s)' - PARAMS = ('01e6706e12f6465387abaeb1cf0c6389',); args=('01e6706e12f6465387abaeb1cf0c6389',)

[Rigel Di Scala]

It still fails using len():

>>> player_ = players.get()
>>> self.assertEqual(2, len(player_.awards.all()))

(0.000) QUERY = 'SELECT "model_fields_player"."id", "model_fields_player"."name" FROM "model_fields_player" WHERE "model_fields_player"."id" = %s' - PARAMS = ('f48b661fe6b1442f88098656204f3abf',); args=('f48b661fe6b1442f88098656204f3abf',)

(0.000) QUERY = 'SELECT "model_fields_award"."id", "model_fields_award"."name" FROM "model_fields_award" INNER JOIN "model_fields_player_awards" ON ( "model_fields_award"."id" = "model_fields_player_awards"."award_id" ) WHERE "model_fields_player_awards"."player_id" = %s' - PARAMS = ('f48b661fe6b1442f88098656204f3abf',); args=('f48b661fe6b1442f88098656204f3abf',)

>>> players = players.prefetch_related('awards')
>>> player = players.get()
>>> # For sqlite3, this fails with "2 != 0":
>>> self.assertEqual(2, len(player.awards.all()))

(0.000) QUERY = 'SELECT "model_fields_player"."id", "model_fields_player"."name" FROM "model_fields_player" WHERE "model_fields_player"."id" = %s' - PARAMS = ('f48b661fe6b1442f88098656204f3abf',); args=('f48b661fe6b1442f88098656204f3abf',)

(0.000) QUERY = 'SELECT ("model_fields_player_awards"."player_id") AS "_prefetch_related_val_player_id", "model_fields_award"."id", "model_fields_award"."name" FROM "model_fields_award" INNER JOIN "model_fields_player_awards" ON ( "model_fields_award"."id" = "model_fields_player_awards"."award_id" ) WHERE "model_fields_player_awards"."player_id" IN (%s)' - PARAMS = ('f48b661fe6b1442f88098656204f3abf',); args=('f48b661fe6b1442f88098656204f3abf',)

[Rigel Di Scala]

Clearing the prefetch_related behaviour with players.prefetch_related(None), even after the evaluation, makes the test pass.

[brki]

Not sure what happened, I thought I'd posted this here already:

This seems to fix the issue for me, what do you think:
​https://github.com/brki/django/commit/deec2487609c211f2225bf3b415a574c540bea7e

I've adapted the test a bit locally, too:

    def test_prefetch_related(self):
        with self.assertNumQueries(2):
            players = list(Player.objects.filter(id=self.player_id).prefetch_related('awards'))
        with self.assertNumQueries(0):
            player = players[0]
            self.assertEqual(2, len(player.awards.all()))

[Rigel Di Scala]

With your fix:

{{{(0.000) QUERY = 'SELECT "model_fields_player"."id", "model_fields_player"."name" FROM "model_fields_player" WHERE "model_fields_player"."id" = %s' - PARAMS = ('8aa7fd54d3d24c7bbca418a584fc44c9',); args=('8aa7fd54d3d24c7bbca418a584fc44c9',)

(0.000) QUERY = 'SELECT ("model_fields_player_awards"."player_id") AS "_prefetch_related_val_player_id", "model_fields_award"."id", "model_fields_award"."name" FROM "model_fields_award" INNER JOIN "model_fields_player_awards" ON ( "model_fields_award"."id" = "model_fields_player_awards"."award_id" ) WHERE "model_fields_player_awards"."player_id" IN (%s)' - PARAMS = ('8aa7fd54d3d24c7bbca418a584fc44c9',); args=('8aa7fd54d3d24c7bbca418a584fc44c9',)}}}

[brki]

There's a pull request open with a fix for this: ​https://github.com/django/django/pull/4782.

It was failing before with at least sqlite and mysql.

The fix was tested on sqlite, mysql and postgresql.

[Simon Charette]

Don't mark your own patch as RFC. Simply uncheck Patch needs improvement once you've addressed the comments on the PR.

[brki]

New pull request: ​https://github.com/django/django/pull/4799

[Tim Graham]

Added some comments for improvement on the PR.

[brki]

Adjusted PR as suggested.

[Tim Graham <timograham@…>]

In bfb5b71:

Fixed #24912 -- Fixed prefetch_related failure for UUIDField primary keys

This resolves a problem on databases besides PostgreSQL when using
prefetch_related with a source model that uses a UUID primary key.

[Tim Graham <timograham@…>]

In c58755d8:

[1.8.x] Fixed #24912 -- Fixed prefetch_related failure for UUIDField primary keys

This resolves a problem on databases besides PostgreSQL when using
prefetch_related with a source model that uses a UUID primary key.

Backport of bfb5b7150ff52571a7a3cf10e0dd0d1dbd45c4b5 from master