chain joins from SelectState context, not Select

Fixed issue in new :meth:`_sql.Select.join` method where chaining from the
current JOIN wasn't looking at the right state, causing an expression like
"FROM a JOIN b <onclause>, b JOIN c <onclause>" rather than
"FROM a JOIN b <onclause> JOIN c <onclause>".

Added :meth:`_sql.Select.outerjoin_from` method to complement
:meth:`_sql.Select.join_from`.

Fixes: #5858
Change-Id: I1346ebe0963bbd1e4bf868650e3ee1d6d3072f04

1 files changed, 56 insertions, 0 deletions

diff --git a/test/sql/test_select.py b/test/sql/test_select.py
index 2a2381427..96c6abd07 100644
--- a/test/sql/test_select.py
+++ b/test/sql/test_select.py
@@ -41,6 +41,13 @@ child = Table(
     Column("data", String(50)),
 )
 
+grandchild = Table(
+    "grandchild",
+    metadata,
+    Column("id", Integer, primary_key=True),
+    Column("child_id", ForeignKey("child.id")),
+)
+
 
 class FutureSelectTest(fixtures.TestBase, AssertsCompiledSQL):
     __dialect__ = "default"
@@ -96,6 +103,20 @@ class FutureSelectTest(fixtures.TestBase, AssertsCompiledSQL):
             "ON mytable.myid = myothertable.otherid",
         )
 
+    def test_join_nofrom_implicit_left_side_explicit_onclause_3level(self):
+        stmt = (
+            select(parent)
+            .join(child, child.c.parent_id == parent.c.id)
+            .join(grandchild, grandchild.c.child_id == child.c.id)
+        )
+
+        self.assert_compile(
+            stmt,
+            "SELECT parent.id, parent.data FROM parent JOIN child "
+            "ON child.parent_id = parent.id "
+            "JOIN grandchild ON grandchild.child_id = child.id",
+        )
+
     def test_join_nofrom_explicit_left_side_explicit_onclause(self):
         stmt = select(table1).join_from(
             table1, table2, table1.c.myid == table2.c.otherid
@@ -108,6 +129,18 @@ class FutureSelectTest(fixtures.TestBase, AssertsCompiledSQL):
             "ON mytable.myid = myothertable.otherid",
         )
 
+    def test_outerjoin_nofrom_explicit_left_side_explicit_onclause(self):
+        stmt = select(table1).outerjoin_from(
+            table1, table2, table1.c.myid == table2.c.otherid
+        )
+
+        self.assert_compile(
+            stmt,
+            "SELECT mytable.myid, mytable.name, mytable.description "
+            "FROM mytable LEFT OUTER JOIN myothertable "
+            "ON mytable.myid = myothertable.otherid",
+        )
+
     def test_join_nofrom_implicit_left_side_implicit_onclause(self):
         stmt = select(parent).join(child)
 
@@ -117,6 +150,16 @@ class FutureSelectTest(fixtures.TestBase, AssertsCompiledSQL):
             "ON parent.id = child.parent_id",
         )
 
+    def test_join_nofrom_implicit_left_side_implicit_onclause_3level(self):
+        stmt = select(parent).join(child).join(grandchild)
+
+        self.assert_compile(
+            stmt,
+            "SELECT parent.id, parent.data FROM parent JOIN child "
+            "ON parent.id = child.parent_id "
+            "JOIN grandchild ON child.id = grandchild.child_id",
+        )
+
     def test_join_nofrom_explicit_left_side_implicit_onclause(self):
         stmt = select(parent).join_from(parent, child)
 
@@ -172,6 +215,19 @@ class FutureSelectTest(fixtures.TestBase, AssertsCompiledSQL):
             "ON parent.id = child.parent_id",
         )
 
+    def test_right_nested_inner_join(self):
+        inner = child.join(grandchild)
+
+        stmt = select(parent).outerjoin_from(parent, inner)
+
+        self.assert_compile(
+            stmt,
+            "SELECT parent.id, parent.data FROM parent "
+            "LEFT OUTER JOIN "
+            "(child JOIN grandchild ON child.id = grandchild.child_id) "
+            "ON parent.id = child.parent_id",
+        )
+
     def test_joins_w_filter_by(self):
         stmt = (
             select(parent)