Problem with get_children() method when using TreeManager. Wrong result.

[jcuotpc]

I have encountered an strange problem with the use of TreeManager

Here is my code:

# other imports
from mptt.models import MPTTModel, TreeForeignKey
from mptt.managers import TreeManager

class SectionManager(TreeManager):
def get_queryset(self):
    return super().get_queryset().filter(published=True)

class Section(MPTTModel):
    published = models.BooleanField(
    default=True,
    help_text="If unpublished, this section will show only"
              " to editors. Else, it will show for all."
)

objects = TreeManager()
published_objects = SectionManager()

When I test it. I get the following correct results:

# show all objects
Section.objects.count()  # result is correct - 65
Section.objects.root_nodes().count() # result is correct - 12

# show published objects, just one is not published.
Section.published_objects.count() # result is correct - 64
Section.published_objects.root_nodes().count()  # result is corrct - 12

But one child of the roots is unpublished and it does not show in the results. Here is the test:

for root in Section.objects.root_nodes(): 
    print(f"root_section_{root.id} has {root.get_children().count()} children") 

    # results ...
    root_section_57 has 13 children # correct - 13 items
    # ... more results

for root in Section.published_objects.root_nodes(): 
    print(f"root_section_{root.id} has {root.get_children().count()} children") 

    # results ...
    root_section_57 has 13 children # WRONG - should be only 12 children
    # ... more results

I may not understand something, or I may have hit a bug??
Any ideas?

[marco-silva0000]

I just found this bug exactly.

model:

class Pipe(MPTTModel, models.Model):
    name = models.CharField(max_length=64, null=True, blank=True)
    code = models.CharField(max_length=64, null=True, blank=True)
    shape = models.GeometryField(null=True, blank=True)
    _type = models.ForeignKey(PipeType, null=True, blank=True, on_delete=models.CASCADE)

    parent = TreeForeignKey(
        "self", on_delete=models.CASCADE, null=True, blank=True, related_name="children"
    )

>>> Pipe.objects.filter(name__icontains='BOAVISTA').all()
<TreeQuerySet [<Pipe: Pipe object (15)>]>
>>> Pipe.objects.filter(name__icontains='BOAVISTA').first().__dict__
{'_state': <django.db.models.base.ModelState object at 0x7f2065d4af28>, 'id': 15, 'name': 'BOAVISTA PINHEIRO', 'code': 'AHM-D-None-BOAVISTA PINHEIRO', '_reservoir_id': 2, 'shape': <MultiLineString object at 0x7f2065c793c8>, '_type_id': 2, 'parent_id': 9, 'lft': 308, 'rght': 561, 'tree_id': 2, 'level': 1, '_mptt_cached_fields': {'parent': 9}}
>>> parents_qset = Pipe.objects.filter(name__icontains='BOAVISTA')
>>> parents_qset.union(*[sibling.get_descendants() for sibling in parents_qset.all()])
<TreeQuerySet [<Pipe: Pipe object (553)>, <Pipe: Pipe object (334)>, <Pipe: Pipe object (725)>, <Pipe: Pipe object (492)>, <Pipe: Pipe object (438)>, <Pipe: Pipe object (302)>, <Pipe: Pipe object (521)>, <Pipe: Pipe object (591)>, <Pipe: Pipe object (854)>, <Pipe: Pipe object (21)>, <Pipe: Pipe object (768)>, <Pipe: Pipe object (641)>, <Pipe: Pipe object (750)>, <Pipe: Pipe object (15)>, <Pipe: Pipe object (176)>, <Pipe: Pipe object (208)>, <Pipe: Pipe object (754)>, <Pipe: Pipe object (773)>, <Pipe: Pipe object (303)>, <Pipe: Pipe object (621)>, '...(remaining elements truncated)...']>
>>> parents_qset.union(*[sibling.get_descendants() for sibling in parents_qset.all()]).count()
127
>>> parents_qset.union(*[sibling.get_descendants() for sibling in parents_qset.all()]).filter(name='R3').count()
127  # there is only one sibling with R3 name
>>> set2=parents_qset.union(*[sibling.get_descendants() for sibling in parents_qset.all()])
>>> set2.first().__dict__
{'_state': <django.db.models.base.ModelState object at 0x7f2065c8f438>, 'id': 14, 'name': 'MALAVADOS', 'code': 'AHM-D-None-MALAVADOS', '_reservoir_id': 2, 'shape': <MultiLineString object at 0x7f2065c79450>, '_type_id': 2, 'parent_id': 15, 'lft': 309, 'rght': 372, 'tree_id': 2, 'level': 2, '_mptt_cached_fields': {'parent': 15}}
>>> set2.get(name='R2')
Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File "/home/msilva/.local/share/virtualenvs/regantes-backend-VEQEWK_9/lib/python3.7/site-packages/django/db/models/query.py", line 412, in get
    (self.model._meta.object_name, num)
infrastructure.models.Pipe.MultipleObjectsReturned: get() returned more than one Pipe -- it returned 127!
>>> set2.first().name
'MALAVADOS'
set2.filter(name='R2').values_list('name', flat=True)
<TreeQuerySet ['RA', 'R8-13', 'R18-2-1', 'R3-1', 'R18-2', 'R18-3', 'R7-1', 'BOAVISTA PINHEIRO', 'R10-1', 'R8-14', 'ASSEICEIRA', 'R8-1', 'R14', 'R13', 'R8-9-3', 'R3-3', 'R4-4', 'R13-A-1-1', 'R17-1', 'R7-4', '...(remaining elements truncated)...']>

[marco-silva0000]

here is more logging with database queries
http://dpaste.com/2E2VF1K

[matthiask]

Does it disappear after running Pipe.objects.rebuild()? If yes then you have hit one of the many cases of MPTT attributes getting out of sync (see https://github.com/django-mptt/django-mptt/issues?q=is%3Aopen+is%3Aissue+label%3A%22Broken+Tree%22)

[marco-silva0000]

@matthiask I did ran the rebuild after I post this, but I have the same results, so either my code is breaking the tree(which I think is unlikely because I get this error before updating anything on the tree), or there is a problem with the api when using the union from django.

I'll investigate the queries and create a test afterwards if I can.

[marco-silva0000]

after further investigation, I'm sure that the tree is ok and that the query being generated is wrong.

Here are some more tests, where it is proved that only when there are union calls the code breaks.
http://dpaste.com/1E12XFT
Please note here:

>>> set2.filter(name='R2').count()
(0.054) SELECT COUNT(*) FROM ((SELECT "infrastructure_pipe"."id", "infrastructure_pipe"."name", "infrastructure_pipe"."code", "infrastructure_pipe"."_reservoir_id", "infrastructure_pipe"."shape"::bytea, "infrastructure_pipe"."_type_id", "infrastructure_pipe"."parent_id", "infrastructure_pipe"."lft", "infrastructure_pipe"."rght", "infrastructure_pipe"."tree_id", "infrastructure_pipe"."level" FROM "infrastructure_pipe" WHERE UPPER("infrastructure_pipe"."name"::text) LIKE UPPER('%BOAVISTA%') ORDER BY "infrastructure_pipe"."tree_id" ASC, "infrastructure_pipe"."lft" ASC) UNION (SELECT "infrastructure_pipe"."id", "infrastructure_pipe"."name", "infrastructure_pipe"."code", "infrastructure_pipe"."_reservoir_id", "infrastructure_pipe"."shape"::bytea, "infrastructure_pipe"."_type_id", "infrastructure_pipe"."parent_id", "infrastructure_pipe"."lft", "infrastructure_pipe"."rght", "infrastructure_pipe"."tree_id", "infrastructure_pipe"."level" FROM "infrastructure_pipe" WHERE ("infrastructure_pipe"."lft" >= 309 AND "infrastructure_pipe"."lft" <= 560 AND "infrastructure_pipe"."tree_id" = 2) ORDER BY "infrastructure_pipe"."tree_id" ASC, "infrastructure_pipe"."lft" ASC)) subquery; args=('%BOAVISTA%', 309, 560, 2)
127

I'm filtering name="R2" but those parameters never get to the query on the database level.

>>> import sys
... print(sys.version)
3.7.3 (default, Mar 26 2019, 21:43:19) 
[GCC 8.2.1 20181127]
>>> import django
>>> django.VERSION
(2, 2, 1, 'final', 0)
>>> import mptt
>>> mptt.VERSION
('0', '10', '0')
>>> from django.db import connection
>>> print(connection.cursor().connection.server_version)
90506