render select froms first

The FROM clauses that are established on a :func:`_sql.select` construct
when using the :meth:`_sql.Select.select_from` method will now render first
in the FROM clause of the rendered SELECT, which serves to maintain the
ordering of clauses as was passed to the :meth:`_sql.Select.select_from`
method itself without being affected by the presence of those clauses also
being mentioned in other parts of the query. If other elements of the
:class:`_sql.Select` also generate FROM clauses, such as the columns clause
or WHERE clause, these will render after the clauses delivered by
:meth:`_sql.Select.select_from` assuming they were not explictly passed to
:meth:`_sql.Select.select_from` also. This improvement is useful in those
cases where a particular database generates a desirable query plan based on
a particular ordering of FROM clauses and allows full control over the
ordering of FROM clauses.

Fixes: #7888
Change-Id: I740f262a3841f829239011120a59b5e58452db5b

5 files changed, 99 insertions, 15 deletions

diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index d9f137349..4e8e2ac13 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -550,6 +550,91 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
             dialect=dialect,
         )
 
+    @testing.combinations(
+        (
+            select(table1.c.name)
+            .select_from(table1, table2)
+            .where(table1.c.myid == table2.c.otherid),
+            "SELECT mytable.name FROM mytable, myothertable "
+            "WHERE mytable.myid = myothertable.otherid",
+        ),
+        (
+            select(table1.c.name)
+            .select_from(table2, table1)
+            .where(table1.c.myid == table2.c.otherid),
+            "SELECT mytable.name FROM myothertable, mytable "
+            "WHERE mytable.myid = myothertable.otherid",
+        ),
+        (
+            select(table1.c.name)
+            .where(table1.c.myid == table2.c.otherid)
+            .select_from(table2, table1),
+            "SELECT mytable.name FROM myothertable, mytable "
+            "WHERE mytable.myid = myothertable.otherid",
+        ),
+        (
+            select(table1.c.name)
+            .where(table1.c.myid == table2.c.otherid)
+            .select_from(table1, table2),
+            "SELECT mytable.name FROM mytable, myothertable "
+            "WHERE mytable.myid = myothertable.otherid",
+        ),
+        (
+            select(table3.c.userid, table1.c.name)
+            .where(table1.c.myid == table2.c.otherid)
+            .select_from(table1, table3, table2),
+            "SELECT thirdtable.userid, mytable.name "
+            "FROM mytable, thirdtable, myothertable "
+            "WHERE mytable.myid = myothertable.otherid",
+        ),
+        (
+            select(table3.c.userid, table1.c.name)
+            .where(table1.c.myid == table2.c.otherid)
+            .select_from(table3, table1, table2),
+            "SELECT thirdtable.userid, mytable.name "
+            "FROM thirdtable, mytable, myothertable "
+            "WHERE mytable.myid = myothertable.otherid",
+        ),
+        (
+            select(table3.c.userid, table1.c.name)
+            .where(table1.c.myid == table2.c.otherid)
+            .select_from(table1, table2),
+            "SELECT thirdtable.userid, mytable.name "
+            "FROM mytable, myothertable, thirdtable "
+            "WHERE mytable.myid = myothertable.otherid",
+        ),
+        (
+            select(table3.c.userid, table1.c.name)
+            .where(table1.c.myid == table2.c.otherid)
+            .select_from(table3, table2),
+            "SELECT thirdtable.userid, mytable.name "
+            "FROM thirdtable, myothertable, mytable "
+            "WHERE mytable.myid = myothertable.otherid",
+        ),
+        (
+            select(table3.c.userid, table1.c.name)
+            .where(table1.c.myid == table2.c.otherid)
+            .select_from(table3, table2)
+            .join_from(table3, table1, table3.c.userid == table1.c.myid),
+            "SELECT thirdtable.userid, mytable.name "
+            "FROM thirdtable "
+            "JOIN mytable ON thirdtable.userid = mytable.myid, "
+            "myothertable WHERE mytable.myid = myothertable.otherid",
+        ),
+        (
+            select(table3.c.userid, table1.c.name)
+            .where(table1.c.myid == table2.c.otherid)
+            .select_from(table2, table3)
+            .join_from(table3, table1, table3.c.userid == table1.c.myid),
+            "SELECT thirdtable.userid, mytable.name "
+            "FROM myothertable, thirdtable "
+            "JOIN mytable ON thirdtable.userid = mytable.myid "
+            "WHERE mytable.myid = myothertable.otherid",
+        ),
+    )
+    def test_select_from_ordering(self, stmt, expected):
+        self.assert_compile(stmt, expected)
+
     def test_from_subquery(self):
         """tests placing select statements in the column clause of
         another select, for the
@@ -1265,11 +1350,9 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
         # don't correlate in a FROM list
         self.assert_compile(
             select(users, s.c.street).select_from(s),
-            "SELECT users.user_id, users.user_name, "
-            "users.password, s.street FROM users, "
-            "(SELECT addresses.street AS street FROM "
-            "addresses, users WHERE addresses.user_id = "
-            "users.user_id) AS s",
+            "SELECT users.user_id, users.user_name, users.password, s.street "
+            "FROM (SELECT addresses.street AS street FROM addresses, users "
+            "WHERE addresses.user_id = users.user_id) AS s, users",
         )
         self.assert_compile(
             table1.select().where(
diff --git a/test/sql/test_external_traversal.py b/test/sql/test_external_traversal.py
index 30d25be90..13116225c 100644
--- a/test/sql/test_external_traversal.py
+++ b/test/sql/test_external_traversal.py
@@ -1282,7 +1282,7 @@ class ClauseTest(fixtures.TestBase, AssertsCompiledSQL):
             s,
             "SELECT table1.col1, table1.col2, table1.col3 FROM table1 "
             "WHERE table1.col1 = "
-            "(SELECT 1 FROM table1, table1 AS table1_1 "
+            "(SELECT 1 FROM table1 AS table1_1, table1 "
             "WHERE table1.col1 = table1_1.col1)",
         )
         s = CloningVisitor().traverse(s)
@@ -1290,7 +1290,7 @@ class ClauseTest(fixtures.TestBase, AssertsCompiledSQL):
             s,
             "SELECT table1.col1, table1.col2, table1.col3 FROM table1 "
             "WHERE table1.col1 = "
-            "(SELECT 1 FROM table1, table1 AS table1_1 "
+            "(SELECT 1 FROM table1 AS table1_1, table1 "
             "WHERE table1.col1 = table1_1.col1)",
         )
 
@@ -2645,7 +2645,7 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
         self.assert_compile(
             select_copy,
             "SELECT table1.col1, table1.col2, "
-            "table1.col3 FROM table1, table2",
+            "table1.col3 FROM table2, table1",
         )
 
         self.assert_compile(
diff --git a/test/sql/test_functions.py b/test/sql/test_functions.py
index 693a41c3a..6c00660ff 100644
--- a/test/sql/test_functions.py
+++ b/test/sql/test_functions.py
@@ -1671,11 +1671,11 @@ class TableValuedCompileTest(fixtures.TestBase, AssertsCompiledSQL):
         self.assert_compile(
             stmt,
             "SELECT result_elem[:result_elem_1] AS field "
-            "FROM json_array_elements("
+            'FROM "check" AS _check, json_array_elements('
             "(SELECT check_inside.response[:response_1] AS anon_1 "
             'FROM "check" AS check_inside '
             "WHERE check_inside.id = _check.id)"
-            ') AS result_elem, "check" AS _check '
+            ") AS result_elem "
             "WHERE result_elem[:result_elem_2] = :param_1",
         )
 
diff --git a/test/sql/test_selectable.py b/test/sql/test_selectable.py
index 7fa39825c..d05d7ad8b 100644
--- a/test/sql/test_selectable.py
+++ b/test/sql/test_selectable.py
@@ -816,7 +816,7 @@ class SelectableTest(
             [table1.join(table2)],
             [table1],
         ),
-        ([table1], [table2], [table1, table2], [table1]),
+        ([table1], [table2], [table2, table1], [table1]),
         (
             [table1.c.col1, table2.c.col1],
             [],
@@ -924,8 +924,8 @@ class SelectableTest(
         stmt = select(t1, t2, t3, t4).select_from(j2)
         self.assert_compile(
             stmt,
-            "SELECT t1.a, t2.b, t3.c, t4.d FROM t3, "
-            "t4 JOIN (t1 JOIN t2 ON t1.a = t3.c) ON t4.d = t2.b",
+            "SELECT t1.a, t2.b, t3.c, t4.d FROM "
+            "t4 JOIN (t1 JOIN t2 ON t1.a = t3.c) ON t4.d = t2.b, t3",
         )
 
         stmt = select(t1).select_from(t3).select_from(j2)
diff --git a/test/sql/test_text.py b/test/sql/test_text.py
index 0f645a2d2..6acc97c9c 100644
--- a/test/sql/test_text.py
+++ b/test/sql/test_text.py
@@ -179,8 +179,9 @@ class SelectCompositionTest(fixtures.TestBase, AssertsCompiledSQL):
             select(table1.alias("t"), text("foo.f"))
             .where(text("foo.f = t.id"))
             .select_from(text("(select f from bar where lala=heyhey) foo")),
-            "SELECT t.myid, t.name, t.description, foo.f FROM mytable AS t, "
-            "(select f from bar where lala=heyhey) foo WHERE foo.f = t.id",
+            "SELECT t.myid, t.name, t.description, foo.f FROM "
+            "(select f from bar where lala=heyhey) foo, "
+            "mytable AS t WHERE foo.f = t.id",
         )
 
     def test_expression_element_role(self):