Having both a CTE, a GROUP BY and parameters causes crashes.
nils-van-zuijlen opened this issue · 1 comments
nils-van-zuijlen commented
There are some features which are not supported yet. Please check the Limitations first to see if your bug is listed.
Software versions
- Django: 4.2
- mssql-django: 1.5
- python: 3.11.8
- SQL Server: 14.0.0
- OS: Linux 6.6.36 NixOS
Table schema and Model
Database Connection Settings
DATABASES = {
"default": {
"ENGINE": "mssql",
"NAME": "master",
"USER": "sa",
"PASSWORD": "REDACTED",
"HOST": "127.0.0.1",
"PORT": 1433,
"OPTIONS": {
# This database doesn't have any triggers so can use return
# rows from bulk insert feature
"return_rows_bulk_insert": True
},
}
}Problem description and steps to reproduce
Using a GROUP BY clause in a query that begins with a CTE (WITH ...) and that has parameters causes a crash:
ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. (319) (SQLExecDirectW)")
The following code will trigger the behaviour:
from django.db import DEFAULT_DB_ALIAS, connections
with connections[DEFAULT_DB_ALIAS].cursor() as cursor:
cursor.execute("""
WITH a AS (SELECT 1 as b)
SELECT b FROM a
WHERE b = %s
GROUP BY b
""", [1])Expected behavior and actual behavior
I expect the query to be executed without issues.
Error message/stack trace
In [1]: from django.db import DEFAULT_DB_ALIAS, connections
In [2]: with connections[DEFAULT_DB_ALIAS].cursor() as cursor:
...: cursor.execute(
...: "WITH a AS (SELECT 1 as b) SELECT b FROM a WHERE b = %s GROUP BY b",
...: [1])
...:
---------------------------------------------------------------------------
ProgrammingError Traceback (most recent call last)
File ~/Documents/project/.venv/lib/python3.11/site-packages/django/db/backends/utils.py:89, in CursorWrapper._execute(self, sql, params, *ignored_wrapper_args)
88 else:
---> 89 return self.cursor.execute(sql, params)
File ~/Documents/project/.venv/lib/python3.11/site-packages/mssql/base.py:677, in CursorWrapper.execute(self, sql, params)
676 try:
--> 677 return self.cursor.execute(sql, params)
678 except Database.Error as e:
ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. (319) (SQLExecDirectW)")
The above exception was the direct cause of the following exception:
ProgrammingError Traceback (most recent call last)
Cell In[2], line 2
1 with connections[DEFAULT_DB_ALIAS].cursor() as cursor:
----> 2 cursor.execute(
3 "WITH a AS (SELECT 1 as b) SELECT b FROM a WHERE b = %s GROUP BY b",
4 [1])
File ~/Documents/project/.venv/lib/python3.11/site-packages/django/db/backends/utils.py:102, in CursorDebugWrapper.execute(self, sql, params)
100 def execute(self, sql, params=None):
101 with self.debug_sql(sql, params, use_last_executed_query=True):
--> 102 return super().execute(sql, params)
File ~/Documents/project/.venv/lib/python3.11/site-packages/django/db/backends/utils.py:67, in CursorWrapper.execute(self, sql, params)
66 def execute(self, sql, params=None):
---> 67 return self._execute_with_wrappers(
68 sql, params, many=False, executor=self._execute
69 )
File ~/Documents/project/.venv/lib/python3.11/site-packages/django/db/backends/utils.py:80, in CursorWrapper._execute_with_wrappers(self, sql, params, many, executor)
78 for wrapper in reversed(self.db.execute_wrappers):
79 executor = functools.partial(wrapper, executor)
---> 80 return executor(sql, params, many, context)
File ~/Documents/project/.venv/lib/python3.11/site-packages/django/db/backends/utils.py:84, in CursorWrapper._execute(self, sql, params, *ignored_wrapper_args)
82 def _execute(self, sql, params, *ignored_wrapper_args):
83 self.db.validate_no_broken_transaction()
---> 84 with self.db.wrap_database_errors:
85 if params is None:
86 # params default might be backend specific.
87 return self.cursor.execute(sql)
File ~/Documents/project/.venv/lib/python3.11/site-packages/django/db/utils.py:91, in DatabaseErrorWrapper.__exit__(self, exc_type, exc_value, traceback)
89 if dj_exc_type not in (DataError, IntegrityError):
90 self.wrapper.errors_occurred = True
---> 91 raise dj_exc_value.with_traceback(traceback) from exc_value
File ~/Documents/project/.venv/lib/python3.11/site-packages/django/db/backends/utils.py:89, in CursorWrapper._execute(self, sql, params, *ignored_wrapper_args)
87 return self.cursor.execute(sql)
88 else:
---> 89 return self.cursor.execute(sql, params)
File ~/Documents/project/.venv/lib/python3.11/site-packages/mssql/base.py:677, in CursorWrapper.execute(self, sql, params)
675 self.last_params = params
676 try:
--> 677 return self.cursor.execute(sql, params)
678 except Database.Error as e:
679 self.connection._on_error(e)
ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. (319) (SQLExecDirectW)")
Any other details that can be helpful
This is caused by the variable declaration added in #354. I think the new code path should not be used for raw queries, because if a raw query runs into the issue fixed by that PR, it can be fixed by manually adding the variable declaration.
mShan0 commented
Thanks for bringing this up. Will be looking into this further