- the "mini" column labels generated when using subqueries, which

are to work around glitchy SQLite behavior that doesnt understand
"foo.id" as equivalent to "id", are now only generated in the case
that those named columns are selected from (part of [ticket:513])
- MS-SQL better detects when a query is a subquery and knows not to
generate ORDER BY phrases for those [ticket:513]

2 files changed, 29 insertions, 11 deletions

diff --git a/lib/sqlalchemy/ansisql.py b/lib/sqlalchemy/ansisql.py
index 03053b998..c3170aa79 100644
--- a/lib/sqlalchemy/ansisql.py
+++ b/lib/sqlalchemy/ansisql.py
@@ -355,9 +355,7 @@ class ANSICompiler(sql.Compiled):
         group_by = self.get_str(cs.group_by_clause)
         if group_by:
             text += " GROUP BY " + group_by
-        order_by = self.get_str(cs.order_by_clause)
-        if order_by:
-            text += " ORDER BY " + order_by
+        text += self.order_by_clause(cs)            
         text += self.visit_select_postclauses(cs)
         if cs.parens:
             self.strings[cs] = "(" + text + ")"
@@ -460,7 +458,7 @@ class ANSICompiler(sql.Compiled):
                         inner_columns[self.get_str(co)] = co
                 # TODO: figure this out, a ColumnClause with a select as a parent
                 # is different from any other kind of parent
-                elif select.is_subquery and isinstance(co, sql._ColumnClause) and not co.is_literal and co.table is not None and not isinstance(co.table, sql.Select):
+                elif select.is_selected_from and isinstance(co, sql._ColumnClause) and not co.is_literal and co.table is not None and not isinstance(co.table, sql.Select):
                     # SQLite doesnt like selecting from a subquery where the column
                     # names look like table.colname, so add a label synonomous with
                     # the column name
@@ -529,12 +527,8 @@ class ANSICompiler(sql.Compiled):
             if t:
                 text += " \nHAVING " + t
 
-        order_by = self.get_str(select.order_by_clause)
-        if order_by:
-            text += " ORDER BY " + order_by
-
+        text += self.order_by_clause(select)
         text += self.visit_select_postclauses(select)
-
         text += self.for_update_clause(select)
 
         if getattr(select, 'parens', False):
@@ -556,6 +550,13 @@ class ANSICompiler(sql.Compiled):
 
         return (select.limit or select.offset) and self.limit_clause(select) or ""
 
+    def order_by_clause(self, select):
+        order_by = self.get_str(select.order_by_clause)
+        if order_by:
+            return " ORDER BY " + order_by
+        else:
+            return ""
+
     def for_update_clause(self, select):
         if select.for_update:
             return " FOR UPDATE"
diff --git a/lib/sqlalchemy/sql.py b/lib/sqlalchemy/sql.py
index 4959c4e21..2388ecc3e 100644
--- a/lib/sqlalchemy/sql.py
+++ b/lib/sqlalchemy/sql.py
@@ -2263,6 +2263,9 @@ class Select(_SelectBaseMixin, FromClause):
         # indicates if this select statement is a subquery inside another query
         self.is_subquery = False
 
+        # indicates if this select statement is in the from clause of another query
+        self.is_selected_from = False
+
         # indicates if this select statement is a subquery as a criterion
         # inside of a WHERE clause
         self.is_where = False
@@ -2272,7 +2275,10 @@ class Select(_SelectBaseMixin, FromClause):
         self.__correlated = {}
         self.__correlator = Select._CorrelatedVisitor(self, False)
         self.__wherecorrelator = Select._CorrelatedVisitor(self, True)
+        self.__fromvisitor = Select._FromVisitor(self)
 
+        self.order_by_clause = self.group_by_clause = None
+        
         if columns is not None:
             for c in columns:
                 self.append_column(c)
@@ -2327,6 +2333,17 @@ class Select(_SelectBaseMixin, FromClause):
                 return
             [select.correlate(x) for x in self.select._Select__froms]
 
+    class _FromVisitor(NoColumnVisitor):
+        def __init__(self, select):
+            NoColumnVisitor.__init__(self)
+            self.select = select
+            
+        def visit_select(self, select):
+            if select is self.select:
+                return
+            select.is_selected_from = True
+            select.is_subquery = True
+
     def append_column(self, column):
         if _is_literal(column):
             column = literal_column(str(column), table=self)
@@ -2369,6 +2386,7 @@ class Select(_SelectBaseMixin, FromClause):
 
     def _process_froms(self, elem, asfrom):
         for f in elem._get_from_objects():
+            self.__fromvisitor.traverse(f)
             self.__froms.add(f)
         if asfrom:
             self.__froms.add(elem)
@@ -2432,8 +2450,7 @@ class Select(_SelectBaseMixin, FromClause):
     def get_children(self, column_collections=True, **kwargs):
         return (column_collections and list(self.columns) or []) + \
             list(self.froms) + \
-            [x for x in (self.whereclause, self.having) if x is not None] + \
-            [self.order_by_clause, self.group_by_clause]
+            [x for x in (self.whereclause, self.having, self.order_by_clause, self.group_by_clause) if x is not None]
 
     def accept_visitor(self, visitor):
         visitor.visit_select(self)