some enhancemnets to unions, unions and selects need to be more commonly derived,

also more tweaks to mapper eager query compilation involving distinct etc.

3 files changed, 44 insertions, 13 deletions

diff --git a/lib/sqlalchemy/ansisql.py b/lib/sqlalchemy/ansisql.py
index 79215dec7..28003047a 100644
--- a/lib/sqlalchemy/ansisql.py
+++ b/lib/sqlalchemy/ansisql.py
@@ -165,10 +165,11 @@ class ANSICompiler(sql.Compiled):
         else:
             sep = " " + compound.operator + " "
         
+        s = string.join([self.get_str(c) for c in compound.clauses], sep)
         if compound.parens:
-            self.strings[compound] = "(" + string.join([self.get_str(c) for c in compound.clauses], sep) + ")"
+            self.strings[compound] = "(" + s + ")"
         else:
-            self.strings[compound] = string.join([self.get_str(c) for c in compound.clauses], sep)
+            self.strings[compound] = s
         
     def visit_clauselist(self, list):
         if list.parens:
@@ -184,6 +185,8 @@ class ANSICompiler(sql.Compiled):
         for tup in cs.clauses:
             text += " " + tup[0] + " " + self.get_str(tup[1])
         self.strings[cs] = text
+        self.froms[cs] = "(" + text + ")"
+        print "cs from text:" + self.froms[cs]
             
     def visit_binary(self, binary):
         result = self.get_str(binary.left)
@@ -276,8 +279,10 @@ class ANSICompiler(sql.Compiled):
             text += self.limit_clause(select)
             
         if getattr(select, 'issubquery', False):
+            print "subquery"
             self.strings[select] = "(" + text + ")"
         else:
+            print "not a subquery"
             self.strings[select] = text
 
         self.froms[select] = "(" + text + ")"
diff --git a/lib/sqlalchemy/mapping/mapper.py b/lib/sqlalchemy/mapping/mapper.py
index 2860cff7c..05cfcd349 100644
--- a/lib/sqlalchemy/mapping/mapper.py
+++ b/lib/sqlalchemy/mapping/mapper.py
@@ -306,7 +306,7 @@ class Mapper(object):
         will be executed and its resulting rowset used to build new object instances.  
         in this case, the developer must insure that an adequate set of columns exists in the 
         rowset with which to build new object instances."""
-        if arg is not None and isinstance(arg, sql.Select):
+        if arg is not None and isinstance(arg, sql.Selectable):
             return self.select_statement(arg, **kwargs)
         else:
             return self.select_whereclause(arg, **kwargs)
@@ -444,26 +444,38 @@ class Mapper(object):
     def register_deleted(self, obj, uow):
         for prop in self.props.values():
             prop.register_deleted(obj, uow)
-            
+    
+    def _should_nest(self, **kwargs):
+        """returns True if the given statement options indicate that we should "nest" the
+        generated query as a subquery inside of a larger eager-loading query.  this is used
+        with keywords like distinct, limit and offset and the mapper defines eager loads."""
+        return (
+            getattr(self, '_has_eager', False)
+            and (kwargs.has_key('limit') or kwargs.has_key('offset') or kwargs.get('distinct', True))
+        )
+        
     def _compile(self, whereclause = None, **kwargs):
-        if getattr(self, '_has_eager', False) and (kwargs.has_key('limit') or kwargs.has_key('offset')):
-            s2 = sql.select(self.table.primary_key, whereclause, **kwargs)
-            s2.order_by(self.table.rowid_column)
+        if self._should_nest(**kwargs):
+            s2 = sql.select(self.table.primary_key, whereclause, use_labels=True, **kwargs)
+            if not kwargs.get('distinct', True):
+                s2.order_by(self.table.rowid_column)
             s3 = s2.alias('rowcount')
             crit = []
             for i in range(0, len(self.table.primary_key)):
                 crit.append(s3.primary_key[i] == self.table.primary_key[i])
-            statement = sql.select([self.table], sql.and_(*crit))
+            statement = sql.select([self.table], sql.and_(*crit), use_labels=True)
             if kwargs.has_key('order_by'):
-                statement.order_by(kwargs['order_by'])
+                statement.order_by(*kwargs['order_by'])
             statement.order_by(self.table.rowid_column)
         else:
-            statement = sql.select([self.table], whereclause, **kwargs)
-            statement.order_by(self.table.rowid_column)
+            statement = sql.select([self.table], whereclause, use_labels=True, **kwargs)
+            if not kwargs.get('distinct', True):
+                statement.order_by(self.table.rowid_column)
         # plugin point
+        
+        # give all the attached properties a chance to modify the query
         for key, value in self.props.iteritems():
             value.setup(key, statement, **kwargs) 
-        statement.use_labels = True
         return statement
 
     def _identity_key(self, row):
diff --git a/lib/sqlalchemy/sql.py b/lib/sqlalchemy/sql.py
index 10e8f3e74..26c9a862b 100644
--- a/lib/sqlalchemy/sql.py
+++ b/lib/sqlalchemy/sql.py
@@ -907,6 +907,12 @@ class TailClauseMixin(object):
     def group_by(self, *clauses):
         self._append_clause('group_by_clause', "GROUP BY", *clauses)
     def _append_clause(self, attribute, prefix, *clauses):
+        if len(clauses) == 1 and clauses[0] is None:
+            try:
+                delattr(self, attribute)
+            except AttributeError:
+                pass
+            return
         if not hasattr(self, attribute):
             l = ClauseList(*clauses)
             setattr(self, attribute, l)
@@ -920,8 +926,14 @@ class TailClauseMixin(object):
             
 class CompoundSelect(Selectable, TailClauseMixin):
     def __init__(self, keyword, *selects, **kwargs):
+        self.id = "Compound(%d)" % id(self)
         self.keyword = keyword
         self.selects = selects
+        self.use_labels = kwargs.pop('use_labels', False)
+        self.rowid_column = selects[0].rowid_column
+        for s in self.selects:
+            s.order_by(None)
+            s.group_by(None)
         self.clauses = []
         order_by = kwargs.get('order_by', None)
         if order_by:
@@ -944,7 +956,9 @@ class CompoundSelect(Selectable, TailClauseMixin):
                 return e
         else:
             return None
-        
+    def _get_from_objects(self):
+        return [self]
+       
 class Select(Selectable, TailClauseMixin):
     """finally, represents a SELECT statement, with appendable clauses, as well as 
     the ability to execute itself and return a result set."""