Queryset crashes when recreated from a pickled query with FilteredRelation used in aggregation.

[Beda Kosata]

I am pickling query objects (queryset.query) for later re-evaluation as per ​https://docs.djangoproject.com/en/2.2/ref/models/querysets/#pickling-querysets. However, when I tried to rerun a query that contains a FilteredRelation inside a filter, I get an psycopg2.errors.UndefinedTable: missing FROM-clause entry for table "t3" error.

I created a minimum reproducible example.

models.py

from django.db import models


class Publication(models.Model):

    title = models.CharField(max_length=64)


class Session(models.Model):

    TYPE_CHOICES = (('A', 'A'), ('B', 'B'))

    publication = models.ForeignKey(Publication, on_delete=models.CASCADE)
    session_type = models.CharField(choices=TYPE_CHOICES, default='A', max_length=1)
    place = models.CharField(max_length=16)
    value = models.PositiveIntegerField(default=1)

The actual code to cause the crash:

import pickle

from django.db.models import FilteredRelation, Q, Sum

from django_error.models import Publication, Session


p1 = Publication.objects.create(title='Foo')
p2 = Publication.objects.create(title='Bar')
Session.objects.create(publication=p1, session_type='A', place='X', value=1)
Session.objects.create(publication=p1, session_type='B', place='X', value=2)
Session.objects.create(publication=p2, session_type='A', place='X', value=4)
Session.objects.create(publication=p2, session_type='B', place='X', value=8)
Session.objects.create(publication=p1, session_type='A', place='Y', value=1)
Session.objects.create(publication=p1, session_type='B', place='Y', value=2)
Session.objects.create(publication=p2, session_type='A', place='Y', value=4)
Session.objects.create(publication=p2, session_type='B', place='Y', value=8)

qs = Publication.objects.all().annotate(
    relevant_sessions=FilteredRelation('session', condition=Q(session__session_type='A'))
).annotate(x=Sum('relevant_sessions__value'))
# just print it out to make sure the query works
print(list(qs))

qs2 = Publication.objects.all()
qs2.query = pickle.loads(pickle.dumps(qs.query))
# the following crashes with an error
#     psycopg2.errors.UndefinedTable: missing FROM-clause entry for table "t3"
#     LINE 1: ...n"."id" = relevant_sessions."publication_id" AND (T3."sessio...
print(list(qs2))

In the crashing query, there seems to be a difference in the table_map attribute - this is probably where the t3 table is coming from.

Please let me know if there is any more info required for hunting this down.

Cheers
Beda

p.s.- I also tried in Django 3.1 and the behavior is the same.
p.p.s.- just to make sure, I am not interested in ideas on how to rewrite the query - the above is a very simplified version of what I use, so it would probably not be applicable anyway.

[Mariusz Felisiak]

Thanks for this ticket, I was able to reproduce this issue.

[Mariusz Felisiak]

Tests.

[Beda Kosata]

Just a note, the failing queryset does not have to be constructed using the

qs2 = Publication.objects.all()
qs2.query = pickle.loads(pickle.dumps(qs.query))

The same problem occurs even if the whole queryset is pickled and unpickled and then a copy is created by calling .all().

qs2 = pickle.loads(pickle.dumps(qs)).all()

[David Wobrock]

Hi,

I started from the test Mariusz wrote, and followed the code down to where it diverged, comparing the pickling case to the expected QuerySet.

Details can be found in the PR: ​https://github.com/django/django/pull/13484

[Simon Charette]

Left some comments on the PR regarding the __hash__ implementations but it looks like David identified the underlying issue appropriately.

[David Wobrock]

Integrated the suggested changes, thanks!

I'm wondering if the patch should be backported to 2.2 and 3.0 - I guess I'll let you consider and handle this.

[Mariusz Felisiak <felisiak.mariusz@…>]

In c32d8f33:

Fixed #31926 -- Fixed recreating queryset with FilteredRelation when using a pickled Query.

In a pickled join, the join_fields had the same values, but weren't the
same object (contrary to when not pickling the QuerySet).

[Mariusz Felisiak <felisiak.mariusz@…>]

In 0ef04fd:

Refs #31926 -- Fixed reverse related identity crash on Q() limit_choices_to.

[GitHub <noreply@…>]

In 6e4f7ec8:

Refs #31926 -- Made test_pickle_filteredrelation_m2m do not depend on auto-PK.

This caused failures on CockroachDB that use random rather than serial
pk values.