#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 django-updates+unsubscr...@googlegroups.com.
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.
