[Django] #24912: prefetch_related fails with sqlite3 database and UUIDField primary keys
Django
----------------------------------------------+-----------------------
Reporter: brki | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.8
Severity: Normal | Keywords: UUIDField
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+-----------------------
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?).
--
Ticket URL: <https://code.djangoproject.com/ticket/24912>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: brki | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: UUIDField | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by alasdairnicol):
* needs_better_patch: => 0
* needs_docs: => 0
* version: 1.8 => master
* needs_tests: => 0
* stage: Unreviewed => Accepted
Comment:
I can reproduce the problem in sqlite.
--
Ticket URL: <https://code.djangoproject.com/ticket/24912#comment:1>
Django
Reporter: brki | Owner: zedr
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: UUIDField | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by zedr):
* owner: nobody => zedr
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/24912#comment:2>
Django
Reporter: brki | Owner: zedr
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: UUIDField | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by zedr):
I am able to replicate this on "1.9.dev20150604085936".
--
Ticket URL: <https://code.djangoproject.com/ticket/24912#comment:3>
Django
Reporter: brki | Owner: zedr
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: UUIDField | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by zedr):
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',)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/24912#comment:4>
Django
Reporter: brki | Owner: zedr
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: UUIDField | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by zedr):
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',)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/24912#comment:5>
Django
Reporter: brki | Owner: zedr
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: UUIDField | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by zedr):
Interestingly, clearing the `prefetch_related` behavior does not fix the
problem:
{{{
>>> players = Player.objects.filter(id=self.player_id)
>>> # Enable prefetching
>>> players = players.prefetch_related('awards')
>>> # Disable prefetching (before the queryset is evaluated)
>>> players.prefetch_related(None)
>>> 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 =
('9674a6f11c044484b89bac792e53e642',);
args=('9674a6f11c044484b89bac792e53e642',)
(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 =
('9674a6f11c044484b89bac792e53e642',);
args=('9674a6f11c044484b89bac792e53e642',)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/24912#comment:6>
Django
Reporter: brki | Owner: zedr
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: UUIDField | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (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:
{{{
# This test passes if the target database is Postgresql, but fails for
sqlite3:
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()))
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/24912#comment:7>
Django
Reporter: brki | Owner:
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: UUIDField | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by zedr):
* status: assigned => new
* owner: zedr =>
Comment:
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',)}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/24912#comment:8>
Django
Reporter: brki | Owner: brki
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: UUIDField | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by brki):
* status: new => assigned
* owner: => brki
--
Ticket URL: <https://code.djangoproject.com/ticket/24912#comment:9>
Django
Reporter: brki | Owner: brki
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
| checkin
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by brki):
* stage: Accepted => Ready for checkin
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/24912#comment:10>
Django
Reporter: brki | Owner: brki
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
Changes (by charettes):
* needs_better_patch: 0 => 1
* has_patch: 0 => 1
* stage: Ready for checkin => Accepted
Comment:
Don't mark your own patch as RFC. Simply uncheck ''Patch needs
improvement'' once you've addressed the comments on the PR.
--
Ticket URL: <https://code.djangoproject.com/ticket/24912#comment:11>
Django
Reporter: brki | Owner: brki
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: UUIDField | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by brki):
* needs_better_patch: 1 => 0
Comment:
New pull request: https://github.com/django/django/pull/4799
--
Ticket URL: <https://code.djangoproject.com/ticket/24912#comment:12>
Django
Reporter: brki | Owner: brki
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Keywords: UUIDField | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* needs_better_patch: 0 => 1
* severity: Normal => Release blocker
Comment:
Added some comments for improvement on the PR.
--
Ticket URL: <https://code.djangoproject.com/ticket/24912#comment:13>
Django
Reporter: brki | Owner: brki
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: UUIDField | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by brki):
* needs_better_patch: 1 => 0
Comment:
Adjusted PR as suggested.
--
Ticket URL: <https://code.djangoproject.com/ticket/24912#comment:14>
Django
Reporter: brki | Owner: brki
Component: Database layer | Version: master
(models, ORM) |
Keywords: UUIDField | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham <timograham@…>):
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"bfb5b7150ff52571a7a3cf10e0dd0d1dbd45c4b5" bfb5b71]:
{{{
#!CommitTicketReference repository=""
revision="bfb5b7150ff52571a7a3cf10e0dd0d1dbd45c4b5"
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.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/24912#comment:15>
Django
Reporter: brki | Owner: brki
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: UUIDField | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Tim Graham <timograham@…>):
In [changeset:"c58755d8757d6d6ad1ab2c52a631aff1b9bae2da" c58755d8]:
{{{
#!CommitTicketReference repository=""
revision="c58755d8757d6d6ad1ab2c52a631aff1b9bae2da"
[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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/24912#comment:16>