Code

Opened 3 years ago

Closed 3 years ago

#16766 closed Uncategorized (invalid)

Senseless query for complex relations

Reported by: theaspect@… Owned by: nobody
Component: Database layer (models, ORM) Version: 1.3
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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)

Attachments (0)

Change History (1)

comment:1 Changed 3 years ago by akaariai

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Resolution set to invalid
  • Status changed from new to closed

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

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.