[Django] #36464: TupleIn lookup uses tuple comparision even if the supports_tuple_lookups feature is disable for right-hand-side subqueries

[Django]

#36464: TupleIn lookup uses tuple comparision even if the supports_tuple_lookups
feature is disable for right-hand-side subqueries
-------------------------------------+-------------------------------------
Reporter: Simon | Owner: Simon Charette
Charette |
Type: Bug | Status: assigned
Component: Database | Version: 5.2
layer (models, ORM) |
Severity: Release | Keywords:
blocker |
Triage Stage: | Has patch: 1
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Refer to this support request from [https://forum.djangoproject.com/t
/need-assistance-for-full-composite-primary-key-support-in-mssql-
django/41412/3 the SQL Server third-party backends maintainers].

The problem can be triggered directly when doing `filter(pk__in=queryset)`
or indirectly when doing updates that involve related tables as we've not
implemented `UPDATE FROM` yet and it is simulated by doing `UPDATE table
SET ... WHERE (pk_field0, ..., pk_fieldn) IN (SELECT ... FROM
other_table)`.

The latter can be observed in the SQL generated by the
`composite_pk.test_update.CompositePKUpdateTests.test_update_token_by_tenant_name`
even when `supports_tuple_lookups` is off

{{{#!sql
UPDATE "composite_pk_token"
SET "secret" = 'bar'
WHERE ("composite_pk_token"."tenant_id",
"composite_pk_token"."id") IN
(SELECT U0."tenant_id",
U0."id"
FROM "composite_pk_token" U0
INNER JOIN "composite_pk_tenant" U1 ON (U0."tenant_id" = U1."id")
WHERE U1."name" = 'A')
}}}

Which can be emulated by using `EXISTS` instead

{{{#!sql
UPDATE "composite_pk_token"
SET "secret" = 'bar'
WHERE EXISTS
(SELECT 1 AS "a"
FROM "composite_pk_token" U0
INNER JOIN "composite_pk_tenant" U1 ON (U0."tenant_id" = U1."id")
WHERE (U1."name" = 'A'
AND "composite_pk_token"."tenant_id" = (U0."tenant_id")
AND "composite_pk_token"."id" = (U0."id"))
LIMIT 1)
}}}

Note that we didn't run into issues before because even if we have test
coverage for this case the sole backend we test against that has
`supports_tuple_lookups` disabled (Oracle < 23.4) happens to support tuple
comparisons for subqueries. It feels like it should nonetheless be solved
in Django itself.
--
Ticket URL: <https://code.djangoproject.com/ticket/36464>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#36464: TupleIn lookup uses tuple comparision even if the supports_tuple_lookups
feature is disable for right-hand-side subqueries
-------------------------------------+-------------------------------------

Reporter: Simon Charette | Owner: Simon

| Charette
Type: Bug | Status: assigned

Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by David Sanders):

* stage: Unreviewed => Accepted

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

[Django]

#36464: TupleIn lookup uses tuple comparision even if the supports_tuple_lookups
feature is disable for right-hand-side subqueries
-------------------------------------+-------------------------------------
Reporter: Simon Charette | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 1 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Jacob Walls):

* needs_tests: 0 => 1

Comment:

I'm assuming we'll want some sort of low-level test against the compiled
SQL when the feature flag is false as modeled in the report.
--
Ticket URL: <https://code.djangoproject.com/ticket/36464#comment:2>