[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near ')' ... Mssqlserver does not support COUNT_BIG (expr)

Question

[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near ')' ... Mssqlserver does not support COUNT_BIG (expr)

Rashnan opened this issue 9 months ago · 6 comments

I have a bookstore database website running in a virtual environment which is connecting to a locally hosted mssql server (docker container). I am using factory boy to randomly generate test data for my models. When calling the OrderInfo factory the generate fails and gives an incorrect sql syntax error. This same factory generation succeeded when using the default sqlite database, so it is most likely an issue with either the mssql-django package or the mssql server itself. My theory is that expressions inside COUNT_BIG are not supported properly within mssql servers, the docs say it should be supported (https://learn.microsoft.com/en-us/sql/t-sql/functions/count-big-transact-sql?view=sql-server-ver16).
When generating an OrderInfo the factory searches for a user with an existing billing_info and payment_info. This step is generating the error.

user = factory.Iterator(
    models.Account.objects
        .annotate(
            num_billing_info=Count('billing_infos'),
            num_payment_info=Count(Q(payment_infos__exp_date__gt=timezone.now().date()))
        )
        .filter(num_billing_info__gt=0, num_payment_info__gt=0)
)

This bug is not listed under Limitations.

Software versions (relevant packages only)

Django: 5.0.3
mssql-django: 1.4.2
python: 3.12.1
factory-boy: 3.3.0
Faker: 24.1.0
SQL Server: 2022 docker image
OS: windows 11

Table schema and Model

Database Connection Settings

ENGINE: 'mssql'
NAME: 'booksdb'
USER: env['MSSQL_USER']
PASSWORD: env['MSSQL_PASSWORD']
HOST: localhost*image name*
PORT: 1433

DATABASES = {
    'sqlite': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': BASE_DIR / 'db.sqlite3',
    },
    'default': {
        'ENGINE': 'mssql',
        'NAME': env['MSSQL_DBNAME'],
        'USER': env['MSSQL_USER'],
        'PASSWORD': env['MSSQL_PASSWORD'],
        'HOST': env['MSSQL_HOST'],
        'PORT': env['MSSQL_PORT']
    },
}

Problem description and steps to reproduce

Sending a get request to this view reproduces the issue and gives the incorrect sql syntax error. This combined with the model snippets at the end should be enough to produce a minimum reproducible example.

def test_view(req):
    print(models.Account.objects.annotate(
        num_billing_info=Count('billing_infos'),
        num_payment_info=Count(Q(payment_infos__exp_date__gt=now().date()))
    ).filter(num_billing_info__gt=0, num_payment_info__gt=0).query)

    return JsonResponse(
        [
            serializers.AccountSerializer(acc)
            for acc in models.Account.objects.annotate(
                num_billing_info=Count('billing_infos'),
                num_payment_info=Count(Q(payment_infos__exp_date__gt=now().date()))
            ).filter(num_billing_info__gt=0, num_payment_info__gt=0)
        ]
    )

This is the sql that is printed. Analyzing with sql server management studio 20 (ssms) says that the issue is the ending bracket of all the COUNT_BIG with conditions within. Note that the print does not show quotations for dates but ssms still shows the ending bracket as an issue when quotations are placed. For one COUNT_BIG condition two sets of brackets (ex: COUNT_BIG((expr)) ... ) was generated but removing this in ssms and trying still showed the ending bracket of the COUNT_BIG as an issue. Replacing the COUNT_BIG (expr) with an equivalent SUM ( CASE WHEN expr THEN 1 ELSE 0 ... END ) worked. This workaround cannot be implemented manually into the django query as the specific sql is generated by the mssql-django package.

SELECT
    [booksdb_account].[id],
    [booksdb_account].[password],
    [booksdb_account].[last_login],
    [booksdb_account].[is_superuser],
    [booksdb_account].[username],
    [booksdb_account].[first_name],
    [booksdb_account].[last_name],
    [booksdb_account].[email],
    [booksdb_account].[is_staff],
    [booksdb_account].[is_active],
    [booksdb_account].[date_joined],
    COUNT_BIG([booksdb_billinginfo].[id]) AS [num_billing_info],
    COUNT_BIG([booksdb_paymentinfo].[exp_date] > 2024 -03 -28) AS [num_payment_info]
FROM
    [booksdb_account]
    LEFT OUTER JOIN [booksdb_billinginfo] ON (
        [booksdb_account].[id] = [booksdb_billinginfo].[user_id]
    )
    LEFT OUTER JOIN [booksdb_paymentinfo] ON (
        [booksdb_account].[id] = [booksdb_paymentinfo].[user_id]
    )
GROUP BY
    [booksdb_account].[id],
    [booksdb_account].[password],
    [booksdb_account].[last_login],
    [booksdb_account].[is_superuser],
    [booksdb_account].[username],
    [booksdb_account].[first_name],
    [booksdb_account].[last_name],
    [booksdb_account].[email],
    [booksdb_account].[is_staff],
    [booksdb_account].[is_active],
    [booksdb_account].[date_joined]
HAVING
    (
        COUNT_BIG([booksdb_billinginfo].[id]) > 0
        AND COUNT_BIG(([booksdb_paymentinfo].[exp_date] > 2024 -03 -28)) > 0
    )

Expected behavior and actual behavior

No error message should be shown and the factory generation should proceed as intended.

Error message/stack trace
Traceback (most recent call last):
File "C:\Users----\bookstore\Lib\site-packages\django\db\backends\utils.py", line 105, in _execute
return self.cursor.execute(sql, params)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users----\bookstore\Lib\site-packages\mssql\base.py", line 675, in execute
return self.cursor.execute(sql, params)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near ')'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "C:\Users----\bookstore\bookstore\manage.py", line 22, in
main()
File "C:\Users----\bookstore\bookstore\manage.py", line 18, in main
execute_from_command_line(sys.argv)
File "C:\Users----\bookstore\Lib\site-packages\django\core\management_init_.py", line 442, in execute_from_command_line
utility.execute()
File "C:\Users----\bookstore\Lib\site-packages\django\core\management_init_.py", line 436, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "C:\Users----\bookstore\Lib\site-packages\django\core\management\base.py", line 413, in run_from_argv
self.execute(*args, **cmd_options)
File "C:\Users----\bookstore\Lib\site-packages\django\core\management\base.py", line 459, in execute
output = self.handle(*args, **options)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users----\bookstore\bookstore\booksdb\management\commands\setupdb.py", line 38, in handle
factories.OrderInfoFactory.create_batch(50)
File "C:\Users----\bookstore\Lib\site-packages\factory\base.py", line 540, in create_batch
return [cls.create(**kwargs) for _ in range(size)]
^^^^^^^^^^^^^^^^^^^^
File "C:\Users----\bookstore\Lib\site-packages\factory\base.py", line 528, in create
return cls._generate(enums.CREATE_STRATEGY, kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users----\bookstore\Lib\site-packages\factory\django.py", line 121, in _generate
return super()._generate(strategy, params)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users----\bookstore\Lib\site-packages\factory\base.py", line 465, in _generate
return step.build()
^^^^^^^^^^^^
File "C:\Users----\bookstore\Lib\site-packages\factory\builder.py", line 270, in build
step.resolve(pre)
File "C:\Users----\bookstore\Lib\site-packages\factory\builder.py", line 211, in resolve
self.attributes[field_name] = getattr(self.stub, field_name)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users----\bookstore\Lib\site-packages\factory\builder.py", line 356, in getattr
value = value.evaluate_pre(
^^^^^^^^^^^^^^^^^^^
File "C:\Users----\bookstore\Lib\site-packages\factory\declarations.py", line 67, in evaluate_pre
return self.evaluate(instance, step, context)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users----\bookstore\Lib\site-packages\factory\declarations.py", line 262, in evaluate
self.iterator = self.iterator_builder()
^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users----\bookstore\Lib\site-packages\factory\declarations.py", line 254, in
self.iterator_builder = lambda: utils.ResetableIterator(itertools.cycle(iterator))
^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users----\bookstore\Lib\site-packages\django\db\models\query.py", line 400, in iter
self._fetch_all()
File "C:\Users----\bookstore\Lib\site-packages\django\db\models\query.py", line 1928, in _fetch_all
self._result_cache = list(self._iterable_class(self))
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users----\bookstore\Lib\site-packages\django\db\models\query.py", line 91, in iter
results = compiler.execute_sql(
^^^^^^^^^^^^^^^^^^^^^
File "C:\Users----\bookstore\Lib\site-packages\django\db\models\sql\compiler.py", line 1562, in execute_sql
cursor.execute(sql, params)
File "C:\Users----\bookstore\Lib\site-packages\django\db\backends\utils.py", line 122, in execute
return super().execute(sql, params)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users----\bookstore\Lib\site-packages\django\db\backends\utils.py", line 79, in execute
return self._execute_with_wrappers(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users----\bookstore\Lib\site-packages\django\db\backends\utils.py", line 92, in _execute_with_wrappers
return executor(sql, params, many, context)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users----\bookstore\Lib\site-packages\django\db\backends\utils.py", line 100, in _execute
with self.db.wrap_database_errors:
File "C:\Users----\bookstore\Lib\site-packages\django\db\utils.py", line 91, in exit
raise dj_exc_value.with_traceback(traceback) from exc_value
File "C:\Users----\bookstore\Lib\site-packages\django\db\backends\utils.py", line 105, in _execute
return self.cursor.execute(sql, params)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users----\bookstore\Lib\site-packages\mssql\base.py", line 675, in execute
return self.cursor.execute(sql, params)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
django.db.utils.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near ')'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)")

Any other details that can be helpful
code extract from models.py

class Account(AbstractUser):
    ....

class BillingInfo(models.Model):
    user = models.ForeignKey(Account, on_delete=models.CASCADE, related_name='billing_infos')
    ....

class PaymentInfo(models.Model):
    user = models.ForeignKey(Account, on_delete=models.CASCADE, related_name='payment_infos')
    exp_date = models.DateField()
    ....

class OrderInfo(models.Model):
    user = models.ForeignKey(Account, on_delete=models.CASCADE, related_name='orders')
    ....

code extract from factories.py

class OrderInfoFactory(django.DjangoModelFactory):
    class Meta:
        model = models.OrderInfo

    user = factory.Iterator(
        models.Account.objects
        .annotate(
            num_billing_info=Count('billing_infos'),
            num_payment_info=Count(Q(payment_infos__exp_date__gt=timezone.now().date()))
        )
        .filter(num_billing_info__gt=0, num_payment_info__gt=0)
    )
    ...

Answer 1 · 2024-04-01T21:43:34.000Z

Thank you for the detailed issue description. We will add this to our list of issues to investigate and report back with our findings.

Answer 2 · 2024-04-01T21:49:42.000Z

Does this updated query work on your end?

SELECT
    [booksdb_account].[id],
    [booksdb_account].[password],
    [booksdb_account].[last_login],
    [booksdb_account].[is_superuser],
    [booksdb_account].[username],
    [booksdb_account].[first_name],
    [booksdb_account].[last_name],
    [booksdb_account].[email],
    [booksdb_account].[is_staff],
    [booksdb_account].[is_active],
    [booksdb_account].[date_joined],
    COUNT_BIG([booksdb_billinginfo].[id]) AS [num_billing_info],
    COUNT(CASE WHEN[booksdb_paymentinfo].[exp_date] > '2024-03-28' THEN 1 ELSE 0 END) AS [num_payment_info]
FROM
    [booksdb_account]
    LEFT OUTER JOIN [booksdb_billinginfo] ON (
        [booksdb_account].[id] = [booksdb_billinginfo].[user_id]
    )
    LEFT OUTER JOIN [booksdb_paymentinfo] ON (
        [booksdb_account].[id] = [booksdb_paymentinfo].[user_id]
    )
GROUP BY
    [booksdb_account].[id],
    [booksdb_account].[password],
    [booksdb_account].[last_login],
    [booksdb_account].[is_superuser],
    [booksdb_account].[username],
    [booksdb_account].[first_name],
    [booksdb_account].[last_name],
    [booksdb_account].[email],
    [booksdb_account].[is_staff],
    [booksdb_account].[is_active],
    [booksdb_account].[date_joined]
HAVING
    (
        COUNT_BIG([booksdb_billinginfo].[id]) > 0
        AND COUNT_BIG((CASE WHEN [booksdb_paymentinfo].[exp_date] > '2024-03-28' THEN 1 ELSE 0 END)) > 0
    )

Answer 3 · 2024-04-02T15:37:22.000Z

Yes it does.

Answer 4 · 2024-04-02T16:48:19.000Z

Thanks for checking. We'll try to implement this in the near future

Answer 5 · 2024-05-07T21:04:53.000Z

Hi, after further look at the issue. I think it's best to update your query like the example below. We may not address the fix, since it's difficult to know the exact condition that people using. Just add Case...When... for complex queries.

Account.objects.annotate(
    num_billing_info=Count("billing_infos"),
    num_payment_info=Count(
        Case(
            When(Q(payment_infos__exp_date__gt=datetime.now().date()), then=1),
            default=0,
        )
    ),
).filter(num_billing_info__gt=0, num_payment_info__gt=0)

Answer 6 · 2024-05-09T12:03:48.000Z

Alright will do.