id summary reporter owner description type status component version severity resolution keywords cc stage has_patch needs_docs needs_tests needs_better_patch easy ui_ux 8439 Q objects still incorrectly choosing inner join over outer join mikemintz Malcolm Tredinnick "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" closed Database layer (models, ORM) dev fixed 1.0-blocker Accepted 0 0 0 0 0 0