- Added support for rendering "FULL OUTER JOIN" to both Core and ORM.

Pull request courtesy Stefan Urbanek.  fixes #1957

10 files changed, 163 insertions, 35 deletions

diff --git a/doc/build/changelog/changelog_11.rst b/doc/build/changelog/changelog_11.rst
index 97d1e3025..f5c87d399 100644
--- a/doc/build/changelog/changelog_11.rst
+++ b/doc/build/changelog/changelog_11.rst
@@ -22,6 +22,18 @@
     :version: 1.1.0b1
 
     .. change::
+        :tags: feature, sql
+        :tickets: 1957
+        :pullreq: github:209
+
+        Added support for rendering "FULL OUTER JOIN" to both Core and ORM.
+        Pull request courtesy Stefan Urbanek.
+
+        .. seealso::
+
+            :ref:`change_1957`
+
+    .. change::
         :tags: feature, engine
 
         Added connection pool events :meth:`ConnectionEvents.close`,
diff --git a/doc/build/changelog/migration_11.rst b/doc/build/changelog/migration_11.rst
index cca2b1ae8..11dc8a61f 100644
--- a/doc/build/changelog/migration_11.rst
+++ b/doc/build/changelog/migration_11.rst
@@ -980,6 +980,23 @@ will not have much impact on the behavior of the column during an INSERT.
 
 :ticket:`3216`
 
+.. _change_1957:
+
+Core and ORM support for FULL OUTER JOIN
+----------------------------------------
+
+The new flag :paramref:`.FromClause.outerjoin.full`, available at the Core
+and ORM level, instructs the compiler to render ``FULL OUTER JOIN``
+where it would normally render ``LEFT OUTER JOIN``::
+
+    stmt = select([t1]).select_from(t1.outerjoin(t2, full=True))
+
+The flag also works at the ORM level::
+
+    q = session.query(MyClass).outerjoin(MyOtherClass, full=True)
+
+:ticket:`1957`
+
 .. _change_3501:
 
 ResultSet column matching enhancements; positional column setup for textual SQL
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index b85e32db0..5abb1f3d6 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -861,9 +861,16 @@ class MySQLCompiler(compiler.SQLCompiler):
             return ""
 
     def visit_join(self, join, asfrom=False, **kwargs):
+        if join.full:
+            join_type = " FULL OUTER JOIN "
+        elif join.isouter:
+            join_type = " LEFT OUTER JOIN "
+        else:
+            join_type = " INNER JOIN "
+
         return ''.join(
             (self.process(join.left, asfrom=True, **kwargs),
-             (join.isouter and " LEFT OUTER JOIN " or " INNER JOIN "),
+             join_type,
              self.process(join.right, asfrom=True, **kwargs),
              " ON ",
              self.process(join.onclause, **kwargs)))
diff --git a/lib/sqlalchemy/orm/query.py b/lib/sqlalchemy/orm/query.py
index b12f6d6b5..4606c2ffb 100644
--- a/lib/sqlalchemy/orm/query.py
+++ b/lib/sqlalchemy/orm/query.py
@@ -1921,6 +1921,10 @@ class Query(object):
 
          .. versionadded:: 1.0.0
 
+        :param full=False: render FULL OUTER JOIN; implies ``isouter``.
+
+         .. versionadded:: 1.1
+
         :param from_joinpoint=False: When using ``aliased=True``, a setting
          of True here will cause the join to be from the most recent
          joined target, rather than starting back from the original
@@ -1938,14 +1942,16 @@ class Query(object):
             SQLAlchemy versions was the primary ORM-level joining interface.
 
         """
-        aliased, from_joinpoint, isouter = kwargs.pop('aliased', False),\
+        aliased, from_joinpoint, isouter, full = kwargs.pop('aliased', False),\
             kwargs.pop('from_joinpoint', False),\
-            kwargs.pop('isouter', False)
+            kwargs.pop('isouter', False),\
+            kwargs.pop('full', False)
         if kwargs:
             raise TypeError("unknown arguments: %s" %
                             ', '.join(sorted(kwargs)))
         return self._join(props,
-                          outerjoin=isouter, create_aliases=aliased,
+                          outerjoin=isouter, full=full,
+                          create_aliases=aliased,
                           from_joinpoint=from_joinpoint)
 
     def outerjoin(self, *props, **kwargs):
@@ -1955,13 +1961,14 @@ class Query(object):
         Usage is the same as the ``join()`` method.
 
         """
-        aliased, from_joinpoint = kwargs.pop('aliased', False), \
-            kwargs.pop('from_joinpoint', False)
+        aliased, from_joinpoint, full = kwargs.pop('aliased', False), \
+            kwargs.pop('from_joinpoint', False), \
+            kwargs.pop('full', False)
         if kwargs:
             raise TypeError("unknown arguments: %s" %
                             ', '.join(sorted(kwargs)))
         return self._join(props,
-                          outerjoin=True, create_aliases=aliased,
+                          outerjoin=True, full=full, create_aliases=aliased,
                           from_joinpoint=from_joinpoint)
 
     def _update_joinpoint(self, jp):
@@ -1977,7 +1984,7 @@ class Query(object):
         self._joinpath = jp
 
     @_generative(_no_statement_condition, _no_limit_offset)
-    def _join(self, keys, outerjoin, create_aliases, from_joinpoint):
+    def _join(self, keys, outerjoin, full, create_aliases, from_joinpoint):
         """consumes arguments from join() or outerjoin(), places them into a
         consistent format with which to form the actual JOIN constructs.
 
@@ -2089,10 +2096,10 @@ class Query(object):
             self._join_left_to_right(
                 left_entity,
                 right_entity, onclause,
-                outerjoin, create_aliases, prop)
+                outerjoin, full, create_aliases, prop)
 
     def _join_left_to_right(self, left, right,
-                            onclause, outerjoin, create_aliases, prop):
+                            onclause, outerjoin, full, create_aliases, prop):
         """append a JOIN to the query's from clause."""
 
         self._polymorphic_adapters = self._polymorphic_adapters.copy()
@@ -2157,7 +2164,7 @@ class Query(object):
         else:
             self._joinpoint = {'_joinpoint_entity': right}
 
-        self._join_to_left(l_info, left, right, onclause, outerjoin)
+        self._join_to_left(l_info, left, right, onclause, outerjoin, full)
 
     def _prepare_right_side(self, r_info, right, onclause, create_aliases,
                             prop, overlap):
@@ -2244,7 +2251,7 @@ class Query(object):
 
         return right, onclause
 
-    def _join_to_left(self, l_info, left, right, onclause, outerjoin):
+    def _join_to_left(self, l_info, left, right, onclause, outerjoin, full):
         info = l_info
         left_mapper = getattr(info, 'mapper', None)
         left_selectable = info.selectable
@@ -2257,7 +2264,7 @@ class Query(object):
                 try:
                     clause = orm_join(clause,
                                       right,
-                                      onclause, isouter=outerjoin)
+                                      onclause, isouter=outerjoin, full=full)
                 except sa_exc.ArgumentError as ae:
                     raise sa_exc.InvalidRequestError(
                         "Could not find a FROM clause to join from.  "
@@ -2281,7 +2288,8 @@ class Query(object):
 
         assert clause is not None
         try:
-            clause = orm_join(clause, right, onclause, isouter=outerjoin)
+            clause = orm_join(
+                clause, right, onclause, isouter=outerjoin, full=full)
         except sa_exc.ArgumentError as ae:
             raise sa_exc.InvalidRequestError(
                 "Could not find a FROM clause to join from.  "
diff --git a/lib/sqlalchemy/orm/util.py b/lib/sqlalchemy/orm/util.py
index edea9e50c..f76be0380 100644
--- a/lib/sqlalchemy/orm/util.py
+++ b/lib/sqlalchemy/orm/util.py
@@ -783,7 +783,7 @@ class _ORMJoin(expression.Join):
     def __init__(
             self,
             left, right, onclause=None, isouter=False,
-            _left_memo=None, _right_memo=None):
+            full=False, _left_memo=None, _right_memo=None):
 
         left_info = inspection.inspect(left)
         left_orm_info = getattr(left, '_joined_from_info', left_info)
@@ -835,7 +835,7 @@ class _ORMJoin(expression.Join):
                 onclause = pj
             self._target_adapter = target_adapter
 
-        expression.Join.__init__(self, left, right, onclause, isouter)
+        expression.Join.__init__(self, left, right, onclause, isouter, full)
 
         if not prop and getattr(right_info, 'mapper', None) \
                 and right_info.mapper.single:
@@ -874,14 +874,20 @@ class _ORMJoin(expression.Join):
             _right_memo=other._right_memo
         )
 
-    def join(self, right, onclause=None, isouter=False, join_to_left=None):
-        return _ORMJoin(self, right, onclause, isouter)
+    def join(
+            self, right, onclause=None,
+            isouter=False, full=False, join_to_left=None):
+        return _ORMJoin(self, right, onclause, full, isouter)
 
-    def outerjoin(self, right, onclause=None, join_to_left=None):
-        return _ORMJoin(self, right, onclause, True)
+    def outerjoin(
+            self, right, onclause=None,
+            full=False, join_to_left=None):
+        return _ORMJoin(self, right, onclause, True, full=full)
 
 
-def join(left, right, onclause=None, isouter=False, join_to_left=None):
+def join(
+        left, right, onclause=None, isouter=False,
+        full=False, join_to_left=None):
     """Produce an inner join between left and right clauses.
 
     :func:`.orm.join` is an extension to the core join interface
@@ -919,10 +925,10 @@ def join(left, right, onclause=None, isouter=False, join_to_left=None):
        is no longer used, and is deprecated.
 
     """
-    return _ORMJoin(left, right, onclause, isouter)
+    return _ORMJoin(left, right, onclause, isouter, full)
 
 
-def outerjoin(left, right, onclause=None, join_to_left=None):
+def outerjoin(left, right, onclause=None, full=False, join_to_left=None):
     """Produce a left outer join between left and right clauses.
 
     This is the "outer join" version of the :func:`.orm.join` function,
@@ -930,7 +936,7 @@ def outerjoin(left, right, onclause=None, join_to_left=None):
     See that function's documentation for other usage details.
 
     """
-    return _ORMJoin(left, right, onclause, True)
+    return _ORMJoin(left, right, onclause, True, full)
 
 
 def with_parent(instance, prop):
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 8d5f585ce..c9a649748 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -1841,9 +1841,15 @@ class SQLCompiler(Compiled):
             return ""
 
     def visit_join(self, join, asfrom=False, **kwargs):
+        if join.full:
+            join_type = " FULL OUTER JOIN "
+        elif join.isouter:
+            join_type = " LEFT OUTER JOIN "
+        else:
+            join_type = " JOIN "
         return (
             join.left._compiler_dispatch(self, asfrom=True, **kwargs) +
-            (join.isouter and " LEFT OUTER JOIN " or " JOIN ") +
+            join_type +
             join.right._compiler_dispatch(self, asfrom=True, **kwargs) +
             " ON " +
             join.onclause._compiler_dispatch(self, **kwargs)
diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py
index fcd22a786..249d0c604 100644
--- a/lib/sqlalchemy/sql/selectable.py
+++ b/lib/sqlalchemy/sql/selectable.py
@@ -313,7 +313,7 @@ class FromClause(Selectable):
 
         return Select([self], whereclause, **params)
 
-    def join(self, right, onclause=None, isouter=False):
+    def join(self, right, onclause=None, isouter=False, full=False):
         """Return a :class:`.Join` from this :class:`.FromClause`
         to another :class:`FromClause`.
 
@@ -341,6 +341,11 @@ class FromClause(Selectable):
 
         :param isouter: if True, render a LEFT OUTER JOIN, instead of JOIN.
 
+        :param full: if True, render a FULL OUTER JOIN, instead of LEFT OUTER
+         JOIN.  Implies :paramref:`.FromClause.join.isouter`.
+
+         .. versionadded:: 1.1
+
         .. seealso::
 
             :func:`.join` - standalone function
@@ -349,9 +354,9 @@ class FromClause(Selectable):
 
         """
 
-        return Join(self, right, onclause, isouter)
+        return Join(self, right, onclause, isouter, full)
 
-    def outerjoin(self, right, onclause=None):
+    def outerjoin(self, right, onclause=None, full=False):
         """Return a :class:`.Join` from this :class:`.FromClause`
         to another :class:`FromClause`, with the "isouter" flag set to
         True.
@@ -379,6 +384,11 @@ class FromClause(Selectable):
          join.  If left at ``None``, :meth:`.FromClause.join` will attempt to
          join the two tables based on a foreign key relationship.
 
+        :param full: if True, render a FULL OUTER JOIN, instead of
+         LEFT OUTER JOIN.
+
+         .. versionadded:: 1.1
+
         .. seealso::
 
             :meth:`.FromClause.join`
@@ -387,7 +397,7 @@ class FromClause(Selectable):
 
         """
 
-        return Join(self, right, onclause, True)
+        return Join(self, right, onclause, True, full)
 
     def alias(self, name=None, flat=False):
         """return an alias of this :class:`.FromClause`.
@@ -648,7 +658,7 @@ class Join(FromClause):
 
     _is_join = True
 
-    def __init__(self, left, right, onclause=None, isouter=False):
+    def __init__(self, left, right, onclause=None, isouter=False, full=False):
         """Construct a new :class:`.Join`.
 
         The usual entrypoint here is the :func:`~.expression.join`
@@ -665,9 +675,10 @@ class Join(FromClause):
             self.onclause = onclause
 
         self.isouter = isouter
+        self.full = full
 
     @classmethod
-    def _create_outerjoin(cls, left, right, onclause=None):
+    def _create_outerjoin(cls, left, right, onclause=None, full=False):
         """Return an ``OUTER JOIN`` clause element.
 
         The returned object is an instance of :class:`.Join`.
@@ -689,10 +700,11 @@ class Join(FromClause):
         :class:`.Join` object.
 
         """
-        return cls(left, right, onclause, isouter=True)
+        return cls(left, right, onclause, isouter=True, full=full)
 
     @classmethod
-    def _create_join(cls, left, right, onclause=None, isouter=False):
+    def _create_join(cls, left, right, onclause=None, isouter=False,
+                     full=False):
         """Produce a :class:`.Join` object, given two :class:`.FromClause`
         expressions.
 
@@ -724,6 +736,10 @@ class Join(FromClause):
 
         :param isouter: if True, render a LEFT OUTER JOIN, instead of JOIN.
 
+        :param full: if True, render a FULL OUTER JOIN, instead of JOIN.
+
+         .. versionadded:: 1.1
+
         .. seealso::
 
             :meth:`.FromClause.join` - method form, based on a given left side
@@ -732,7 +748,7 @@ class Join(FromClause):
 
         """
 
-        return cls(left, right, onclause, isouter)
+        return cls(left, right, onclause, isouter, full)
 
     @property
     def description(self):
@@ -1050,7 +1066,7 @@ class Join(FromClause):
                 chain(sqlutil.ClauseAdapter(right_a))
 
             return left_a.join(right_a, adapter.traverse(self.onclause),
-                               isouter=self.isouter)
+                               isouter=self.isouter, full=self.full)
         else:
             return self.select(use_labels=True, correlate=False).alias(name)
 
diff --git a/test/dialect/mysql/test_compiler.py b/test/dialect/mysql/test_compiler.py
index 0571ce526..8a7893445 100644
--- a/test/dialect/mysql/test_compiler.py
+++ b/test/dialect/mysql/test_compiler.py
@@ -576,3 +576,30 @@ class SQLTest(fixtures.TestBase, AssertsCompiledSQL):
             'PRIMARY KEY (id, other_id)'
             ')PARTITION BY HASH(other_id) PARTITIONS 2'
         )
+
+    def test_inner_join(self):
+        t1 = table('t1', column('x'))
+        t2 = table('t2', column('y'))
+
+        self.assert_compile(
+            t1.join(t2, t1.c.x == t2.c.y),
+            "t1 INNER JOIN t2 ON t1.x = t2.y"
+        )
+
+    def test_outer_join(self):
+        t1 = table('t1', column('x'))
+        t2 = table('t2', column('y'))
+
+        self.assert_compile(
+            t1.outerjoin(t2, t1.c.x == t2.c.y),
+            "t1 LEFT OUTER JOIN t2 ON t1.x = t2.y"
+        )
+
+    def test_full_outer_join(self):
+        t1 = table('t1', column('x'))
+        t2 = table('t2', column('y'))
+
+        self.assert_compile(
+            t1.outerjoin(t2, t1.c.x == t2.c.y, full=True),
+            "t1 FULL OUTER JOIN t2 ON t1.x = t2.y"
+        )
\ No newline at end of file
diff --git a/test/orm/test_joins.py b/test/orm/test_joins.py
index 540056dae..e7e943e8d 100644
--- a/test/orm/test_joins.py
+++ b/test/orm/test_joins.py
@@ -455,6 +455,15 @@ class JoinTest(QueryTest, AssertsCompiledSQL):
             "FROM users LEFT OUTER JOIN orders ON users.id = orders.user_id"
         )
 
+    def test_full_flag(self):
+        User = self.classes.User
+
+        self.assert_compile(
+            create_session().query(User).outerjoin('orders', full=True),
+            "SELECT users.id AS users_id, users.name AS users_name "
+            "FROM users FULL OUTER JOIN orders ON users.id = orders.user_id"
+        )
+
 
     def test_multi_tuple_form(self):
         """test the 'tuple' form of join, now superseded
diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index 66612eb33..dae178d31 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -1553,6 +1553,26 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
             "mytable.myid = :myid_1 OR myothertable.othername != :othername_1 "
             "OR EXISTS (select yay from foo where boo = lar)", )
 
+    def test_full_outer_join(self):
+        for spec in [
+            join(table1, table2, table1.c.myid == table2.c.otherid, full=True),
+            outerjoin(
+                table1, table2,
+                table1.c.myid == table2.c.otherid, full=True),
+            table1.join(
+                table2,
+                table1.c.myid == table2.c.otherid, full=True),
+            table1.outerjoin(
+                table2,
+                table1.c.myid == table2.c.otherid, full=True),
+        ]:
+            stmt = select([table1]).select_from(spec)
+        self.assert_compile(
+            stmt,
+            "SELECT mytable.myid, mytable.name, mytable.description FROM "
+            "mytable FULL OUTER JOIN myothertable "
+            "ON mytable.myid = myothertable.otherid")
+
     def test_compound_selects(self):
         assert_raises_message(
             exc.ArgumentError,