Michael Manfre avatar Michael Manfre committed 608b132

Fixed #18 - Ordering subqueries is prevented due to MSSQL limitation.

This change assumes that the 'with_col_aliases' SQLCompiler.as_sql is always
used to mean that the query is a sub-query. This is currently true for the
core as of Django 1.5 trunk.

Comments (0)

Files changed (2)

docs/changelog.txt

 
 - Backend now supports returning the ID from an insert without needing an additional query. This is disabled
   for SQL Server 2000 (assuming that version still works with this backend). :issue:`17`
+- Subqueries will have their ordering removed because SQL Server only supports it when using TOP or FOR XML. 
+  This relies upon the ``with_col_aliases`` argument to ``SQLCompiler.as_sql`` only being ``True`` when the query 
+  is a subquery, which is currently the case for all usages in Django 1.5 master. :issue:`18`
 
 v1.2
 ----

sqlserver_ado/compiler.py

 import datetime
 import re
 
+from contextlib import contextmanager
 
 # query_class returns the base class to use for Django queries.
 # The custom 'SqlServerQuery' class derives from django.db.models.sql.query.Query
 def _remove_order_limit_offset(sql):
     return _re_order_limit_offset.sub('',sql).split(None, 1)[1]
 
+@contextmanager
+def prevent_ordering_query(compiler_):
+    try:
+        setattr(query, '_mssql_ordering_not_allowed', True)
+        yield
+    finally:
+        delattr(query, '_mssql_ordering_not_allowed')
+
 class SQLCompiler(compiler.SQLCompiler):
     def resolve_columns(self, row, fields=()):
         # If the results are sliced, the resultset will have an initial 
         # Get out of the way if we're not a select query or there's no limiting involved.
         check_limits = with_limits and (self.query.low_mark or self.query.high_mark is not None)
         if not check_limits:
-            return super(SQLCompiler, self).as_sql(with_limits, with_col_aliases)
+            # The ORDER BY clause is invalid in views, inline functions, 
+            # derived tables, subqueries, and common table expressions, 
+            # unless TOP or FOR XML is also specified.
+            self.query._mssql_ordering_not_allowed = with_col_aliases
+            result = super(SQLCompiler, self).as_sql(with_limits, with_col_aliases)
+            # remove in case query is every reused
+            delattr(self.query, '_mssql_ordering_not_allowed')            
+            return result
 
         raw_sql, fields = super(SQLCompiler, self).as_sql(False, with_col_aliases)
         
 
         return ', '.join(outer), ', '.join(inner) + from_clause.format(**parens)
 
+    def get_ordering(self):
+        # The ORDER BY clause is invalid in views, inline functions, 
+        # derived tables, subqueries, and common table expressions, 
+        # unless TOP or FOR XML is also specified.
+        if getattr(self.query, '_mssql_ordering_not_allowed', False):
+            return (None, None)
+        return super(SQLCompiler, self).get_ordering()
+
 class SQLInsertCompiler(compiler.SQLInsertCompiler, SQLCompiler):
     # search for after table/column list
     _re_values_sub = re.compile(r'(?P<prefix>\)|\])(?P<default>\s*|\s*default\s*)values(?P<suffix>\s*|\s+\()?', re.IGNORECASE)
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.