#28297: Same queryset result in two different queries on ORM
-------------------------------------+-------------------------------------
Reporter: Marcus Renno | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: join, annotation, F | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by Marcus Renno:
Old description:
> Sometimes when I run a set of filter/annotation the result is different
> for the same variables.
>
> This is the queryset command:
>
> {{{
> ingredients = ['tomato']
> self.queryset = Recipe.objects.all()
> self.queryset =
> self.queryset.annotate(total=Count('steps__ingredients', distinct=True))
> self.queryset =
> self.queryset.filter(steps__ingredients__ingredient__name__in=ingredients)
> self.queryset =
> self.queryset.annotate(available=Count('steps__ingredients',
> distinct=True))
> self.queryset = self.queryset.filter(total=F('available'))
> }}}
>
> This is the wrong query result that comes often times:
>
> {{{
> SELECT recipebook_recipe.id, recipebook_recipe.name,
> recipebook_recipe.dificulty, recipebook_recipe.duration, COUNT(DISTINCT
> recipebook_steprecipe_ingredients.recipeingredient_id) AS total,
> COUNT(DISTINCT recipebook_steprecipe_ingredients.recipeingredient_id) AS
> available FROM recipebook_recipe LEFT OUTER JOIN recipebook_recipe_steps
> ON (recipebook_recipe.id = recipebook_recipe_steps.recipe_id) LEFT OUTER
> JOIN recipebook_steprecipe ON (recipebook_recipe_steps.steprecipe_id =
> recipebook_steprecipe.id) LEFT OUTER JOIN
> recipebook_steprecipe_ingredients ON (recipebook_steprecipe.id =
> recipebook_steprecipe_ingredients.steprecipe_id) INNER JOIN
> recipebook_recipe_steps T6 ON (recipebook_recipe.id = T6.recipe_id) INNER
> JOIN recipebook_steprecipe T7 ON (T6.steprecipe_id = T7.id) INNER JOIN
> recipebook_steprecipe_ingredients T8 ON (T7.id = T8.steprecipe_id) INNER
> JOIN recipebook_recipeingredient T9 ON (T8.recipeingredient_id = T9.id)
> INNER JOIN recipebook_ingredient ON (T9.ingredient_id =
> recipebook_ingredient.id) WHERE recipebook_ingredient.name IN ("tomato")
> GROUP BY recipebook_recipe.id HAVING COUNT(DISTINCT
> recipebook_steprecipe_ingredients.recipeingredient_id) = (COUNT(DISTINCT
> recipebook_steprecipe_ingredients.recipeingredient_id)) ORDER BY NULL
> }}}
>
> And this is the right query that shows up sometimes:
>
> {{{
> SELECT recipebook_recipe.id, recipebook_recipe.name,
> recipebook_recipe.dificulty, recipebook_recipe.duration, COUNT(DISTINCT
> recipebook_steprecipe_ingredients.recipeingredient_id) AS total,
> COUNT(DISTINCT T8.recipeingredient_id) AS available FROM
> recipebook_recipe LEFT OUTER JOIN recipebook_recipe_steps ON
> (recipebook_recipe.id = recipebook_recipe_steps.recipe_id) LEFT OUTER
> JOIN recipebook_steprecipe ON (recipebook_recipe_steps.steprecipe_id =
> recipebook_steprecipe.id) LEFT OUTER JOIN
> recipebook_steprecipe_ingredients ON (recipebook_steprecipe.id =
> recipebook_steprecipe_ingredients.steprecipe_id) INNER JOIN
> recipebook_recipe_steps T6 ON (recipebook_recipe.id = T6.recipe_id) INNER
> JOIN recipebook_steprecipe T7 ON (T6.steprecipe_id = T7.id) INNER JOIN
> recipebook_steprecipe_ingredients T8 ON (T7.id = T8.steprecipe_id) INNER
> JOIN recipebook_recipeingredient T9 ON (T8.recipeingredient_id = T9.id)
> INNER JOIN recipebook_ingredient ON (T9.ingredient_id =
> recipebook_ingredient.id) WHERE recipebook_ingredient.name IN ("tomato")
> GROUP BY recipebook_recipe.id HAVING COUNT(DISTINCT
> recipebook_steprecipe_ingredients.recipeingredient_id) = (COUNT(DISTINCT
> T8.recipeingredient_id)) ORDER BY NULL
> }}}
>
> As you can see they are different. The wrong one does not set the
> variable 'available' appropriately. I wonder if this is something on the
> method `.query.join()`
New description:
Sometimes when I run a set of filter/annotation the result is different
for the same variables.
This is the queryset command:
{{{
ingredients = ['tomato']
self.queryset = Recipe.objects.all()
self.queryset = self.queryset.annotate(total=Count('steps__ingredients',
distinct=True))
self.queryset =
self.queryset.filter(steps__ingredients__ingredient__name__in=ingredients)
self.queryset =
self.queryset.annotate(available=Count('steps__ingredients',
distinct=True))
self.queryset = self.queryset.filter(total=F('available'))
}}}
This is the wrong query result that comes often times:
{{{
SELECT recipebook_recipe.id, recipebook_recipe.name,
recipebook_recipe.dificulty, recipebook_recipe.duration, COUNT(DISTINCT
recipebook_steprecipe_ingredients.recipeingredient_id) AS total,
COUNT(DISTINCT recipebook_steprecipe_ingredients.recipeingredient_id) AS
available FROM recipebook_recipe LEFT OUTER JOIN recipebook_recipe_steps
ON (recipebook_recipe.id = recipebook_recipe_steps.recipe_id) LEFT OUTER
JOIN recipebook_steprecipe ON (recipebook_recipe_steps.steprecipe_id =
recipebook_steprecipe.id) LEFT OUTER JOIN
recipebook_steprecipe_ingredients ON (recipebook_steprecipe.id =
recipebook_steprecipe_ingredients.steprecipe_id) INNER JOIN
recipebook_recipe_steps T6 ON (recipebook_recipe.id = T6.recipe_id) INNER
JOIN recipebook_steprecipe T7 ON (T6.steprecipe_id = T7.id) INNER JOIN
recipebook_steprecipe_ingredients T8 ON (T7.id = T8.steprecipe_id) INNER
JOIN recipebook_recipeingredient T9 ON (T8.recipeingredient_id = T9.id)
INNER JOIN recipebook_ingredient ON (T9.ingredient_id =
recipebook_ingredient.id) WHERE recipebook_ingredient.name IN ("tomato")
GROUP BY recipebook_recipe.id HAVING COUNT(DISTINCT
recipebook_steprecipe_ingredients.recipeingredient_id) = (COUNT(DISTINCT
recipebook_steprecipe_ingredients.recipeingredient_id)) ORDER BY NULL
}}}
And this is the right query that shows up sometimes:
{{{
SELECT recipebook_recipe.id, recipebook_recipe.name,
recipebook_recipe.dificulty, recipebook_recipe.duration, COUNT(DISTINCT
recipebook_steprecipe_ingredients.recipeingredient_id) AS total,
COUNT(DISTINCT T8.recipeingredient_id) AS available FROM recipebook_recipe
LEFT OUTER JOIN recipebook_recipe_steps ON (recipebook_recipe.id =
recipebook_recipe_steps.recipe_id) LEFT OUTER JOIN recipebook_steprecipe
ON (recipebook_recipe_steps.steprecipe_id = recipebook_steprecipe.id) LEFT
OUTER JOIN recipebook_steprecipe_ingredients ON (recipebook_steprecipe.id
= recipebook_steprecipe_ingredients.steprecipe_id) INNER JOIN
recipebook_recipe_steps T6 ON (recipebook_recipe.id = T6.recipe_id) INNER
JOIN recipebook_steprecipe T7 ON (T6.steprecipe_id = T7.id) INNER JOIN
recipebook_steprecipe_ingredients T8 ON (T7.id = T8.steprecipe_id) INNER
JOIN recipebook_recipeingredient T9 ON (T8.recipeingredient_id = T9.id)
INNER JOIN recipebook_ingredient ON (T9.ingredient_id =
recipebook_ingredient.id) WHERE recipebook_ingredient.name IN ("tomato")
GROUP BY recipebook_recipe.id HAVING COUNT(DISTINCT
recipebook_steprecipe_ingredients.recipeingredient_id) = (COUNT(DISTINCT
T8.recipeingredient_id)) ORDER BY NULL
}}}
As you can see they are different. The wrong one does not set the variable
'available' appropriately. I wonder if this is something on the method
`.query.join()`
--
--
Ticket URL: <https://code.djangoproject.com/ticket/28297#comment:1>
Django <https://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 unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/069.c3d57f103b703e8f796c17b103f389f2%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
