#6658: Unexpected select_related() behaviour
---------------------------------------+------------------------------------
Reporter: [EMAIL PROTECTED] | Owner: nobody
Status: new | Component: Database wrapper
Version: SVN | Resolution:
Keywords: select_related, exclude | Stage: Unreviewed
Has_patch: 0 | Needs_docs: 0
Needs_tests: 0 | Needs_better_patch: 0
---------------------------------------+------------------------------------
Old description:
> 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))) O
> RDER 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.
New description:
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.
--
Ticket URL: <http://code.djangoproject.com/ticket/6658#comment:2>
Django Code <http://code.djangoproject.com/>
The web framework for perfectionists with deadlines
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-updates?hl=en
-~----------~----~----~----~------~----~------~--~---
