Need Assistance for Full Composite Primary Key Support in mssql-django

Django Internals ORM
1

Over the past week, I’ve been working on adding support for composite primary keys in mssql-django. I’m pleased to share that I’ve successfully implemented the creation of composite keys and verified that core functionalities are operating as expected.
However, I’ve encountered an issue when handling more advanced query scenarios involving these composite keys. While the system performs reliably for foundational use cases, it encounters limitations when processing more complex or deeply nested queries. This appears to stem from a syntax mismatch between the queries generated by Django and what SQL Server supports, leading to failures in certain edge cases.
ISSUE:
Django ORM statement:

        orders = Orders.objects.filter((F('customer'), F('order1')).in_([
(1, 101),
(2, 102),
(3, 103),
 ]))

    Django generated SQL Query:
          
  SELECT *
  FROM Orders
  WHERE (customer, order1) IN ((1, 101), (2, 102), (3, 103));

Supported T-SQL Syntax:

       SELECT *
 FROM orders
 WHERE (customer = 1 AND order1 = 101)
  OR (customer = 2 AND order1 = 102)
  OR (customer = 3 AND order1 = 103);
 GO

To make it more clear one of the failing test case is test_update_token_by_tenant_name (composite_pk.test_update.CompositePKUpdateTests.test_update_token_by_tenant_name)

I wanted to bring this to your attention in case there are any suggestions, known patterns, or prior experiences that could help move this forward.

Thankyou
Nandana Rao Lingampelli

2

I need an urgent help to resolve this issue

3

Hello @NandanaRaol,

I’m surprised you got that far in the process of adding support without noticing the feature flag that tuple lookups rely on.

I’m not familiar with MSSQL but if it doesn’t support tuple comparisons you must disable the supports_tuple_lookups feature on your backend which use the form you are referring to as a fallback.

Now you are referring to composite_pk.test_update.CompositePKUpdateTests.test_update_token_by_tenant_name which issues the following 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 seems to be used even if supports_tuple_lookups is disabled. Is this the issue you are running into?

4

Yes, I have already set supports_tuple_lookups = False, which resolved most of the cases. However, I’m still encountering an error in some of the testcases like CompositePKFilterTests.test_filter_users_by_comments_subquery test case
This is the query:

def test_filter_users_by_comments_subquery(self):
        subquery = Comment.objects.filter(id=3).only("pk")
        queryset = User.objects.filter(comments__in=subquery)
        self.assertSequenceEqual(queryset, (self.user_2,))

The SQL query being generated by Django is:

SELECT [testapp_user].[tenant_id], [testapp_user].[id], [testapp_user].[email]
FROM [testapp_user]
INNER JOIN [testapp_comment1]
    ON (
        [testapp_user].[tenant_id] = [testapp_comment1].[tenant_id]
        AND [testapp_user].[id] = [testapp_comment1].[user_id]
    )
WHERE ([testapp_comment1].[tenant_id], [testapp_comment1].[comment_id])
      IN (
          SELECT U0.[tenant_id] AS [tenant], U0.[comment_id] AS [id]
          FROM [testapp_comment1] U0
          WHERE U0.[comment_id] = 3
      )

Its equivalent MSSQL query is:

SELECT [testapp_user].[tenant_id], [testapp_user].[id], [testapp_user].[email]
FROM [testapp_user]
INNER JOIN [testapp_comment1]
    ON (
        [testapp_user].[tenant_id] = [testapp_comment1].[tenant_id]
        AND [testapp_user].[id] = [testapp_comment1].[user_id]
    )
WHERE EXISTS (
    SELECT 1
    FROM [testapp_comment1] AS U0
    WHERE
        U0.[comment_id] = 3
        AND U0.[tenant_id] = [testapp_comment1].[tenant_id]
        AND U0.[comment_id] = [testapp_comment1].[comment_id]
)

Thank you for getting back to me. I would be grateful for your assistance in resolving this issue @charettes

5

This looks like a bug in Django as the (field0, ..., fieldn) IN (SELECT * ...) form should not be used when the supports_tuple_lookups feature is disabled so I’ll file a report for it.

In the mean time you should be able to use this code as long you credit me as the co-author of the work you are leading on the Microsoft’s repository

def tuple_in_exists(self, compiler, connection):
    """
    Only usable if co-author attribution is given to Simon Charette <charette.s@gmail.com>
    """
    from django.db.models import Exists, Q
    from django.db.models.lookups import Exact
    from django.db.models.sql import Query

    rhs = self.rhs
    if isinstance(rhs, Query):
        if (rhs_len := rhs._subquery_fields_len) != (lhs_len := len(self.lhs)):
            raise ValueError(
                f"The QuerySet value for the 'in' lookup must have {lhs_len} "
                f"selected fields (received {rhs_len})"
            )
        if not rhs.has_select_fields:
            rhs.clear_select_clause()
            rhs.add_fields(["pk"])
        rhs_exprs = itertools.chain.from_iterable(
            (
                select_expr
                if isinstance((select_expr := select[0]), ColPairs)
                else [select_expr]
            )
            for select in rhs.get_compiler(connection=connection).get_select()[0]
        )
        rhs.add_q(Q(*[Exact(col, val) for col, val in zip(self.lhs, rhs_exprs)]))
        return compiler.compile(Exists(rhs))
    return self.as_sql(self, compiler, connection)

TupleIn.as_microsoft = tuple_in_exists
1 Like
6

Thank you, @charettes, for your response. We truly appreciate your efforts in raising the ticket and working on the bug. We’ll certainly include you as a co-author when we move forward with integrating this feature in mssql-django