Incorrect SQL join construction

[Brian May]

Continued from <​http://groups.google.com/group/django-users/browse_thread/thread/bc3ecc16c3def53b>

Python 2.5.2 (r252:60911, Oct  5 2008, 19:24:49) 
[GCC 4.3.2] on linux2
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> import inventory.models as models
>>> object=models.software.objects.get(id=267)
>>> print models.software_installation.objects.filter(software=object).count()
10
>>> print models.software_installation.objects.filter(software=object)
[]
>>> print models.software_installation.objects.filter(software=object).query.as_sql()
('SELECT `inventory_software_installation`.`id`, 
`inventory_software_installation`.`software_id`, 
`inventory_software_installation`.`license_key_id` FROM 
`inventory_software_installation` INNER JOIN `inventory_software` 
ON (`inventory_software_installation`.`software_id` = 
`inventory_software`.`id`) LEFT OUTER JOIN `inventory_license_key` 
ON (`inventory_software_installation`.`license_key_id` = 
`inventory_license_key`.`id`) INNER JOIN `inventory_software` T4 ON 
(`inventory_license_key`.`software_id` = T4.`id`) INNER JOIN 
`inventory_license` ON (`inventory_license_key`.`license_id` = 
`inventory_license`.`id`) INNER JOIN `inventory_software` T6 ON 
(`inventory_license`.`software_id` = T6.`id`) WHERE 
`inventory_software_installation`.`software_id` = %s  ORDER BY 
`inventory_software`.`name` ASC, T4.`name` ASC, T6.`name` ASC, 
`inventory_license`.`vendor_id` ASC, `inventory_license_key`.`key` 
ASC', (267,))

class software(models.Model):
    name = models.CharField(max_length=100)
    class Meta:
        ordering = ('name',)

class license(models.Model):
    software = models.ForeignKey(software)
    vendor_id = models.CharField(max_length=10,null=True,blank=True)
    class Meta:
        ordering = ('software','vendor_id')

class license_key(models.Model):
    software = models.ForeignKey(software)
    license  = models.ForeignKey(license)
    key = models.CharField(max_length=50,null=True,blank=True)
    class Meta:
        ordering = ('software','license','key')

class software_installation(models.Model):
    software = models.ForeignKey(software)
    license_key = models.ForeignKey(license_key,null=True,blank=True)
    class Meta:
        ordering = ('software','license_key')

The ordering Meta tag is important; if I remove the appropriate meta tags (I haven't worked out which ones yet), then everything works fine.

Brian May

PS Thanks for the very prompt response.

[Malcolm Tredinnick]

Fixed the description to prevent eye bleeding. We really a need a "preview" button people can use to check things ... oh, wait.. we have one! :-)

[mattrussell]

The names of the fields you've defined are shadowing the names of the classes.
i.e you cannot name a class "software" then use "software" as a attribute name for a field on another class (in the same module.
Python won't stop (or warn) you about this, but a tool such as pylink/pychecker would.
(nb. this is how python scoping works)
I'd be surprised if naming your classes in camel case notation (as PEP8 suggests) doesn't fix your problem.

[Malcolm Tredinnick]

In this particular case, the potential problems in comment:2 don't apply. The class names and attribute names are in different namespaces, so clashes won't occur (although it certainly makes the code confusing). There is a legitimate bug here.

The issue is that ordering joins aren't being promoted to outer joins in all the right circumstances.

[Malcolm Tredinnick]

(In [9916]) Fixed #10028 -- Fixed a problem when ordering by related models.

Some results were inadvertently being excluded if we were ordering across a
nullable relation which itself ordering by a non-nullable relation.

[Malcolm Tredinnick]

(In [9917]) [1.0.X] Fixed #10028 -- Fixed a problem when ordering by related models.

Some results were inadvertently being excluded if we were ordering across a
nullable relation which itself ordering by a non-nullable relation.

Backport of r9916 from trunk.

[Jacob]

Milestone 1.1 deleted