Opened 5 years ago

Closed 5 years ago

Last modified 5 years ago

#24912 closed Bug (fixed)

prefetch_related fails with sqlite3 database and UUIDField primary keys

Reported by: brki Owned by: brki
Component: Database layer (models, ORM) Version: master
Severity: Release blocker Keywords: UUIDField
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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?).

Change History (16)

comment:1 Changed 5 years ago by Alasdair Nicol

Triage Stage: UnreviewedAccepted
Version: 1.8master

I can reproduce the problem in sqlite.

comment:2 Changed 5 years ago by Rigel Di Scala

Owner: changed from nobody to Rigel Di Scala
Status: newassigned

comment:3 Changed 5 years ago by Rigel Di Scala

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

comment:4 Changed 5 years ago by 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',)

comment:5 Changed 5 years ago by 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',)
Last edited 5 years ago by Rigel Di Scala (previous) (diff)

comment:6 Changed 5 years ago by Rigel Di Scala

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

Last edited 5 years ago by Rigel Di Scala (previous) (diff)

comment:7 Changed 5 years ago by 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()))
Last edited 5 years ago by brki (previous) (diff)

comment:8 Changed 5 years ago by Rigel Di Scala

Owner: Rigel Di Scala deleted
Status: assignednew

Deassigning myself.

Last edited 5 years ago by Rigel Di Scala (previous) (diff)

comment:9 Changed 5 years ago by brki

Owner: set to brki
Status: newassigned

comment:10 Changed 5 years ago by brki

Triage Stage: AcceptedReady for checkin

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.

comment:11 Changed 5 years ago by Simon Charette

Has patch: set
Patch needs improvement: set
Triage Stage: Ready for checkinAccepted

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

comment:12 Changed 5 years ago by brki

Patch needs improvement: unset

comment:13 Changed 5 years ago by Tim Graham

Patch needs improvement: set
Severity: NormalRelease blocker

Added some comments for improvement on the PR.

comment:14 Changed 5 years ago by brki

Patch needs improvement: unset

Adjusted PR as suggested.

comment:15 Changed 5 years ago by Tim Graham <timograham@…>

Resolution: fixed
Status: assignedclosed

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.

comment:16 Changed 5 years ago by 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

Note: See TracTickets for help on using tickets.
Back to Top