TupleIn lookup uses tuple containment even if the supports_tuple_lookups feature is disabled for right-hand-side subqueries

[Simon Charette]

Refer to this support request from ​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

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

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.

[Jacob Walls]

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.

[Simon Charette]

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.

I was thinking one thing we could do is run the composite_pk suite on all backends with the feature disabled somehow. The fallback SQL should work on all backends anyway.

[Natalia Bidart]

There is a consistently failing test in oracle 19, likely due a "lack of cloning" which I can't debug right now.

[nessita <124304+nessita@…>]

In 192bc7a7:

Fixed #36464 -- Fixed "in" tuple lookup on backends lacking native support.

When native support for tuple lookups is missing in a DB backend, it can
be emulated with an EXISTS clause. This is controlled by the backend
feature flag "supports_tuple_lookups".

The mishandling of subquery right-hand side in TupleIn (added to
support CompositePrimaryKey in Refs #373) was likely missed because
the only core backend we test with the feature flag disabled
(Oracle < 23.4) supports it natively.

Thanks to Nandana Raol for the report, and to Sarah Boyce, Jacob Walls,
and Natalia Bidart for reviews.

[Natalia <124304+nessita@…>]

In a150160:

[5.2.x] Fixed #36464 -- Fixed "in" tuple lookup on backends lacking native support.

When native support for tuple lookups is missing in a DB backend, it can
be emulated with an EXISTS clause. This is controlled by the backend
feature flag "supports_tuple_lookups".

The mishandling of subquery right-hand side in TupleIn (added to
support CompositePrimaryKey in Refs #373) was likely missed because
the only core backend we test with the feature flag disabled
(Oracle < 23.4) supports it natively.

Thanks to Nandana Raol for the report, and to Sarah Boyce, Jacob Walls,
and Natalia Bidart for reviews.

Backport of 192bc7a7be92e20cc250907fb4083df689715679 from main.