Cannot prepare SQL statement for table with composite primary key

[Wedge009]

Software versions

• Django: 5.2.5
• mssql-django: 1.6
• python: 3.13.6
• SQL Server: 2022 (16.00.4200)
• OS: Windows 11

Table schema and Model
Note I didn't design the table, we are stuck with a legacy database.

class TableName(models.Model):
    pk = models.CompositePrimaryKey('id', 'tag')
    id = models.IntegerField(db_column='ID')
    tag = models.CharField(db_column='TAG', max_length=20)
    description = models.CharField(db_column='DESCR', max_length=255)

    class Meta:
        managed = False
        db_table = 'table_name'
        unique_together = (('tag', 'description'),)

Database Connection Settings
Submission blocked by corporate proxy.

Problem description and steps to reproduce

new_record = models.TableName(id=5053, tag='TITLE', description='New Title')
new_record.save()

Expected behavior and actual behavior
Before I added composite primary key specifications to the model (using mssql-django 1.5 on Django 5.0.14), this would save the record successfully. But trying the composite primary key support in Django 5.2, I get: ProgrammingError('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]An expression of non-boolean type specified in a context where a condition is expected, near ','. (4145) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)")

Error message/stack trace

mssql-django/mssql/base.py

Lines 671 to 682 in 6d6d457

	def execute(self, sql, params=None):
	self.last_sql = sql
	if 'GROUP BY' in sql:
	sql, params = self.format_group_by_params(sql, params)
	sql = self.format_sql(sql, params)
	params = self.format_params(params)
	self.last_params = params
	try:
	return self.cursor.execute(sql, params)
	except Database.Error as e:
	self.connection._on_error(e)
	raise

sql =('SET NOCOUNT OFF; UPDATE [table_name] SET [DESCR] = %s WHERE ([table_name].[ID], [table_name].[TAG]) = (%s, %s)')
params = ('New Title', 5053, 'TITLE')

format_sql changes the %ss to ?s, parameters remain unchanged after the format_params call.

Any other details that can be helpful
I'm not yet sure what the precise cause of the problem is, but so far it looks to me like an issue with how mssql-django is mapping the Django ORM calls to SQL.

[Wedge009]

It looks like SQL Server doesn't like the expression ([table_name].[ID], [table_name].[TAG]) = (5053, 'TITLE'), and as best as I can tell, this is what mssql-django is producing.

Before composite primary key support, the query would have been using [table_name].[ID] = 5053 AND [table_name].[TAG] = 'TITLE' instead.

[Wedge009]

On further investigation, it looks like the SQL is being generated by Django's SQL compiler and it appears tuple comparison for querying against composite primary keys are fine for the database back-ends Django officially supports, such as Oracle and PostgreSQL, but perhaps not for SQL Server. Without knowing more about the details, I expect mssql-django needs to adapt the SQL to something that SQL Server can process without producing the complaint about non-boolean expressions.

[Wedge009]

I don't pretend to know how this project integrates with Django, but it looks to me like there's some Microsoft-specific handling of the SQL compilation in as_microsoft:

mssql-django/mssql/compiler.py

Lines 454 to 456 in 6d6d457

	def compile(self, node, *args, **kwargs):
	node = self._as_microsoft(node)
	return super().compile(node, *args, **kwargs)

...and inside the base compile there's also room for vendor implementation:
https://github.com/django/django/blob/a3b1107a4955bdd994908efb4c6e1d03c281e69f/django/db/models/sql/compiler.py#L572-L578
(Django 5.2.5 tag)

...but when I stepped through the SQL compilation for a query against a table with composite primary key, there wasn't any such vendor implementation, it just used Django's default. Thus, sql results in a WHERE clause that SQL Server doesn't like:
([table_name].[ID], [table_name].[TAG]) = (%s, %s)

[Wedge009]

It turns out this is resolved by #462.