Code

Opened 6 years ago

Closed 6 years ago

Last modified 3 years ago

#8439 closed (fixed)

Q objects still incorrectly choosing inner join over outer join

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

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

Attachments (0)

Change History (5)

comment:1 Changed 6 years ago by mtredinnick

  • milestone set to 1.0
  • Needs documentation unset
  • Needs tests unset
  • Owner changed from nobody to mtredinnick
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted

comment:2 Changed 6 years ago by mtredinnick

#8389 has another example of the same problem if I need another test case.

comment:3 Changed 6 years ago by jacob

  • Keywords 1.0-blocker added

comment:4 Changed 6 years ago by mtredinnick

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

(In [8832]) Fixed #8439 -- Complex combinations of Q-objects (using both conjunctions and
disjunctions) were producing incorrect SQL when nullable relations were
involved. This fixes that.

comment:5 Changed 3 years ago by jacob

  • milestone 1.0 deleted

Milestone 1.0 deleted

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.