[Django] #36954: Filtering an ManyToManyField causes the `.values` call to be filtered

[Django]

#36954: Filtering an ManyToManyField causes the `.values` call to be filtered
-------------------------------------+-------------------------------------
Reporter: Ryan "RB" Barnes | Type: Bug
Status: new | Component: Database
| layer (models, ORM)
Version: 5.1 | Severity: Normal
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Given two following tables:


{{{
class Technician(models.Model):
...

class Appointment(models.Model):
technicians = models.ManyToManyField(Technician)
}}}


If a have an appointment with two technicians assigned to it and create
the follow queries I get results I would not expect

{{{
tech_1 = Technician()
tech_1.save()
tech_2 = Technician()
tech_2.save()

appointment = Appointment(technicians=[tech_1, tech_2])
appointment.save()

# No issues here
r1 =
Appointment.objects.filter(id=appointment.id).values_list('technicians',
flat=True)
assert tech_1.id in r1
assert tech_2.id in r1

# Now filter for a single technician
r2 = Appointment.objects.filter(id=appointment.id,
technicians__in=[tech_1]).values_list('technicians', flat=True)
assert tech_1.id in r2

# Fails here as tech_2 has been filtered out
assert tech_2.id in r2
}}}

This is certainly because the SQL produced joins against the through
model, but then filters it directly while also selecting from it.

I know I can get around this but using subqueries and ArrayAggs in various
ways but it feels like a footgun that most users would miss.
--
Ticket URL: <https://code.djangoproject.com/ticket/36954>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#36954: Filtering an ManyToManyField causes the `.values` call to be filtered
-------------------------------------+-------------------------------------

Reporter: Ryan "RB" Barnes | Owner: Mahi
| Singhal
Type: Bug | Status: assigned
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mahi Singhal):

* owner: (none) => Mahi Singhal
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/36954#comment:1>

[Django]

#36954: Filtering an ManyToManyField causes the `.values` call to be filtered
-------------------------------------+-------------------------------------
Reporter: Ryan "RB" Barnes | Owner: Mahi
| Singhal

Type: Bug | Status: closed

Component: Database layer | Version: 5.1
(models, ORM) |

Severity: Normal | Resolution: invalid

Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* resolution: => invalid
* status: assigned => closed

Comment:

There's no bug here please refer to
TicketClosingReasons/UseSupportChannels

Your second query literally generate

{{{#!sql
SELECT technician_id
FROM appointment
INNER JOIN appointment_technicians ON (
appointment_technicians.appointment.id = appointment.id
)
WHERE
appointment.id = ?
AND appointment_technicians.technician_id IN ?
}}}

If you're interested in all technicians for a particular appointment where
a particular technician is you effectively need to use `Exists`

{{{#!python
Appointment.objects.filter(
Exists(
Technician.objects.filter(
id=tech_1,
appointments__in=OuterRef("pk"),
)
),
id=appointment.id,
)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36954#comment:2>

[Django]

#36954: Filtering an ManyToManyField causes the `.values` call to be filtered
-------------------------------------+-------------------------------------
Reporter: Ryan "RB" Barnes | Owner: Mahi
| Singhal
Type: Bug | Status: closed
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Ryan "RB" Barnes):

Hi Simon,

Thanks for the reply.

As stated at the end of my ticket I am aware of the possible workarounds
but the requirement for a workaround is my problem with the SQL being
generated.

My issue with the following querysets is that they produce different
results because of the generated SQL and that is unexpected.

{{{
Appointment.objects.filter(id=appointment.id).values_list('technicians',
flat=True)
# [1, 2]

Appointment.objects.filter(id=appointment.id,
technicians__in=[tech_1]).values_list('technicians', flat=True)

# [1]
}}}

Accessing technicians from a filtered queryset produces different results

{{{
qs = Appointment.objects.filter(id=appointment.id,
technicians__in=[tech_1])
qs.values_list('technicians', flat=True)
# [1]
qs.get().technicians.all()
# [1, 2]
}}}

The engineers I've talked to were not aware of this interaction as it
feels unintuitive.

Either way I will keep this in mind as another thing that the ORM
abstraction does not address.

Thank you for your time on this matter.
--
Ticket URL: <https://code.djangoproject.com/ticket/36954#comment:3>