#8439 closed (fixed)
Q objects still incorrectly choosing inner join over outer join
Reported by: | mikemintz | Owned by: | Malcolm Tredinnick |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | dev |
Severity: | Keywords: | 1.0-blocker | |
Cc: | Triage Stage: | Accepted | |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
I am basically reopening #3592 with an example that is broken in the latest SVN. I am using SVN r8445.
Here are my test models:
class Enemy(models.Model): pass class Troop(models.Model): pass class Soldier(models.Model): troop = models.ForeignKey(Troop) class TroopKill(models.Model): enemy = models.ForeignKey(Enemy) troop = models.ForeignKey(Troop) class SoldierKill(models.Model): enemy = models.ForeignKey(Enemy) soldier = models.ForeignKey(Soldier) grenade = models.BooleanField()
I want to find all enemies that were killed either by my soldier, or by the troop my soldier is in. So I execute the following query:
direct_kills = Q(soldierkill__soldier__pk=1) troop_kills = Q(troopkill__troop__soldier__pk=1) all_kills = direct_kills | troop_kills print Enemy.objects.filter(all_kills).all()
And I get the correct SQL:
SELECT "enemy"."id" FROM "enemy" LEFT OUTER JOIN "soldierkill" ON ("enemy"."id" = "soldierkill"."enemy_id") LEFT OUTER JOIN "troopkill" ON ("enemy"."id" = "troopkill"."enemy_id") LEFT OUTER JOIN "troop" ON ("troopkill"."troop_id" = "troop"."id") LEFT OUTER JOIN "soldier" T6 ON ("troop"."id" = T6."troop_id") WHERE ("soldierkill"."soldier_id" = 1 OR T6."id" = 1 )
But when I try to add just one more condition (the direct kill had to have grenade=True):
direct_kills = Q(soldierkill__soldier__pk=1,soldierkill__grenade=True) troop_kills = Q(troopkill__troop__soldier__pk=1) all_kills = direct_kills | troop_kills print Enemy.objects.filter(all_kills).all()
It switches incorrectly to inner joins.
SELECT "enemy"."id" FROM "enemy" INNER JOIN "soldierkill" ON ("enemy"."id" = "soldierkill"."enemy_id") INNER JOIN "troopkill" ON ("enemy"."id" = "troopkill"."enemy_id") INNER JOIN "troop" ON ("troopkill"."troop_id" = "troop"."id") INNER JOIN "soldier" T6 ON ("troop"."id" = T6."troop_id") WHERE (("soldierkill"."grenade" = true AND "soldierkill"."soldier_id" = 1 ) OR T6."id" = 1 )
Am I doing something wrong, or is this broken?
Thanks,
Mike
Change History (5)
comment:1 by , 16 years ago
milestone: | → 1.0 |
---|---|
Owner: | changed from | to
Triage Stage: | Unreviewed → Accepted |
comment:2 by , 16 years ago
comment:3 by , 16 years ago
Keywords: | 1.0-blocker added |
---|
comment:4 by , 16 years ago
Resolution: | → fixed |
---|---|
Status: | new → closed |
Note:
See TracTickets
for help on using tickets.
#8389 has another example of the same problem if I need another test case.