Opened 7 years ago

Closed 6 years ago

Last modified 4 years ago

#10028 closed (fixed)

Incorrect SQL join construction

Reported by: brian Owned by: mtredinnick
Component: Database layer (models, ORM) Version: 1.0
Severity: Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description (last modified by mtredinnick)

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.

Change History (7)

comment:1 Changed 7 years ago by mtredinnick

  • Description modified (diff)
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Summary changed from no objects found to Incorrect SQL join construction

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! :-)

comment:2 Changed 7 years ago by 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.

comment:3 Changed 6 years ago by jacob

  • milestone set to 1.1
  • Triage Stage changed from Unreviewed to Accepted

comment:4 Changed 6 years ago by mtredinnick

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.

comment:5 Changed 6 years ago by mtredinnick

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

(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.

comment:6 Changed 6 years ago by mtredinnick

(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.

comment:7 Changed 4 years ago by jacob

  • milestone 1.1 deleted

Milestone 1.1 deleted

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