13
votes

Les annotations Django Count et Sum interfèrent les unes avec les autres

Lors de la construction d'un QuerySet complexe avec plusieurs annotations, j'ai rencontré un problème que je pouvais reproduire avec la configuration simple suivante.

Voici les modèles:

SELECT "sandbox_player"."id",
       "sandbox_player"."name",
       COUNT(DISTINCT "sandbox_weapon"."id") AS "weapon_count",
       SUM("sandbox_unit"."rarity")          AS "rarity_sum"
FROM "sandbox_player"
         LEFT OUTER JOIN "sandbox_unit" ON ("sandbox_player"."id" = "sandbox_unit"."player_id")
         LEFT OUTER JOIN "sandbox_weapon" ON ("sandbox_unit"."id" = "sandbox_weapon"."unit_id")
GROUP BY "sandbox_player"."id", "sandbox_player"."name"

Avec ma base de données de test, j'obtiens les résultats (corrects) suivants:

Player.objects.annotate(
    weapon_count=Count('unit_set__weapon_set', distinct=True),
    rarity_sum=Sum('unit_set__rarity'))

[{'id': 1, 'name': 'James', 'weapon_count': 23, 'rarity_sum': 99},
 {'id': 2, 'name': 'Max', 'weapon_count': 41, 'rarity_sum': 183},
 {'id': 3, 'name': 'Bob', 'weapon_count': 26, 'rarity_sum': 113}]

Si je combine maintenant les deux annotations dans le même QuerySet , QuerySet un résultat différent (inexact):

Player.objects.annotate(weapon_count=Count('unit_set__weapon_set'))

[{'id': 1, 'name': 'James', 'weapon_count': 23},
 {'id': 2, 'name': 'Max', 'weapon_count': 41},
 {'id': 3, 'name': 'Bob', 'weapon_count': 26}]


Player.objects.annotate(rarity_sum=Sum('unit_set__rarity'))

[{'id': 1, 'name': 'James', 'rarity_sum': 42},
 {'id': 2, 'name': 'Max', 'rarity_sum': 89},
 {'id': 3, 'name': 'Bob', 'rarity_sum': 67}]

Remarquez comment rarity_sum a maintenant des valeurs différentes de celles d'avant. La suppression de distinct=True n'affecte pas le résultat. J'ai également essayé d'utiliser la fonction DistinctSum de cette réponse , auquel cas tous les rarity_sum sont définis sur 18 (également inexacts).

Pourquoi est-ce? Comment combiner les deux annotations dans le même QuerySet ?

Edit : voici la requête sqlite générée par le QuerySet combiné:

class Player(models.Model):
    name = models.CharField(max_length=200)

class Unit(models.Model):
    player = models.ForeignKey(Player, on_delete=models.CASCADE,
                               related_name='unit_set')
    rarity = models.IntegerField()

class Weapon(models.Model):
    unit = models.ForeignKey(Unit, on_delete=models.CASCADE,
                             related_name='weapon_set')

Les données utilisées pour les résultats ci-dessus sont disponibles ici .


8 commentaires

J'ai essayé de reproduire le comportement. Malheureusement, j'ai obtenu le bon résultat. Capture d'écran de Django Shell . Est-ce que je manque quelque chose?


@JPG: maintenant c'est bizarre. Quelle configuration utilisez-vous? J'utilise Django 2.2.2, Python 3.7.3 et un backend sqlite. Projet 100% vanilla avec l'application Django_extensions incluse et les modèles ci-dessus.


Python 3.6.7 et Django 2.2.1 avec sqlite DB.


Voulez-vous essayer avec exactement les mêmes données que moi?


Aussi, obtenez-vous la même requête SQL (voir ma question modifiée)? Pouvez-vous exécuter ma requête SQL sur votre base de données pour vérifier si vous obtenez des résultats cohérents?


Ouais. Maintenant, j'ai le même résultat que vous. je


Je rencontre ce comportement également même si je n'ai pas eu le temps de mettre en place un exemple simple pour signaler le bogue. Je suppose que Django ORM fait des trucs bizarres et construit de mauvaises requêtes. Afin d'éviter cela, j'ai décidé d'utiliser Subquery . De cette façon, je m'assure que la requête effectuée est exactement celle que je souhaite


@ivissani: comment procéderiez-vous dans son cas?


3 Réponses :


30
votes

Ce n'est pas le problème avec Django ORM, c'est juste la façon dont les bases de données relationnelles fonctionnent. Lorsque vous créez des ensembles de requêtes simples comme

SELECT "sandbox_player"."id", "sandbox_player"."name", 
(
    SELECT COUNT(U2."id") AS "weapon_count"
    FROM "sandbox_player" U0 
    LEFT OUTER JOIN "sandbox_unit" U1
        ON (U0."id" = U1."player_id")
    LEFT OUTER JOIN "sandbox_weapon" U2 
        ON (U1."id" = U2."unit_id")
    WHERE U0."id" = ("sandbox_player"."id") 
    GROUP BY U0."id", U0."name"
) AS "weapon_count", 
(
    SELECT SUM(U1."rarity") AS "rarity_sum"
    FROM "sandbox_player" U0
    LEFT OUTER JOIN "sandbox_unit" U1
        ON (U0."id" = U1."player_id")
    WHERE U0."id" = ("sandbox_player"."id")
GROUP BY U0."id", U0."name") AS "rarity_sum"
FROM "sandbox_player"

ou

>>> from django.db.models import Count, IntegerField, OuterRef, Subquery, Sum
>>> weapon_count = Player.objects.annotate(weapon_count=Count('unit_set__weapon_set')).filter(pk=OuterRef('pk'))
>>> rarity_sum = Player.objects.annotate(rarity_sum=Sum('unit_set__rarity')).filter(pk=OuterRef('pk'))
>>> qs = Player.objects.annotate(
...     weapon_count=Subquery(weapon_count.values('weapon_count'), output_field=IntegerField()),
...     rarity_sum=Subquery(rarity_sum.values('rarity_sum'), output_field=IntegerField())
... )
>>> qs.values()
<QuerySet [{'id': 1, 'name': 'player_1', 'weapon_count': 2, 'rarity_sum': 10}]>

ORM fait exactement ce que vous attendez de lui: rejoignez Player with Weapon

sqlite> SELECT "sandbox_player"."id",
   ...>        "sandbox_player"."name",
   ...>        "sandbox_weapon"."id",
   ...>        "sandbox_unit"."rarity"
   ...> FROM "sandbox_player"
   ...>          LEFT OUTER JOIN "sandbox_unit" ON ("sandbox_player"."id" = "sandbox_unit"."player_id")
   ...>          LEFT OUTER JOIN "sandbox_weapon" ON ("sandbox_unit"."id" = "sandbox_weapon"."unit_id");
id          name        id          rarity    
----------  ----------  ----------  ----------
1           player_1    1           10        
1           player_1    2           10   

ou Player avec Unit

>>> from sandbox.models import Player
>>> from django.db.models import Count, Sum

>>> Player.objects.annotate(weapon_count=Count('unit_set__weapon_set')).values()
<QuerySet [{'id': 1, 'name': 'player_1', 'weapon_count': 2}]>

>>> Player.objects.annotate(rarity_sum=Sum('unit_set__rarity')).values()
<QuerySet [{'id': 1, 'name': 'player_1', 'rarity_sum': 10}]>


>>> Player.objects.annotate(
...     weapon_count=Count('unit_set__weapon_set', distinct=True),
...     rarity_sum=Sum('unit_set__rarity')).values()
<QuerySet [{'id': 1, 'name': 'player_1', 'weapon_count': 2, 'rarity_sum': 20}]>

et effectuez une agrégation COUNT ou SUM sur eux.

Notez que bien que la première requête ait deux jointures entre trois tables, la table intermédiaire Unit n'est ni dans les colonnes référencées dans SELECT , ni dans la clause GROUP BY . Le seul rôle joué par l' Unit ici est de rejoindre le Player avec une Weapon .

Maintenant, si vous regardez votre troisième jeu de requêtes, les choses se compliquent. Encore une fois, comme dans la première requête, les jointures sont entre trois tables, mais maintenant Unit est référencée dans SELECT car il existe une agrégation SUM pour Unit.rarity :

insert into sandbox_player values (1, "player_1");

insert into sandbox_unit values(1, 10, 1);

insert into sandbox_weapon values (1, 1), (2, 1);

Et c'est la différence cruciale entre la deuxième et la troisième requêtes. Dans la deuxième requête, vous joignez le Player à l' Unit , donc une seule Unit sera répertoriée une fois pour chaque joueur auquel elle fait référence.

Mais dans la troisième requête, vous joignez le Player à l' Unit , puis l' Unit à l' Weapon , donc non seulement une seule Unit sera répertoriée une fois pour chaque joueur auquel elle fait référence, mais également pour chaque arme qui fait référence à l' Unit .

Jetons un coup d'œil à l'exemple simple:

SELECT "sandbox_player"."id",
       "sandbox_player"."name",
       COUNT(DISTINCT "sandbox_weapon"."id") AS "weapon_count",
       SUM("sandbox_unit"."rarity")          AS "rarity_sum"
FROM "sandbox_player"
         LEFT OUTER JOIN "sandbox_unit" ON ("sandbox_player"."id" = "sandbox_unit"."player_id")
         LEFT OUTER JOIN "sandbox_weapon" ON ("sandbox_unit"."id" = "sandbox_weapon"."unit_id")
GROUP BY "sandbox_player"."id", "sandbox_player"."name"

Un joueur, une unité et deux armes qui font référence à la même unité.

Confirmez que le problème existe:

SELECT "sandbox_player"."id", "sandbox_player"."name", SUM("sandbox_unit"."rarity") AS "rarity_sum"
FROM "sandbox_player"
LEFT OUTER JOIN "sandbox_unit" ON ("sandbox_player"."id" = "sandbox_unit"."player_id")
GROUP BY "sandbox_player"."id", "sandbox_player"."name"

À partir de cet exemple, il est facile de voir que le problème est que dans la requête combinée, l'unité sera répertoriée deux fois, une fois pour chacune des armes qui la référencent:

SELECT "sandbox_player"."id", "sandbox_player"."name", COUNT("sandbox_weapon"."id") AS "weapon_count"
FROM "sandbox_player"
LEFT OUTER JOIN "sandbox_unit" 
    ON ("sandbox_player"."id" = "sandbox_unit"."player_id")
LEFT OUTER JOIN "sandbox_weapon" 
    ON ("sandbox_unit"."id" = "sandbox_weapon"."unit_id")
GROUP BY "sandbox_player"."id", "sandbox_player"."name"

Que devrais tu faire?

Comme @ivissani l'a mentionné, l'une des solutions les plus simples serait d'écrire des sous-requêtes pour chacune des agrégations:

Player.objects.annotate(rarity_sum=Sum('unit_set__rarity'))

qui produit le SQL suivant

Player.objects.annotate(weapon_count=Count('unit_set__weapon_set'))


10 commentaires

Merci pour votre réponse. Vos deux sous-requêtes manquent une .filter(pk=OuterRef('pk')) pour donner des résultats précis avec mes données, cependant. Aussi, pourquoi distinct=True requis sur la sous-requête?


Aussi, pour les enregistrements. Bien que je comprenne ce que vous voulez dire, je ne suis pas d'accord pour dire que ce n'est pas un problème d'ORM de Django. Je pense que ma requête de combinaison d'origine capture assez bien mon intention et devrait fonctionner immédiatement. En fait, j'ai découvert que c'était officiellement considéré comme un bug et référencé dans la documentation officielle .


@abey, en effet, il devrait y avoir .filter appel .filter , sinon weapon_count et rarity_sum seront les mêmes pour tous les joueurs. C'est plutôt malheureux, car la sous-requête renvoie plusieurs comptes (un pour chaque joueur) et sqlite met joyeusement le premier compte dans la requête tout en jetant tout le reste, il est donc assez facile à ignorer. PostgreSQL, par exemple, lancera une erreur dans ce cas: more than one row returned by a subquery used as an expression . J'ai mis à jour le code Python et SQL.


@abey distinct semble s'y être glissé à partir des exemples que j'ai copiés à partir de votre question. Dans le cas de la sous-requête COUNT(DISTINCT ..) ne fait pas de mal, mais c'est plutôt inutile, donc je l'ai supprimée aussi.


@abey Le fait de le considérer comme un bogue ou non dépend entièrement de votre position concernant l'ORM. Si vous traitez l'appel .annotate comme "mettez COUNT dans ma requête" (ce qui semble assez logique), alors tout fonctionne comme prévu. Mais si en mettant .annotate vous voulez dire "compter les objets", alors c'est bien un bug. Cependant, les intentions des utilisateurs et la logique sous-jacente n'ont pas de mappage 1 à 1, donc, à mon avis, la solution idéale serait de produire une erreur ou un avertissement dans ce cas. Transformer la requête en sous-requête ou en quelque chose de très complexe me semble automatiquement trop magique et inattendu.


Voyant que diviser la requête en deux instructions .annotate successives a le même comportement erroné, et que deux morceaux de code totalement indépendants pourraient ajouter une telle instruction .annotate chacun au même QuerySet , je maintiendrai ma position selon laquelle il s'agit bien d'un punaise. Et je ne veux pas être en désaccord avec les responsables;)


Belles découvertes. +1 de moi :)


Merveilleuse explication! Je pense que cela devrait être ajouté à la documentation Django: D


Indépendamment du fait que annotate () devrait "intrinsèquement" signifier ceci ou cela, il n'en reste pas moins que ce bogue hante depuis 11 ans les développeurs Django qui ont perdu des dizaines d'heures chacun pour le trouver (moi y compris, tout à l'heure). Ce n'est objectivement tout simplement pas le comportement attendu.


Je ne parviens pas à faire fonctionner cela dans la version 3.0.7. Obtenir AttributeError: 'IntegerField' object has no attribute 'select_format'



8
votes

Quelques notes pour compléter l'excellente réponse de rktavi:

1) Ce problème est apparemment considéré comme un bogue depuis 10 ans déjà. Il est même mentionné dans la documentation officielle .

2) Lors de la conversion des QuerySets de mon projet actuel en sous-requêtes (selon la réponse de rktavi), j'ai remarqué que combiner des annotations Subquery (pour les comptes distinct=True qui fonctionnaient toujours correctement) avec une sous- Subquery (pour les sommes) donne un traitement extrêmement long ( 35 sec contre 100 ms) et des résultats incorrects pour la somme. Cela est vrai dans ma configuration actuelle (11 comptes filtrés sur diverses relations imbriquées et 1 somme filtrée sur une relation multi-imbriquée, SQLite3) mais ne peut pas être reproduit avec les modèles simples ci-dessus. Ce problème peut être délicat car une autre partie de votre code pourrait ajouter une annotation à votre QuerySet (par exemple une fonction Table.order_FOO() ), conduisant au problème.

3) Avec la même configuration, j'ai des preuves anecdotiques que les QuerySets de type sous-requête sont plus rapides que les QuerySets d'annotation à nu (dans les cas où vous n'avez que distinct=True comptes distinct=True , bien sûr). J'ai pu observer cela à la fois avec SQLite3 local (83 ms contre 260 ms) et PostgreSQL hébergé (320 ms contre 540 ms).

En raison de ce qui précède, j'éviterai complètement d'utiliser des annotations simples en faveur des sous-requêtes.


0 commentaires

1
votes

Sur la base de l'excellente réponse de @rktavi, j'ai créé deux classes helpers qui simplifient les Subquery / Count et Subquery / Sum :

from django.db.models import OuterRef

weapons = Weapon.objects.filter(unit__player_id=OuterRef('id'))
units = Unit.objects.filter(player_id=OuterRef('id'))

qs = Player.objects.annotate(weapon_count=SubqueryCount(weapons),
                             rarity_sum=SubquerySum(units, 'rarity'))

On peut utiliser ces aides comme ceci:

class SubqueryCount(Subquery):
    template = "(SELECT count(*) FROM (%(subquery)s) _count)"
    output_field = PositiveIntegerField()


class SubquerySum(Subquery):
    template = '(SELECT sum(_sum."%(column)s") FROM (%(subquery)s) _sum)'

    def __init__(self, queryset, column, output_field=None, **extra):
        if output_field is None:
            output_field = queryset.model._meta.get_field(column)
        super().__init__(queryset, output_field, column=column, **extra)


0 commentaires