I'm trying to understand why a LEFT OUTER JOIN is being used in queries that filter on a NULL foreign key. It seems that the same result can be achieved without the LEFT OUTER JOIN.
Here is an example: > cat models.py from django.db import models from django.contrib.auth.models import User # Create your models here. class Widget(models.Model): owner = models.ForeignKey(User, null=True, blank=True) python manage.py shell >>> from widgets.models import Widget >>> from django.db import connection >>> Widget.objects.filter(owner=None) >>> connection.queries [{'time': '0.001', 'sql': u'SELECT `widgets_widget`.`id`, `widgets_widget`.`owner_id` FROM `widgets_widget` LEFT OUTER JOIN `auth_user` ON (`widgets_widget`.`owner_id` = `auth_user`.`id`) WHERE `auth_user`.`id` IS NULL LIMIT 21'}] It seems that this could be accomplished with a simpler query that does not use a join: SELECT `widgets_widget`.`id`, `widgets_widget`.`owner_id` FROM `widgets_widget` WHERE `owner_id` IS NULL LIMIT 21'} This becomes a bigger issue when the filter is combined with an update command as the join forces the update to be split into two SQL commands. This opens the door to race conditions. >>> from django.contrib.auth.models import >>> u = User.objects.get(pk=1) >>> connection.queries = [] >>> Widget.objects.filter(owner=None).update(owner=u) 1L >>> connection.queries [{'time': '0.006', 'sql': u'SELECT U0.`id` FROM `widgets_widget` U0 LEFT OUTER JOIN `auth_user` U1 ON (U0.`owner_id` = U1.`id`) WHERE U1.`id` IS NULL'}, {'time': '0.002', 'sql': u'UPDATE `widgets_widget` SET `owner_id` = 1 WHERE `widgets_widget`.`id` IN (1)'}] >>> The behavior is the also the same with owner__isnull=True. Why is the LEFT OUTER JOIN used? Is there a different way this filter should be structured so that the LEFT OUTER JOIN is not used? Thanks, Brian --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~----------~----~----~----~------~----~------~--~---