render WITH clause after INSERT for INSERT..SELECT on Oracle, MySQL

Fixed INSERT FROM SELECT with CTEs for the Oracle and MySQL dialects, where
the CTE was being placed above the entire statement as is typical with
other databases, however Oracle and MariaDB 10.2 wants the CTE underneath
the "INSERT" segment. Note that the Oracle and MySQL dialects don't yet
work when a CTE is applied to a subquery inside of an UPDATE or DELETE
statement, as the CTE is still applied to the top rather than inside the
subquery.

Also adds test suite support CTEs against backends.

Change-Id: I8ac337104d5c546dd4f0cd305632ffb56ac8bf90
Fixes: #4275
Fixes: #4230

1 files changed, 43 insertions, 0 deletions

diff --git a/test/sql/test_insert.py b/test/sql/test_insert.py
index 6d41a4dca..6ea5b4f37 100644
--- a/test/sql/test_insert.py
+++ b/test/sql/test_insert.py
@@ -278,6 +278,31 @@ class InsertTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL):
             checkparams={"name_1": "bar"}
         )
 
+    def test_insert_from_select_cte_follows_insert_one(self):
+        dialect = default.DefaultDialect()
+        dialect.cte_follows_insert = True
+
+        table1 = self.tables.mytable
+
+        cte = select([table1.c.name]).where(table1.c.name == 'bar').cte()
+
+        sel = select([table1.c.myid, table1.c.name]).where(
+            table1.c.name == cte.c.name)
+
+        ins = self.tables.myothertable.insert().\
+            from_select(("otherid", "othername"), sel)
+        self.assert_compile(
+            ins,
+            "INSERT INTO myothertable (otherid, othername) "
+            "WITH anon_1 AS "
+            "(SELECT mytable.name AS name FROM mytable "
+            "WHERE mytable.name = :name_1) "
+            "SELECT mytable.myid, mytable.name FROM mytable, anon_1 "
+            "WHERE mytable.name = anon_1.name",
+            checkparams={"name_1": "bar"},
+            dialect=dialect
+        )
+
     def test_insert_from_select_cte_two(self):
         table1 = self.tables.mytable
 
@@ -293,6 +318,24 @@ class InsertTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL):
             "SELECT c.myid, c.name, c.description FROM c"
         )
 
+    def test_insert_from_select_cte_follows_insert_two(self):
+        dialect = default.DefaultDialect()
+        dialect.cte_follows_insert = True
+        table1 = self.tables.mytable
+
+        cte = table1.select().cte("c")
+        stmt = cte.select()
+        ins = table1.insert().from_select(table1.c, stmt)
+
+        self.assert_compile(
+            ins,
+            "INSERT INTO mytable (myid, name, description) "
+            "WITH c AS (SELECT mytable.myid AS myid, mytable.name AS name, "
+            "mytable.description AS description FROM mytable) "
+            "SELECT c.myid, c.name, c.description FROM c",
+            dialect=dialect
+        )
+
     def test_insert_from_select_select_alt_ordering(self):
         table1 = self.tables.mytable
         sel = select([table1.c.name, table1.c.myid]).where(