queryset.extra(order_by=[added_column]) doesn't work

[shaib]

Hi,

Take this little snippet. User is django.contrib.auth.models.User (I tried it on Django 1.4):

User.objects.extra(select=dict(lname='lower(last_name)'), order_by=['lname'])

According to documentation, this should order the users by their last name, lowercased. It works as advertised on Oracle and Sqlite, but on SQL Server with current django-pyodbc-azure it explodes with

[42S22] [FreeTDS][SQL Server]Invalid column name 'lname'. (207) (SQLExecDirectW)

It seems that the reason is that the added names are treated as if they were regular columns, which doesn't work with SQL Server. The generated query is:

>>> print User.objects.extra(select=dict(lname='lower(last_name)')).extra(order_by=['lname']).query
SELECT (lower(last_name)) AS [lname], [auth_user].[id], [auth_user].[username], [auth_user].[first_name], [auth_user].[last_name], [auth_user].[email], [auth_user].[password], [auth_user].[is_staff], [auth_user].[is_active], [auth_user].[is_superuser], [auth_user].[last_login], [auth_user].[date_joined] FROM [auth_user] ORDER BY [lname] ASC

Note that things become even worse when slicing is involved:

>>> print User.objects.extra(select=dict(lname='lower(last_name)')).extra(order_by=['lname'])[0:10].query
SELECT * FROM (SELECT (lower(last_name)) AS [lname], [auth_user].[id], [auth_user].[username], [auth_user].[first_name], [auth_user].[last_name], [auth_user].[email], [auth_user].[password], [auth_user].[is_staff], [auth_user].[is_active], [auth_user].[is_superuser], [auth_user].[last_login], [auth_user].[date_joined], (ROW_NUMBER() OVER (ORDER BY [lname] ASC)) AS [rn] FROM [auth_user]) AS X WHERE X.rn BETWEEN 1 AND 10

Also, it appears that taking the order_by out to a separate extra call changes nothing.

[shmuelt]

Hi michiya,
we will be really happy if this bug will be resolved, any way we can expedite? ( donation? )

[michiya]

@shaib @shmuelt Thank you for your letting me know and I'm sorry for my delay. I would fix this issue in the near future.

[michiya]

The problem will be resolved by updating the backend to the new version (1.0.8). Let me know if you still have the same problem with the new one.

[shmuelt]

Thanks,
You are a king, will check asap

From: Michiya Takahashi [mailto:notifications@github.com]
Sent: Thursday, October 31, 2013 10:20 AM
To: michiya/django-pyodbc-azure
Cc: Shmuel Tauber
Subject: Re: [django-pyodbc-azure] queryset.extra(order_by=[added_column]) doesn't work (#3)

Closed #3#3.

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/3.

[shmuelt]

Michiya,

Healarium want to share a small thank you
Do you prefer amazon.com or amazon.jp?

From: Michiya Takahashi [mailto:notifications@github.com]
Sent: Thursday, October 31, 2013 10:20 AM
To: michiya/django-pyodbc-azure
Cc: Shmuel Tauber
Subject: Re: [django-pyodbc-azure] queryset.extra(order_by=[added_column]) doesn't work (#3)

Closed #3#3.

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/3.

[michiya]

Thank you for your kindness, and just knowing that you care is enough for me. Let me know if you find other bugs.

[shaib]

Hi Michiya,

I don't know how to reach you otherwise -- wanted to call your attention to https://code.djangoproject.com/wiki/SummerOfCode2014#Improvingthelesspopulardatabasebackends, and invite you to add points to the idea and consider mentoring or co-mentoring a student (of course, only if you're interested).

Have fun, and thanks for your work on this project,

Shai.