Opened 17 years ago
Closed 17 years ago
#6658 closed (fixed)
Unexpected select_related() behaviour
Reported by: | Owned by: | nobody | |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | dev |
Severity: | Keywords: | select_related, exclude qs-rf-fixed | |
Cc: | Triage Stage: | Fixed on a branch | |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
select_related() sometimes seems to break queries with multiple chained .exclude's.
Let's say we're having the following example:
Thread.objects.exclude(forum__type = 'fcin').exclude(forum__type = 'stin').exclude(forum__type = 'suin').exclude(forum__type = 'fc').order_by('-creationDate')[:8]
returns:
u'SELECT `forum_thread`.`id`,`forum_thread`.`forum_id`,`forum_thread`.`headline`,`forum_thread`.`author_id`,`forum_thread`.`author_user_id`,`forum_thread`.`text`,`forum_thread`.`creationDate`,`forum_thread`.`updateDate`,`forum_thread`.`hitCount` FROM `forum_thread` INNER JOIN `forum_forum` AS `forum_thread__forum` ON `forum_thread`.`forum_id` = `forum_thread__forum`.`id` WHERE ((NOT (`forum_thread__forum`.`type` = fcin)) AND (NOT (`forum_thread__forum`.`type` = stin)) AND (NOT (`forum_thread__forum`.`type` = suin)) AND (NOT (`forum_thread__forum`.`type` = fc))) ORDER BY `forum_thread`.`creationDate` DESC LIMIT 8 '
resulting in three results
Thread.objects.select_related().exclude(forum__type = 'fcin').exclude(forum__type = 'stin').exclude(forum__type = 'suin').exclude(forum__type = 'fc').order_by('-creationDate')[:8]
returns:
u'SELECT `forum_thread`.`id`,`forum_thread`.`forum_id`,`forum_thread`.`headline`,`forum_thread`.`author_id`,`forum_thread`.`author_user_id`,`forum_thread`.`text`,`forum_thread`.`creationDate`,`forum_thread`.`updateDate`,`forum_thread`.`hitCount`,`forum_forum`.`id`,`forum_forum`.`parent_id`,`forum_forum`.`name`,`forum_forum`.`description`,`forum_forum`.`type`,`forum_forum`.`hitCount`,`forum_forum`.`forumFCTeam_id`,`forum_forum`.`forumFCPlayer_id`,`community_userprofile`.`id`,`community_userprofile`.`gender`,`community_userprofile`.`birthdaydate`,`community_userprofile`.`city`,`community_userprofile`.`zip`,`community_userprofile`.`picture`,`community_userprofile`.`organisation`,`community_userprofile`.`taskInOrganisation`,`community_userprofile`.`ICQ`,`community_userprofile`.`MSN`,`community_userprofile`.`Skype`,`community_userprofile`.`website`,`community_userprofile`.`irc_channel`,`community_userprofile`.`forumThreadCount`,`community_userprofile`.`forumPostCount`,`community_userprofile`.`gBookEntriesMade`,`community_userprofile`.`commentCount`,`community_userprofile`.`globalForumAdmin`,`community_userprofile`.`occupation`,`community_userprofile`.`occupationDetails`,`community_userprofile`.`aboutMe`,`community_userprofile`.`favGame_id`,`community_userprofile`.`favGameCustom`,`community_userprofile`.`favSeries`,`community_userprofile`.`favFood`,`community_userprofile`.`favClub`,`community_userprofile`.`favDrink`,`community_userprofile`.`favMovie`,`community_userprofile`.`favBook`,`community_userprofile`.`favMobile`,`community_userprofile`.`favBand`,`community_userprofile`.`favSong`,`community_userprofile`.`favCarBrand`,`community_userprofile`.`favCar`,`community_userprofile`.`favSport`,`community_userprofile`.`favCustom`,`community_userprofile`.`wforGame`,`community_userprofile`.`wforHardware`,`community_userprofile`.`wforMovie`,`community_userprofile`.`wforIdea`,`community_userprofile`.`PCPicture`,`community_userprofile`.`PCCPU`,`community_userprofile`.`PCRAM`,`community_userprofile`.`PCHDD`,`community_userprofile`.`PCGPU`,`community_userprofile`.`PCSPU`,`community_userprofile`.`PCMonitor`,`community_userprofile`.`PCPSU`,`community_userprofile`.`PCMB`,`community_userprofile`.`PCProvider`,`community_userprofile`.`PCConnection`,`community_userprofile`.`PCCustom`,`community_userprofile`.`PCMouse`,`community_userprofile`.`PCPad`,`community_userprofile`.`PCHeadset`,`community_userprofile`.`PCBungee`,`community_userprofile`.`PCSkates`,`community_userprofile`.`PCKeyboard`,`community_userprofile`.`PCOther`,`community_userprofile`.`gallery_id`,`community_userprofile`.`adminComments_id`,`community_userprofile`.`user_id`,`community_userprofile`.`betsWon`,`community_userprofile`.`betsLost`,`community_userprofile`.`hitCount`,`community_userprofile`.`showLastname`,`community_userprofile`.`showTFC`,`community_userprofile`.`showPFC`,`auth_user`.`id`,`auth_user`.`username`,`auth_user`.`first_name`,`auth_user`.`last_name`,`auth_user`.`email`,`auth_user`.`password`,`auth_user`.`is_staff`,`auth_user`.`is_active`,`auth_user`.`is_superuser`,`auth_user`.`last_login`,`auth_user`.`date_joined` FROM `forum_thread` INNER JOIN `forum_forum` AS `forum_thread__forum` ON `forum_thread`.`forum_id` = `forum_thread__forum`.`id` , `forum_forum`, `community_userprofile`, `auth_user` WHERE ((NOT (`forum_thread__forum`.`type` = fcin)) AND (NOT (`forum_thread__forum`.`type` = stin)) AND (NOT (`forum_thread__forum`.`type` = suin)) AND (NOT (`forum_thread__forum`.`type` = fc))) AND `forum_thread`.`forum_id` = `forum_forum`.`id` AND `forum_thread`.`author_id` = `community_userprofile`.`id` AND `community_userprofile`.`user_id` = `auth_user`.`id` ORDER BY `forum_thread`.`creationDate` DESC LIMIT 8 '
resulting in NO results
If you need additional info about the Models, please say so.
Change History (7)
comment:1 by , 17 years ago
Description: | modified (diff) |
---|
comment:2 by , 17 years ago
Description: | modified (diff) |
---|
comment:3 by , 17 years ago
Keywords: | qs-rf added |
---|
comment:4 by , 17 years ago
Triage Stage: | Unreviewed → Accepted |
---|
Please post the relevant fields from you model. If Thread.forum
is a ManyToMany relation, you have multiple colliding bugs here. exclude()
with many-to-many is a bit flawed at the moment.
Could you also check that for the three elements returned in the first query, there really are existing entries in those related tables. I can't see why there wouldn't be, but let's rule that out. I can't quite repeat your problem at the moment, but that's partly because I'm fixing exclude() first, which could well change the results of your first query (although, again, I don't quite see how at the moment). Understanding which breakage you're hitting will help.
comment:5 by , 17 years ago
class Forum(models.Model): parent = models.ForeignKey('self', null=True, blank=True, unique=False) name = models.CharField(max_length=50) description = models.CharField(max_length=100, null=True, blank=True) type = models.CharField(max_length=6, blank=False, null=False, choices=FORUM_TYPES, default='p') hitCount = models.IntegerField("Zugriffe", default=0, blank=True, null=True) forumFCTeam = models.ForeignKey(Team, verbose_name="FanClub Team", null=True, blank=True) forumFCPlayer = models.ForeignKey(Player, verbose_name="FanClub Player", null=True, blank=True) def __unicode__(self): return self.name class Thread(models.Model): forum = models.ForeignKey(Forum, related_name="Threads", core=True) headline = models.CharField(max_length=100) author = models.ForeignKey(UserProfile, related_name="ForumThreadUserProfile", core=True) author_user = models.ForeignKey(User, related_name="ForumThreadUser", null=True, blank=True, editable=False) text = models.TextField() creationDate = models.DateTimeField(null=True, editable=False, blank=True) updateDate = models.DateTimeField(null=True, editable=False, blank=True, default=datetime.datetime.now()) hitCount = models.IntegerField("Zugriffe", default=0, null=True, blank=True) def __unicode__(self): return self.headline
comment:6 by , 17 years ago
Keywords: | qs-rf-fixed added; qs-rf removed |
---|---|
Triage Stage: | Accepted → Fixed on a branch |
It's a little hard to tell from this example (a simpler one exhibiting the same failure would have been nice), but I think this is caused by an unnecessary table join in the select_related()
case. The query is joining to forum_forum
twice there, but that rules out the effect of the earlier filtering.
The queryset-refactor code doesn't create this extra table join and I can't make it fail in any similar way. So I'm pretty sure this is fixed on the branch. This ticket will be closed when the branch is merged into trunk.
comment:7 by , 17 years ago
Resolution: | → fixed |
---|---|
Status: | new → closed |
(In [7477]) Merged the queryset-refactor branch into trunk.
This is a big internal change, but mostly backwards compatible with existing
code. Also adds a couple of new features.
Fixed #245, #1050, #1656, #1801, #2076, #2091, #2150, #2253, #2306, #2400, #2430, #2482, #2496, #2676, #2737, #2874, #2902, #2939, #3037, #3141, #3288, #3440, #3592, #3739, #4088, #4260, #4289, #4306, #4358, #4464, #4510, #4858, #5012, #5020, #5261, #5295, #5321, #5324, #5325, #5555, #5707, #5796, #5817, #5987, #6018, #6074, #6088, #6154, #6177, #6180, #6203, #6658
Fixed description.