Senseless query for complex relations

[theaspect@…]

Here my models

class Group(Model):
    pass

class SubGroup(Model):
    group = ForeignKey(Group)

class User(Model):
    group = (Group)
    subgroup = (SubGroup, null = true, blank = true)

so, I want all Users whose subgroup's group does not equal group, note null option for user

User.objects.except('subgroup__group' = F('group'))

in query we get all users with subgroup = null, and in iteration, all reference to user.subgroup.group will throw exception, because, obviously, subgroup is None

here sql that I've got from django

SELECT * FROM `user` LEFT OUTER JOIN `subgroup` ON (`user`.`subgroup_id` = `subgroup`.`id`) WHERE NOT ((`subgroup`.`group_id` =  `user`.`group_id` AND NOT (`subgroup`.`id` IS NULL)))

here what i'm expect

SELECT * FROM user u
join group g on g.id = u.group_id
left join subgroup s on u.subgroup_id = s.id and s.group_id = g.id
where not (s.group_id = u.group_id)

[Anssi Kääriäinen]

Your expected query isn't the correct one.

On Python level, you are excluding users for whom user.subgroup.group == user.group is True. If user.subgroup is None, then so is user.subgroup.group, and None == user.group is False. Thus the user will not be excluded if the user.subgroup is None.

On SQL level things work differently. If s.group_id is NULL, then s.group_id = u.group_id is unknown, and NOT unknown evaluates to unknown, that is to a value which is not true => the user will be excluded from the query. This is one of the joys of SQL's three-valued boolean logic :)