Using Substr with aggregate throws error

[JordanReiter]

Example code:

from django.db.models import Count
from django.db.models import functions
from django.contrib.auth import get_user_model
User = get_user_model()
user_counts_by_letter = User.objects.annotate(
    first_letter=functions.Substr(User.USERNAME_FIELD, 1, 1)
).values('first_letter').annotate(
    user_count=Count('pk')
).order_by('first_letter')
print(user_counts_by_letter.query)
list(user_counts_by_letter)
from django.db import connection
cursor = connection.cursor()
# works when substring parameters are written out in SQL
cursor.execute('''
SELECT 
    SUBSTRING([auth_user].[username], 1, 1) AS [first_letter], 
    COUNT_BIG([auth_user].[id]) AS [user_count] 
FROM [auth_user] 
GROUP BY SUBSTRING([auth_user].[username], 1, 1) 
ORDER BY [first_letter] ASC
''')

# fails when substring parameters are sent as parameters
cursor.execute('''
SELECT 
    SUBSTRING([auth_user].[username], %s, %s) AS [first_letter], 
    COUNT_BIG([auth_user].[id]) AS [user_count] 
FROM [auth_user] 
GROUP BY SUBSTRING([auth_user].[username], %s, %s) 
ORDER BY [first_letter] ASC
''', (1, 1, 1, 1)
)

Seems the only fix would be to write out the values for the substring rather than sending as params when the SQL is aggregate.

I'm not sure if this applies for other, similar functions, but it would be straightforward to test.

[nattster]

Same problem here. Seem like this might be an issue in pyodbc.

I tried:

import pyodbc
conn=pyodbc.connect("MARS_Connection=yes;DATABASE=testdb;DRIVER=ODBC Driver 13 for SQL Server;SERVER=127.0.0.1;PWD=password;UID=username")
cursor = conn.cursor()

# this does not work
cursor.execute('''
SELECT 
    SUBSTRING([auth_user].[username], ?, ?) AS [first_letter], 
    COUNT_BIG([auth_user].[id]) AS [user_count] 
FROM [auth_user] 
GROUP BY SUBSTRING([auth_user].[username], ?, ?) 
ORDER BY [first_letter] ASC
''', (1, 1, 1, 1)
)
# throw following exception
#   ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Column 
#   'auth_user.username' is invalid in the select list because it is not contained in either an aggregate 
#   function or the GROUP BY clause. (8120) (SQLExecDirectW)")


# this work
cursor.execute('''
SELECT 
    SUBSTRING([auth_user].[username], 1, 1) AS [first_letter], 
    COUNT_BIG([auth_user].[id]) AS [user_count] 
FROM [auth_user] 
GROUP BY SUBSTRING([auth_user].[username], 1, 1) 
ORDER BY [first_letter] ASC
'''
)

Version:

pyodbc==4.0.17
django-pyodbc-azure==1.11.0.0

Python 3.5.2
Ubuntu 16.04.2 LTS
mssql-server=14.0.600.250-2  and SQL Server Express 2016 on Windows Server 2016 can reproduce this issue

I'll try to reproduce this with SQL Server on windows, and report the issue to pyodbc.

[shaib]

Actually, we encountered this issue on Oracle as well -- the root cause is that each of the parameters is passed in independently, so at statement compilation time, the database server cannot know that you are actually calling SUBSTR with the same parameters in the select list and in the group by clause.

See https://code.djangoproject.com/ticket/27632 -- it was fixed for Oracle, and I suppose a similar fix can be applied here.

[rnovacek]

This change (credits to @nattster) fixes very similar issue (with Concat and GROUP BY) for me:

https://github.com/michiya/django-pyodbc-azure/compare/azure-2.0...rnovacek:fix-concat-group-by-new

I didn't test it thoroughly yet, but our app doesn't show any problems so far.

[MarkBird]

+1 for this. Had similar issue with Sum aggregating a Case statement - monkey patched CursorWrapper with the above changes, that fixed the problem.