#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.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion visit
https://groups.google.com/d/msgid/django-updates/010701976ee6a370-94e52119-4dc5-479c-ac51-65994a287fd5-000000%40eu-central-1.amazonses.com.
