Code

Opened 6 years ago

Closed 6 years ago

#6981 closed (fixed)

Nullable foreign keys use inner joins with select related

Reported by: Petr Marhoun <petr.marhoun@…> Owned by: nobody
Component: Database layer (models, ORM) Version: queryset-refactor
Severity: Keywords: qs-rf
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

Short version - if select related has name of nullable foreign key as an argument, inner joins are used and all objects with null are filtered.

I don't know if it is a problem - maybe it should be so. But it is not intuitive.

Long version (example):

from django.db import models

class One(models.Model):
    a = models.IntegerField()
    
class Two(models.Model):
    b = models.IntegerField()
    
class Three(models.Model):
    c = models.IntegerField()
    one = models.ForeignKey(One)
    two = models.ForeignKey(Two, null=True)
>>> from tmp.models import One, Two, Three
>>> from django.db import connection
>>> 
>>> One.objects.all().delete()
>>> first = [One.objects.create(a=a) for a in range(10)]
>>> 
>>> Two.objects.all().delete()
>>> second = [Two.objects.create(b=b) for b in range(10)]
>>> 
>>> Three.objects.all().delete()
>>> for c in range(10):
...     one = first[c]
...     two = c % 2 and second[c] or None
...     Three.objects.create(c=c, one=one, two=two)
>>>
>>> # No select related
... print len(Three.objects.all())
10
>>> print connection.queries[-1]['sql']
SELECT "tmp_three"."id", "tmp_three"."c", "tmp_three"."one_id", "tmp_three"."two_id" FROM "tmp_three"
>>> 
>>> # Select related without argument - only not-null foreign keys are used
>>> print len(Three.objects.select_related()) 
10
>>> print connection.queries[-1]['sql']
SELECT "tmp_three"."id", "tmp_three"."c", "tmp_three"."one_id", "tmp_three"."two_id", "tmp_one"."id", "tmp_one"."a" FROM "tmp_three" INNER JOIN "tmp_one" ON ("tmp_three"."one_id" = "tmp_one"."id")
>>> 
>>> # Select related with argument which is not foreign key - it is ingored
... print len(Three.objects.select_related('c')) 
10
>>> print connection.queries[-1]['sql']
SELECT "tmp_three"."id", "tmp_three"."c", "tmp_three"."one_id", "tmp_three"."two_id" FROM "tmp_three"
>>> 
>>> # Select related with argument which does not exists - it is ingored
... print len(Three.objects.select_related('nonsense')) 
10
>>> print connection.queries[-1]['sql']
SELECT "tmp_three"."id", "tmp_three"."c", "tmp_three"."one_id", "tmp_three"."two_id" FROM "tmp_three"
>>> 
>>> # Select related with argument which is not-null foreign key - inner join is used
... print len(Three.objects.select_related('one')) 
10
>>> print connection.queries[-1]['sql']
SELECT "tmp_three"."id", "tmp_three"."c", "tmp_three"."one_id", "tmp_three"."two_id", "tmp_one"."id", "tmp_one"."a" FROM "tmp_three" INNER JOIN "tmp_one" ON ("tmp_three"."one_id" = "tmp_one"."id")
>>> 
>>> # Select related with argument which is null foreign key - inner join is used
... print len(Three.objects.select_related('two')) 
5
>>> print connection.queries[-1]['sql']
SELECT "tmp_three"."id", "tmp_three"."c", "tmp_three"."one_id", "tmp_three"."two_id", "tmp_two"."id", "tmp_two"."b" FROM "tmp_three" INNER JOIN "tmp_two" ON ("tmp_three"."two_id" = "tmp_two"."id")
>>> 
>>> # Select related with arguments for both foreign keys - inner joins are used
... print len(Three.objects.select_related('two')) 
5
>>> print connection.queries[-1]['sql']
SELECT "tmp_three"."id", "tmp_three"."c", "tmp_three"."one_id", "tmp_three"."two_id", "tmp_two"."id", "tmp_two"."b" FROM "tmp_three" INNER JOIN "tmp_two" ON ("tmp_three"."two_id" = "tmp_two"."id")

Attachments (0)

Change History (2)

comment:1 Changed 6 years ago by Petr Marhoun <petr.marhoun@…>

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

Last example has a mistake, right version:

>>> # Select related with argument which is both foreign keys - inner joins are used
... print len(Three.objects.select_related('one', 'two')) 
5
>>> print connection.queries[-1]['sql']
SELECT "tmp_three"."id", "tmp_three"."c", "tmp_three"."one_id", "tmp_three"."two_id", "tmp_one"."id", "tmp_one"."a", "tmp_two"."id", "tmp_two"."b" FROM "tmp_three" INNER JOIN "tmp_one" ON ("tmp_three"."one_id" = "tmp_one"."id") INNER JOIN "tmp_two" ON ("tmp_three"."two_id" = "tmp_two"."id")

comment:2 Changed 6 years ago by mtredinnick

  • Resolution set to fixed
  • Status changed from new to closed

(In [7418]) queryset-refactor: When using select_related() with an explicit foreign key,
use the right join type if the FK is nullable. Fixed #6981.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.